Online Tech Support

This Online Tech Support page is focusing on Oracle user creation. To create user in Oracle database the simple syntax is:

CREATE USER <username> IDENTIFIED BY <password>;

This command will create a new user and will apply the current database configuration setting on it. The first example will create user “john” with password “john1234“.

CREATE USER john IDENTIFIED BY john1234;

While creating the users the best practice would be to let users to change their passwords on first logon and before getting access to the database. To apply this condition the additional command is PASSWORD EXPIRE. Take a look at the example below where the Oracle create user command will be executed with additional PASSWORD EXPIRE keyword:

CREATE USER john IDENTIFIED BY john1234 PASSWORD EXPIRE;

In addition, you can set to the user different Oracle tablespaces and initial quota that would be other than it is set by default. On this following example we are using the same username john and we will assign to it Oracle data tablespace JOHN_DATA and temporary tablespace JOHN_TEMP and his initial quotas will be set on 15 Mb and 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;

To create new users there is an option to use Oracle Profiles using them you can apply the same restrictions on many user. For example the restrictions could be like maximum amount of open sessions or connection times or giving grants commonly using the permissions profile.

The last part of this page we are explaining a bit a second type of user that you can create in Oracle. Those users are meant to be for external programs or tools that will only be used to connect to the database. The advantage is that these users need to be identified only first time by the external tool. When somebody is using database only via the same tool then the created user will get access only through the same program. This way create user cannot be used by any possible source and this gives a bit more security.

The next example will create Oracle user for external type of connection only.

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

See Also:
Online Tech Support Home