Creating a New User in Oracle Database 11g Express Edition

This post describes how to create a New User in Oracle Database 11g Express Edition:

Go to cmd and type sqlplus
Then it will ask you for the user-name and password.
Enter user-name: sys
Enter password: vijaya
It will show you the following error:
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

So, just try the another role you've created.
Enter username: vijaya
Enter password: vijaya

Now it will say:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

////////////////////////
Or, if you directly open "Run SQL command line", it will directly go to:
">SQL" instead of "C:\Users\meVeekay>", so you can directly do:
SQL> conn sys as sysdba
and it will ask you for the password. Once you enter your password, it will connect you to the database.
////////////////////////

Now, try sys login as sysdba role:
SQL> conn sys as sysdba
Enter password: vijaya
Connected.
Now, you can create a new user:
SQL> create user vkpandey identified by vijaya;
Once the user is created, you need to give sysdba role to the new user:
SQL> grant sysdba to vkpandey;
Now, it should show you the "Grant succeeded." message.



Now, if you open SqlDeveloper tool and try to create a new database connection with the username and password you just created,
it will display the following error:
User Lacks CREATE SESSION Privilege, Logon Failed
this is because you haven't given the session privilege.
To solve this issue, we can do:
//GRANT CONNECT TO username;
GRANT CONNECT TO vkpandey;

Depending on the Oracle version, however, the CONNECT role has many more privileges than the name suggests.
So good idea is to grant CREATE SESSION instead. So simply do:
SQL> grant create session to vkpandey;
Grant succeeded.

Now you can create a new database connection in Oracle SqlDeveloper Tool:
For example:
Connection name: VKConnection
Username: vkpandey
password: vijaya
Hostname: localhost
port: 1521
SID: xe

Try Test option, it should show you "Status: Success" message.
Now, enter Connect button.

So, in Summary:

Follow the below steps for creating a user in Oracle.
--Connect as System user
CONNECT <USER-NAME>/<PASSWORD>@<DATABASE NAME>;

--Create user query
CREATE USER <USER NAME> IDENTIFIED BY <PASSWORD>;

--Provide roles
GRANT CONNECT,RESOURCE,DBA TO <USER NAME>;

--Provide privileges
GRANT CREATE SESSION, GRANT ANY PRIVILEGE TO <USER NAME>;
GRANT UNLIMITED TABLESPACE TO <USER NAME>;

--Provide access to tables.
GRANT SELECT,UPDATE,INSERT ON <TABLE NAME> TO <USER NAME>;

Creating a New User in Oracle Database 11g Express Edition

Getting Insufficient Privileges when creating tables in Oracle SQL Developer Error??
If you get the following error message when creating a new table from sql developer tool:
java.sql.sqlsyntaxerrorexception ora-01031 insufficient privileges

Run the following command from a privileged user and re-connect with your user:
GRANT RESOURCE to my_user;
//GRANT RESOURCE to vkpandey;