> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Common access management queries

> This article shows the basics of defining SQL users and roles and applying those privileges and permissions to databases, tables, rows, and columns.

<Tip>
  **Self-managed**

  If you're working with self-managed ClickHouse please see [SQL users and roles](/docs/concepts/features/security/access-rights).
</Tip>

This article shows the basics of defining SQL users and roles and applying those privileges and permissions to databases, tables, rows, and columns.

<h2 id="admin-user">
  Admin user
</h2>

ClickHouse Cloud services have an admin user, `default`, that is created when the service is created.  The password is provided at service creation, and it can be reset by ClickHouse Cloud users that have the **Admin** role.

When you add additional SQL users for your ClickHouse Cloud service, they will need a SQL username and password.  If you want them to have administrative-level privileges, then assign the new users the role `default_role`. For example, adding user `clickhouse_admin`:

```sql theme={null}
CREATE USER IF NOT EXISTS clickhouse_admin
IDENTIFIED WITH sha256_password BY 'P!@ssword42!';
```

```sql theme={null}
GRANT default_role TO clickhouse_admin;
```

<Note>
  When using the SQL Console, your SQL statements won't be run as the `default` user. Instead, statements will be run as a user named `sql-console:${cloud_login_email}`, where `cloud_login_email` is the email of the user currently running the query.

  These automatically generated SQL Console users have the `default` role.
</Note>

<h2 id="passwordless-authentication">
  Passwordless authentication
</h2>

There are two roles available for SQL console: `sql_console_admin` with identical permissions to `default_role` and `sql_console_read_only` with read-only permissions.

Admin users are assigned the `sql_console_admin` role by default, so nothing changes for them. However, the `sql_console_read_only` role allows non-admin users to be granted read-only or full access to any instance. An admin needs to configure this access. The roles can be adjusted using the `GRANT` or `REVOKE` commands to better fit instance-specific requirements, and any modifications made to these roles will be persisted.

<h3 id="granular-access-control">
  Granular access control
</h3>

This access control functionality can also be configured manually for user-level granularity. Before assigning the new `sql_console_*` roles to users, SQL console user-specific database roles matching the namespace `sql-console-role:<email>` should be created. For example:

```sql theme={null}
CREATE ROLE OR REPLACE sql-console-role:<email>;
GRANT <some grants> TO sql-console-role:<email>;
```

When a matching role is detected, it will be assigned to the user instead of the boilerplate roles. This introduces more complex access control configurations, such as creating roles like `sql_console_sa_role` and `sql_console_pm_role`, and granting them to specific users. For example:

```sql theme={null}
CREATE ROLE OR REPLACE sql_console_sa_role;
GRANT <whatever level of access> TO sql_console_sa_role;
CREATE ROLE OR REPLACE sql_console_pm_role;
GRANT <whatever level of access> TO sql_console_pm_role;
CREATE ROLE OR REPLACE `sql-console-role:christoph@clickhouse.com`;
CREATE ROLE OR REPLACE `sql-console-role:jake@clickhouse.com`;
CREATE ROLE OR REPLACE `sql-console-role:zach@clickhouse.com`;
GRANT sql_console_sa_role to `sql-console-role:christoph@clickhouse.com`;
GRANT sql_console_sa_role to `sql-console-role:jake@clickhouse.com`;
GRANT sql_console_pm_role to `sql-console-role:zach@clickhouse.com`;
```

<h2 id="test-admin-privileges">
  Test admin privileges
</h2>

Log out as the user `default` and log back in as user `clickhouse_admin`.

All of these should succeed:

```sql theme={null}
SHOW GRANTS FOR clickhouse_admin;
```

```sql theme={null}
CREATE DATABASE db1
```

```sql theme={null}
CREATE TABLE db1.table1 (id UInt64, column1 String) ENGINE = MergeTree() ORDER BY id;
```

```sql theme={null}
INSERT INTO db1.table1 (id, column1) VALUES (1, 'abc');
```

```sql theme={null}
SELECT * FROM db1.table1;
```

```sql theme={null}
DROP TABLE db1.table1;
```

```sql theme={null}
DROP DATABASE db1;
```

<h2 id="non-admin-users">
  Non-admin users
</h2>

Users should have the privileges necessary, and not all be admin users. The rest of this document provides example scenarios and the roles required.

<h3 id="preparation">
  Preparation
</h3>

Create these tables and users to be used in the examples.

<h4 id="creating-a-sample-database-table-and-rows">
  Creating a sample database, table, and rows
</h4>

