GRANT

On this page Carat arrow pointing down
Warning:
As of November 24, 2023, CockroachDB v22.1 is no longer supported. For more details, refer to the Release Support Policy.

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.

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 targets TYPE target_types ALL TABLES IN SCHEMA schema_name_list TO role_spec_list WITH GRANT OPTION privilege_list ON targets TYPE target_types ALL TABLES 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.
targets A comma-separated list of database or table names, preceded by the object type (e.g., DATABASE mydatabase).
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.
target_types A comma-separated list of user-defined types.
ALL TABLES IN SCHEMA Grant privileges on all tables 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.
privilege_list A comma-separated list of privileges to grant.
WITH ADMIN OPTION Designate the user as a role admin. Role admins can grant or revoke membership for the specified role.
WITH GRANT OPTION New in v22.1: Allow the user to grant the specified privilege to others.

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
CONNECT Database
SELECT Table
INSERT Table
DELETE Table
UPDATE Table
USAGE Schema, Type
ZONECONFIG Database, Table

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

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

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:

icon/buttons/copy
$ cockroach demo

Grant privileges on databases

icon/buttons/copy
> CREATE USER 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

icon/buttons/copy
> GRANT SELECT ON TABLE movr.public.* 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             | true
  movr          | public      | promo_codes                | max     | SELECT          | false
  movr          | public      | promo_codes                | root    | ALL             | true
  movr          | public      | rides                      | admin   | ALL             | true
  movr          | public      | rides                      | max     | DELETE          | false
  movr          | public      | rides                      | max     | SELECT          | false
  movr          | public      | rides                      | root    | ALL             | true
  movr          | public      | user_promo_codes           | admin   | ALL             | true
  movr          | public      | user_promo_codes           | max     | SELECT          | false
  movr          | public      | user_promo_codes           | root    | ALL             | true
  movr          | public      | users                      | admin   | ALL             | true
  movr          | public      | users                      | max     | SELECT          | false
  movr          | public      | users                      | root    | ALL             | true
  movr          | public      | vehicle_location_histories | admin   | ALL             | true
  movr          | public      | vehicle_location_histories | max     | SELECT          | false
  movr          | public      | vehicle_location_histories | root    | ALL             | true
  movr          | public      | vehicles                   | admin   | ALL             | true
  movr          | public      | vehicles                   | max     | SELECT          | false
  movr          | public      | vehicles                   | root    | ALL             | true
(19 rows)

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

icon/buttons/copy
> CREATE TYPE 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 developer WITH CREATEDB;
icon/buttons/copy
> CREATE USER 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