ALTER USER Statement (alter_user_statement) 

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

Syntax

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

<user_mode> ::= DBA | RESOURCE | STANDARD
<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 user who is not a member of a user group.

The current user must have right of ownership for the user for whom properties are to be changed.

The specified user cannot be logged on to the database system when the ALTER USER statement is executed.

Database user class

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

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

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

·        STANDARD: This specification means that the current database user class is withdrawn for this database user and that he or she loses the right to create his or her own base tables. All base tables created by the user are dropped.

·        No Database User Class: If a database user class has not been specified, the user class of the database user remains unchanged.

NULL

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

DEFAULT ROLE

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

·        ALL: All roles assigned to the user 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 for the user is opened.

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

TIMEOUT, COSTWARNING, COSTLIMIT, [NOT] EXCLUSIVE, DEFAULTCODE, ENABLE | DISABLE CONNECT

You can find an explanation of these options in the description for the CREATE USER statement (create_user_statement).