Once we've granted permissions, it often proves necessary to revoke them at a later date. Fortunately, SQL provides us with the REVOKE command to remove previously granted permissions. Here's the syntax:

REVOKE [GRANT OPTION FOR] <permissions>
ON <table>
FROM <user/role>

You'll notice that the syntax of this command is similar to that of the GRANT command. The only difference is that WITH GRANT OPTION is specified on the REVOKE command line rather than at the end of the command. As an example, let's imagine we want to revoke Mary's previously granted permission to remove records from the Customers database. We'd use the following command:

REVOKE DELETE
ON Customers
FROM Mary

And that's all there is to it! There's one additional mechanism supported by Microsoft SQL Server that is worth mentioning -- the DENY command. This command can be used to explicitly deny a permission to a user that they might otherwise have through a current or future role membership. Here's the syntax:

DENY <permissions>
ON <table>
TO <user/role>

Returning to our previous example, let's imagine that Mary was also a member of the Managers role that also had access to the Customers table. The previous REVOKE statement would not be sufficient to deny her access to the table. It would remove the permission granted to her through a GRANT statement targeting her user account, but would not affect the permissions gained through her membership in the Managers role. However, if we use a DENY statement it will block her inheritance of the permission. Here's the command:

DENY DELETE
ON Customers
TO Mary

The DENY command essentially creates a "negative permission" in the database access controls. If we later decide to give Mary permission to remove rows from the Customers table, we can't simply use the GRANT command. That command would be immediately overridden by the existing DENY. Instead, we would first use the REVOKE command to remove the negative permission entry as follows:

REVOKE DELETE
ON Customers
FROM Mary

You'll notice that this command is exactly the same as the one used to remove a positive permission. Remember that the DENY and GRANT commands both work in a similar fashion -- they both create permissions (positive or negative) in the database access control mechanism. The REVOKE command removes all positive and negative permissions for the specified user. Once this command has been issued, Mary will be able to delete rows from the table if she is a member of a role that possesses that permission. Alternatively, a GRANT command could be issued to provide the DELETE permission directly to her account.

Throughout the course of this article, you've learned a good deal about the access control mechanisms supported by the Standard Query Language. This introduction should provide you with a good starting point, but I encourage you to reference your DBMS documentation to learn the enhanced security measures supported by your system. You'll find that many databases support more advanced access control mechanisms, such as granting permissions on specific columns.

Be sure to check back next week for another informative article on databases. If you're not already receiving our newsletter, be sure to subscribe and you'll receive a weekly reminder in your inbox!