Create and Assign Roles

A role is a collection of privileges that can be granted to one or more users or roles. Assigning roles prevents you from having to manually grant sets of privileges for each individual user.

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.

Conclusion

That’s the simple workflow for implementing a Role in Vertica. You can perform other tasks like revoking privileges from a role, deleting roles, and disabling a user’s access to a role. For more information see About Database Roles in the Vertica product documentation.