Where do I find the tables that a role has access to in Vertica?

Question

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.

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 00:14
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 05:02

score 4 · Accepted Answer · edited Dec 17 '14 at 13:29

4

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.)

edited Dec 17 '14 at 13:29

Kermit

1,194
13
27

answered Dec 16 '14 at 22:45

Monica Cellio

156
6

score 0 · Answer 2 · answered Jan 02 '20 at 09:08

0

you can view the privileges for user with following command. SELECT * FROM grants where grantee='u_uat_Logging'; -- here SELECT * FROM grants where grantee='uat_ccPlatform_Logging_readonly';

answered Jan 02 '20 at 09:08

ROHIT VERMA

1

score 0 · Answer 3 · edited Jan 02 '20 at 13:54

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

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

Where do I find the tables that a role has access to in Vertica?

3 Answers3