Show TOC

Background documentationSET Statement (set_statement)

 

The SET statement (set_statement) changes the properties of a database session.

Structure

Syntax Syntax

  1. <set_statement> ::=
  SET ROLE ALL [EXCEPT <role_name>,...]
| SET ROLE NONE
| SET ROLE <role_name>,... [IDENTIFIED BY <password>]
| SET ISOLATION LEVEL <unsigned_integer>
| SET CURRENT_SCHEMA = <schema_name>
End of the code.
Examples

SQL Tutorial, Roles, Schemas

SET ROLE

DEFAULT ROLE in the ALTER USER statement or ALTER USERGROUP statement specifies which of the roles assigned to the current database user or user group are active in the user session or group member session. If a role is active, the current database user has all the privileges that are included in the role.

If the ALTER USER statement or ALTER USERGROUP statement assigns a role to the current database user that is activated automatically when a session is opened, then this role is deactivated when the SET statement is executed if it has not been identified by the SET ROLE specification in the SET statement.

  • ALL: All roles assigned to the current database user are active. EXCEPT can be used to exclude specified roles from activation.

  • NONE: None of the roles is active.

  • Role name specified: The roles specified here must exist and be assigned to the current database user. If a password exists for the role, it must be defined in the SET statement except for the owner of the role. The role identified with role name is activated.

SET ISOLATION LEVEL

Specifying an isolation level changes the lock behavior for all subsequent SQL statements of the current database session. The isolation level is set using an integer without a plus/minus sign after the keywords ISOLATION LEVEL. The following values are permitted: 0, 1, 2, 3, 10, 15, 20 and 30.

SET CURRENT_SCHEMA

Specifying a schema name defines the specified schema as the current schema for the database session. This means that if a schema name is not specified in subsequent SQL statements, this schema is assumed implicitly. The schema name must identify an existing schema.

More Information

Concepts of the Database System, Isolation Level