Oracle7 Server SQL Reference

SET ROLE

Purpose

To enable and disable roles for your current session.

Prerequisites

You also must already have been granted the roles that you name in the SET ROLE statement.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the label of roles granted to you.

Syntax

Keywords and Parameters

role

is a role to be enabled for the current session. Any roles not listed are disabled for the current session.

password is the password for a role. If the role has a password, you must specify the password to enable the role.

ALL EXCEPT

enables all roles granted to you for the current session, except those listed in the EXCEPT clause. Roles listed in the EXCEPT clause must be roles granted directly to you; they cannot be roles granted to you through other roles. You cannot use this option to enable roles with passwords that have been granted directly to you.

If you list a role in the EXCEPT clause that has been granted to you both directly and through another role, the role is still enabled by virtue of your enabling the role to which it has been granted.

NONE

disables all roles for the current session.

Default Privilege Domain

At logon Oracle7 establishes your default privilege domain by enabling your default roles. Your default privilege domain contains all privileges granted explicitly to you and all privileges in the privilege domains of your default roles. You can then perform any operations authorized by the privileges in your default privilege domain.

Changing Your Privilege Domain

During your session, you can change your privilege domain with the SET ROLE command. The SET ROLE command changes the roles currently enabled for your session. You can change your enabled roles any number of times during a session. The number of roles that can be concurrently enabled is limited by the initialization parameter MAX_ENABLED_ROLES .

You can use the SET ROLE command to enable or disable any of the following roles:

roles that have been granted directly to you

roles granted to you through other roles

You cannot use the SET ROLE command to enable roles that you have not been granted either directly or through other roles.

Your current privilege domain is also changed in the following cases:

if you are granted a privilege

if one of your privileges is revoked

if one of your enabled roles is revoked

if the privilege domain of one of your enabled roles is changed

If none of the above conditions occur and you do not issue the SET ROLE command, your default privilege domain remains in effect for the duration of your session. In the last two cases, the change in your privilege domain does not take effect until you logon to Oracle7 again or issue a SET ROLE statement.

You can determine which roles are in your current privilege domain at any time by examining the SESSION_ROLES data dictionary view.

To change your default roles, use the ALTER USER command.

Example I

To enable the role GARDENER identified by the password MARIGOLDS for your current session, issue the following statement:

SET ROLE gardener IDENTIFIED BY marigolds

Example II

To enable all roles granted to you for the current session, issue the following statement:

SET ROLE ALL

Example III

To enable all roles granted to you except BANKER, issue the following statement:

SET ROLE ALL EXCEPT banker

Example IV

To disable all roles granted to you for the current session, issue the following statement:

SET ROLE NONE