Funky Permissions on view

@Vertica_Curtis,

The think that the error is misleading. The owner of a view must have the SELECT privilege on the underlying object(s) of the view...

Example:

dbadmin=> SELECT version();
              version
------------------------------------
 Vertica Analytic Database v9.2.0-2
(1 row)

dbadmin=> SELECT user;
 current_user
--------------
 dbadmin
(1 row)

dbadmin=> CREATE SCHEMA test;
CREATE SCHEMA

dbadmin=> CREATE USER user3;
CREATE USER

dbadmin=> GRANT USAGE, SELECT ON SCHEMA test TO user3;
GRANT PRIVILEGE

dbadmin=> CREATE TABLE test.vendor_dimension (vendor_key INT);
CREATE TABLE

dbadmin=> INSERT INTO test.vendor_dimension SELECT 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> CREATE VIEW test.view_x AS SELECT * FROM test.vendor_dimension;
CREATE VIEW

dbadmin=> GRANT SELECT ON test.view_x TO user3;
GRANT PRIVILEGE

dbadmin=> \c - user3
You are now connected as user "user3".

dbadmin=> SELECT * FROM test.view_x;
 vendor_key
------------
          1
(1 row)

That's all fine. Now I'll switch the owner of the view.

dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".

dbadmin=> SELECT table_schema, table_name, owner_name FROM views WHERE table_name = 'view_x';
 table_schema | table_name | owner_name
--------------+------------+------------
 test         | view_x     | dbadmin
(1 row)

dbadmin=> SELECT grantor, grantee, object_schema, object_name FROM grants WHERE object_name = 'view_x';
 grantor | grantee | object_schema | object_name
---------+---------+---------------+-------------
 dbadmin | user3   | test          | view_x
(1 row)

dbadmin=> ALTER VIEW test.view_x OWNER TO user3;
ALTER VIEW

dbadmin=> SELECT table_schema, table_name, owner_name FROM views WHERE table_name = 'view_x';
 table_schema | table_name | owner_name
--------------+------------+------------
 test         | view_x     | user3
(1 row)

dbadmin=> SELECT grantor, grantee, object_schema, object_name FROM grants WHERE object_name = 'view_x';
 grantor | grantee | object_schema | object_name
---------+---------+---------------+-------------

The SELECT grant is now gone, but the user shouldn't need it anymore cause it owns it. But if we query it as the new owner, we get an error!

dbadmin=> \c - user3
You are now connected as user "user3".

dbadmin=> SELECT * FROM test.view_x;
ERROR 4367:  Permission denied for relation view_x

Even if we re-grant the SELECT privilege!

dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".

dbadmin=> GRANT SELECT ON test.view_x TO user3;
GRANT PRIVILEGE

dbadmin=> SELECT grantor, grantee, object_schema, object_name FROM grants WHERE object_name = 'view_x';
 grantor | grantee | object_schema | object_name
---------+---------+---------------+-------------
 user3   | user3   | test          | view_x
(1 row)

dbadmin=> \c - user3
You are now connected as user "user3".

dbadmin=> SELECT * FROM test.view_x;
ERROR 4367:  Permission denied for relation view_x

But if I GRANT SELECT on the underlying object, we're ok...

dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".

dbadmin=> GRANT SELECT ON test.vendor_dimension TO user3;
GRANT PRIVILEGE

dbadmin=> \c - user3
You are now connected as user "user3".

dbadmin=> SELECT * FROM test.view_x;
 vendor_key
------------
          1
(1 row)

@Vertica_Curtis,

Following your particular example a little closer, you'll need to GRANT the SELECT privilege to the view owner on the underlying object(s) adding the WITH GRANT OPTION if you want other users to read from the view...

Example:

dbadmin=> CREATE USER user1;
CREATE USER

dbadmin=> GRANT USAGE, SELECT ON SCHEMA test TO user1;
GRANT PRIVILEGE

dbadmin=> CREATE USER user3;
CREATE USER

dbadmin=> GRANT USAGE, SELECT ON SCHEMA test TO user3;
GRANT PRIVILEGE

dbadmin=> CREATE TABLE test.vendor_dimension (vendor_key INT);
CREATE TABLE

dbadmin=> INSERT INTO test.vendor_dimension SELECT 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> CREATE VIEW test.view_x AS SELECT * FROM test.vendor_dimension;
CREATE VIEW

dbadmin=> GRANT SELECT ON test.view_x TO user3;
GRANT PRIVILEGE

dbadmin=> \c - user3
You are now connected as user "user3".

dbadmin=> SELECT * FROM test.view_x;
 vendor_key
------------
          1
(1 row)

dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".

dbadmin=> ALTER VIEW test.view_x OWNER TO user1;
ALTER VIEW

dbadmin=> GRANT SELECT ON test.vendor_dimension TO user1 WITH GRANT OPTION;
GRANT PRIVILEGE

dbadmin=> GRANT SELECT ON test.view_x TO user3;
GRANT PRIVILEGE

dbadmin=> SELECT table_schema, table_name, owner_name FROM tables WHERE table_name = 'vendor_dimension';
 table_schema |    table_name    | owner_name
