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?

Best Answer

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.

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