I'm trying to use the System Tables to fetch some metadata about the whole Vertica cluster at a table level (including permissions).

Where can I find the information about permissions required to SELECT on a table?

I've poked around in the GRANTS and ROLES system tables, but I can't find any role->table mapping.

I considered using HAS_TABLE_PRIVILEGE() but that does it on user level. I'd rather do it at the role level.

  • What is your desired result? This may help – Kermit Jun 19 '14 at 20:33
  • I've actually found the answer (it's in the GRANTS table). I wasn't able to view them previously because I didn't have the required permissions. I can't post an answer though, because I can't answer my own question for 8 hours. – vinaykola Jun 20 '14 at 0:14
  • But you said you "poked around" in the GRANTS table... – Kermit Jun 20 '14 at 1:03
  • Yeah, the result was one row which contained the role I was assigned to, which led me to believe only roles would be present in the GRANTS table. I hadn't created any tables at that point. – vinaykola Jun 20 '14 at 5:02
SELECT * FROM grants ORDER BY grantor, grantee;

The privileges_description column shows the privilege types, such as (in your case) SELECT. (If you only want the SELECTs, you can refine that query.)

Source: Viewing Privileges Granted on Objects in the Admin Guide. (That's from the documentation for version 7.1.x.)


Privileges view for User

You can view the privileges which are assigned to user directly for user with following command:

-- here u_uat_Logging is the name of the User
SELECT * FROM grants where grantee='u_uat_Logging';

Privileges view for Role

You can view the privileges which is assigned using the role , for user with following command:

-- here uat_ccPlatform_Logging_readonly is the name of the Role
SELECT * FROM grants where grantee='uat_ccPlatform_Logging_readonly';

Other important tables to view the information

  1. users - to view the user info
  2. grants - to view the user's privileges
  3. roles - to view the roles
  4. client_auth - to view the authentications(IP restriction/allowed IP access)
  5. resource_pools - to view the resources pool info

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.