How To CREATE USER In Oracle
  Home
 

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;

online tech support computer help computer technician computer problems oracle create user oracle create user in oracle oracle sql database sql oracle retirement planning retirement

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;

online tech support computer help computer technician computer problems oracle create user oracle create user in oracle oracle sql database sql oracle retirement planning retirement

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;

online tech support computer help computer technician computer problems oracle create user oracle create user in oracle oracle sql database sql oracle retirement planning retirement

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;

online tech support computer help computer technician computer problems oracle create user oracle create user externally create user oracle create user in oracle oracle sql database sql oracle retirement planning retirement


See Also:
Online Tech Support Home

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*