CREATE USERGROUP Statement (create_usergroup_statement) 

The CREATE USERGROUP statement (create_usergroup_statement) defines a user group.

Syntax

<create_usergroup_statement> ::=
  CREATE USERGROUP <usergroup_name>
    [<usergroup_mode>]
    [TIMEOUT <unsigned_integer>]
    [COSTWARNING <unsigned_integer>]
    [COSTLIMIT <unsigned_integer>]
    [[NOT] EXCLUSIVE]
    [DEFAULTCODE <ASCII_or_UNICODE>]
    [<connect_mode>]

<usergroup_mode> ::= RESOURCE | STANDARD
<ASCII_or_UNICODE> ::= ASCII | UNICODE
<connect_mode> ::= ENABLE CONNECT | DISABLE CONNECT

Explanation

The current database user must be a database administrator (DBA user).

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

You can use the CREATE USER statement to define multiple users who are to belong to this user group. All private objects created by members of the user group are identified by the user group name. The owner of a private object is the group, not the user who created the object. Each user can work with any private object of the group, as if this user were the owner of the object. Privileges can only be granted or revoked from the group. A privilege cannot be granted or revoked from a single member of the group.

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 user group is created, the database user class RESOURCE or STANDARD is defined for this user group using  usergroup_mode.

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

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

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 in this user group from executing query statements or insert statements in the form of INSERT...SELECT... beyond a specified degree of complexity and that are therefore cost-intensive.

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 users in this user group 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.

ENABLE CONNECT, DISABLE CONNECT

You use connect_mode to define whether members of the user group defined later will be able to open database sessions. This is only valid however if CONNECT mode is not specified in the CREATE USER statement for the relevant group member.

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

●     ENABLE CONNECT : Members of the user group can open database sessions.

●     DISABLE CONNECT : Members of the user group cannot open database sessions.