CREATE USER
CREATE USERusername
[IDENTIFIED (BYpassword
| EXTERNALLY | GLOBALLY} ] [DEFAULT TABLESPACEtablespace_name
[ [TEMPORARY TABLESPACEtablespace_name
[ [QUOTA [integer
[K | M] | UNLIMITED] ONtablespace_name
[ [PROFILEprofile_name
] [DEFAULT ROLE {role_name
[,role_name
...] | ALL [EXCEPTrole_name
[,role_name
...] ] | NONE } [PASSWORD EXPIRE] [ACCOUNT {LOCK | UNLOCK}
Creates a new database user (username); assigns security and storage properties.
Specifies that the password indicated must be provided when connecting as the user enabling the role.
Specifies that the operating system validates the user enabling the role.
Specifies that the Oracle Security Service verifies the user enabling the role.
Changes the name of the tablespace that will be used by default when this user creates a database object.
Changes the name of the tablespace that will be used for the creation of temporary segments for operations, like sorting, that require more memory than is available.
Specifies the amount of space that this user is permitted to use for object storage in the specified tablespace. UNLIMITED means there is no storage limit, subject to the total size of the tablespace.
Changes the user’s profile to profile_name, which subjects the user to the limits specified in that profile.
Specifies the roles (role_name...) assigned to the user as default roles. ALL means that all roles granted to the user will be default except those specified in the EXCEPT clause; NONE means that none of the roles granted to the user will be default roles.
Causes the userid’s password to be pre-expired. It must be reset before the user can connect to the database.
Causes the account to be created in a locked mode and disabled. The account must be unlocked with the ALTER USER ... ACCOUNT UNLOCK command.
Causes the account to be created in an unlocked mode. This is the default.
You must have the CREATE USER privilege to issue this command. If the user is authenticated EXTERNALLY, the username must be prefixed by the current INIT.ORA parameter OS_AUTHENT_PREFIX and must match an operating system account name.
The following example creates a new user scott:
CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE users TEMPORARY TABLESPACE TEMP QUOTA 500K ON users
The following example creates the same user, but the account is authenticated by the operating system:
CREATE USER ops$scott IDENTIFIED EXTERNALLY DEFAULT TABLESPACE users TEMPORARY TABLESPACE TEMP QUOTA 500K ON users