GRANT Permissions Between Users

reli

Hi all,
in Vertica ther is two users not admin, and they have grant all permissions on schema.
If one user create table the other user cant select the new table
the error is:"Permission denied for.."
until admin user run the script

GRANT SELECT ON ALL TABLES IN SCHEMA public to user;

There is a way to prevent this and allow user to see all the tables even the ones he did not create?

s_crossman

Hi Reli,

I think the inherited schema and or table/view feature will help you with this. The related doc pages are:
https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Privileges/EnableSchemaInheritance.htm
https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Privileges/TableViewInheritance.htm

There are two related system tables to help you see the impact of what you set. They are inheriting_objects and inherited_privileges.

This should be availabl ein any of the currently supported Vertica versions.

I hope it helps,

reli

Thank you very much I will check it!

reli

I use DEFAULT INCLUDE PRIVILEGES and still if user1 create view user 2 can't see it ,the error is :
Permission denied for relation view1

DaveT

Likely cause is that USER1 is either not the owner of the base table on which the view was created or USER1 does not have SELECT WITH GRANT OPTION on that base table. You need one of those two to be true in order for USER2 to be able to inherit the SELECT privilege for that view. See the Note box in this link:
https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/SQLReferenceManual/Statements/SELECT/SELECT.htm

Of course, USER2 also needs USAGE + SELECT on the schema but I assume this has already been granted.

One other possibility is that the view was created before inherited privileges were defined for the schema.

reli

User1 has SELECT WITH GRANT OPTION on the table in the view but not the owner the table is the only owner on the view.
User2 has SELECT WITH GRANT OPTION on the table in the view , and the view create after the inherited privileges were defined for the schema, and still User2 get the error: Permission denied for relation view1

DaveT

What version of Vertica are you running? Suggest opening a support case if you think there is an issue. Here are examples from Vertica 10.1.1 where it fails as expected due to missing privileges; and then where it works because required privileges are present:

This example fails because user u1 does not have SELECT WITH GRANT OPTION against the base table (either explicitly or inherited). Therefore, user u2 cannot select from the view.

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

select version();

version

Vertica Analytic Database v10.1.1-2

create schema priv default include privileges;
CREATE SCHEMA

grant usage, select, create on schema priv to u1;
GRANT PRIVILEGE

grant usage, select on schema priv to u2;
GRANT PRIVILEGE

create table priv.t1(c1 int);
WARNING 6978: Table "t1" will include privileges from schema "priv"
CREATE TABLE

\c - u1
You are now connected as user "u1".

create view priv.v1 as select * from priv.t1;
WARNING 7070: View "v1" will include privileges from schema "priv"
CREATE VIEW

\c - u2
You are now connected as user "u2".

select * from priv.v1;
ERROR 4367: Permission denied for relation v1

The example below works because user u1 now has SELECT WITH GRANT OPTION against the base table. It was inherited through the schema in this case. Now user u2 can select from the view that user u1 created.

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

drop schema priv cascade;
DROP SCHEMA

create schema priv default include privileges;
CREATE SCHEMA

grant usage on schema priv to u1;
GRANT PRIVILEGE

grant select on schema priv to u1 with grant option;
GRANT PRIVILEGE

grant create on schema priv to u1;
GRANT PRIVILEGE

grant usage, select on schema priv to u2;
GRANT PRIVILEGE

create table priv.t1(c1 int);
WARNING 6978: Table "t1" will include privileges from schema "priv"
CREATE TABLE

\c - u1
You are now connected as user "u1".

create view priv.v1 as select * from priv.t1;
WARNING 7070: View "v1" will include privileges from schema "priv"
CREATE VIEW

\c - u2
You are now connected as user "u2".

select * from priv.v1;

c1

(0 rows)

reli

Now it's work!!!!