What privileges my role has? how to check?

Sankarmn

My DBA created the role and I need to know what privileges does the role has. When I check the grants table I don't see any in privileges_description. I can view few of the system tables though.

Also could someone point me to what all the privileges the predefined roles has:
DBADMIN
PSEUDOSUPERUSER
DBDUSER
SYSMONITOR
PUBLIC

Jim_Knicely

You can get GRANT details on the user defined role "secusr_full_access" using:

SELECT grantor,privileges_description,object_name,object_type,grantee FROM grants WHERE grantee='secusr_full_access';

SYSMONITOR is a Vertica built-in role. Details about it can be found here:

https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Roles/SYSMONITORROLE.htm

mosheg

The default Roles are predefined automatically and assigned as shown in table roles.
On installation, Vertica automatically grants and enables predefined roles as follows:
Roles DBADMIN, PSEUDOSUPERUSER, and DBDUSER are irrevocably granted to the dbadmin user. These roles are always enabled for dbadmin, and can never be dropped.
PUBLIC is granted to dbadmin, and to all other users as they are created. This role is always enabled and cannot be dropped or revoked.
Their privileges description explained here: https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Roles/PredefinedRoles.htm

You can obtain information about roles in three ways:
1) Verify specific role assignments with the function HAS_ROLE.
2) View all available (granted) and enabled roles.
3) Obtain comprehensive information about roles, the users assigned to them, and the privileges granted to those users and roles by querying system tables ROLES, USERS, AND GRANTS, respectively.
System tables do not show whether a role is available to a user indirectly through other roles. Call HAS_ROLE to obtain that information.
See:
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Roles/ViewingAUsersRole.htm

Sankarmn

I get the below:
SELECT grantor,privileges_description,object_name,object_type,grantee FROM grants WHERE grantee='secusr';
grantor | privileges_description | object_name | object_type | grantee
---------+-----------------------------------------------------------------------------+---------------------+--------------+---------
dbadmin | USAGE | user_st_pool | RESOURCEPOOL | secusr
secusr | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, USAGE*, CREATE*, TRUNCATE* | secusr | SCHEMA | secusr
dbadmin | | sysmonitor | ROLE | secusr
dbadmin | | secusr_full_access | ROLE | secusr

How do I know the privileges assigned for sysmonitor and secusr_full_acess as the description columns aren't populated?
Any other query views that can get the details?

Sankarmn

@Jim_Knicely, though secusr_full_access is object name and providing this as grantee from grants tables get its privileges is some thing Learned. thanks!