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>] [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>
SQL Tutorial, Database Users and Their Privileges
The current database user must be a database administrator (DBA user). He or she becomes the owner of the created database user. The specified user name must not be identical to the name of an existing database user of a user group, role or schema.
The password must be specified when a database session is started. It ensures that only authorized database 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 database user for storing his or her private and temporary tables (in the context of sizes specified for the data volumes during installation).
When a database user is created, the database user class DBA, RESOURCE or STANDARD is defined for this database user using user_mode. The database user class specifies the operations that the defined database user can execute. If no database user group class is specified when defining a database user, the STANDARD class is assumed implicitly.
Name |
Properties |
---|---|
DBA |
Database administrators Creation of RESOURCE and STANDARD database users Definition of database objects Granting of all or part of privileges for these database objects to other database users |
RESOURCE |
Definition of data and database procedures Granting of privileges for these database objects to other database users |
STANDARD |
Access to data and database procedures that were defined by other database users and for which the user has been granted privileges Definition of view tables, synonyms and temporary tables |
The database user classes are arranged in hierarchies as follows:
The database user class RESOURCE encompasses all the rights of a STANDARD database user.
The database user class DBA encompasses all the rights of a RESOURCE database user.
The database system administrator can generate database users of the database user classes DBA, RESOURCE and STANDARD. He or she has owner rights over all database users. The database system administrator has the same function and rights as a DBA user.
The timeout value is specified in seconds and must be between 30 and 32400. Only the database system administrator can define database users with the timeout value 0.
This specification limits costs by preventing specified database users from executing QUERY statements or INSERT statements in the form of INSERT...SELECT... beyond a specified degree of complexity, which would otherwise be cost-intensive. COSTLIMIT specifies the estimated SELECT cost value beyond which the SQL statement is not executed.
Before these SQL statements are executed, the costs expected to result from this statement are estimated. This SELECT cost estimate can be output using the EXPLAIN statement. In interactive mode, the estimated SELECT cost value is compared with the COSTLIMIT value specified for the database user. The COSTLIMIT value is ignored with QUERY statements or INSERT statements of the form INSERT...SELECT... that are embedded in a programming language.
If the EXCLUSIVE syntax rule is not specified, NOT EXCLUSIVE is assumed implicitly.
EXCLUSIVE: This prevents the specified database user from opening two different database sessions simultaneously.
NOT EXCLUSIVE: Allows the database user to open several database sessions simultaneously.
The value of the special database parameter DefaultCodePage is overridden with the code attribute ASCII or UNICODE specified in syntax rule DEFAULTCODE <ascii_or_unicode> for objects of the specified database user.
You can use connect_mode to define whether a database user can open database sessions. For CREATE USERGROUP statement this is only valid however if CONNECT mode (connect_mode) is not specified in the CREATE USER statement.
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 database user (members of the database user group) can open database sessions.
DISABLE CONNECT: The database user (members of the database user group) cannot open database sessions. ALTER USER|USERGROUP statement: The database sessions of the specified database user (members of the specified database user group) are terminated. You can enable the individual members of a database user group to open database sessions again using the ALTER USER statement.
The current user must have owner authorization over the source_user.
If the source database user is not a member of a user group, the new database user receives the same user class and values for TIMEOUT, COSTWARNING, COSTLIMIT and EXCLUSIVE as the source database user. The new database user receives all privileges granted to the source database user by other database users.
If the source database user is a member of a user group, a new member is created in this user group with the new database user name.
The database user issuing the SQL statement must be the owner of the specified user group. The new database user then becomes a member of this user group. For further explanations, see CREATE USERGROUP Statement (create_usergroup_statement).
ALTER USER Statement (alter_user_statement)
Database Administration, Managing Users, Database Users