<Steps>
  <Step title="Create a test database" id="create-a-test-database">
    ```sql theme={null}
    CREATE DATABASE db1;
    ```
  </Step>

  <Step title="Create a table" id="create-a-table">
    ```sql theme={null}
    CREATE TABLE db1.table1 (
       id UInt64,
       column1 String,
       column2 String
    )
    ENGINE MergeTree
    ORDER BY id;
    ```
  </Step>

  <Step title="Populate the table with sample rows" id="populate">
    ```sql theme={null}
    INSERT INTO db1.table1
       (id, column1, column2)
    VALUES
       (1, 'A', 'abc'),
       (2, 'A', 'def'),
       (3, 'B', 'abc'),
       (4, 'B', 'def');
    ```
  </Step>

  <Step title="Verify the table" id="verify">
    ```sql title="Query" theme={null}
    SELECT *
    FROM db1.table1
    ```

    ```response title="Response" theme={null}
    Query id: 475015cc-6f51-4b20-bda2-3c9c41404e49

    ┌─id─┬─column1─┬─column2─┐
    │  1 │ A       │ abc     │
    │  2 │ A       │ def     │
    │  3 │ B       │ abc     │
    │  4 │ B       │ def     │
    └────┴─────────┴─────────┘
    ```
  </Step>

  <Step title={<>Create <code>column_user</code></>} id="create-a-user-with-restricted-access-to-columns">
    Create a regular user that will be used to demonstrate restrict access to certain columns:

    ```sql theme={null}
    CREATE USER column_user IDENTIFIED BY 'password';
    ```
  </Step>

  <Step title={<>Create <code>row_user</code></>} id="create-a-user-with-restricted-access-to-rows-with-certain-values">
    Create a regular user that will be used to demonstrate restricting access to rows with certain values:

    ```sql theme={null}
    CREATE USER row_user IDENTIFIED BY 'password';
    ```
  </Step>
</Steps>

<h4 id="creating-roles">
  Creating roles
</h4>

With this set of examples:

* roles for different privileges, such as columns and rows will be created
* privileges will be granted to the roles
* users will be assigned to each role

Roles are used to define groups of users for certain privileges instead of managing each user separately.

<Steps>
  <Step title={<>Create a role to restrict users of this role to only see <code>column1</code> in database <code>db1</code> and <code>table1</code>:</>} id="create-column-role">
    ```sql theme={null}
    CREATE ROLE column1_users;
    ```
  </Step>

  <Step title={<>Set privileges to allow view on <code>column1</code></>} id="set-column-privileges">
    ```sql theme={null}
    GRANT SELECT(id, column1) ON db1.table1 TO column1_users;
    ```
  </Step>

  <Step title={<>Add the <code>column_user</code> user to the <code>column1_users</code> role</>} id="add-column-user-to-role">
    ```sql theme={null}
    GRANT column1_users TO column_user;
    ```
  </Step>

  <Step title={<>Create a role to restrict users of this role to only see selected rows, in this case, only rows containing <code>A</code> in <code>column1</code></>} id="create-row-role">
    ```sql theme={null}
    CREATE ROLE A_rows_users;
    ```
  </Step>

  <Step title={<>Add the <code>row_user</code> to the <code>A_rows_users</code> role</>} id="add-row-user-to-role">
    ```sql theme={null}
    GRANT A_rows_users TO row_user;
    ```
  </Step>

  <Step title={<>Create a policy to allow view on only where <code>column1</code> has the values of <code>A</code></>} id="create-row-policy">
    ```sql theme={null}
    CREATE ROW POLICY A_row_filter ON db1.table1 FOR SELECT USING column1 = 'A' TO A_rows_users;
    ```
  </Step>

  <Step title="Set privileges to the database and table" id="set-db-table-privileges">
    ```sql theme={null}
    GRANT SELECT(id, column1, column2) ON db1.table1 TO A_rows_users;
    ```
  </Step>

  <Step title="Grant explicit permissions for other roles to still have access to all rows" id="grant-other-roles-access">
    ```sql theme={null}
    CREATE ROW POLICY allow_other_users_filter 
    ON db1.table1 FOR SELECT USING 1 TO clickhouse_admin, column1_users;
    ```

    <Note>
      When attaching a policy to a table, the system will apply that policy, and only those users and roles defined will be able to do operations on the table, all others will be denied any operations. In order to not have the restrictive row policy applied to other users, another policy must be defined to allow other users and roles to have regular or other types of access.
    </Note>
  </Step>
</Steps>

<h2 id="verification">
  Verification
</h2>

<h3 id="testing-role-privileges-with-column-restricted-user">
  Testing role privileges with column restricted user
