Show TOC

ALTER USER Statement (alter_user_statement)Locate this document in the navigation structure

Use

The ALTER USER statement ( alter_user_statement) changes the properties assigned to a database user.

Structure
				
<alter_user_statement>::=
  ALTER USER <user_name>
    [<user_mode>]
    [TIMEOUT <unsigned_integer> | TIMEOUT NULL]
    [COSTLIMIT <unsigned_integer> | COSTLIMIT NULL]
    [DEFAULT ROLE ALL [EXCEPT <role_name>]
| DEFAULT ROLE NONE
| DEFAULT ROLE <role_name>]
    [[NOT] EXCLUSIVE]
    [DEFAULTCODE <ascii_or_unicode>]
    [<connect_mode>]

<user_mode>::=
  DBA 
| RESOURCE 
| STANDARD

<ascii_or_unicode>::=
  ASCII
| UNICODE

<connect_mode>::= 
  ENABLE CONNECT 
| DISABLE CONNECT

Examples

SQL Tutorial, Database Users and Their Privileges, Roles

Explanation

At least one of the optional clauses must be specified. The user name specified must denote a defined database user who is not a member of a user group.

The current database user must have right of ownership for the user for whom properties are to be changed. The specified database user cannot be logged on to the database system when the ALTER USER statement is executed.

user_mode DBA | RESOURCE | STANDARD

You can use  user_mode to specify the user class for the database user. The database user class specifies the operations that the defined database user can execute.

Table 1: Database User Classes (User Mode)

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

  • DBA: This specification means that the database user class DBA is to be assigned to the specified database user. Only the database system administrator can assign this database user class.

  • RESOURCE: This specification means that the database user class RESOURCE is to be assigned to the specified database user. If the database user had previously been assigned the database user class DBA, the right of ownership/authorization for all database users that this user has created is withdrawn for this user. The database system administrator becomes the new owner.

  • STANDARD: This specification means that the current database user class is withdrawn for this database user and that they lose the right to create their own base tables. All existing base tables created by the database user are deleted.

  • No database user class: If a database user class has not been specified, the user class of the database user remains unchanged.

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.

If the NULL value is specified, the value defined previously is canceled.

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.

If the NULL value is specified, the value defined previously is canceled.

DEFAULT ROLE

DEFAULT ROLE defines which of the roles assigned to the database user/user group is activated automatically when a database session is opened.

  • ALL: All roles assigned to the database user/user group are activated when a session is opened. EXCEPT can be used to exclude specified roles from activation.

  • NONE: None of the roles are active when a database session is opened by a database user/member of a database user group.

  • Role name specified: The roles specified here must exist and be assigned to the database user/user group. They are activated automatically when a database session is opened for a database user/member of a database user group.

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.

[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.

The connect mode is displayed in system table USERS in column CONNECTMODE (SINGLE, MULTIPLE, DISABLED).

More Information