When a user logs on to Oracle Database, the database enables all privileges granted explicitly to the user and all privileges in the user's default roles. During the session, the user or an application can use the
ROLE statement any number of times to enable or disable the roles currently enabled for the session.
Note:For most roles, you cannot enable or disable a role unless it was granted to you either directly or through other roles. However, a secure application role can be granted and enabled by its associated PL/SQL package. See the
ROLEsemantics for USING package and Oracle Database Security Guide for information about secure application roles.
Specify a role to be enabled for the current session. Any roles not listed and not already enabled are disabled for the current session.
Roles listed in the
EXCEPT clause must be roles granted directly to you. They cannot be roles granted to you through other roles.
If you list a role in the
EXCEPT clause that has been granted to you both directly and through another role, then the role remains enabled by virtue of the role to which it has been granted.
NONE to disable all roles for the current session, including the
SET ROLE dw_manager IDENTIFIED BY warehouse;
To enable all roles granted to you for the current session, issue the following statement:
SET ROLE ALL;
To enable all roles granted to you except
dw_manager, issue the following statement:
SET ROLE ALL EXCEPT dw_manager;
To disable all roles granted to you for the current session, issue the following statement:
SET ROLE NONE;