</h3>

<Steps>
  <Step title={<>Log into the clickhouse client using the <code>clickhouse_admin</code> user</>} id="login-admin-user">
    ```bash theme={null}
    clickhouse-client --user clickhouse_admin --password password
    ```
  </Step>

  <Step title="Verify access to database, table and all rows with the admin user." id="verify-admin-access">
    ```sql theme={null}
    SELECT *
    FROM db1.table1
    ```

    ```response theme={null}
    Query id: f5e906ea-10c6-45b0-b649-36334902d31d

    ┌─id─┬─column1─┬─column2─┐
    │  1 │ A       │ abc     │
    │  2 │ A       │ def     │
    │  3 │ B       │ abc     │
    │  4 │ B       │ def     │
    └────┴─────────┴─────────┘
    ```
  </Step>

  <Step title={<>Log into the ClickHouse client using the <code>column_user</code> user</>} id="login-column-user">
    ```bash theme={null}
    clickhouse-client --user column_user --password password
    ```
  </Step>

  <Step title={<>Test <code>SELECT</code> using all columns</>} id="test-select-all-columns">
    ```sql theme={null}
    SELECT *
    FROM db1.table1
    ```

    ```response theme={null}
    Query id: 5576f4eb-7450-435c-a2d6-d6b49b7c4a23

    0 rows in set. Elapsed: 0.006 sec.

    Received exception from server (version 22.3.2):
    Code: 497. DB::Exception: Received from localhost:9000. 
    DB::Exception: column_user: Not enough privileges. 
    To execute this query it's necessary to have grant 
    SELECT(id, column1, column2) ON db1.table1. (ACCESS_DENIED)
    ```

    <Note>
      Access is denied since all columns were specified and the user only has access to `id` and `column1`
    </Note>
  </Step>

  <Step title={<>Verify <code>SELECT</code> query with only columns specified and allowed:</>} id="verify-allowed-columns">
    ```sql theme={null}
    SELECT
        id,
        column1
    FROM db1.table1
    ```

    ```response theme={null}
    Query id: cef9a083-d5ce-42ff-9678-f08dc60d4bb9

    ┌─id─┬─column1─┐
    │  1 │ A       │
    │  2 │ A       │
    │  3 │ B       │
    │  4 │ B       │
    └────┴─────────┘
    ```
  </Step>
</Steps>

<h3 id="testing-role-privileges-with-row-restricted-user">
  Testing role privileges with row restricted user
</h3>

<Steps>
  <Step title={<>Log into the ClickHouse client using <code>row_user</code></>} id="login-row-user">
    ```bash theme={null}
    clickhouse-client --user row_user --password password
    ```
  </Step>

  <Step title="View rows available" id="view-available-rows">
    ```sql theme={null}
    SELECT *
    FROM db1.table1
    ```

    ```response theme={null}
    Query id: a79a113c-1eca-4c3f-be6e-d034f9a220fb

    ┌─id─┬─column1─┬─column2─┐
    │  1 │ A       │ abc     │
    │  2 │ A       │ def     │
    └────┴─────────┴─────────┘
    ```

    <Note>
      Verify that only the above two rows are returned, rows with the value `B` in `column1` should be excluded.
    </Note>
  </Step>
</Steps>

<h2 id="modifying-users-and-roles">
  Modifying users and roles
</h2>

Users can be assigned multiple roles for a combination of privileges needed. When using multiple roles, the system will combine the roles to determine privileges, the net effect will be that the role permissions will be cumulative.

For example, if one `role1` allows for only select on `column1` and `role2` allows for select on `column1` and `column2` then the user will have access to both columns.

