Revoking Privileges from Users (REVOKE)

Revoking Privileges from Users (REVOKE)
The REVOKE statement is used to take away privileges that were granted with the
GRANT statement. A REVOKE statement can take away all or some of the privileges that
were previously granted to a user. The format of the statement is:
REVOKE [GRANT OPTION FOR] {PrivilegeList | ALL PRIVILEGES}
ON ObjectName
FROM {AuthorizationIdList | PUBLIC} [RESTRICT | CASCADE]
The keyword ALL PRIVILEGES refers to all the privileges granted to a user by the user
revoking the privileges. The optional GRANT OPTION FOR clause allows privileges
passed on via the WITH GRANT OPTION of the GRANT statement to be revoked separately
from the privileges themselves.
The RESTRICT and CASCADE qualifiers operate exactly as in the DROP TABLE
statement (see Section 6.3.3). Since privileges are required to create certain objects, revoking
a privilege can remove the authority that allowed the object to be created (such an
object is said to be abandoned). The REVOKE statement fails if it results in an abandoned
object, such as a view, unless the CASCADE keyword has been specified. If CASCADE
is specified, an appropriate DROP statement is issued for any abandoned views, domains,
constraints, or assertions.
The privileges that were granted to this user by other users are not affected by this REVOKE
statement. Therefore, if another user has granted the user the privilege being revoked, the
other user’s grant still allows the user to access the table. For example, in Figure 6.1 User A
grants User B INSERT privilege on the Staff table WITH GRANT OPTION (step 1). User B
passes this privilege on to User C (step 2). Subsequently, User C gets the same privilege
from User E (step 3). User C then passes the privilege on to User D (step 4). When User A
revokes the INSERT privilege from User B (step 5), the privilege cannot be revoked from
User C, because User C has also received the privilege from User E. If User E had not
given User C this privilege, the revoke would have cascaded to User C and User D.
Example 6.10 REVOKE specific privileges from PUBLIC
Revoke the privilege SELECT on the Branch table from all users.
REVOKE SELECT
ON Branch
FROM PUBLIC;
Example 6.11 REVOKE specific privileges from named user
Revoke all privileges you have given to Director on the Staff table.
REVOKE ALL PRIVILEGES
ON Staff
FROM Director;
This is equivalent to REVOKE SELECT . . . , as this was the only privilege that has been
given to Director.