View privileges on a role.

SankarmnSankarmn Community Edition User ✭✭

I am viewing grants system table that shows all privileges for the user and the role is listed as objects. DBA has created a role and assigned.
How to know/see what privileges does the assigned role has for the user?


Best Answer


  • Options
    sahil_kumarsahil_kumar Vertica Employee Employee

    VS_ROLES contain roles information

    lak=> SELECT grantor, privileges_description, object_schema, object_type, object_name, grantee
    lak-> FROM grants WHERE grantee = 'bob';
    grantor | privileges_description | object_schema | object_type | object_name | grantee
    dbadmin | USAGE | | RESOURCEPOOL | general | bob
    dbadmin | | | ROLE | appadmin | bob
    dbadmin | | | ROLE | appadmin | bob
    dbadmin | | | ROLE | applogs | bob
    (4 rows)

    lak=> select * from vs_roles where name in('appadmin','applogs');
    oid | tag | name | num_assigned_roles | assigned_roles | predefined_role | admin_option | clientauthentications | locked | ldapdn | ldapurihash
    45035996273841606 | 0 | applogs | 0 | | f | 0 | 0 | 0 | | 0
    45035996273841608 | 0 | appadmin | 2 | appdata, applogs | f | 2 | 0 | 0 | | 0
    (2 rows)

  • Options
    SankarmnSankarmn Community Edition User ✭✭

    Thanks Sahil.
    VS_ROLES doesn't give privileges of the role. In your case what privileges does appadmin and applogs have? they could be privileges on few tables, views on other schemas and so on....

  • Options
    SankarmnSankarmn Community Edition User ✭✭

    @DaveT , When I check the grants table for the role/user I see INSERT* and USAGE*. Does this mean that I can insert into any schema tables. Appreciate if you can send the link to these details.

  • Options
    DaveTDaveT Vertica Employee Employee
    edited May 2020

    That means you have INSERT and USAGE for the OBJECT_NAME of OBJECT_TYPE in that row. Since USAGE is involved it sounds like it is a schema. So, you are setup for INSERT into all the tables in that schema but it depends on how the schema and tables were created or altered. If you did not create or alter your schema to include schema privileges by default then that role will not have access to insert into tables via that privilege unless you create those tables to include schema privileges. In other words, if you want all or most tables to inherit schema privileges then you should add the DEFAULT INCLUDE SCHEMA PRIVILEGES option when you create the schema. If you do that then the role to which you granted INSERT will have insert privileges into newly created tables by default. Otherwise, you need to add that option to the CREATE TABLE or ALTER TABLE command.

  • Options
    SankarmnSankarmn Community Edition User ✭✭

    GRANTOR | privileges_description | object_schema | object_type | object_name | grantee
    comadmin | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, USAGE*, CREATE* | | SCHEMA | comadmin | comadmin

    Above is the one I have. what activities I can't do if I have only 'INSERT, SELECT, UPDATE, DELETE, REFERENCES, USAGE, CREATE'?

  • Options
    SankarmnSankarmn Community Edition User ✭✭

    Experts your view on the above post? what's the difference between a privilege that has (INSERT *) star at the end vs without (INSERT).

Leave a Comment

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