How Grants and Revokes Work with SET ROLE and Default Role Settings

Privilege grants and the SET ROLE statement affect when and how grants and revokes take place.

When Grants and Revokes Take Effect

Depending on the privilege that is granted or revoked, a grant or revoke takes effect at different times.

The grants and revokes take effect as follows:

You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.

How the SET ROLE Statement Affects Grants and Revokes

During a user session, a user or an application can use the SET ROLE statement multiple times to change the roles enabled for the session.

The user must already be granted the roles that are named in the SET ROLE statement.

The following example enables the role clerk, which you have already been granted, and specifies the password.

SET ROLE clerk IDENTIFIED BY password;

Replace password with a password that is secure.

The following example shows how to use SET ROLE to disable all roles.

SET ROLE NONE;

Related Topics

Specifying the Default Role for a User

When a user logs on, Oracle Database enables all privileges granted explicitly to the user and all privileges in the user’s default roles.

  1. Ensure that the user who you want to set the default role for has been directly granted the role with a GRANT statement, or that the role was created by the user with the CREATE ROLE privilege.

  2. Use the ALTER USER statement with the DEFAULT ROLE clause to specify the default roles for the user.

For example, to set the default roles payclerk and pettycash for user jane:

ALTER USER jane DEFAULT ROLE payclerk, pettycash;

You cannot set default roles for a user in the CREATE USER statement. When you first create a user, the default user role setting is ALL, which causes all roles subsequently granted to the user to be default roles. Use the ALTER USER statement to limit the default user roles.

Note: When you create a role (other than a global role or an application role), it is granted implicitly to you, and your set of default roles is updated to include the new role. Be aware that only 148 roles can be enabled for a user session. When aggregate roles, such as the DBA role, are granted to a user, the roles granted to the role are included in the number of roles the user has. For example, if a role has 20 roles granted to it and you grant that role to the user, then the user now has 21 additional roles. Therefore, when you grant new roles to a user, use the DEFAULT ROLE clause of the ALTER USER statement to ensure that not too many roles are specified as that user’s default roles.

Related Topics

The Maximum Number of Roles That a User Can Have Enabled

You can grant a user as many roles as you want, but no more than 148 roles can be enabled for a logged-in user at any given time.

The 148 role maximum includes roles that are granted to other roles, not just top-level roles. Therefore, not all privileges will be available to this user during the user session. As a best practice, restrict the number of roles granted to a user to the minimum roles the user needs.

Related Topics

Configuring Read-Only Users

You can override the privileges and roles that have been granted to a user by making the user a read-only user.

This allows SELECT operations but will not permit CREATE, INSERT, UPDATE, or DELETE.

This feature enables an administrator to block users from using their full set of privileges for as long as the user is set to read-only. For example, a database user who has been granted full privileges to insert, update, and delete data, but then made read-only will be unable to perform INSERT, UPDATE, or DELETE operations until they are altered to be read-write. The read-only restriction overrides privilege grants, including schema or system grants. Read-only restrictions even override the DBA role. If the user tries to perform these types of operations, an ORA-28194: Can perform read operations only error appears.

Use cases for configuring read-only users are as follows:

Read-only users may be appropriate in cases where users normally need only read access to data, but need the ability to elevate to read-write under certain conditions. With a single SQL command, these accounts can change “modes” and gain the ability to perform data updates.

To configure the read-only restriction for a user, you use the CREATE USER or ALTER USER statement. To find the read-only status of a user, you can query the READ_ONLY column of the DBA_USERS or ALL_USERS data dictionary view.

Operation Procedure
Creating a user as read-only CREATE USER *user_name* READ ONLY;
Modifying a user to be read-only ALTER USER *user_name* READ ONLY;
Enabling the user to have read-write access again ALTER USER *user_name* READ WRITE;
Finding the read-only status of a user SELECT USERNAME, READ_ONLY from DBA_USERS WHERE USERNAME = '*user_name*';
Output similar to the following appears. For example, if user PFITCH has read-only access:
USERNAME READ_ONLY ------------ ----------- PFITCH YES

Related Topics