Using CREATE USER statement in Oracle

This article is written about how to use the Oracle CREATE USER announcement with syntax and examples.

Description

The CREATE USER announcement creates a database account that allows you to log into the Oracle database.

Syntax

The syntax for the CREATE USER statement in Oracle/PLSQL is:

CREATE USER user_name 
  IDENTIFIED { BY password
             | EXTERNALLY [ AS 'certificate_DN' ]
             | GLOBALLY [ AS '[ directory_DN ]' ]
             }
  [ DEFAULT TABLESPACE tablespace
  | TEMPORARY TABLESPACE
       { tablespace | tablespace_group }
  | QUOTA integer [ K | M | G | T | P | E ]
        | UNLIMITED }
        ON tablespace
    [ QUOTA integer [ K | M | G | T | P | E ]
        | UNLIMITED }
            ON tablespace
    ]
  | PROFILE profile_name
  | PASSWORD EXPIRE
  | ACCOUNT { LOCK | UNLOCK }
     [ DEFAULT TABLESPACE tablespace
     | TEMPORARY TABLESPACE
         { tablespace | tablespace_group }
     | QUOTA integer [ K | M | G | T | P | E ]
           | UNLIMITED }
           ON tablespace
       [ QUOTA integer [ K | M | G | T | P | E ]
           | UNLIMITED }
           ON tablespace
        ]
     | PROFILE profile
     | PASSWORD EXPIRE
     | ACCOUNT { LOCK | UNLOCK } ]
     ] ;

Parameters or Arguments

user_name The identify of the database account that you want to create. PROFILE profile_name Optional. It is the name of the profile that you desire to assign to the user account to restriction the quantity of database sources assigned to the person account. If you miss this option, the DEFAULT profile is assigned to the user. PASSWORD EXPIRE Optional. If this choice is set, then the password need to be reset before the user can log into the Oracle database. ACCOUNT LOCK Optional. It disables access to the user account. ACCOUNT UNLOCK Optional. It allows get admission to to the consumer account.

Example

If you desired to execute a easy CREATE USER announcement that creates a new consumer and assigns a password, you ought to do the following:

For example:

CREATE USER smithj
  IDENTIFIED BY pwd4smithj
  DEFAULT TABLESPACE tbs_perm_01
  TEMPORARY TABLESPACE tbs_temp_01
  QUOTA 20M on tbs_perm_01;

This CREATE USER announcement would create a new consumer referred to as smithj in the Oracle database whose password is pwd4smithj, the default tablespace would be tbs_perm_01 with a quota of 20MB, and the transient tablespace would be tbs_temp_01.

If you wanted to make positive that the consumer changed their password before logging into the database, you could add the PASSWORD EXPIRE choice as follows:

CREATE USER smithj
  IDENTIFIED BY pwd4smithj
  DEFAULT TABLESPACE tbs_perm_01
  TEMPORARY TABLESPACE tbs_temp_01
  QUOTA 20M on tbs_perm_01
  PASSWORD EXPIRE;

External Database User

To create an External Database user, you ought to execute the following CREATE USER statement:

CREATE USER external_user1
  IDENTIFIED EXTERNALLY
  DEFAULT TABLESPACE tbs_perm_01
  QUOTA 5M on tbs_perm_01
  PROFILE external_user_profile;

This CREATE USER assertion would create an External Database person known as external_user1 that has a default tablespace of tbs_perm_01 with a quote of 5MB, and is restricted with the aid of the database resources assigned to external_user_profile.

To create an External Database user that is solely handy by way of an working gadget account, you may want to run the following CREATE USER statement:

CREATE USER ops$external_user1
  IDENTIFIED EXTERNALLY
  DEFAULT TABLESPACE tbs_perm_01
  QUOTA 5M on tbs_perm_01
  PROFILE external_user_profile;

Note that the solely distinction between this CREATE USER statement and the preceding is the ops$ in the front of the user_name.

Global Database User

To create a Global Database user, you should execute the following CREATE USER statement:

CREATE USER global_user1
  IDENTIFIED GLOBALLY AS 'CN=manager, OU=division, O=oracle, C=US'
  DEFAULT TABLESPACE tbs_perm_01
  QUOTA 10M on tbs_perm_01;

This CREATE USER announcement would create a Global Database consumer known as global_user1 that has a default tablespace of tbs_perm_01 with a quote of 10M.