Request for SQL code help
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?
Best Answers
-
Bryan_H Administrator
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 | bryan0 -
Bryan_H Administrator
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)0
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
Answers
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.
@Bryan_H Thank you! This worked perfectly and I was able to yield the results I wanted!