How To Create An User In Oracle

This online tutorial is based on examples to make it easier to follow. To create user in Oracle the most basic syntax is

CREATE USER <username> IDENTIFIED BY <password>;

This following command will create a new user into Oracle database and will apply the current default database configuration setting on the new user. Please take a look at the following command that will create a new user named “john” and with password “john1234“.

CREATE USER john IDENTIFIED BY john1234;

The best practise is to let Oracle Create User command to force the new user change his/her password before getting into Oracle database for the very first logging. To do so, the additional keyword is PASSWORD EXPIRE. In the following Oracle DBA example we are showing how looks the command Oracle Create User with keyword PASSWORD EXPIRE:

CREATE USER john IDENTIFIED BY john1234 PASSWORD EXPIRE;

Also you can set to command create user a different Oracle tablespace and quota than it is set by default. The following Oracle DBA example of Create User in Oracle will assign user “john” to Oracle data tablespace JOHN_DATA and Oracle temporary tablespace JOHN_TEMP and his JOHN_DATA quota will be 15 Mb and quota for JOHN_TEMP is 10 Mb.

CREATE USER john IDENTIFIED BY john1234
    DEFAULT TABLESPACE john_data
      QUOTA 15M ON john_data
  TEMPORARY TABLESPACE john_temp
      QUOTA 10M ON john_temp
   PASSWORD EXPIRE;

In addition, you can set to the Oracle Create User command a common Oracle profile where you have set all necessary restrictions for example like maximum amount of open sessions or connection times or you can set Oracle Grant commands and give commonly used permissions to the profile.

The second type of Create User in Oracle command is for external programs or tools that will only to be used to connect to the database. The advantage is that this type of users will need to be identified first times by the external tool or a program that will used this user to connect to a database. And later one the same user will be allowed get access to the Oracle database only via the same tool or a program. This way the created user cannot be used to log on database from any source or as the normal user. This way created user will give a bit more security to the system. Take a look at the next Oracle DBA example that will demonstrate how to create a new user as the external type of connection only.

CREATE USER john IDENTIFIED EXTERNALLY
    DEFAULT TABLESPACE john_data
      QUOTA 15M ON john_data;

See Also:
Oracle Home