Viewing user roles
You can obtain information about roles in three ways:
-
Verify specific role assignments with the function
HAS_ROLE. -
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.
Note
System tables do not show whether a role is available to a user indirectly through other roles. CallHAS_ROLEto obtain that information.
Verifying role assignments
The function
HAS_ROLE checks whether a Vertica role is granted to the specified user or role. Non-superusers can use this function to check their own role membership. Superusers can use it to determine role assignments for other users and roles. You can also use Management Console to check role assignments.
In the following example, a dbadmin user checks whether user MikeL is assigned the admnistrator role:
=> \c
You are now connected as user "dbadmin".
=> SELECT HAS_ROLE('MikeL', 'administrator');
HAS_ROLE
----------
t
(1 row)
User MikeL checks whether he has the regional_manager role:
=> \c - MikeL
You are now connected as user "MikeL".
=> SELECT HAS_ROLE('regional_manager');
HAS_ROLE
----------
f
(1 row)
The dbadmin grants the regional_manager role to the administrator role. On checking again, MikeL verifies that he now has the regional_manager role:
dbadmin=> \c
You are now connected as user "dbadmin".
dbadmin=> GRANT regional_manager to administrator;
GRANT ROLE
dbadmin=> \c - MikeL
You are now connected as user "MikeL".
dbadmin=> SELECT HAS_ROLE('regional_manager');
HAS_ROLE
----------
t
(1 row)
Viewing available and enabled roles
SHOW AVAILABLE ROLES lists all roles granted to you:
=> SHOW AVAILABLE ROLES;
name | setting
-----------------+-----------------------------
available roles | logreader, logwriter
(1 row)
SHOW ENABLED ROLES lists the roles enabled in your session:
=> SHOW ENABLED ROLES;
name | setting
---------------+----------
enabled roles | logreader
(1 row)
Querying system tables
You can query tables ROLES, USERS, AND GRANTS, either separately or joined, to obtain detailed information about user roles, users assigned to those roles, and the privileges granted explicitly to users and implicitly through roles.
The following query on ROLES returns the names of all roles users can access, and the roles granted (assigned) to those roles. An asterisk (*) appended to a role indicates that the user can grant the role to other users:
=> SELECT * FROM roles;
name | assigned_roles
-----------------+----------------
public |
dbduser |
dbadmin | dbduser*
pseudosuperuser | dbadmin*
logreader |
logwriter |
logadmin | logreader, logwriter
(7 rows)
The following query on system table USERS returns all users with the DBADMIN role. An asterisk (*) appended to a role indicates that the user can grant the role to other users:
=> SELECT user_name, is_super_user, default_roles, all_roles FROM v_catalog.users WHERE all_roles ILIKE '%dbadmin%';
user_name | is_super_user | default_roles | all_roles
-----------+---------------+--------------------------------------+--------------------------------------
dbadmin | t | dbduser*, dbadmin*, pseudosuperuser* | dbduser*, dbadmin*, pseudosuperuser*
u1 | f | | dbadmin*
u2 | f | | dbadmin
(3 rows)
The following query on system table GRANTS returns the privileges granted to user Jane or role R1. An asterisk (*) appended to a privilege indicates that the user can grant the privilege to other users:
=> SELECT grantor,privileges_description,object_name,object_type,grantee FROM grants WHERE grantee='Jane' OR grantee='R1';
grantor | privileges_description | object_name | object_type | grantee
--------+------------------------+-------------+--------------+-----------
dbadmin | USAGE | general | RESOURCEPOOL | Jane
dbadmin | | R1 | ROLE | Jane
dbadmin | USAGE* | s1 | SCHEMA | Jane
dbadmin | USAGE, CREATE* | s1 | SCHEMA | R1
(4 rows)