Understanding Users, Privileges, and Roles

This blog post was authored by Soniya Shah.

Every Vertica database has one or more users. When users connect to the database, they log in with credentials that a superuser defines. Database users should only have access to the database resources they need to perform their tasks. To navigate these necessities, Vertica has designated users, privileges, and roles.

About Users

Database users use the database in various ways, depending on their privileges and roles. They generally fall into one of three groups:
• Superuser: This user is often the database administrator and is automatically created when you create a new database. The superuser can perform all database operations, including granting and revoking privileges to other users and roles. The database superuser does NOT have the same privileges as the Linux superuser (root).
• Object owner: This user can create a particular database object, such as a table, schema, or view. By default, only an owner or superuser can act on a database object.
• Everyone else: All non-superuser or object owners are PUBLIC users. These users are granted the PUBLIC role. Object owners are considered public users for objects they do not own.

About Privileges

Privileges are a type of permission that lets users perform an action on a database object. Privileges are granted to or revoked from users or roles. Before Vertica executes a statement, it checks to see if the requesting user has the necessary privileges to perform the operation. For example, to let a user create a table, the owner or superuser must grant the user “create” privileges on the schema where the user wants to create the table. For more information, see Privileges Required for Common Database Operations in the Vertica documentation.

About Roles

A role is a collection of privileges, such as “administrator”. Superusers can grant to or revoke from one or more roles. Use roles to make managing permissions easier. Using roles avoids having to manually grant sets of privileges user by user. For example, several users might be assigned the “administrator” role.

You can also use roles to maintain consistency. For example, if you must grant multiple privileges to users individually, you could forget to assign a role. Using roles can help avoid this issue.

Superusers can grant or revoke privileges to or from the administrator role, and all users who are granted the role will be affected by the change. Vertica has five pre-defined roles:
• PUBLIC: The default role assigned automatically to each Vertica user.
• PSEUDOSUPERUSER: This role has all the privileges of the database superuser. However, this role cannot revoke or change any superuser privileges.
• DBADMIN: This role can create or drop users, schemas, and roles. This role can also view all system tables and view and terminate user sessions.
• DBDUSER: This role allows non-DBADMIN users to access Database Designer using command-line functions.
• SYSMONITOR: The DBADMIN can assign a user the SYSMONITOR role to grant access to specific monitoring utilities without granting full DBADMIN access.

You cannot drop or rename predefined roles. By default, all roles (except PUBLIC) must be enabled at the start of a user session.

The following diagram gives an overview of how users, privileges, and roles work together:



From this diagram, we see that role1 has the ability to perform INSERTS on table t1 and role2 can perform DELETES on table t1. User1 is given role1 while user2 is given role2. The superuser has all privileges on table 11.

Note: In MC, users, roles, and privileges are different. For more information about how this works in MC, see the Vertica documentation.

Examples

Let’s take a look at some examples to better understand users, roles, and privileges.

First, let’s create a user John and a role called administrator: => CREATE USER John IDENTIFIED BY ‘password’; => CREATE ROLE administrator; Then, let’s grant John usage privileges on the PUBLIC schema, and all privileges on the VMart database. => GRANT USAGE ON SCHEMA PUBLIC to John; => GRANT ALL ON DATABASE VMart TO John; Next, let’s grant INSERT privileges on mytable to the administrator role: => GRANT INSERT ON mytable TO administrator; Then, we can grant the administrator role to John. => GRANT administrator TO John; To verify that all these steps were performed, check the grants system table: => SELECT privileges_description, object_schema, object_name, object_type, grantee FROM grants WHERE grantee='John'; For more information, see Managing Users and Privileges in the Vertica documentation.