Vertica Quick Tip: Checking User Role Membership

Posted February 27, 2018 by Soniya Shah, Information Developer

Business Team Meeting Discussion Working Concept
This blog post was authored by Jim Knicely.

The HAS_ROLE function returns a Boolean value that indicates whether a role has been assigned to a user.


To create a read only user and role, do the following: dbadmin=> CREATE ROLE read_only_role; CREATE ROLE dbadmin=> CREATE USER read_only; CREATE USER dbadmin=> GRANT read_only_role TO read_only; GRANT ROLE Later, I can verify that I made that grant by querying the V_CATALOG.GRANTS system table: dbadmin=> SELECT object_name, grantee, object_type dbadmin-> FROM grants dbadmin-> WHERE grantee = 'read_only' dbadmin-> AND object_type = 'ROLE'; object_name | grantee | object_type ----------------+-----------+------------- read_only_role | read_only | ROLE (1 row) But it’s a lot easier to use the HAS_ROLE function: dbadmin=> SELECT has_role('read_only', 'read_only_role'); has_role ---------- t (1 row) Have Fun!