GRANT

On this page Carat arrow pointing down

The GRANT statement controls each role or user's SQL privileges for interacting with specific databases, schemas, tables, or user-defined types. For privileges required by specific statements, see the documentation for the respective SQL statement.

You can use GRANT to directly grant privileges to a role or user, or you can grant membership to an existing role, which grants that role's privileges to the grantee. Users granted a privilege with WITH GRANT OPTION can in turn grant that privilege to others. The owner of an object implicitly has the GRANT OPTION for all privileges, and the GRANT OPTION is inherited through role memberships.

For new databases, users with the following roles are automatically granted the ALL privilege:

Note:

The GRANT statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Syntax

GRANT ALL PRIVILEGES ON grant_targets TYPE target_types ALL SEQUENCES TABLES FUNCTIONS PROCEDURES IN SCHEMA schema_name_list SYSTEM ALL PRIVILEGES privilege_list TO role_spec_list WITH GRANT OPTION privilege_list ON grant_targets TYPE target_types ALL SEQUENCES TABLES FUNCTIONS PROCEDURES IN SCHEMA schema_name_list TO role_spec_list WITH GRANT OPTION TO role_spec_list WITH ADMIN OPTION

Parameters

Parameter Description
ALL
ALL PRIVILEGES
Grant all privileges.
privilege_list A comma-separated list of privileges to grant. For guidelines, see Managing privileges.
grant_targets A comma-separated list of database, table, sequence, or function names. The list should be preceded by the object type (e.g., DATABASE mydatabase). If the object type is not specified, all names are interpreted as table or sequence names.
target_types A comma-separated list of user-defined types.
ALL SEQUENCES IN SCHEMA Grant privileges on all sequences in a schema or list of schemas.
ALL TABLES IN SCHEMA Grant privileges on all tables and sequences in a schema or list of schemas.
ALL FUNCTIONS IN SCHEMA Grant privileges on all user-defined functions in a schema or list of schemas.
schema_name_list A comma-separated list of schemas.
role_spec_list A comma-separated list of roles.
WITH ADMIN OPTION Designate the user as a role admin. Role admins can grant or revoke membership for the specified role.
WITH GRANT OPTION Allow the user to grant the specified privilege to others.

Supported privileges

Roles and users can be granted the following privileges:

Privilege Levels Description
ALL System, Database, Schema, Table, Sequence, Type For the object to which ALL is applied, grants all privileges at the system, database, schema, table, sequence, or type level.
BACKUP System, Database, Table Grants the ability to create backups at the system, database, or table level.
CANCELQUERY System Grants the ability to cancel queries.
CHANGEFEED Table Grants the ability to create changefeeds on a table.
CONNECT Database Grants the ability to view a database's metadata, which consists of objects in a database's information_schema and pg_catalog system catalogs. This allows the role to view the database's table, schemas, user-defined types, and list the database when running SHOW DATABASES. The CONNECT privilege is also required to run backups of the database.
CONTROLJOB System Grants the ability to pause, resume, and cancel jobs. Non-admin roles cannot control jobs created by admin roles.
CREATE Database, Schema, Table, Sequence Grants the ability to create objects at the database, schema, table, or sequence level. When applied at the database level, grants the ability to configure multi-region zone configs. In CockroachDB v23.2 and later, the cluster setting sql.auth.public_schema_create_privilege.enabled controls whether users receive CREATE privileges on the public schema or not. The setting applies at the time that the public schema is created, which happens whenever a database is created. The setting is true by default, but can be set to false for increased compatibility with PostgreSQL version 15 as described in this commit.
CREATEDB System Grants the ability to create or rename a database.
CREATELOGIN System Grants the ability to manage authentication using the WITH PASSWORD, VALID UNTIL, and LOGIN/NOLOGIN role options.
CREATEROLE System Grants the ability to create, modify, or delete non-admin roles.
DELETE Table, Sequence Grants the ability to delete objects at the table or sequence level.
DROP Database, Table, Sequence Grants the ability to drop objects at the database, table, or sequence level.
EXECUTE Function Grants the ability to execute functions.
EXTERNALCONNECTION System Grants the ability to connect to external systems such as object stores, key management systems, Kafka feeds, or external file systems. Often used in conjunction with the BACKUP, RESTORE, and CHANGEFEED privilege.
EXTERNALIOIMPLICITACCESS System Grants the ability to interact with external resources that require implicit access.
INSERT Table, Sequence Grants the ability to insert objects at the table or sequence level.
MODIFYCLUSTERSETTING System Grants the ability to modify cluster settings.
MODIFYSQLCLUSTERSETTING System Grants the ability to modify SQL cluster settings (cluster settings prefixed with sql.).
NOSQLLOGIN System Prevents roles from connecting to the SQL interface of a cluster.
RESTORE System, Database Grants the ability to restore backups at the system or database level. Refer to RESTORE Required privileges for more details.
SELECT Table, Sequence Grants the ability to run selection queries at the table or sequence level.
UPDATE Table, Sequence Grants the ability to run update statements at the table or sequence level.
USAGE Schema, Sequence, Type Grants the ability to use schemas, sequences, or user-defined types.
VIEWACTIVITY System Grants the ability to view other user's activity statistics of a cluster.
VIEWACTIVITYREDACTED System Grants the ability to view other user's activity statistics, but prevents the role from accessing the statement diagnostics bundle in the DB Console, and viewing some columns in introspection queries that contain data about the cluster.
VIEWCLUSTERMETADATA System Grants the ability to view range information, data distribution, store information, and Raft information.
VIEWCLUSTERSETTING System Grants the ability to view cluster settings and their values.
VIEWDEBUG System Grants the ability to view the Advanced Debug Page of the DB Console and work with the debugging and profiling endpoints.
VIEWJOB System Grants the ability to view jobs on the cluster.
VIEWSYSTEMTABLE System Grants read-only access (SELECT) on all tables in the system database, without granting the ability to modify the cluster. This privilege was introduced in v23.1.11.
ZONECONFIG Database, Table, Sequence Grants the ability to configure replication zones at the database, table, and sequence level.

