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.
.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.