Oracle - User Management

CREATE USER Statement

CREATE USER statement is used to create and configure a database user through one can access the Oracle database.

Minimum pre-requisite to issue this command is to have CREATE USER system privilege. When you create a user with the CREATE USER statement, the user's privilege domain will be empty. In order to log on to Oracle Database, a user must have the CREATE SESSION system privilege. You should use GRANT statements to assign privileges to the user.

Syntax:

CREATE USER user IDENTIFIED [ BY password]

                                                               [ EXTERNALLY AS certificate_DN]

                                                               [ GLOBALLY AS directory_DN ]

[ DEFAULT TABLESPACE tablespace_name ]

[ TEMPORARY TABLESPACE tablespace_name / tablespace_group_name ]

[ QUOTA size  / UNLIMITED ON tablespace_name ]

[ PROFILE profile_name ]

[ PASSWORD EXPIRE ]

[ ACCOUNT LOCK / UNLOCK ]

IDENTIFIED Clause

The IDENTIFIED clause is used to specify how user logs in to the Oracle Database.

BY password

The BY password is used to create a local user and indicates that the user must specify password to log on to the database. Passwords are case sensitive. Any subsequent CONNECT string used to connect this user to the database must specify the password using the same case (upper, lower, or mixed) that is used in this CREATE USER statement. Passwords can contain any single-byte, multi-byte, or special characters, or any combination of these, from your database character set.

EXTERNALLY Clause

Use the EXTERNALLY clause, If we want to authenticate the user with an external service. External service can be an operating system or third-party service. It ensures right user login to the right database. This clause only can be used for SSL-authenticated external users only. The certificate_DN is the distinguished name in the user's PKI certificate in the user's wallet.

GLOBALLY Clause

The GLOBALLY clause is used to create a global user. Such a user must be authorized by the enterprise directory service (Oracle Internet Directory).

DEFAULT TABLESPACE Clause

This clause can be used to specify the default tablespace for objects that the user creates. If you skip this clause, then the user's objects are stored in the database default tablespace. If no default tablespace has been specified for the database, then the user's objects are stored in the SYSTEM tablespace.

Under some circumstances this clause cannot be used. Those are a locally managed temporary tablespace, including an undo tablespace, or a dictionary-managed temporary tablespace, as a user's default tablespace.

TEMPORARY TABLESPACE Clause

This clause is used to specify the tablespace or tablespace group for the user's temporary segments. If you skip this clause, then the user's temporary segments are stored in the database default temporary tablespace or, if none has been specified, in the SYSTEM tablespace.

Below are the restrictions to use this clause.

The tablespace must be a temporary tablespace and must have a standard block size.

The tablespace cannot be an undo tablespace or a tablespace with automatic segment-space management.

QUOTA Clause

QUOTA clause is used to specify the maximum amount of space the user can allocate in the tablespace. A CREATE USER statement can have multiple QUOTA clauses for multiple tablespaces. UNLIMITED lets the user allocate space in the tablespace without bound.

PROFILE Clause

This clause is used to specify the profile you want to assign to the user. The profile limits the amount of database resources the user can use. If you omit this clause, then Oracle Database assigns the DEFAULT profile to the user.

PASSWORD EXPIRE Clause

This clause is used to specify PASSWORD EXPIRE if you want the user's password to expire. This setting forces the user or the DBA to change the password before the user can log in to the database.

ACCOUNT Clause

The ACCOUNT LOCK  clause is used to  lock the user's account and disable access. The ACCOUNT UNLOCK  clause is used to  unlock the user's account and enable access to the account.

Example:

CREATE USER shibin

              IDENTIFIED BY itzme

              DEFAULT TABLESPACE sample

              QUOTA 10M ON sample

              TEMPORARY TABLESPACE temp_sample

              QUOTA 5M ON system

              PROFILE sample_user

              PASSWORD EXPIRE;

The user shibin has the following characteristics:

• The password itzme
• Default tablespace sample, with a quota of 10 megabytes
• Temporary tablespace temp_sample
• Access to the tablespace system, with a quota of 5 megabytes
• Limits on database resources defined by the profile sample_user
• An expired password, which must be changed before shibin can log in to the database.

Thanks for reading my Blog !