Privilege ownership chains

The ability to revoke privileges on objects can cascade throughout an organization. If the grant option was revoked from a user, the privilege that this user granted to other users will also be revoked.

If a privilege was granted to a user or role by multiple grantors, to completely revoke this privilege from the grantee the privilege has to be revoked by each original grantor. The only exception is a superuser may revoke privileges granted by an object owner, with the reverse being true, as well.

In the following example, the SELECT privilege on table t1 is granted through a chain of users, from a superuser through User3.

• A superuser grants User1 CREATE privileges on the schema s1:

  => \c - dbadmin
  You are now connected as user "dbadmin".
  => CREATE USER User1;
  CREATE USER
  => CREATE USER User2;
  CREATE USER
  => CREATE USER User3;
  CREATE USER
  => CREATE SCHEMA s1;
  CREATE SCHEMA
  => GRANT USAGE on SCHEMA s1 TO User1, User2, User3;
  GRANT PRIVILEGE
  => CREATE ROLE reviewer;
  CREATE ROLE
  => GRANT CREATE ON SCHEMA s1 TO User1;
  GRANT PRIVILEGE
  

• User1 creates new table t1 within schema s1 and then grants SELECT WITH GRANT OPTION privilege on s1.t1 to User2:

  => \c - User1
  You are now connected as user "User1".
  => CREATE TABLE s1.t1(id int, sourceID VARCHAR(8));
  CREATE TABLE
  => GRANT SELECT on s1.t1 to User2 WITH GRANT OPTION;
  GRANT PRIVILEGE
  

• User2 grants SELECT WITH GRANT OPTION privilege on s1.t1 to User3:

  => \c - User2
  You are now connected as user "User2".
  => GRANT SELECT on s1.t1 to User3 WITH GRANT OPTION;
  GRANT PRIVILEGE
  

• User3 grants SELECT privilege on s1.t1 to the reviewer role:

  => \c - User3
  You are now connected as user "User3".
  => GRANT SELECT on s1.t1 to reviewer;
  GRANT PRIVILEGE
  

Users cannot revoke privileges upstream in the chain. For example, User2 did not grant privileges on User1, so when User1 runs the following REVOKE command, Vertica rolls back the command:

=> \c - User2
You are now connected as user "User2".
=> REVOKE CREATE ON SCHEMA s1 FROM User1;
ROLLBACK 0:  "CREATE" privilege(s) for schema "s1" could not be revoked from "User1"

Users can revoke privileges indirectly from users who received privileges through a cascading chain, like the one shown in the example above. Here, users can use the CASCADE option to revoke privileges from all users "downstream" in the chain. A superuser or User1 can use the CASCADE option to revoke the SELECT privilege on table s1.t1 from all users. For example, a superuser or User1 can execute the following statement to revoke the SELECT privilege from all users and roles within the chain:

=> \c - User1
You are now connected as user "User1".
=> REVOKE SELECT ON s1.t1 FROM User2 CASCADE;
REVOKE PRIVILEGE

When a superuser or User1 executes the above statement, the SELECT privilege on table s1.t1 is revoked from User2, User3, and the reviewer role. The GRANT privilege is also revoked from User2 and User3, which a superuser can verify by querying the V_CATALOG.GRANTS system table.

=> SELECT * FROM grants WHERE object_name = 's1' AND grantee ILIKE 'User%';
 grantor | privileges_description | object_schema | object_name | grantee
---------+------------------------+---------------+-------------+---------
 dbadmin | USAGE                  |               | s1          | User1
 dbadmin | USAGE                  |               | s1          | User2
 dbadmin | USAGE                  |               | s1          | User3
(3 rows)