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

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

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|

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)

That's it, thank you!

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.

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

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.

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