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.
Syntax
Parameters
Parameter | Description |
---|---|
ALL ALL PRIVILEGES |
Grant all privileges. |
targets |
A comma-separated list of database, schema, table, or user-defined type names, followed by the name of the object (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. |
name_list |
A comma-separated list of users and/or roles to whom to grant privileges. |
privilege_list ON ... |
Specify a comma-separated list of privileges to grant. |
privilege_list TO ... |
Specify a comma-separated list of roles whose membership to grant. |
WITH ADMIN OPTION |
Designate the user as a role admin. Role admins can grant or revoke membership for the specified role. |
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, Database |
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 theGRANT
andSELECT
privileges on that table.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 theadmin
role. To grant membership to theadmin
role, the user must haveWITH ADMIN OPTION
on theadmin
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 theadmin
role and has theALL
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 anadmin
role and assigned theALL
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:
$ cockroach demo
Grant privileges on databases
> CREATE USER max WITH PASSWORD roach;
> GRANT ALL ON DATABASE movr TO max;
> SHOW GRANTS ON DATABASE movr;
database_name | grantee | privilege_type
----------------+---------+-----------------
movr | admin | ALL
movr | max | ALL
movr | root | ALL
(3 rows)
Grant privileges on specific tables in a database
> GRANT DELETE ON TABLE rides TO max;
> 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
> GRANT SELECT ON TABLE movr.public.* TO max;
> 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 | SELECT
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
> GRANT SELECT ON TABLE vehicles TO public;
> 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
> CREATE SCHEMA cockroach_labs;
> GRANT ALL ON SCHEMA cockroach_labs TO max;
> 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
> CREATE TYPE status AS ENUM ('available', 'unavailable');
> GRANT ALL ON TYPE status TO max;
> SHOW GRANTS ON TYPE status;
database_name | schema_name | type_name | grantee | privilege_type
----------------+-------------+-----------+---------+-----------------
movr | public | status | admin | ALL
movr | public | status | demo | ALL
movr | public | status | max | ALL
movr | public | status | public | USAGE
movr | public | status | root | ALL
(5 rows)
Grant the privilege to manage the replication zones for a database or table
> 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
> CREATE ROLE developer WITH CREATEDB;
> CREATE USER abbey WITH PASSWORD lincoln;
> GRANT developer TO abbey;
> SHOW GRANTS ON ROLE developer;
role_name | member | is_admin
------------+--------+-----------
developer | abbey | false
(1 row)
Grant the admin option
> GRANT developer TO abbey WITH ADMIN OPTION;
> SHOW GRANTS ON ROLE developer;
role_name | member | is_admin
------------+--------+-----------
developer | abbey | true
(1 row)