# Authorization

User authorization is the act of defining access policies for authenticated CockroachDB users. CockroachDB allows you to create, manage, and remove your cluster's users and assign SQL-level privileges to the users. Additionally, if you have an Enterprise license, you can use role-based access management (RBAC) for simplified user management.

## Create and manage users

You can use either of the following methods to create and manage users:

- Use the `CREATE USER` and `DROP USER` statements to create and remove users.
- Use the `cockroach user` command with appropriate flags.

## Create and manage roles

Roles are SQL groups that contain any number of users and roles as members.

### Terminology

Term | Description
--- | ---
Role | A group containing any number of users or other roles. Note: All users belong to the `public` role, to which you can grant and revoke privileges.
Role admin | A member of the role that's allowed to modify role membership. To create a role admin, use `WITH ADMIN OPTION`.
Superuser / Admin | A member of the `admin` role. Only superusers can `CREATE ROLE` or `DROP ROLE`. The `admin` role is created by default and cannot be dropped.
`root` | A user that exists by default as a member of the `admin` role. The `root` user must always be a member of the `admin` role.
Inherit | The behavior that grants a role's privileges to its members.
Direct member | A user or role that is an immediate member of the role. Example: `A` is a member of `B`.
Indirect member | A user or role that is a member of the role by association. Example: `A` is a member of `C` ... is a member of `B` where "..." is an arbitrary number of memberships.

To create and manage your cluster's roles, use the following statements:

- `CREATE ROLE` (Enterprise)
- `DROP ROLE` (Enterprise)
- `GRANT <roles>` (Enterprise)
- `REVOKE <roles>` (Enterprise)
- `GRANT <privileges>`
- `REVOKE <privileges>`
- `SHOW ROLES`
- `SHOW GRANTS`

## Assign privileges

In CockroachDB, privileges are granted to users and roles at the database and table levels. They are not yet supported for other granularities such as columns or rows.

When a user connects to a database, either via the built-in SQL client or a client driver, CockroachDB checks the user and role's privileges for each statement executed. If the user does not have sufficient privileges for a statement, CockroachDB gives an error.

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

### Supported privileges

For a full list of supported privileges, see the `GRANT` documentation.

### Granting privileges

To grant privileges to a role or user, use the `GRANT` statement, for example:

    > GRANT SELECT, INSERT ON bank.accounts TO maxroach;

### Showing privileges

To show privileges granted to roles or users, use the `SHOW GRANTS` statement, for example:

    > SHOW GRANTS ON DATABASE bank FOR maxroach;

### Revoking privileges

To revoke privileges from roles or users, use the `REVOKE` statement, for example:

    > REVOKE INSERT ON bank.accounts FROM maxroach;

## Example

For the purpose of this example, you need an enterprise license and one CockroachDB node running in insecure mode:

    $ cockroach start \
    --insecure \
    --store=roles \
    --listen-addr=localhost:26257

1. As the `root` user, use the `cockroach user` command to create a new user, `maxroach`:

       $ cockroach user set maxroach --insecure

2. As the `root` user, open the built-in SQL client:

       $ cockroach sql --insecure

3. Create a database and set it as the default:

       > CREATE DATABASE test_roles;

       > SET DATABASE = test_roles;

4. Create a role and then list all roles in your database:

       > CREATE ROLE system_ops;

       > SHOW ROLES;

       +------------+
       |  rolename  |
       +------------+
       | admin      |
       | system_ops |
       +------------+