Required privileges

  • To grant privileges, the user granting the privileges must also have the privilege being granted on the target database or tables. For example, a user granting the SELECT privilege on a table to another user must have the SELECT privileges on that table and WITH GRANT OPTION on SELECT.

  • To grant roles, the user granting role membership must be a role admin (i.e., members with the WITH ADMIN OPTION) or a member of the admin role. To grant membership to the admin role, the user must have WITH ADMIN OPTION on the admin role.

Details

Granting privileges

When a role or user is granted privileges for a table, the privileges are limited to the table. The user does not automatically get privileges to new or existing tables in the database. To grant privileges to a user on all new and/or existing tables in a database, see Grant privileges on all tables in a database.

For privileges required by specific statements, see the documentation for the respective SQL statement.

Granting roles

  • Users and roles can be members of roles.
  • The root user is automatically created as an admin role and assigned the ALL privilege for new databases.
  • All privileges of a role are inherited by all its members.
  • Membership loops are not allowed (direct: A is a member of B is a member of A or indirect: A is a member of B is a member of C ... is a member of A).

Limitations

User/role management operations (such as GRANT and REVOKE) are schema changes. As such, they inherit the limitations of schema changes.

For example, schema changes wait for concurrent transactions using the same resources as the schema changes to complete. In the case of role memberships being modified inside a transaction, most transactions need access to the set of role memberships. Using the default settings, role modifications require schema leases to expire, which can take up to 5 minutes.

This means that long-running transactions elsewhere in the system can cause user/role management operations inside transactions to take several minutes to complete. This can have a cascading effect. When a user/role management operation inside a transaction takes a long time to complete, it can in turn block all user-initiated transactions being run by your application, since the user/role management operation in the transaction has to commit before any other transactions that access role memberships (i.e., most transactions) can make progress.

If you want user/role management operations to finish more quickly, and do not care whether concurrent transactions will immediately see the side effects of those operations, set the session variable allow_role_memberships_to_change_during_transaction to true.

When this session variable is enabled, any user/role management operations issued in the current session will only need to wait for the completion of statements in other sessions where allow_role_memberships_to_change_during_transaction is not enabled.

To accelerate user/role management operations across your entire application, you have the following options:

  1. Set the session variable in all sessions by passing it in the client connection string.
  2. Apply the allow_role_memberships_to_change_during_transaction setting globally to an entire cluster using the ALTER ROLE ALL statement:

    icon/buttons/copy
    ALTER ROLE ALL SET allow_role_memberships_to_change_during_transaction = true;
    

