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.
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%'
Create user with password
Create user with password
Transact-SQL
CREATE USER UserName IDENTIFIED BY 'MyPassword';
1
CREATE USER UserName IDENTIFIED BY 'MyPassword';
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';
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;
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;
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;
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:
Add user to the Role
Transact-SQL
GRANT RoleName TO UserName;
1
GRANT RoleName TO UserName;
Remove user from Role
Transact-SQL
REVOKE RoleName FROM UserName;
1
REVOKE RoleName FROM UserName;
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;
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';
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