5. Grant privileges to the `system_ops` role you created:

       > GRANT CREATE, SELECT ON DATABASE test_roles TO system_ops;

       > SHOW GRANTS ON DATABASE test_roles;

       +------------+--------------------+------------+------------+
       |  Database  |       Schema       |    User    | Privileges |
       +------------+--------------------+------------+------------+
       | test_roles | crdb_internal      | admin      | ALL        |
       | test_roles | crdb_internal      | root       | ALL        |
       | test_roles | crdb_internal      | system_ops | CREATE     |
       | test_roles | crdb_internal      | system_ops | SELECT     |
       | test_roles | information_schema | admin      | ALL        |
       | test_roles | information_schema | root       | ALL        |
       | test_roles | information_schema | system_ops | CREATE     |
       | test_roles | information_schema | system_ops | SELECT     |
       | test_roles | pg_catalog         | admin      | ALL        |
       | test_roles | pg_catalog         | root       | ALL        |
       | test_roles | pg_catalog         | system_ops | CREATE     |
       | test_roles | pg_catalog         | system_ops | SELECT     |
       | test_roles | public             | admin      | ALL        |
       | test_roles | public             | root       | ALL        |
       | test_roles | public             | system_ops | CREATE     |
       | test_roles | public             | system_ops | SELECT     |
       +------------+--------------------+------------+------------+

6. Add the `maxroach` user to the `system_ops` role:

       > GRANT system_ops TO maxroach;

7. To test the privileges you just added to the `system_ops` role, use `\q` or `ctrl-d` to exit the interactive shell, and then open the shell again as the `maxroach` user (who is a member of the `system_ops` role):

       $ cockroach sql --user=maxroach --database=test_roles --insecure

8. As the `maxroach` user, create a table:

       > CREATE TABLE employees (
           id UUID DEFAULT uuid_v4()::UUID PRIMARY KEY,
           profile JSONB
         );

   We were able to create the table because `maxroach` has `CREATE` privileges.

9. As the `maxroach` user, try to drop the table:

       > DROP TABLE employees;

       pq: user maxroach does not have DROP privilege on relation employees

   You cannot drop the table because your current user (`maxroach`) is a member of the `system_ops` role, which doesn't have `DROP` privileges.

10. `maxroach` has `CREATE` and `SELECT` privileges, so try a `SHOW` statement:

        > SHOW GRANTS ON TABLE employees;

        +------------+--------+-----------+------------+------------+
        |  Database  | Schema |   Table   |    User    | Privileges |
        +------------+--------+-----------+------------+------------+
        | test_roles | public | employees | admin      | ALL        |
        | test_roles | public | employees | root       | ALL        |
        | test_roles | public | employees | system_ops | CREATE     |
        | test_roles | public | employees | system_ops | SELECT     |
        +------------+--------+-----------+------------+------------+

11. Now switch back to the `root` user to test more of the SQL statements related to roles. Use `\q` or `ctrl-d` to exit the interactive shell, and then open the shell again as the `root` user:

        $ cockroach sql --insecure

12. As the `root` user, revoke privileges and then drop the `system_ops` role:

        > REVOKE ALL ON DATABASE test_roles FROM system_ops;

        > SHOW GRANTS ON DATABASE test_roles;

        +------------+--------------------+-------+------------+
        |  Database  |       Schema       | User  | Privileges |
        +------------+--------------------+-------+------------+
        | test_roles | crdb_internal      | admin | ALL        |
        | test_roles | crdb_internal      | root  | ALL        |
        | test_roles | information_schema | admin | ALL        |
        | test_roles | information_schema | root  | ALL        |
        | test_roles | pg_catalog         | admin | ALL        |
        | test_roles | pg_catalog         | root  | ALL        |
        | test_roles | public             | admin | ALL        |
        | test_roles | public             | root  | ALL        |
        +------------+--------------------+-------+------------+

        > REVOKE ALL ON TABLE test_roles.* FROM system_ops;

        > SHOW GRANTS ON TABLE test_roles.*;

        +------------+--------+-----------+-------+------------+
        |  Database  | Schema |   Table   | User  | Privileges |
        +------------+--------+-----------+-------+------------+
        | test_roles | public | employees | admin | ALL        |
        | test_roles | public | employees | root  | ALL        |
        +------------+--------+-----------+-------+------------+

    Note:
    All of a role or user's privileges must be revoked before it can be dropped.

        > DROP ROLE system_ops;

## See also

- Client Connection Parameters
- SQL Statements
- `CREATE ROLE`
- `DROP ROLE`
- `SHOW ROLES`
- `GRANT <privileges>`
- `GRANT <roles>` (Enterprise)
- `REVOKE <privileges>`
- `REVOKE <roles>` (Enterprise)
- `SHOW GRANTS`