View privileges on a role.

Sankarmn

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?

DaveT

The column GRANTEE in system view GRANTS can be either a user or a role. Use that to find the privileges granted to the role. The column OBJECT_NAME would be a role for cases where that role was granted to a user or role.

sahil_kumar

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)

Sankarmn

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

Sankarmn

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

DaveT

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

Sankarmn

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

Sankarmn

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