When to Use REVOKE Before GRANT

You can use combinations of REVOKE and GRANT to replace PUBLIC with specific users as grantees, and to remove table-level privileges on some columns.

Replacing PUBLIC with Specified Users

If a table owner grants a privilege to PUBLIC, the owner cannot revoke the same privilege from any specific user. For example, assume PUBLIC has default Select privileges on your customer table. Suppose that you issue the following statement in an attempt to exclude ted from accessing your table:

REVOKE ALL ON customer FROM ted

This statement results in ISAM error message 111, No record found, because the system catalog tables (syscolauth or systabauth) contain no table-level privilege entry for a user named ted. This REVOKE operation does not prevent ted from keeping all the table-level privileges given to PUBLIC on the customer table.

To restrict table-level privileges, first revoke the privileges with the PUBLIC keyword, then re-grant them to some appropriate list of users and roles. The following statements revoke the Index and Alter privileges from all users for the customer table, and then grant these privileges specifically to user mary:

REVOKE INDEX, ALTER ON customer FROM PUBLIC 
GRANT INDEX, ALTER ON customer TO mary

Restricting Access to Specific Columns

Unlike GRANT, the REVOKE statement has no syntax to specify privileges on a subset of columns in a table. To revoke the Select, Update, or References privilege on a column from a user, you must revoke the privilege for all the columns of the table. To provide access to some of the columns on which you previously had granted privileges, issue a new GRANT statement to restore the appropriate privilege on specific columns.

The next example cancels Select privileges for PUBLIC on certain columns:

REVOKE SELECT ON customer FROM PUBLIC
GRANT SELECT (fname, lname, company, city) ON customer TO PUBLIC

In the next example, mary first receives the ability to reference four columns in customer, then the table owner restricts references to two columns:

GRANT REFERENCES (fname, lname, company, city) ON customer TO mary
REVOKE REFERENCES ON customer FROM mary
GRANT REFERENCES (company, city) ON customer TO mary