CREATE USER Statement (create_user_statement) 

The CREATE USER statement (create_user_statement) defines a database user. The existence and properties of the database user are recorded in the database catalog in the form of metadata.

Syntax

<create_user_statement> ::=
  CREATE USER <user_name> PASSWORD <password>
    [<user_mode>]
    [TIMEOUT <unsigned_integer>]
    [COSTWARNING <unsigned_integer>]
    [COSTLIMIT <unsigned_integer>]
    [[NOT] EXCLUSIVE]
    [DEFAULTCODE <ASCII_or_UNICODE>]
    [<connect_mode>]
| CREATE USER <user_name> PASSWORD <password> LIKE <source_user>
| CREATE USER <user_name> PASSWORD <password> USERGROUP <usergroup_name>

<user_mode>        ::= DBA | RESOURCE | STANDARD
<ASCII_or_UNICODE> ::= ASCII | UNICODE
<connect_mode>     ::= ENABLE CONNECT | DISABLE CONNECT
<source_user>      ::= <user_name>

Examples

SQL Tutorial, Database Users and Their Privileges

Explanation

The current user must be a database administrator (DBA user). He or she becomes the owner of the created user.

The specified user name must not be identical to the name of an existing user, user group, role or schema.

The password must be specified when a database session is started. It ensures that only authorized users can access the database system.

Any unsigned_integer specified must be greater than 0.

There is no restriction to the amount of disk space available to the user for storing his or her private and temporary tables (in the context of sizes specified for the data volumes during installation).

Database User Class

When a database user is created, the database user class DBA, RESOURCE, or STANDARD of the defined user is determined using user_mode. The database user class specifies the operations that the defined user can execute.

If no database user class is specified, the STANDARD class is assumed implicitly.

The database user classes are arranged in hierarchies as follows:

●     The RESOURCE user class encompasses all the rights of STANDARD users.

●     The DBA user class encompasses all the rights of RESOURCE users.

●     The database system administrator (SYSDBA user) can generate database users of database user classes DBA, RESOURCE and STANDARD. He or she has owner rights over all users. The database system administrator has the same functionand rights as a DBA user.

TIMEOUT

The timeout value is specified in seconds and must be between 30 and 32400.

Only the database system administrator (SYSDBA user) can define users with the timeout value 0.

COSTWARNING/COSTLIMIT

COSTWARNING and COSTLIMIT limit costs by preventing users from executing query statements or insert statements in the form of INSERT...SELECT... beyond a specified degree of complexity.

Before these SQL statements are executed, the costs expected to result from this statement are estimated. This SELECT costs estimate can be output with the EXPLAIN statement. In interactive mode, the estimated SELECT cost value is compared with the COSTWARNING and COSTLIMIT values specified for the user.

The COSTWARNING and COSTLIMIT values are ignored with query statements or insert statements of the form INSERT...SELECT... that are embedded in a programming language.

●     COSTWARNING: specifies the estimated SELECT cost value beyond which the user receives a warning. In this case, the user is asked whether he or she really wants to execute the SQL statement.

●     COSTLIMIT: specifies the estimated SELECT cost value beyond which the SQL statement is not executed.

The COSTLIMIT value must be greater than the COSTWARNING value.

EXCLUSIVE

If the EXCLUSIVE condition is not specified, NOT EXCLUSIVE is assumed implicitly.

●     EXCLUSIVE: prevents the user from opening two different database sessions simultaneously.

●     NOT EXCLUSIVE: Allows the user to open several database sessions simultaneously.

DEFAULTCODE ASCII, DEFAULTCODE UNICODE

The value of the special database parameter DEFAULT_CODE is overridden with the code attribute specified in DEFAULTCODE for objects of the specified user.

LIKE

The current user must have owner authorization over the source_user .

If the source user is not a member of a user group, the new user receives the same user class and values for PERMLIMIT, TEMPLIMIT, TIMEOUT, COSTWARNING, COSTLIMIT, and EXCLUSIVE as the source user. The new user receives all privileges granted to the source user by other users.

If the source user is a member of a user group, a new member is created in this user group with the new user name.

USERGROUP

The user issuing the SQL statement must be the owner of the specified user group. The new user then becomes a member of this user group.

See also:

CREATE USERGROUP Statement (create_usergroup_statement)

ENABLE CONNECT, DISABLE CONNECT

You use connect_mode to define whether a user can open database sessions.

If CONNECT mode is not specified, ENABLE CONNECT is implicitly assumed. ENABLE and DISABLE cannot both be specified at the same time.

●     ENABLE CONNECT: The user can open database sessions.

●     DISABLE CONNECT: The user cannot open database sessions.

See also:

Concepts of the Database System, Authentication