View privileges on a role.

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?

Tagged:

Best Answer

Answers

  • sahil_kumarsahil_kumar 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)

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

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

  • DaveTDaveT Employee
    edited May 15

    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.
    https://vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATESCHEMA.htm
    https://vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATETABLE.htm

  • 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'?

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