# GRANT <privileges>

The `GRANT <privileges>` statement lets you control each role or user's SQL privileges for interacting with specific databases and tables.

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

## Synopsis

## Required privileges

New in v20.1 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.

## Supported privileges

Roles and users can be granted the following privileges. Some privileges are applicable both for databases and tables, while other are applicable only for tables (see Levels in the table below).

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

Privilege | Levels
--- | ---
`ALL` | Database, Table
`CREATE` | Database, Table
`DROP` | Database, Table
`GRANT` | Database, Table
`SELECT` | Table
`INSERT` | Table
`DELETE` | Table
`UPDATE` | Table
New in v20.1 `ZONECONFIG` | Database, Table

## Parameters

Parameter | Description
--- | ---
`table_name` | A comma-separated list of table names. Alternately, to grant privileges to all tables, use `*`. `ON TABLE table.*` grants apply to all existing tables in a database but will not affect tables created after the grant.
`database_name` | A comma-separated list of database names. Privileges granted on databases will be inherited by any new tables created in the databases, but do not affect existing tables in the database.
`user_name` | A comma-separated list of users and/or roles to whom you want to grant privileges.

## Examples

### Grant privileges on databases

    > GRANT CREATE ON DATABASE db1, db2 TO maxroach, betsyroach;

    > SHOW GRANTS ON DATABASE db1, db2;

    +----------+------------+------------+
    | Database |    User    | Privileges |
    +----------+------------+------------+
    | db1      | betsyroach | CREATE     |
    | db1      | maxroach   | CREATE     |
    | db1      | root       | ALL        |
    | db2      | betsyroach | CREATE     |
    | db2      | maxroach   | CREATE     |
    | db2      | root       | ALL        |
    +----------+------------+------------+
    (6 rows)

### Grant privileges on specific tables in a database

    > GRANT DELETE ON TABLE db1.t1, db1.t2 TO betsyroach;

    > SHOW GRANTS ON TABLE db1.t1, db1.t2;

    +-------+------------+------------+
    | Table |    User    | Privileges |
    +-------+------------+------------+
    | t1    | betsyroach | DELETE     |
    | t1    | root       | ALL        |
    | t2    | betsyroach | DELETE     |
    | t2    | root       | ALL        |
    +-------+------------+------------+
    (4 rows)

### Grant privileges on all tables in a database

    > GRANT SELECT ON TABLE db2.* TO henryroach;

    > SHOW GRANTS ON TABLE db2.*;

    +-------+------------+------------+
    | Table |    User    | Privileges |
    +-------+------------+------------+
    | t1    | henryroach | SELECT     |
    | t1    | root       | ALL        |
    | t2    | henryroach | SELECT     |
    | t2    | root       | ALL        |
    +-------+------------+------------+
    (4 rows)

### Make a table readable to every user in the system

    > GRANT SELECT ON TABLE myTable TO public;

    > SHOW GRANTS ON TABLE myTable;

      database_name | schema_name | table_name | grantee | privilege_type
    +---------------+-------------+------------+---------+----------------+
      defaultdb     | public      | mytable    | admin   | ALL
      defaultdb     | public      | mytable    | public  | SELECT
      defaultdb     | public      | mytable    | root    | ALL
    (3 rows)

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

    > GRANT ZONECONFIG ON TABLE mytable TO myuser;

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

## See also

- Authorization
- `REVOKE <roles>`
- `GRANT <roles>`
- `REVOKE <privileges>`
- `SHOW GRANTS`
- `SHOW ROLES`
- `CONFIGURE ZONE`
- Manage Users