grant and revoke statements are sensitive to the order in which they are issued. For example, if Jose’s group has been granted select permission on the titles table and then Jose’s permission to select the advance column has been revoked, Jose can select all the columns except advance, while the other users in his group can still select all the columns.
A grant or revoke statement that applies to a group or role changes any conflicting permissions that have been assigned to any member of that group or role. For example, if the owner of the titles table has granted different permissions to various members of the sales group, and wants to standardize, he or she might issue the following statements:
revoke all on titles from sales grant select on titles(title, title_id, type, pub_id) to sales
Similarly, a grant or revoke statement issued to public will change, for all users, all previously issued permissions that conflict with the new regime.
The same grant and revoke statements issued in different orders can create entirely different situations. For example, the following set of statements leaves Jose, who belongs to the public group, without any select permission on titles:
grant select on titles(title_id, title) to jose revoke select on titles from public
In contrast, the same statements issued in the opposite order result in only Jose having select permission and only on the title_id and title columns:
revoke select on titles from public grant select on titles(title_id, title) to jose
When you use the keyword public with grant, you are including yourself. With revoke on object creation permissions, you are included in public unless you are the Database Owner. With revoke on object access permissions, you are included in public unless you are the object owner. You may want to deny yourself permission to use your own table, while giving yourself permission to access a view built on it. To do this, you must issue grant and revoke statements explicitly setting your permissions. You can reinstitute the permission with a grant statement.
