permissions for views that access other schema's table does not work; only dbadmin owner works?

ftobin

Why doesn't granting select&usage appropriately give the right permissions to access a view in another schema?

I'm finding that the only way to let user 'bob' use a view you create as 'alice' in schema1 that accesses a table in schema2 is to let dbadmin be the owner.

-- as dbadmin
create table bi_stats.test_table (col1 int);

-- bob can access this table fine
select * from bi_stats.test_table;
 col1 
------
(0 rows)

-- as alice
create or replace view bizint.test_table_view as select * from bi_stats.test_table;

-- as dbadmin, give bob access to these schemas and the view
grant select on bi_stats.test_table to bob with grant option;
grant usage on schema bizint to bob;
grant usage on schema bi_stats to bob;
grant select on bizint.test_table_view to bob;

-- bob still cannot use this view
select * from bizint.test_table_view;
ERROR 4367:  Permission denied for relation test_table_view

-- what works: as alice
alter view bizint.test_table_view owner to dbadmin;

-- testing as bob, now it works?!!!!!
select * from bizint.test_table_view;
 col1 
------
(0 rows)

What is going on? Why do I need to set the owner of the test_table_view to dbadmin?

Bryan_H

Re-reading your scenario, "alice" owns the view and needs select with grant option on the base table:
grant select on bi_stats.test_table to alice with grant option;
This allows "alice" to select from the table and grant select to the view object. Then "alice" grants select on the view to "bob". Here's a complete working example:

I created three users for the following test: mytable, myview, myuser
[bryan@hpbox ~]$ vsql -U dbadmin
d2=> create schema securetables authorization mytable;
CREATE SCHEMA
d2=> create schema secureviews authorization myview;
CREATE SCHEMA
d2=> \q
[bryan@hpbox ~]$ vsql -U mytable
d2=> create table securetables.table (i int, v varchar);
CREATE TABLE
d2=> grant usage on schema securetables to myview;
GRANT PRIVILEGE
d2=> grant select on securetables."table" to myview with grant option;
GRANT PRIVILEGE
d2=> \q
[bryan@hpbox ~]$ vsql -U myview
d2=> create view secureviews.view as select * from securetables."table";
CREATE VIEW
d2=> grant select on secureviews.view to myuser;
WARNING 5682:  USAGE privilege on schema "secureviews" also needs to be granted to "myuser"
GRANT PRIVILEGE
d2=> grant usage on schema secureviews to myuser;
GRANT PRIVILEGE
d2=> \q
[bryan@hpbox ~]$ vsql -U myuser
d2=> select * from secureviews.view ;
 i | v
---+---
(0 rows)

Bryan_H

Hi, did you set SELECT WITH GRANT OPTION on the base table? Please see current documentation for an explanation of view access settings: https://docs.vertica.com/12.0.x/en/data-analysis/views/using-views/#ViewAccessPermissions

ftobin

Thank you, but adding that step didn't change the results. (I edited above to include that step -- I also changed the references from 'public' to 'bob' since 'public' cannot be granted with grant option).

The step that is still required (and only step that is required) is the granting of ownership of the view to dbadmin. Once that is completed, it works fine.

Here are the permissions granted:

select grantor, object_schema, object_name, privileges_description, grantee
    from grants
where object_name in ('test_table_view', 'test_table', 'BIZINT', 'BI_STATS')
    and grantee = 'bob'
order by 2, 3;

|grantor|object_schema|object_name    |privileges_description|grantee|
|-------|-------------|---------------|----------------------|-------|
|alice |bizint       |test_table_view|SELECT                |bob|
|dbadmin|bizint        |test_table     |SELECT*               |bob|
|alice |             |BIZINT         |USAGE                 |bob|
|dbadmin|             |BI_STATS       |USAGE                 |bob|

ftobin

That's it, thank you!

Sergey_Cherepan_1

There is a better solution to a problem.
There is a concept of object (table, view) ownership, and it directly affect view grants.

You need to introduce proper schema object ownership with owner accounts:

create account bi_stats_owner account lock;

Same owner account should own all objects in bi_stats schema. Also, it should own all views in other schemas that refer to tables in bi_stats schema.

Now, grants for USAGE and SELECT on schema bizint is enough to see data in bi_stats.test_table through view bizint.test_table_view.
No direct grants on tables, no WITH GRANT OPTION etc.

This approach works with roles, means it works with LDAP integration.

ftobin

@Sergey_Cherepan_1 said:

Same owner account should own all objects in bi_stats schema. Also, it should own all views in other schemas that refer to tables in bi_stats schema.

That's an interesting idea, but in our case the owner of bi_stats (which, being the owner, would have more elevated privs) needs to be different than the views accessing it. The views are created and recreated by frequently by regular users, who have more limited privs. Also, the views could be created & owned by multiple different users.

Sergey_Cherepan_1

Clarification: owner account concept - account is locked, nobody allowed to use it, no exceptions. It is only own objects. Owner account does not have any privileges granted and is not a privileged account.

VValdar

@Sergey_Cherepan_1 I was wondering how I missed the create account command, but that's probably a create user.
Nice idea btw, never thought of it.