How to Take Away SQL Privileges

By Allen G. Taylor

If you have a way to give SQL access privileges to people, you should also have a way of taking those privileges away. People’s job functions change, and with these changes their data access needs change. Say an employee leaves the organization to join a competitor. You should probably revoke all access privileges to that person — immediately.

SQL allows you to remove 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.

For example, suppose you used WITH GRANT OPTION when you granted certain privileges to a user. Eventually, when you want to revoke those privileges, you can use CASCADE in the REVOKE statement. When you revoke a user’s privileges in this way, you also yank privileges from anyone to whom that person had granted privileges.

On the other hand, the REVOKE statement with the RESTRICT option works only if the grantee hasn’t delegated the specified privileges. In that case, the REVOKE statement revokes the grantee’s privileges just fine. But 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.

This is a clear warning to you that you need to find out who was granted privileges by the person whose privileges you are trying to revoke. You may or may not want to revoke that person’s privileges.

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 privileges — 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 statement doesn’t execute and returns an error code instead.

The fact that you can grant privileges by using WITH GRANT OPTION, combined 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 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 situation creates a chain of dependency, in which one user’s privileges depend on those of another user. If you’re a DBA or an 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 standard and your product documentation — carefully — to ensure that you understand how the clauses work.