Product Documentation

Appearance

Managing Database Users and Roles ​

You can create database users and roles in Yellowbrick Manager or by using standard SQL commands. Note the following behavior:

  • No default end-users or additional administrators are created during a VPC deployment, but some roles are created. (See Users and Roles.)
  • Database end-user accounts define an individual's access rights.
  • All new users belong to the consumer role, which is an empty role (no default privileges).
  • Any Yellowbrick user account that needs to connect to Yellowbrick Manager must contain the email domain suffix in the username (for example: dba1@ybdwcloud.com, where ybdwcloud.com would be replaced with the email domain for your VPC). Database users that will only ever connect from client applications do not need names that are qualified with the VPC domain (for example, dba2 and ybtools100 are valid database user names).
  • When you create new users and roles, be sure to grant them USAGE ON CLUSTER to at least one cluster. Individual users may be assigned a default cluster where their queries always run.

Creating database users and roles, then managing their access to the system, is the main mechanism for enforcing database security. Users and roles are instance-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.

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. 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.

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.

Parent topic:Database Administration