How to Create and Manage Security Roles in Vertica

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:

1. Create Role

Create Role

Transact-SQL

CREATE ROLE <Role_Name>;

1

CREATE ROLE <Role_Name>;

1. 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>;

1. 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>;;

1. 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>;

1. 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>;

1. 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:

1. 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>’;

1. 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>';

#Securityroles #Vertica #Verticapostsseries #bigdata