Revoking predicated privileges

Use the revoke command to revoke row-level access on a table. The syntax is:

revoke {all [privileges]
    | [all] permission_list}
on table_name (column_list)    
[with { pred_name | {all |no} predicates}]
from {public | name_list | role_list} 

• If column_list is used with pred_name, the predicated row-level access is revoked for the named columns. If there are remaining columns that are referenced by this row-level privilege, the privilege and its related named predicate remain in sysprotects. For the following revoke examples, assume permission was initially granted as:

  grant select on t1(col1,col2,col3)
    where col4 > 99 as pred1 
    to user1
  

  This revokes select permission on t1.col2 with pred1:

  revoke select on t1 (col2) 
    with pred1 
    from user1
  

  However, if user1 selects t1.col3, then pred1 is still applied:

  If the grantor also issued:

  revoke select on t1 (col1, col3) 
    with pred1 
    from user1
  

  or,

  revoke select on t1 with pred1
  

  then all permissions on t1 using pred1 would be revoked from user1.

• Use with cause to revoke all predicates, name predicates, or remove only unpredicated grants for the given access from the named grantee. If the with clause is omitted, both predicated and non-predicated grants are revoked. This example revokes all predicated row-level privileges granted to user1.

  revoke select on t1 with all predicates 
    from user1
  

  The following omits the with clause and revokes all non-predicated select access non-predicated grants granted to user1.

  revoke select on t1 with no predicates
  

See the Reference Manual: Commands.