GRANT/REVOKE Order and Duration of Privileges | Teradata Vantage - GRANT/REVOKE Order and Duration of Privileges - Analytics Database - Teradata Vantage

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.