16.20 - GRANT/REVOKE Order and Duration of Privileges - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ SQL Data Control Language

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1149-162K

If a GRANT ALL ON object TO PUBLIC statement is issued by any user on an object that is lower in the hierarchy than user DBC, all other users inherit privileges on that object, including users created after the GRANT request is issued.

If user DBC then issues a REVOKE ALL ON object_name FROM DBC, users created after the REVOKE request was issued are not granted privileges on that object. However, all previously created users retain the privileges until a REVOKE ALL ON object_name FROM PUBLIC is issued.

For example:

     .LOGON dbc.password

     CREATE USER sys_admin AS
     PERM=900000 PASSWORD=sys_admin;

     GRANT ALL
     ON sys_admin
     TO sys_admin
     WITH GRANT OPTION;

     .LOGON sys_admin,sys_admin

     CREATE USER dept AS
     PERM=500000 PASSWORD=dept;

     GRANT ALL
     ON dept
     TO dept
     WITH GRANT OPTION;

     .LOGON DEPT,DEPT

     CREATE USER user_1 AS
     PERM=100000 PASSWORD=user_1;

     .LOGON USER1,USER1

     CREATE TABLE table_1 (
       column_1 INTEGER,
       column_2 INTEGER)
     PRIMARY INDEX(column_1);

     INSERT table_1(1,2);

     INSERT table_1(3,4);

     GRANT ALL ON table_1
     TO ALL DBC;

     .LOGON dept,dept
     CREATE USER user_2 AS
     PERM=100000 PASSWORD=user_2;

     .LOGON user_2,user_2
     SELECT *
     FROM user_1.table_1;

The rows of table_1 are returned as expected.

     .LOGON dbc,password

     REVOKE ALL ON user_1.table_1
     FROM dbc;

     .LOGON dept,dept

     CREATE USER user_3 AS
     PERM=100000 PASSWORD=user_3;

     .LOGON user_3,user_3

     SELECT *
     FROM user_1.table_1;

An error is returned because user_3 was created after the REVOKE was issued.

     .LOGON user_2,user_2
     SELECT *
     FROM user_1.table_1;

The contents of table_1 are returned as expected.