Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31
All platforms

Managing Database Users and Roles ​

Creating database users and roles, then managing their access to the system, is the main mechanism for enforcing database security. Users and roles are system-wide objects; they do not belong to specific databases. Users are independent accounts with login access, while roles define groups of users that accrue privileges through role membership.

You can create database users and roles by using standard SQL commands, or through Yellowbrick Manager on cloud platforms. After creating users and roles, you can use GRANT and REVOKE commands at different levels of the system to create a fine-grained security model. Objects in the system can be individually secured or secured in the context of a schema, database, or all databases.

You can grant access privileges (ACLs) on various database objects (or on the system itself) to one or more users or roles. Objects in the system can be individually secured or secured in the context of a schema, database, or all databases. For example, you can grant specific privileges so specific users can:

  • Run EXPLAIN on a query but not be able to SELECT from the tables in the query
  • Use INSERT INTO SELECT on a table without having the privilege to bulk load it (which requires BULK LOAD privilege on the database)
  • Back up databases without being able to restore them

You also have flexibility in terms of the definition of roles and users. For example, you can create users with membership in an admin role, but then revoke specific privileges that an admin would normally hold. Conversely, a regular user (non-admin) can be granted certain privileges that would normally belong to an admin. You can create roles that have users (members), then assign privileges to roles. By default, these privileges are inherited by the users that belong to a role.

Cloud Platform Behavior ​

Note that on cloud platforms, behavior is slightly different, notably:

  • No default end-users or additional administrators are created during deployment, but some roles are created. (See Users and Roles.)
  • All new users belong to the consumer role, which is an empty role (no default privileges).
  • Be sure to grant new users and roles usage of at least one compute cluster with USAGE ON CLUSTER.
  • Individual users may be assigned to a default cluster where their queries always run, see ALTER USER SET DEFAULT CLUSTER for more information.

Useful Links ​

For more details and examples, see the following SQL commands:

In ybsql, use the \du command to see the current list of database users and roles. You can also query the following system views:

A set of HAS_*_PRIVILEGE functions are useful for listing the privileges that have been granted to specific users and roles. See System Functions.

You can create schemas in databases and views on tables as other means of access control. For example, you can create a view on a table that projects only certain columns, then you can give roles access to the view but not the table.

For information about managing users and roles on appliance platforms via an LDAP server, see LDAP Integration.