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 the privilege to run EXPLAIN on a query independently of allowing privilege to SELECT from the tables in the query, and you can grant the privilege to back up a database without also granting the privilege to restore it.

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:

• CREATE ROLE
• GRANT
• REVOKE

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

• sys.user
• sys.role
• sys.database

The following functions are useful for listing the privileges that have been granted to specific users and roles:

• HAS_DATABASE_PRIVILEGE
• HAS_FUNCTION_PRIVILEGE
• HAS_SCHEMA_PRIVILEGE
• HAS_SYSTEM_PRIVILEGE
• HAS_TABLE_PRIVILEGE

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.

Parent topic:Database Administration