Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

GRANT Permissions Between Users

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?

Tagged:

Answers

  • s_crossmans_crossman Employee

    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,

  • Thank you very much I will check it!

  • 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

  • DaveTDaveT Employee

    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.

  • 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 :neutral:

  • DaveTDaveT Employee

    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)

  • Now it's work!!!! B)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.