The GRANT <privileges> statement lets you control 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.

Syntax

GRANT {ALL | <privileges...>} ON {DATABASE | SCHEMA | TABLE | TYPE} <targets...> TO <users...>

Parameters

Parameter Description
ALL Grant all privileges.
privileges A comma-separated list of privileges to grant. For a list of supported privileges, see Supported privileges.
targets A comma-separated list of database, schema, table, or user-defined type names.

Note:
To grant privileges on all tables in a database or schema, you can use GRANT ... ON TABLE *. For an example, see Grant privileges on all tables in a database or schema.
users A comma-separated list of users and/or roles to whom you want to grant privileges.

Supported privileges

Roles and users can be granted the following privileges:

Privilege Levels
ALL Database, Schema, Table, Type
CREATE Database, Schema, Table
DROP Database, Table
GRANT Database, Schema, Table, Type
SELECT Table
INSERT Table
DELETE Table
UPDATE Table
USAGE Schema, Type
ZONECONFIG Database, Table

Required privileges

The user granting 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 GRANT and SELECT privileges on that table.

Details

  • When a role or user is granted privileges for a database, new tables created in the database will inherit the privileges, but the privileges can then be changed.

    Note:

    The user does not get privileges to existing tables in the database. To grant privileges to a user on all existing tables in a database, see Grant privileges on all tables in a database

  • When a role or user is granted privileges for a table, the privileges are limited to the table.

  • The root user automatically belongs to the admin role and has the ALL privilege for new databases.

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

Examples

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

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

copy
icon/buttons/copy
$ cockroach demo

Grant privileges on databases

copy
icon/buttons/copy
> CREATE USER max WITH PASSWORD roach;
copy
icon/buttons/copy
> GRANT ALL ON DATABASE movr TO max;
copy
icon/buttons/copy
> SHOW GRANTS ON DATABASE movr;
  database_name |    schema_name     | grantee | privilege_type
----------------+--------------------+---------+-----------------
  movr          | cockroach_labs     | admin   | ALL
  movr          | cockroach_labs     | max     | ALL
  movr          | cockroach_labs     | root    | ALL
  movr          | crdb_internal      | admin   | ALL
  movr          | crdb_internal      | max     | ALL
  movr          | crdb_internal      | root    | ALL
  movr          | information_schema | admin   | ALL
  movr          | information_schema | max     | ALL
  movr          | information_schema | root    | ALL
  movr          | pg_catalog         | admin   | ALL
  movr          | pg_catalog         | max     | ALL
  movr          | pg_catalog         | root    | ALL
  movr          | pg_extension       | admin   | ALL
  movr          | pg_extension       | max     | ALL
  movr          | pg_extension       | root    | ALL
  movr          | public             | admin   | ALL
  movr          | public             | max     | ALL
  movr          | public             | root    | ALL
(18 rows)

Grant privileges on specific tables in a database

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

Grant privileges on all tables in a database or schema

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

Make a table readable to every user in the system

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

Grant privileges on schemas

copy
icon/buttons/copy
> CREATE SCHEMA cockroach_labs;
copy
icon/buttons/copy
> GRANT ALL ON SCHEMA cockroach_labs TO max;
copy
icon/buttons/copy
> SHOW GRANTS ON SCHEMA cockroach_labs;
  database_name |  schema_name   | grantee | privilege_type
----------------+----------------+---------+-----------------
  movr          | cockroach_labs | admin   | ALL
  movr          | cockroach_labs | max     | ALL
  movr          | cockroach_labs | root    | ALL
(3 rows)

Grant privileges on user-defined types

copy
icon/buttons/copy
> CREATE TYPE status AS ENUM ('available', 'unavailable');
copy
icon/buttons/copy
> GRANT ALL ON TYPE status TO max;
copy
icon/buttons/copy
> SHOW GRANTS ON TYPE status;
  database_name | schema_name | type_name | grantee | privilege_type
----------------+-------------+-----------+---------+-----------------
  movr          | public      | status    | admin   | ALL
  movr          | public      | status    | max     | ALL
  movr          | public      | status    | public  | USAGE
  movr          | public      | status    | root    | ALL
(4 rows)

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

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

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

See also



Yes No