Managing Database Users

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.

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 superusers, but then revoke specific privileges that a superuser would normally hold. Conversely, a regular user (non-superuser) can be granted certain privileges that would normally belong to a superuser. 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:
The following functions are useful for listing the privileges that have been granted to specific users and roles:

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 via an LDAP server, see LDAP Integration.