Name

CREATE USER

Synopsis

CREATE USER username
[IDENTIFIED (BY password | EXTERNALLY |
GLOBALLY} ]
   [DEFAULT TABLESPACE tablespace_name[
   [TEMPORARY TABLESPACE tablespace_name[
[QUOTA [integer[K | M] | UNLIMITED] ON
tablespace_name[
   [PROFILE profile_name]
   [DEFAULT ROLE {
role_name[,role_name
...] |
ALL [EXCEPT
role_name[,role_name
...] ] |
      NONE
      }
   [PASSWORD EXPIRE]
   [ACCOUNT {LOCK | UNLOCK}

Creates a new database user (username); assigns security and storage properties.

Keywords

IDENTIFIED BY

Specifies that the password indicated must be provided when connecting as the user enabling the role.

IDENTIFIED EXTERNALLY

Specifies that the operating system validates the user enabling the role.

IDENTIFIED GLOBALLY

Specifies that the Oracle Security Service verifies the user enabling the role.

DEFAULT TABLESPACE

Changes the name of the tablespace that will be used by default when this user creates a database object.

TEMPORARY TABLESPACE

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.

QUOTA

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.

PROFILE

Changes the user’s profile to profile_name, which subjects the user to the limits specified in that profile.

DEFAULT ROLE

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.

PASSWORD EXPIRE

Causes the userid’s password to be pre-expired. It must be reset before the user can connect to the database.

ACCOUNT LOCK

Causes the account to be created in a locked mode and disabled. The account must be unlocked with the ALTER USER ... ACCOUNT UNLOCK command.

ACCOUNT UNLOCK

Causes the account to be created in an unlocked mode. This is the default.

Notes

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.

Examples

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
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset