View Privileges

This blog post was authored by Soniya Shah.

This set of examples shows the privileges a user needs for various operations related to views, including creating and querying. A view is a virtual table based on the result set of a SQL statement, also called a SQL query. To select from a view, you need a combination of privileges on the underlying tables, and their schemas, as well as the view and its schema. This document describes the minimum set of privileges you need to perform certain operations on views.

We’ll use the following objects created by the dbadmin in this example: => \c You are now connected as user "dbadmin" => dbadmin => CREATE SCHEMA s1; => CREATE SCHEMA s2; => CREATE USER u1; => CREATE USER u2; => CREATE USER u3; => CREATE TABLE s1.t1(i int); 1. To create a view, a user needs the following privileges: grantor | privileges_description | object_schema | object_name | grantee --------+------------------------+---------------+-------------+-------- dbadmin | CREATE | | s2 | u1 dbadmin | USAGE | | s1 | u1 dbadmin | SELECT | s1 | t1 | u1 As the dbadmin, grant u1 CREATE, USAGE, and SELECT privileges: => GRANT CREATE ON SCHEMA s2 TO u1; => GRANT USAGE ON SCHEMA s1 TO u1; => GRANT SELECT ON TABLE s1.t1 TO u1; Then we can take a look at the privileges: => SELECT grantor, privileges_description, object_schema, object_name, grantee FROM grants WHERE grantee IN('u1', 'u2') AND object_name IN ('t1', 's1', 's2', 'v1') ORDER BY 5; grantor | privileges_description | object_schema | object_name | grantee ---------+------------------------+---------------+-------------+------ dbadmin | CREATE | | s2 | u1 dbadmin | USAGE | | s1 | u1 dbadmin | SELECT | s1 | t1 | u1 (3 rows) Now u1 can create views on the s1.t1 table: => \c – u1 You are now connected as user "u1". => CREATE VIEW s2.v1 AS SELECT * FROM s1.t1; CREATE VIEW Note: Although u1 can create a view, this does not necessarily mean u1 can query the view.

2. To query a view, u1 needs the following privileges: grantor | privileges_description | object_schema | object_name | grantee --------+------------------------+---------------+-------------+-------- dbadmin | SELECT |s1 | t1 | u1 dbadmin | USAGE | | s2 | u1 As the dbadmin, grant privileges to the user u1: => GRANT CREATE ON SCHEMA s2 TO u1; => GRANT USAGE ON SCHEMA s1 TO u1; => GRANT SELECT ON TABLE s1.t1 TO u1; => GRANT USAGE ON SCHEMA s2 TO u1; Connect to the database as u1 and create a new view: => \c – u1 You are now connected as user "u1". => CREATE VIEW s2.v1 AS SELECT * FROM s1.t1; CREATE VIEW To CREATE and QUERY a view, the following set of privileges is needed: grantor | privileges_description | object_schema | object_name | grantee --------+------------------------+---------------+-------------+-------- dbadmin | USAGE, CREATE | | s2 | u1 dbadmin | USAGE | | s1 | u1 dbadmin | SELECT |s1 | t1 | u1 Connect to the database as u1 and query the view: => \c – u1 You are now connected as user "u1". => SELECT * FROM s2.v1; i --- (0 rows) Suppose now, that as the dbadmin you no longer need u1 to have CREATE privileges on a view. You can revoke the privileges that are requireted to create a view, but leave those that are required to query a view: => \c You are now connected as user "dbadmin" => REVOKE CREATE ON SCHEMA s2 FROM u1; => REVOKE USAGE ON SCHEMA s1 FROM u1; Then we can take a look at the new privileges: => SELECT GRANTOR, privileges_description, object_schema, object_name, grantee FROM grants WHERE grantee IN('u1', 'u2') AND object_name IN ('t1', 's1', 's2', 'v1') ORDER BY 5; grantor | privileges_description | object_schema | object_name | grantee ---------+------------------------+---------------+-------------+------- dbadmin | SELECT |s1 | t1 | u1 dbadmin | USAGE | | s2 | u1 (2 rows) If u1 attempts to create a view, Vertica throws an error.

3. To GRANT SELECT ON VIEW, the grantor user (u1 in our example), needs the following privileges: grantor | privileges_description | object_schema | object_name | grantee --------+------------------------+---------------+-------------+------- dbadmin | USAGE | | s2 | u1 dbadmin | SELECT* |s1 | t1 | u1 Grant privileges to the user u1: => GRANT CREATE ON SCHEMA s2 TO u1; => GRANT USAGE ON SCHEMA s1 TO u1; => GRANT SELECT ON TABLE s1.t1 TO u1; Connect to the database as u1 and create a new view: => \c – u1 You are now connected as user "u1". => CREATE VIEW s2.v1 AS SELECT * FROM s1.t1; CREATE VIEW Then, connect as dbadmin to revoke and grant certain privileges: => \c You are now connected as user "dbadmin" => REVOKE CREATE ON SCHEMA s2 FROM u1; => REVOKE USAGE ON SCHEMA s1 FROM u1; => GRANT USAGE ON SCHEMA s2 TO u1; Now, as the dbadmin grant SELECT privileges to u1: => GRANT SELECT ON TABLE s1.t1 TO u1 WITH GRANT OPTION; Then we can take a look at the new privileges: => SELECT GRANTOR, privileges_description, object_schema, object_name, grantee FROM grants WHERE grantee IN('u1', 'u2') AND object_name IN ('t1', 's1', 's2', 'v1') ORDER BY 5; grantor | privileges_description | object_schema | object_name | grantee ---------+------------------------+---------------+-------------+------ dbadmin | USAGE | | s2 | u1 dbadmin | SELECT* |s1 | t1 | u1 (2 rows) Connect to the database as u1 and grant the SELECT privilege to u2: => \c – u1 You are now connected as user "u1". => GRANT SELECT ON s2.v1 to u2; --- GRANT PRIVILEGE Note: Although the grantor can grant SELECT ON VIEW to the grantee, this does not mean the grantee can query the view. The dbadmin must still grant the USAGE privilege on the target schema, s1, to the grantee, as shown in the next exmaple**.