<Steps>
  <Step title="Using the admin account, create new user to restrict by both row and column with default roles" id="create-restricted-user">
    ```sql theme={null}
    CREATE USER row_and_column_user IDENTIFIED BY 'password' DEFAULT ROLE A_rows_users;
    ```
  </Step>

  <Step title={<>Remove prior privileges for <code>A_rows_users</code> role</>} id="remove-prior-privileges">
    ```sql theme={null}
    REVOKE SELECT(id, column1, column2) ON db1.table1 FROM A_rows_users;
    ```
  </Step>

  <Step title={<>Allow <code>A_row_users</code> role to only select from <code>column1</code></>} id="allow-column1-select">
    ```sql theme={null}
    GRANT SELECT(id, column1) ON db1.table1 TO A_rows_users;
    ```
  </Step>

  <Step title={<>Log into the ClickHouse client using <code>row_and_column_user</code></>} id="login-restricted-user">
    ```bash theme={null}
    clickhouse-client --user row_and_column_user --password password;
    ```
  </Step>

  <Step title="Test with all columns:" id="test-all-columns-restricted">
    ```sql theme={null}
    SELECT *
    FROM db1.table1
    ```

    ```response theme={null}
    Query id: 8cdf0ff5-e711-4cbe-bd28-3c02e52e8bc4

    0 rows in set. Elapsed: 0.005 sec.

    Received exception from server (version 22.3.2):
    Code: 497. DB::Exception: Received from localhost:9000. 
    DB::Exception: row_and_column_user: Not enough privileges. 
    To execute this query it's necessary to have grant 
    SELECT(id, column1, column2) ON db1.table1. (ACCESS_DENIED)
    ```
  </Step>

  <Step title="Test with limited allowed columns:" id="test-limited-columns">
    ```sql theme={null}
    SELECT
        id,
        column1
    FROM db1.table1
    ```

    ```response theme={null}
    Query id: 5e30b490-507a-49e9-9778-8159799a6ed0

    ┌─id─┬─column1─┐
    │  1 │ A       │
    │  2 │ A       │
    └────┴─────────┘
    ```
  </Step>
</Steps>

<h2 id="troubleshooting">
  Troubleshooting
</h2>

There are occasions when privileges intersect or combine to produce unexpected results, the following commands can be used to narrow the issue using an admin account

<h3 id="listing-the-grants-and-roles-for-a-user">
  Listing the grants and roles for a user
</h3>

```sql theme={null}
SHOW GRANTS FOR row_and_column_user
```

```response theme={null}
Query id: 6a73a3fe-2659-4aca-95c5-d012c138097b

┌─GRANTS FOR row_and_column_user───────────────────────────┐
│ GRANT A_rows_users, column1_users TO row_and_column_user │
└──────────────────────────────────────────────────────────┘
```

<h3 id="list-roles-in-clickhouse">
  List roles in ClickHouse
</h3>

```sql theme={null}
SHOW ROLES
```

```response theme={null}
Query id: 1e21440a-18d9-4e75-8f0e-66ec9b36470a

┌─name────────────┐
│ A_rows_users    │
│ column1_users   │
└─────────────────┘
```

<h3 id="display-the-policies">
  Display the policies
</h3>

```sql theme={null}
SHOW ROW POLICIES
```

```response theme={null}
Query id: f2c636e9-f955-4d79-8e80-af40ea227ebc

┌─name───────────────────────────────────┐
│ A_row_filter ON db1.table1             │
│ allow_other_users_filter ON db1.table1 │
└────────────────────────────────────────┘
```

<h3 id="view-how-a-policy-was-defined-and-current-privileges">
  View how a policy was defined and current privileges
</h3>

```sql theme={null}
SHOW CREATE ROW POLICY A_row_filter ON db1.table1
```

```response theme={null}
Query id: 0d3b5846-95c7-4e62-9cdd-91d82b14b80b

┌─CREATE ROW POLICY A_row_filter ON db1.table1────────────────────────────────────────────────┐
│ CREATE ROW POLICY A_row_filter ON db1.table1 FOR SELECT USING column1 = 'A' TO A_rows_users │
└─────────────────────────────────────────────────────────────────────────────────────────────┘
```

<h2 id="example-commands-to-manage-roles-policies-and-users">
  Example commands to manage roles, policies, and users
</h2>

The following commands can be used to:

* delete privileges
* delete policies
* unassign users from roles
* delete users and roles
  <br />

<Tip>
  Run these commands as an admin user or the `default` user
</Tip>

<h3 id="remove-privilege-from-a-role">
  Remove privilege from a role
</h3>

```sql theme={null}
REVOKE SELECT(column1, id) ON db1.table1 FROM A_rows_users;
```

<h3 id="delete-a-policy">
  Delete a policy
</h3>

```sql theme={null}
DROP ROW POLICY A_row_filter ON db1.table1;
```

<h3 id="unassign-a-user-from-a-role">
  Unassign a user from a role
</h3>

```sql theme={null}
REVOKE A_rows_users FROM row_user;
```

<h3 id="delete-a-role">
  Delete a role
</h3>

```sql theme={null}
DROP ROLE A_rows_users;
```

<h3 id="delete-a-user">
  Delete a user
</h3>

```sql theme={null}
DROP USER row_user;
```

<h2 id="summary">
  Summary
</h2>

This article demonstrated the basics of creating SQL users and roles and provided steps to set and modify privileges for users and roles. For more detailed information on each please refer to our user guides and reference documentation.