SQL Series...44

Taking Privileges Away

If you have a way to give access privileges to people, you better also have a way of taking those privileges away. People’s job functions change, and with these changes, their need for access to data changes. People may even leave the organization to join a competitor. You should probably revoke all the access privileges of such people. SQL provides for the removal of access privileges by using the REVOKE statement. This statement acts like the GRANT statement does, except that it has the reverse effect. The syntax for this statement is as follows:

REVOKE [GRANT OPTION FOR] privilege-list

ON object

FROM user-list [RESTRICT|CASCADE] ;

You can use this structure to revoke specified privileges while leaving others intact. The principal difference between the REVOKE statement and the GRANT statement is the presence of the optional RESTRICT or CASCADE keyword in the REVOKE statement. If you used WITH GRANT OPTION to grant the privi-leges you’re revoking, using CASCADE in the REVOKE statement revokes privi-leges for the grantee and also for anyone to whom that person granted those privileges as a result of the WITH GRANT OPTION clause. On the other hand, the REVOKE statement with the RESTRICT option works only if the grantee hasn’t delegated the specified privileges. In the latter case, the REVOKE state-ment revokes the grantee’s privileges. If the grantee passed on the specified privileges, the REVOKE statement with the RESTRICT option doesn’t revoke anything and instead returns an error code.

You can use a REVOKE statement with the optional GRANT OPTION FOR clause to revoke only the grant option for specified privileges while enabling the grantee to retain those privileges for himself. If the GRANT OPTION FOR clause and the CASCADE keyword are both present, you revoke all privileges that the grantee granted, along with the grantee’s right to bestow such privi-leges — as if you’d never granted the grant option in the first place. If the GRANT OPTION FOR clause and the RESTRICT clause are both present, one of two things happens:

If the grantee didn’t grant to anyone else any of the privileges you’re revoking, then the REVOKE statement executes and removes the grantee’s ability to grant privileges.

If the grantee has already granted at least one of the privileges you’re revoking, the REVOKE doesn’t execute and returns an error code instead.

The fact that you can grant privileges by using WITH GRANT OPTION, com-bined with the fact that you can also selectively revoke privileges, makes system security much more complex than it appears at first glance. Multiple grantors, for example, can conceivably grant a privilege to any single user. If one of those grantors then revokes the privilege, the user still retains that privilege because of the still-existing grant from another grantor. If a privilege passes from one user to another by way of the WITH GRANT OPTION, this sit-uation creates a chain of dependency, in which one user’s privileges depend on those of another user. If you’re a DBA or object owner, always be aware that, after you grant a privilege by using the WITH GRANT OPTION clause, that privilege may show up in unexpected places. Revoking the privilege from unwanted users while letting legitimate users retain the same privilege may prove challenging. In general, the GRANT OPTION and CASCADE clauses encompass numerous subtleties. If you use these clauses, check both the SQL:2003 standard and your product documentation carefully to ensure that you understand how the clauses work.