Purpose
You can use this statement to enable or disable a role granted to the current session.
Syntax
SET ROLE
{ role_name [ IDENTIFIED BY password ][, role_name [ IDENTIFIED BY password ] ]...
| ALL [ EXCEPT role_name [, role_name ]... ]
| NONE
} ;
Parameters
| Parameter | Description |
|---|---|
| role_name | The name of the role to be enabled or disabled for the current session. |
| IDENTIFIED BY password | The password of the role. If the role has a password, you must specify this parameter. Otherwise, the role cannot be enabled or disabled. |
| ALL | Enables all roles granted to the current session. The EXCEPT clause specifies the roles excluded. This clause is optional. If you are directly granted any role with a password, this clause cannot be specified. Otherwise, an error is reported. |
| NONE | Disables all roles of the current session. |
Examples
Enable
role1identified by the password******for the current session.obclient> SET ROLE role1 IDENTIFIED BY ******; Query OK, 0 rows affectedEnable all roles granted to the current session. An error is reported if any granted role has a password.
obclient> SET ROLE ALL; ORA-01979: missing or invalid password for role 'ROLE2'Enable all roles granted to the current session except
role2.obclient> SET ROLE ALL EXCEPT role2; Query OK, 0 rows affectedDisable all roles granted to the current session.
obclient> SET ROLE NONE; Query OK, 0 rows affected