Background documentationCREATE USERGROUP Statement (create_usergroup_statement) Locate this document in the navigation structure

 

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

Structure

Syntax Syntax

  1. <create_usergroup_statement> ::
      CREATE USERGROUP <usergroup_name>
        [<usergroup_mode>]
        [TIMEOUT <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
End of the code.
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 database user who created the object. Each database 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 database user for storing his or her private and temporary tables (in the context of sizes specified for the data volumes during installation).

usergroup_mode RESOURCE | STANDARD

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.

Database User Classes

Database User Class

Properties

DBA

Database administrators

Users belonging to this class can:

  • Create RESOURCE and STANDARD database users

  • Create database objects

  • Grant all or part of the privileges for their database objects to other database users

RESOURCE

Users belonging to this class can:

  • Define data

  • Create database procedures

  • Grant privileges for their database objects to other database users

STANDARD

Users belonging to this class can:

  • Access data and database procedures for which they have been granted the necessary privileges

  • Define view tables, synonyms, and temporary tables

TIMEOUT

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.

COSTLIMIT

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.

[NOT] EXCLUSIVE

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.

DEFAULTCODE

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.

ENABLE CONNECT | DISABLE CONNECT

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.