SET Statement (set_statement) (SAP Library

SET Statement (set_statement)

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

Syntax

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

Examples

SQL Tutorial, Structure link Roles

Explanation

SET ROLE

DEFAULT ROLE in the ALTER USER statement or ALTER USERGROUP statement specifies which of the roles assigned to the current user or user group are active in the user session or group member session. If a role is active, the current 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 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 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 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 permissible: 0, 1, 2, 3, 10, 15, 20, and 30.

See also:

Concepts of the Database System, Isolation Level

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.