Privileges Level for a Revoke - Analytics Database - Teradata Vantage

The rows in DBC.AccessRights correlate privileges to the level in the system space hierarchy at which they were created. As a result, it is possible to submit a valid REVOKE statement that does not revoke the privileges you specify because the revocation was not requested at the correct hierarchical level.

For example, if privileges on an object were granted at the database or user level, and you later submit a REVOKE request to rescind those privileges on a particular table in that database or user space, the request completes without returning a message, but no rows are deleted from DBC.AccessRights because there are no rows correlating those privileges with that table for the specified database or user. Instead, the rows in DBC.AccessRights correlate the privileges with the containing user or database.

Consider the following space hierarchy:

Suppose user payroll logs onto the system and grants the SELECT privilege on pay_db to user human_resources and all its descendants.

The BTEQ LOGON command and GRANT request look like this:

     .LOGON tdpid/payroll,password
     GRANT SELECT
     ON pay_db
     TO ALL human_resources;

Result:

     Grant accepted.

User payroll later decides to revoke the SELECT privilege on table_c in database pay_db from human_resources and its descendants. To revoke this privilege, user payroll submits the following REVOKE request:

     REVOKE SELECT
     ON table_c
     FROM ALL human_resources;

Result:

     Revoke accepted.

Payroll now believes that the SELECT privilege on table_c has been revoked for human_resources and its descendants, but it has not been revoked because there were never any rows in DBC.AccessRights granting that privilege to human_resources on table_c. The row granting the SELECT privilege on the entire pay_db database to human_resources remains in DBC.AccessRights, so the originally granted SELECT privilege remains in effect.