
For the most part, creating and assigning roles is fairly straightforward. However, the user to which roles are assigned needs to take action to access the role’s privileges.
When you create a new user, Vertica automatically assigns that user the PUBLIC role. In addition, Vertica automatically enables the PUBLIC role for the new user. For more information see PUBLIC Role in the Vertica product documentation.
This blog provides a quick tutorial on:
• Creating a role
• Assigning privileges to a role
• Granting a user access to the role
• Having the user enable the role
DBADMIN Tasks
As the dbadmin user, perform the following steps on an existing user called User001:1. Create a new role:
dbadmin=> CREATE ROLE NewRoleDemo;
CREATE ROLE
See Creating Database Roles.2. You now need to grant privileges to the new role using GRANT Statements:
dbadmin=> GRANT USAGE ON SCHEMA schema_1 TO NewRoleDemo;
GRANT PRIVILEGE
This command grants the usage privilege on schema_1 to your role NewRoleDemo. Continue granting privileges as necessary. 3. You now have a role with some privileges and you need to grant the role to your user User001 with the GRANT (Role) statement:
dbadmin=> GRANT NewRoleDemo to User001;
GRANT ROLE
You can perform other operations with the GRANT (Role) statement. For example, you can use the WITH ADMIN OPTION parameter to allow User001 to give the user administrative access to the role. This allows User001 to grant the role to other users.
User Task
OK, you have a role with privileges assigned to User001. The dbadmin’s work here is done. It is now up to User001 to enable the role for the user account:User001=> SET ROLE NewRoleDemo;
SET
A DBADMIN can make life easier for users by enabling roles by default:
dbadmin=> ALTER USER User001 default role NewRoleDemo;
This automatically grants the NewRoleDemo role to User001 without User001 having to run SET ROLE. In addition, as a user you can check to see what roles have been assigned to you:
User001=> SHOW AVAILABLE_ROLES;
name | setting
---------------------------------------
available roles | Public, NewRoleDemo
To check to see what roles are enabled for you (remember you can enable roles using SET ROLE):
User001=> SHOW ENABLED_ROLES;
name | setting
---------------------------------------
enabled roles | Public
These commands are helpful if you are not sure if your administrator assigned you a role by default.