Examples

Setup

To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr sample dataset preloaded:

icon/buttons/copy
$ cockroach demo

Grant privileges on databases

icon/buttons/copy
CREATE USER IF NOT EXISTS max WITH PASSWORD 'roach';
icon/buttons/copy
GRANT ALL ON DATABASE movr TO max WITH GRANT OPTION;
icon/buttons/copy
SHOW GRANTS ON DATABASE movr;
  database_name | grantee | privilege_type  | is_grantable
----------------+---------+-----------------+--------------
  movr          | admin   | ALL             | true
  movr          | max     | ALL             | true
  movr          | root    | ALL             | true
(3 rows)

Grant privileges on specific tables in a database

icon/buttons/copy
GRANT DELETE ON TABLE rides TO max;
icon/buttons/copy
SHOW GRANTS ON TABLE rides;
  database_name | schema_name | table_name | grantee | privilege_type  | is_grantable
----------------+-------------+------------+---------+-----------------+--------------
  movr          | public      | rides      | admin   | ALL             | true
  movr          | public      | rides      | max     | DELETE          | false
  movr          | public      | rides      | root    | ALL             | true
(3 rows)

Grant privileges on all tables in a database or schema

To grant all the privileges on existing tables to a user:

icon/buttons/copy
GRANT ALL ON * TO max;
icon/buttons/copy
SHOW GRANTS ON TABLE movr.public.*;
  database_name | schema_name |         table_name         | grantee | privilege_type | is_grantable
----------------+-------------+----------------------------+---------+----------------+---------------
  movr          | public      | promo_codes                | admin   | ALL            |      t
  movr          | public      | promo_codes                | demo    | ALL            |      t
  movr          | public      | promo_codes                | max     | ALL            |      f
  movr          | public      | promo_codes                | root    | ALL            |      t
  movr          | public      | rides                      | admin   | ALL            |      t
  movr          | public      | rides                      | demo    | ALL            |      t
  movr          | public      | rides                      | max     | ALL            |      f
  movr          | public      | rides                      | max     | UPDATE         |      t
  movr          | public      | rides                      | root    | ALL            |      t
  movr          | public      | user_promo_codes           | admin   | ALL            |      t
  movr          | public      | user_promo_codes           | demo    | ALL            |      t
  movr          | public      | user_promo_codes           | max     | ALL            |      f
  movr          | public      | user_promo_codes           | root    | ALL            |      t
  movr          | public      | users                      | admin   | ALL            |      t
  movr          | public      | users                      | demo    | ALL            |      t
  movr          | public      | users                      | max     | ALL            |      f
  movr          | public      | users                      | root    | ALL            |      t
  movr          | public      | vehicle_location_histories | admin   | ALL            |      t
  movr          | public      | vehicle_location_histories | demo    | ALL            |      t
  movr          | public      | vehicle_location_histories | max     | ALL            |      f
  movr          | public      | vehicle_location_histories | root    | ALL            |      t
  movr          | public      | vehicles                   | admin   | ALL            |      t
  movr          | public      | vehicles                   | demo    | ALL            |      t
  movr          | public      | vehicles                   | max     | ALL            |      f
  movr          | public      | vehicles                   | public  | SELECT         |      f
  movr          | public      | vehicles                   | root    | ALL            |      t
(26 rows)

To ensure that anytime a new table is created, all the privileges on that table are granted to a user, use ALTER DEFAULT PRIVILEGES:

icon/buttons/copy
ALTER DEFAULT PRIVILEGES FOR ALL ROLES GRANT ALL ON TABLES TO max;

To check that this is working as expected, create a table:

icon/buttons/copy
CREATE TABLE IF NOT EXISTS usertable(x INT);

Then, check that the all privileges on the newly created table are granted to the user you specified using SHOW GRANTS:

icon/buttons/copy
SHOW GRANTS ON TABLE usertable;
  database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+---------------
  movr          | public      | usertable  | admin   | ALL            |      t
  movr          | public      | usertable  | demo    | ALL            |      t
  movr          | public      | usertable  | max     | ALL            |      f
  movr          | public      | usertable  | root    | ALL            |      t
(4 rows)

