Oracle CREATE USER

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

Syntax of the CREATE USER operator in Oracle/PLSQL

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 and arguments

• user_name – The name of the database account you want to create.
• PROFILE profile_name – Optional. This is the name of the profile you want to assign to the user account to limit the amount of database resources assigned to the user account. If you omit this option, the profile is assigned to the user by default.
• PASSWORD EXPIRE – Optional. If this option is set, the password must 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 turns on access to the user account.

Example:
If you want to run a simple CREATE USER operator that will create a new user and assign a password, you can do the following:

CREATE USER kuzma
IDENTIFIED BY zdd32metfh
DEFAULT TABLESPACE tbs_new_01
TEMPORARY TABLESPACE tbs_temp_01
QUOTA 20M on tbs_perm_01;
In this example, the CREATE USER operator will create a new user named kuzma in the Oracle database whose password is zdd32metfh, the default table space is tbs_new_01 with a 20M quota, and the temporary table space is tbs_temp_01.

If you want to make sure that the user has changed his password before logging on to the database, you can add the PASSWORD EXPIRE option – it looks like this:

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 can perform the following CREATE USER operator:

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

In this example, the oracle CREATE USER operator will create an external database user named external_user1, the default table space is tbs_perm_01 with a 5M quota, and limited database resources assigned to external_user_profile.

To create an external database user that is only available with an operating system account, you can run the following sentence CREATE USER:

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

Note that the only difference between this CREATE USER example and the previous one is ops$ before user_name.

Global Database User

To create a global database user, you can run the following CREATE USER operator:

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;

In this example, CREATE USER will create a global database user called global_user1, which by default has a table space of tbs_perm_01 with a 10M quota.

How to create NEW USER account using Create User statement in Oracle database