Revoking User Privileges and Roles

• Revoking System Privileges and Roles

• Revoking Object Privileges and Roles

Revoking System Privileges and Roles

Any user with the ADMIN OPTION for a system privilege or role can revoke the privilege or role from any other database user or role The grantor does not have to be the user that originally granted the privilege or role. Also, users with the GRANT ANY ROLE can revoke any role.

The following statement revokes the CREATE TABLE system privilege and the ACCTS_REC role from TSMITH:

REVOKE create table, accts_rec FROM tsmith;

Note: The ADMIN OPTION for a system privilege or role cannot be selectively revoked. The privilege or role must be revoked and then the privilege or role re-granted without the ADMIN OPTION.

Revoking Object Privileges and Roles

To revoke an object privilege, the revoker must be the original grantor of the object privilege being revoked.

For example, assuming you are the original grantor, to revoke the SELECT and INSERT privileges on the EMP table from the users JFEE and TSMITH, you would issue the following statement:

REVOKE select, insert ON emp

   FROM jfee, tsmith;

The following statement revokes all privileges (which were originally granted to the role HUMAN_RESOURCE) from the table DEPT:

REVOKE ALL ON dept FROM human_resources;

Note: This statement above would only revoke the privileges that the grantor authorized, not the grants made by other users. The GRANT OPTION for an object privilege cannot be selectively revoked. The object privilege must be revoked and then re-granted without the GRANT OPTION. Users cannot revoke object privileges from themselves.

Revoking Column Selective Object Privileges

For example, assume that role HUMAN_RESOURCES has been granted the UPDATE privilege on the DEPTNO and DNAME columns of the table DEPT. To revoke the UPDATE privilege on just the DEPTNO column, you would issue the following two statements:

REVOKE UPDATE ON dept FROM human_resources;

GRANT UPDATE (dname) ON dept TO human_resources;

The REVOKE statement revokes UPDATE privilege on all columns of the DEPT table from the role HUMAN_RESOURCES. The GRANT statement re-grants UPDATE privilege on the DNAME column to the role HUMAN_RESOURCES.

Revoking the REFERENCES Object Privilege

REVOKE REFERENCES ON dept FROM jward CASCADE CONSTRAINTS;

Any foreign key constraints currently defined that use the revoked REFERENCES privilege are dropped when the CASCADE CONSTRAINTS options is specified.

Effects of Revoking Privileges

System Privileges

1. The security administrator grants the CREATE TABLE system privilege to JFEE with the ADMIN OPTION.

2. JFEE creates a table.

3. JFEE grants the CREATE TABLE system privilege to TSMITH.

4. TSMITH creates a table.

5. The security administrator revokes the CREATE TABLE system privilege from JFEE.

6. JFEE's table continues to exist. TSMITH still has the table and the CREATE TABLE system privilege.

Object Privileges

• Object definitions that depend on a DML object privilege can be affected if the DML object privilege is revoked. For example, assume the procedure body of the TEST procedure includes a SQL statement that queries data from the EMP table. If the SELECT privilege on the EMP table is revoked from the owner of the TEST procedure, the procedure can no longer be executed successfully.

• Object definitions that require the ALTER and INDEX DDL object privileges are not affected if the ALTER or INDEX object privilege is revoked. For example, if the INDEX privilege is revoked from a user that created an index on someone else's table, the index continues to exist after the privilege is revoked.

• When a REFERENCES privilege for a table is revoked from a user, any foreign key integrity constraints defined by the user that require the dropped REFERENCES privilege are automatically dropped. For example, assume that the user JWARD is granted the REFERENCES privilege for the DEPTNO column of the DEPT table and creates a foreign key on the DEPTNO column in the EMP table that references the DEPTNO column. If the REFERENCES privilege on the DEPTNO column of the DEPT table is revoked, the foreign key constraint on the DEPTNO column of the EMP table is dropped in the same operation.

• The object privilege grants propagated using the GRANT OPTION are revoked if a grantor's object privilege is revoked. For example, assume that USER1 is granted the SELECT object privilege with the GRANT OPTION, and grants the SELECT privilege on EMP to USER2. Subsequently, the SELECT privilege is revoked from USER1. This revoke is cascaded to USER2 as well. Any objects that depended on USER1's and USER2's revoked SELECT privilege can also be affected, as described in previous bullet items.

Granting to and Revoking from the User Group PUBLIC

Security administrators and database users should only grant a privilege or role to PUBLIC if every database user requires the privilege or role. This recommendation reinforces the general rule that at any given time, each database user should only have the privileges required to accomplish the group's current tasks successfully.

Revoking a privilege from PUBLIC can cause significant cascading effects. If any privilege related to a DML operation is revoked from PUBLIC (for example, SELECT ANY TABLE, UPDATE ON emp), all procedures in the database, including functions and packages, must be reauthorized before they can be used again. Therefore, exercise caution when granting DML-related privileges to PUBLIC.

See Also: For more information about object dependencies, see "Managing Object Dependencies" .

When Do Grants and Revokes Take Effect?

• All grants/revokes of system and object privileges to anything (users, roles, and PUBLIC) are immediately observed.

• All grants/revokes of roles to anything (users, other roles, PUBLIC) are only observed when a current user session issues a SET ROLE statement to re-enable the role after the grant/revoke, or when a new user session is created after the grant/revoke.