Request for SQL code help

Mmiller_rachel

Hello, I'm a beginner with Vertica.

Would someone be able to provide a SQL code that I can use to see which users have access to a particular schema?

Bryan_H

Access control is mainly found in the GRANTS system table. Look at the "grantee" to find what access a user has, for example:
dbadmin=> select * from grants where grantee = 'bryan';
grant_id | grantor_id | grantor | privileges_description | object_schema | object_name | object_id | object_type | grantee_id | grantee
-------------------+-------------------+---------+------------------------------------------------------+---------------+------------------------------+-------------------+--------------+-------------------+---------
45035996526453360 | 45035996273704962 | dbadmin | USAGE | | general | 45035996273704996 | RESOURCEPOOL | 45035996526453358 | bryan
45035996526454212 | 45035996273704962 | dbadmin | USAGE, CREATE | | bryan | 45035996526453950 | SCHEMA | 45035996526453358 | bryan
45035996526454750 | 45035996273704962 | dbadmin | SELECT, USAGE | | public | 45035996273704980 | SCHEMA | 45035996526453358 | bryan

Bryan_H

Select for object_type and object_name using ILIKE for case-insensitive match:
dbadmin=> select * from grants where object_type ILIKE 'schema' and object_name ILIKE 'bryan';
grant_id | grantor_id | grantor | privileges_description | object_schema | object_name | object_id | object_type | grantee_id | grantee
-------------------+-------------------+---------+--------------------------------------------------------------------------------------------+---------------+-------------+-------------------+-------------+-------------------+---------
45035996526454210 | 45035996273704962 | dbadmin | INSERT*, SELECT*, UPDATE*, DELETE*, ALTER*, REFERENCES*, DROP*, USAGE*, CREATE*, TRUNCATE* | | bryan | 45035996526453950 | SCHEMA | 45035996273704962 | dbadmin
45035996526454212 | 45035996273704962 | dbadmin | USAGE, CREATE | | bryan | 45035996526453950 | SCHEMA | 45035996526453358 | bryan
(2 rows)

Mmiller_rachel

Hi Bryan, thanks for the response! Yes, I'm aware of this code. However, I'm trying to see which user have access to a particular schema rather than which permissions are assigned to each user.

For example, I'd like to see how many users have read/write and read only access to schema X.

Mmiller_rachel

@Bryan_H Thank you! This worked perfectly and I was able to yield the results I wanted!