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
Comments