Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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_HBryan_H Administrator
    Answer ✓

    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_HBryan_H Administrator
    Answer ✓

    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)

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!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.