User Security Auditing in Vertica

Are you a database administer who manages users and roles in your Vertica database? Do you need to keep track of who has permission to access certain database objects?

If so, here’s a quick guide to queries that help you review the users, roles, privileges, and permissions that you have configured in your database. These queries access one or more Vertica system tables to retrieve this information.

Users and Roles – The Basics

All Users

The V_CATALOG.USERS system table holds information about all your database users. For example, the following query returns the user id, user name, and a Boolean value telling you whether or not the user is a superuser:
=> SELECT user_id, user_name, is_superuser FROM users;
Users and their roles

You can also access information about roles from the USERS system table. The following query returns all your database users and, if applicable, all their roles, including default ones:
=> SELECT user_name, all_roles, default_roles FROM users;
Users who have roles

For more information about roles, query the V_CATALOG.ROLES system table. The following query returns only those database users who have assigned roles:
=> SELECT user_name,
role_name FROM (SELECT INSTR(all_roles, roles.name ) has_role,
user_name,
roles.name role_name FROM roles, users ) a
WHERE a.has_role > 0 ORDER BY 1;
Last Time User Opened a Session

Want to know when a user last started a session? You can find this information in the V_MONITOR.EXECUTION_ENGINE_PROFILES system table. The following query lists the last time each user started a database session:
=> SELECT a.user_name ,
MAX(b.session_start_timestamp) FROM execution_engine_profiles a,
user_sessions b WHERE a.session_id = b.session_id
GROUP BY a.user_name; 

Permissions

Users with permissions to access tables

Roles wouldn’t really be useful without the privileges and grants associated with them. That’’s why Vertica also contains a GRANTS system table. The following query returns all database tables along with which users have what permissions to access those tables:
=> SELECT privileges_description,
object_schema,
object_name,
object_type,
grantee FROM grants,
roles WHERE roles.role_id = grants.grantee_id;
Inherited permissions

You can also use the GRANTS and ROLES tables to examine inherited roles. The following query returns a list of tables and permissions that users have inherited from a role that’s assigned to them:
=> SELECT * FROM (SELECT
privileges_description,
object_schema,
object_name,
grantee granted_to ,
'DIRECT ACCESS' role_name from grants,
users WHERE grantee = user_name AND
object_type ='TABLE' UNION ALL
SELECT
privileges_description,
object_schema,
object_name,
user_name granted_to,
'ROLE: '|| role_name FROM
(SELECT
INSTR(all_roles, roles.name ) has_role,
user_name,
roles.name role_name from roles,
users WHERE user_name <>'dbadmin' ) a ,
grants WHERE a.has_role > 0 AND grantee = role_name
AND object_type ='TABLE' ) b
ORDER BY granted_to ,role_name;

House-Keeping

Unused roles

Sometimes you might want to do a little house-keeping with your users and roles. The following query lists all database roles that are not assigned to any user:
=> SELECT name FROM roles MINUS
SELECT DISTINCT role_name FROM
(SELECT INSTR(all_roles, roles.name ) has_role , user_name, roles.name
role_name FROM roles, users ) a WHERE a.has_role > 0;
Roles that may be duplicated based on the permissions that they have

Besides eliminating unused roles, you can further pare down your list of roles by listing which ones are, for all intents and purposes, identical. The following query returns roles that are equal to other roles because they have the same permissions. For example, if role A and role B both have access to a given table, both roles appear in the query results.
=> SELECT privileges_description,
object_schema,
object_name,
COUNT(*) FROM roles r,
grants g WHERE g.grantee = r.name AND object_type ='TABLE'
GROUP BY privileges_description,
object_schema,
object_name HAVING count(*) > 1;
Roles that have duplicate permissions

When you assign a role to a user, Vertica doesn’t check whether that user already has that role. Use the following query to list users who have been assigned the same role more than once:
=> SELECT
r2.name,
g2.privileges_description,
g2.object_schema,
g2.object_name
FROM roles r2 , grants g2,
(SELECT
privileges_description,
object_schema,
object_name FROM roles r,
grants g WHERE g.grantee = r.name
AND object_type ='TABLE'
GROUP BY privileges_description,
object_schema,
object_name HAVING COUNT (*) > 1 ) t
WHERE g2.grantee = r2.name
AND g2.object_type ='TABLE'
AND g2.privileges_description = t.privileges_description
AND g2.object_schema = t.object_schema
AND g2.object_name = t.object_name;
Tables that have not been granted any privileges

Want to see a list of tables you might be missing in your privilege granting endeavors? The following query returns a list of tables that have not been granted any privileges:
=> SELECT table_schema||'.'||table_name FROM tables
MINUS
SELECT object_schema||'.'|| object_name FROM grants
WHERE object_type ='TABLE';

Embedded Roles

Roles that have been granted additional roles

Roles can have additional roles themselves, called embedded roles. Use the following query to list all roles that have been granted one or more additional roles:
=> SELECT a.name,
b.name FROM roles a,
roles b WHERE LENGTH(a.assigned_roles) > 0
AND INSTR( a.assigned_roles ,b.name) > 0;
Users assigned to roles that have additional roles

Following the previous query, you can use this query to see which users are associated with embedded roles:
=> SELECT user_name,
role_name FROM (SELECT INSTR(all_roles, roles.name ) has_role,
user_name,
roles.name role_name FROM roles,
users WHERE LENGTH(assigned_roles) > 0 ) a
WHERE a.has_role > 0
ORDER BY 1;
Embedded roles granted to users

View the embedded roles for a user with the following query:
=> SELECT user_name,
role_name role_assigned,
assigned_roles embedded_roles FROM
(SELECT INSTR(all_roles, roles.name ) has_role,
user_name,
roles.name role_name,
assigned_roles from roles,
users WHERE LENGTH(assigned_roles)>0 ) a
WHERE a.has_role > 0
ORDER BY 1 ;

For More Information

In the Vertica documentation, see

• Managing Users and Privileges
• System tables used in these queries:
  • USERS
  • ROLES
  • GRANTS
  • EXECUTION_ENGINE_PROFILES