Purpose
You can use this statement to activate roles granted to the logged-on user in the current session.
Note
The SET ROLE statement takes effect only on the current session. Subsequent sessions are not affected.
Syntax
SET ROLE {
DEFAULT
| NONE
| ALL
| ALL EXCEPT role_name [, role_name ...]
| role_name [, role_name ...]
};
Parameters
| Parameter | Description |
|---|---|
| DEFAULT | Specifies to retain the default role settings. The system activates roles based on the value of the activate_all_roles_on_login variable and the settings configured by the SET DEFAULT ROLE or ALTER USER statement.
|
| NONE | Specifies to deactivate all roles granted to a user. |
| ALL | Specifies to activate all roles granted to a user. |
| ALL EXCEPT | Specifies to activate all roles granted to a user except for the specified roles. |
| role_name | The name of the role to be activated. Separate multiple roles with commas (,). |
Examples
Activate all roles granted to the logged-on user, except for
role001, in the current session.obclient [(none)]> SET ROLE ALL EXCEPT role001;Activate the
role001androle002roles for the logged-on user in the current session.obclient [(none)]> SET ROLE role001, role002;