Grant system-level privileges on the entire cluster

System-level privileges live above the database level and apply to the entire cluster.

root and admin users have system-level privileges by default, and are capable of granting it to other users and roles using the GRANT statement.

For example, the following statement allows the user maxroach to use the SET CLUSTER SETTING statement by assigning the MODIFYCLUSTERSETTING system privilege:

icon/buttons/copy
CREATE USER IF NOT EXISTS maxroach WITH PASSWORD 'setecastronomy';
icon/buttons/copy
GRANT SYSTEM MODIFYCLUSTERSETTING TO maxroach;

Make a table readable to every user in the system

icon/buttons/copy
GRANT SELECT ON TABLE vehicles TO public;
icon/buttons/copy
SHOW GRANTS ON TABLE vehicles;
  database_name | schema_name | table_name | grantee | privilege_type  | is_grantable
----------------+-------------+------------+---------+-----------------+--------------
  movr          | public      | vehicles   | admin   | ALL             | true
  movr          | public      | vehicles   | max     | SELECT          | false
  movr          | public      | vehicles   | public  | SELECT          | false
  movr          | public      | vehicles   | root    | ALL             | true
(4 rows)

Grant privileges on schemas

icon/buttons/copy
CREATE SCHEMA IF NOT EXISTS cockroach_labs;
icon/buttons/copy
GRANT ALL ON SCHEMA cockroach_labs TO max WITH GRANT OPTION;
icon/buttons/copy
SHOW GRANTS ON SCHEMA cockroach_labs;
  database_name |  schema_name   | grantee | privilege_type  | is_grantable
----------------+----------------+---------+-----------------+--------------
  movr          | cockroach_labs | admin   | ALL             | true
  movr          | cockroach_labs | max     | ALL             | true
  movr          | cockroach_labs | root    | ALL             | true
(3 rows)

Grant privileges on user-defined types

To grant privileges on user-defined types, use the following statements.

icon/buttons/copy
CREATE TYPE IF NOT EXISTS status AS ENUM ('available', 'unavailable');
icon/buttons/copy
GRANT ALL ON TYPE status TO max WITH GRANT OPTION;
icon/buttons/copy
SHOW GRANTS ON TYPE status;
  database_name | schema_name | type_name | grantee | privilege_type  | is_grantable
----------------+-------------+-----------+---------+-----------------+--------------
  movr          | public      | status    | admin   | ALL             | true
  movr          | public      | status    | demo    | ALL             | false
  movr          | public      | status    | max     | ALL             | true
  movr          | public      | status    | public  | USAGE           | false
  movr          | public      | status    | root    | ALL             | true
(5 rows)

Grant the privilege to manage the replication zones for a database or table

icon/buttons/copy
GRANT ZONECONFIG ON TABLE rides TO max;

The user max can then use the CONFIGURE ZONE statement to add, modify, reset, or remove replication zones for the table rides.

Grant role membership

icon/buttons/copy
CREATE ROLE IF NOT EXISTS developer WITH CREATEDB;
icon/buttons/copy
CREATE USER IF NOT EXISTS abbey WITH PASSWORD 'lincoln';
icon/buttons/copy
GRANT developer TO abbey;
icon/buttons/copy
SHOW GRANTS ON ROLE developer;
  role_name | member | is_admin  | is_grantable
------------+--------+-----------+-----------
  developer | abbey  |  false    | false
(1 row)

Grant the admin option

icon/buttons/copy
GRANT developer TO abbey WITH ADMIN OPTION;
icon/buttons/copy
SHOW GRANTS ON ROLE developer;
  role_name | member | is_admin  | is_grantable
------------+--------+-----------+-----------
  developer | abbey  |   true    | true
(1 row)

Grant privileges with the option to grant to others

icon/buttons/copy
GRANT UPDATE ON TABLE rides TO max WITH GRANT OPTION;
icon/buttons/copy
SHOW GRANTS ON TABLE rides;
  database_name | schema_name | table_name | grantee | privilege_type  | is_grantable
----------------+-------------+------------+---------+-----------------+--------------
  movr          | public      | rides      | admin   | ALL             | true
  movr          | public      | rides      | max     | UPDATE          | true
  movr          | public      | rides      | root    | ALL             | true
(3 rows)

See also


Yes No
On this page

Yes No