Create New User In Oracle

In my previous post, I mentioned how to change the password of the default user that is created during the installation of the Oracle database. Now here I will put the simple but mention-worthy PL/SQL programming to create a new user in Oracle.

Steps of Creating New User in Oracle

1. Create a new user providing user name and password.

Connect to the Oracle SQLPLUSW. Run the following code:

CREATE USERNAME IDENTIFIED BY PASSWORD;

Example:

CREATE USER sams
IDENTIFIED BY myoracle;

While creating the user, you can define the default tablespace and default temporary space also. The combined syntax will then be like below:


CREATE USER sams
IDENTIFIED BY myoracle
DEFAULT TABLESACE system
TEMPORARY TABLESPACE temp;


2. Grant the user with essential privileges. One should consider well before granting access to the newly created user. The privileges can be assigned in many layers. For example you can assign the user with the DBA administrative privilege. On the other hand you can also limit the general user with the SELECT, INSERT, UPDATE or DELETE privileges to certain table(s).

The syntax follows:

GRANT CONNECT, RESOURCE to sams

GRANT DBA to sams

GRANT (SELECT INSERT UPDATE DELETE) ON TableName to UserName;

Example:
GRANT SELECT ON Employees to sams;

Happy Oracling!