--------------+------------------+------------
 test         | vendor_dimension | dbadmin
(1 row)

dbadmin=> SELECT table_schema, table_name, owner_name FROM views WHERE table_name = 'view_x';
 table_schema | table_name | owner_name
--------------+------------+------------
 test         | view_x     | user1
(1 row)

dbadmin=> \c - user3
You are now connected as user "user3".

dbadmin=> SELECT * FROM test.view_x;
 vendor_key
------------
          1
(1 row)

See:
http://jira.verticacorp.com:8080/jira/browse/VER-60542

Fun stuff!

Interesting. I'm not a DBA (but I play one on TV) - is that normal behavior for other databases to require permissions on underlying tables? I know a lot of orgs use views to shield the users from the underlying table. Like, a poor man's version of row or column-level security. That seems like an odd requirement, but I don't know how other RDBMS's deal with that.

Oracle handles view privileges the same way as Vertica.

See:

https://docs.oracle.com/cd/B28359_01/server.111/b28310/views001.htm#ADMIN11776

In particular:

• The owner of the view (whether it is you or another user) must have been explicitly granted privileges to access all objects referenced in the view definition. The owner cannot have obtained these privileges through roles. Also, the functionality of the view depends on the privileges of the view owner. For example, if the owner of the view has only the INSERT privilege for Scott's emp table, then the view can be used only to insert new rows into the emp table, not to SELECT, UPDATE, or DELETE rows.

• If the owner of the view intends to grant access to the view to other users, the owner must have received the object privileges to the base objects with the GRANT OPTION or the system privileges with the ADMIN OPTION.

If we did not handle security this way anyone could create a view (i.e. own it) and use it to read from tables in which the owner does not have access!

One solution is to have a specific user that owns views if you don't want the DBADMIN user to own them... That user will have access to the base tables, but typical users would not. They'd only be able to read the view.

Example:

dbadmin=> SELECT user;
 current_user
--------------
 dbadmin
(1 row)

dbadmin=>  CREATE USER user1;
CREATE USER

dbadmin=>  CREATE USER user3;
CREATE USER

dbadmin=> CREATE SCHEMA test;
CREATE SCHEMA

dbadmin=> GRANT USAGE ON SCHEMA test TO user1, user3;
GRANT PRIVILEGE

dbadmin=> CREATE TABLE test.vendor_dimension (vendor_key INT);
CREATE TABLE

dbadmin=> INSERT INTO test.vendor_dimension SELECT 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> CREATE VIEW test.view_x AS SELECT * FROM test.vendor_dimension;
CREATE VIEW

dbadmin=> CREATE USER view_owner;
CREATE USER

dbadmin=> GRANT USAGE, SELECT ON SCHEMA test TO view_owner;
GRANT PRIVILEGE

dbadmin=> ALTER VIEW test.view_x OWNER TO view_owner;
ALTER VIEW

dbadmin=> SELECT table_schema, table_name, owner_name FROM views WHERE table_name = 'view_x';
 table_schema | table_name | owner_name
--------------+------------+------------
 test         | view_x     | view_owner
(1 row)

dbadmin=> GRANT SELECT ON test.vendor_dimension TO view_owner WITH GRANT OPTION;
GRANT PRIVILEGE

dbadmin=> GRANT SELECT ON test.view_x TO user1, user3;
GRANT PRIVILEGE

dbadmin=> SELECT grantor, grantee, privileges_description, object_schema, object_name FROM grants WHERE object_name = 'vendor_dimension';
 grantor |  grantee   |                   privileges_description                   | object_schema |   object_name
---------+------------+------------------------------------------------------------+---------------+------------------
 dbadmin | dbadmin    | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, TRUNCATE* | test          | vendor_dimension
 dbadmin | view_owner | SELECT*                                                    | test          | vendor_dimension
(2 rows)

dbadmin=> SELECT grantor, grantee, privileges_description, object_schema, object_name FROM grants WHERE object_name = 'view_x';
  grantor   | grantee | privileges_description | object_schema | object_name
------------+---------+------------------------+---------------+-------------
 view_owner | user3   | SELECT                 | test          | view_x
 view_owner | user1   | SELECT                 | test          | view_x
(2 rows)

dbadmin=> \c - user1
You are now connected as user "user1".

dbadmin=> SELECT * FROM test.vendor_dimension;
ERROR 4367:  Permission denied for relation vendor_dimension

dbadmin=> SELECT * FROM test.view_x;
 vendor_key
------------
          1
(1 row)

dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".

dbadmin=> \c - user3
You are now connected as user "user3".

dbadmin=> SELECT * FROM test.vendor_dimension;
ERROR 4367:  Permission denied for relation vendor_dimension

dbadmin=> SELECT * FROM test.view_x;
 vendor_key
------------
          1
(1 row)

Obviously in this example, the VIEW_OWNER account would need to be protected!

It sounds a bit like "WITH GRANT OPTION" is a bit overloaded. It provides the ability to allow others to also grant privileges, and also allows others to have visibility to those objects as well. I think.

This particular client doesn't want to use that option, and I think that's where their roadblock is.