What privileges my role has? how to check?

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

Best Answer

Answers

  • moshegmosheg Vertica Employee Administrator
    edited August 2020

    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

  • SankarmnSankarmn ✭✭
    edited August 2020

    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?

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

Leave a Comment

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