If you have many users in Vertica, managing their permissions can be a hassle.
If you’d like to grant access privileges on some tables to a certain user, you can collect all privileges on several tables and grant the user access to a role (instead of granting access to each table directly).
You can also add other users to the role, and they will get the privileges that have been granted to the role.
By grouping your users to roles, you can reduce the amount of overhead you have when managing user permissions. Instead of granting or altering permissions on a user basis, you can do that on a role basis, which will be affected to all of the users that are included in the role.
One last thing to take into account: Roles just give additional privileges to the user, but don’t cancel other permissions that were granted to the user outside of the role. For instance, a user can have grant permissions on a table that is not in the role, or a revoke from a table that in the role.
And now for the scripts:
Create Role
Create Role
Transact-SQL
CREATE ROLE <Role_Name>;
1
CREATE ROLE <Role_Name>;
Grant SELECT on some tables to the Role
Grant SELECT on some tables to the Role
Transact-SQL
GRANT SELECT ON <Table_Name> TO <Role_Name>;
1
GRANT SELECT ON <Table_Name> TO <Role_Name>;
Grant permission on ALL tables to a role
Grant permission to role on ALL tables
Transact-SQL
GRANT SELECT ON ALL TABLES IN SCHEMA <Schema_Name> TO <Role_Name>;;
1
GRANT SELECT ON ALL TABLES IN SCHEMA <Schema_Name> TO <Role_Name>;;
Add a user to the role
Add user to the role
Transact-SQL
GRANT <Role_Name> TO <User_Name>;
1
GRANT <Role_Name> TO <User_Name>;
Add a default role to a user
Add user default role
Transact-SQL
ALTER USER <User_Name> DEFAULT ROLE <Role_Name>;
1
ALTER USER <User_Name> DEFAULT ROLE <Role_Name>;
Remove user from a role
Remove user from role
Transact-SQL
REVOKE <Role_Name> FROM console_prod;
1
REVOKE <Role_Name> FROM console_prod;
After you’ve create the role and added users to it, you can check which tables and which users are related to the role:
Select which tables are in the role
Select which tables in the role
Transact-SQL
SELECT object_schema,object_name,privileges_description,grantor,grantee FROM grants WHERE grantee = '<Role_Name>’;
1
2
3
SELECT object_schema,object_name,privileges_description,grantor,grantee
FROM grants
WHERE grantee = '<Role_Name>’;
Select which Users are in the role
Select which Users in the role
Transact-SQL
SELECT object_schema,object_name,privileges_description,grantor,grantee FROM grants WHERE object_name like ‘<Role_Name>';
1
2
3
SELECT object_schema,object_name,privileges_description,grantor,grantee
FROM grants
WHERE object_name like ‘<Role_Name>';