4. To QUERY the view, u2 needs an additional privilege. grantor | privileges_description | object_schema | object_name | grantee --------+------------------------+---------------+-------------+-------- dbadmin | SELECT* |s1 | t1 | u1 u1 | SELECT |s2 | v1 | u2 dbadmin | USAGE | | s2 | u2 Connect as the dbadmin so we can grant different privileges to the user u2**: => \c => GRANT USAGE ON SCHEMA s2 TO u2; Then, revoke privileges from u1, as they are no longer needed after the grant is done. => REVOKE USAGE ON SCHEMA s2 FROM u1; Then we can take a look at the new privileges: => SELECT GRANTOR, privileges_description, object_schema, object_name, grantee FROM grants WHERE grantee IN('u1', 'u2', 'u3') AND object_name IN ('t1', 's1', 's2', 'v1') ORDER BY 5; grantor | privileges_description | object_schema | object_name | grantee ---------+------------------------+---------------+-------------+------- dbadmin | SELECT* |s1 | t1 | u1 u1 | SELECT |s2 | v1 | u2 dbadmin | USAGE | | s2 | u2 Connect to the database as u2 and query the view: => \c – u2 You are now connected as user "u2". => SELECT * FROM s2.v1; i --- (0 rows) Note: The USAGE privilege on the schema is a broad check that allows the user to use a schema. The dbadmin might grant this privilege to a user to allow access in a broad sense. After the USAGE check succeeds, specific checks for GRANTS, such as SELECT and CREATE, are made. Both the USAGE check and the GRANTS check must succeed for a user to have access to the object. Conversely, the USAGE privilege might be revoked without altering granular grants to temporarily or permanently deny a user from accessing a table, view, or any other schema object. In the examples in this post, we demonstrate how the USAGE schema might be added or revoked to achieve this.

5. GRANTS are not restricted to the creator or owner of a view. As shown in numbers 3 and 4, the owner of a view (u1) grants select on a view to another user (u2). However, any user with SELECT*, where the * indicates the GRANT option, privileges may GRANT to another user.

Grant privileges to the user u1: => GRANT CREATE ON SCHEMA s2 TO u1; => GRANT USAGE ON SCHEMA s1 TO u1; => GRANT SELECT ON TABLE s1.t1 TO u1; As u1, create a view: => \c – u1 You are now connected as user "u1". => CREATE VIEW s2.v1 AS SELECT * FROM s1.t1; As the dbadmin, revoke and grant privileges: => \c You are now connected as user "dbadmin" => REVOKE CREATE ON SCHEMA s2 FROM u1; => REVOKE USAGE ON SCHEMA s1 FROM u1; => GRANT USAGE ON SCHEMA s2 TO u1; => GRANT SELECT ON TABLE s1.t1 TO u1 WITH GRANT OPTION; As u1, grant privileges to u2: => \c – u1 You are now connected as user "u1". => GRANT SELECT ON s2.v1 TO u2; ---- GRANT PRIVILEGE As the dbadmin, revoke and grant privileges: => \c You are now connected as user "dbadmin" => REVOKE USAGE ON SCHEMA s2 FROM u1; => GRANT USAGE ON SCHEMA s2 TO u2; => GRANT USAGE ON SCHEMA s1 TO u2; => GRANT USAGE ON SCHEMA s2 TO u3; => GRANT SELECT on s2.v1 to u2 WITH GRANT OPTION; GRANT PRIVILEGE Note: A views owner (u1 in this case) can always grant SELECT on a view to any other user (u3 in this case). You can change the view owner by using the following statement: => ALTER VIEW s2.v1 OWNER TO u2; However, in this example, we demonstrated how a user (u2) who is not the owner of the view, grants SELECT on a view to another user (u3).

Then we can take a look at the new privileges: => SELECT GRANTOR, privileges_description, object_schema, object_name, grantee FROM grants WHERE grantee IN('u1', 'u2', 'u3') AND object_name IN ('t1', 's1', 's2', 'v1') ORDER BY 5,4; grantor | privileges_description | object_schema | object_name | grantee ---------+------------------------+---------------+-------------+------ dbadmin | SELECT* |s1 | t1 | u1 dbadmin | USAGE | | s1 | u2 dbadmin | USAGE | | s2 | u2 u1 | SELECT* |s2 | v1 | u2 dbadmin | USAGE | | s2 | u3 (5 rows) As u2, you can now grant privileges: => \c – u2 You are now connected as user "u2". => GRANT SELECT ON s2.v1 TO u3; ---- GRANT PRIVILEGE As u3, you can now query the view: => \c – u3 You are now connected as user "u3". => SELECT * FROM s2.v1; i --- (0 rows) For more information, see Privileges Required for Common Database Operations in the Vertica documentation.