Managing Users and Permissions on Vertica environment

Like most database management systems, Vertica allows managing security for users in order to make sure each user gets only the permissions that he actually needs.

Here’s a cheat sheet I use for managing the users in organizations I consult to.

1. Get user permissions

Get user permissions

Transact-SQL

SELECT u.user_name,u.all_roles,u.default_roles,g.privileges_description,g.object_name FROM users u join grants g on u.user_name = g.grantee WHERE u.user_name like '%UserName%'

1

2

3

SELECT u.user_name,u.all_roles,u.default_roles,g.privileges_description,g.object_name

FROM users u join grants g on u.user_name = g.grantee

WHERE u.user_name like '%UserName%'

1. Create user with password

Create user with password

Transact-SQL

CREATE USER UserName IDENTIFIED BY 'MyPassword';

1

CREATE USER UserName IDENTIFIED BY 'MyPassword';

1. Change user password

Change user password

Transact-SQL

ALTER USER UserName IDENTIFIED BY 'new-password' REPLACE 'old-password';

1

ALTER USER UserName IDENTIFIED BY 'new-password' REPLACE 'old-password';

1. Grant all permissions to a user on a table

Grant all permissions to a user on a table

Transact-SQL

GRANT ALL ON TABLE SchemaName.TableName to UserName; -- Or if you like to grant specific privilege like: -- INSERT, SELECT, UPDATE, DELETE, REFERENCES GRANT SELECT ON TABLE SchemaName.TableName to UserName;

1

2

3

4

5

GRANT ALL ON TABLE SchemaName.TableName to UserName;

-- Or if you like to grant specific privilege like:

-- INSERT, SELECT, UPDATE, DELETE, REFERENCES

GRANT SELECT ON TABLE SchemaName.TableName to UserName;

1. Grant privilege on all tables

Grant privilege on all tables

Transact-SQL

GRANT SELECT ON ALL TABLES IN SchemaName.TableName to UserName;

1

GRANT SELECT ON ALL TABLES IN SchemaName.TableName to UserName;

1. Great all permissions to a user on a SCHEMA

Great all permissions to a user on a SCHEMA

Transact-SQL

GRANT ALL ON SCHEMA dwh to aolon_app; -- Or if you want to grant specific a privilege like: -- CREATE, USAGE GRANT SELECT ON SCHEMA SchemaName TO UserName;

1

2

3

4

5

GRANT ALL ON SCHEMA dwh to aolon_app;

-- Or if you want to grant specific a privilege like:

-- CREATE, USAGE

GRANT SELECT ON SCHEMA SchemaName TO UserName;

1. Set user as table owner

Set user as table owner

Transact-SQL

ALTER TABLE TableName OWNER TO UserName;

1

ALTER TABLE TableName OWNER TO UserName;

In addition to managing users, Vertica allow grouping users to roles in order to manage permissions for a group of users. I covered roles extensively here, but here are the parts which are relevant to users:

1. Add user to the Role

Transact-SQL

GRANT RoleName TO UserName;

1

GRANT RoleName TO UserName;

1. Remove user from Role

Transact-SQL

REVOKE RoleName FROM UserName;

1

REVOKE RoleName FROM UserName;

1. Add user default role (if you want a user to have a role, you must set him a default role)

Transact-SQL

ALTER USER UserName DEFAULT ROLE RoleName;

1

ALTER USER UserName DEFAULT ROLE RoleName;

1. Select which Users are in a role

Transact-SQL

SELECT object_schema,object_name,privileges_description,grantor,grantee FROM grants WHERE object_name like 'RoleName';

1

2

3

SELECT object_schema,object_name,privileges_description,grantor,grantee

FROM grants

WHERE object_name like 'RoleName';

1. Select which roles a user assigned to.

Transact-SQL

SELECT user_name,all_roles,default_roles FROM users WHERE user_name like 'UserName';

1

2

3

SELECT user_name,all_roles,default_roles

FROM users

WHERE user_name like 'UserName';

Read previous post in Vertica posts series- How to Create and Manage Security Roles in Vertica

#Securityroles #Vertica #Verticapostsseries #bigdata