Common access management queries

##### Create a role to restrict users of this role to only see selected rows, in this case, only rows containing `A` in `column1`[​](#create-row-role "Direct link to create-row-role")

```
CREATE ROLE A_rows_users;
```

Add the to the role

##### Add the `row_user` to the `A_rows_users` role[​](#add-row-user-to-role "Direct link to add-row-user-to-role")

```
GRANT A_rows_users TO row_user;
```

Create a policy to allow view on only where has the values of

##### Create a policy to allow view on only where `column1` has the values of `A`[​](#create-row-policy "Direct link to create-row-policy")

```
CREATE ROW POLICY A_row_filter ON db1.table1 FOR SELECT USING column1 = 'A' TO A_rows_users;
```

Set privileges to the database and table

##### Set privileges to the database and table[​](#set-db-table-privileges "Direct link to Set privileges to the database and table")

```
GRANT SELECT(id, column1, column2) ON db1.table1 TO A_rows_users;
```

Grant explicit permissions for other roles to still have access to all rows

##### Grant explicit permissions for other roles to still have access to all rows[​](#grant-other-roles-access "Direct link to Grant explicit permissions for other roles to still have access to all rows")

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

## Verification[​](#verification "Direct link to Verification")

### Testing role privileges with column restricted user[​](#testing-role-privileges-with-column-restricted-user "Direct link to Testing role privileges with column restricted user")

Log into the clickhouse client using the user

##### Log into the clickhouse client using the `clickhouse_admin` user[​](#login-admin-user "Direct link to login-admin-user")

```
clickhouse-client --user clickhouse_admin --password password
```

Verify access to database, table and all rows with the admin user.

##### Verify access to database, table and all rows with the admin user.[​](#verify-admin-access "Direct link to Verify access to database, table and all rows with the admin user.")

```
SELECT *
FROM db1.table1
```

```
Query id: f5e906ea-10c6-45b0-b649-36334902d31d

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

Log into the ClickHouse client using the user

##### Log into the ClickHouse client using the `column_user` user[​](#login-column-user "Direct link to login-column-user")

```
clickhouse-client --user column_user --password password
```

Test using all columns

##### Test `SELECT` using all columns[​](#test-select-all-columns "Direct link to test-select-all-columns")

```
SELECT *
FROM db1.table1
```

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

Verify query with only columns specified and allowed:

##### Verify `SELECT` query with only columns specified and allowed:[​](#verify-allowed-columns "Direct link to verify-allowed-columns")

```
SELECT
    id,
    column1
FROM db1.table1
```

```
Query id: cef9a083-d5ce-42ff-9678-f08dc60d4bb9

┌─id─┬─column1─┐
│  1 │ A       │
│  2 │ A       │
│  3 │ B       │
│  4 │ B       │
└────┴─────────┘
```

### Testing role privileges with row restricted user[​](#testing-role-privileges-with-row-restricted-user "Direct link to Testing role privileges with row restricted user")

Log into the ClickHouse client using

##### Log into the ClickHouse client using `row_user`[​](#login-row-user "Direct link to login-row-user")

```
clickhouse-client --user row_user --password password
```

View rows available

##### View rows available[​](#view-available-rows "Direct link to View rows available")

```
SELECT *
FROM db1.table1
```

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

## Modifying users and roles[​](#modifying-users-and-roles "Direct link to Modifying users and roles")

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.

Using the admin account, create new user to restrict by both row and column with default roles

##### Using the admin account, create new user to restrict by both row and column with default roles[​](#create-restricted-user "Direct link to Using the admin account, create new user to restrict by both row and column with default roles")

```
CREATE USER row_and_column_user IDENTIFIED BY 'password' DEFAULT ROLE A_rows_users;
```

Remove prior privileges for role

##### Remove prior privileges for `A_rows_users` role[​](#remove-prior-privileges "Direct link to remove-prior-privileges")

```
REVOKE SELECT(id, column1, column2) ON db1.table1 FROM A_rows_users;
```

Allow role to only select from

##### Allow `A_row_users` role to only select from `column1`[​](#allow-column1-select "Direct link to allow-column1-select")

```
GRANT SELECT(id, column1) ON db1.table1 TO A_rows_users;
```

Log into the ClickHouse client using

##### Log into the ClickHouse client using `row_and_column_user`[​](#login-restricted-user "Direct link to login-restricted-user")

```
clickhouse-client --user row_and_column_user --password password;
```

Test with all columns:

##### Test with all columns:[​](#test-all-columns-restricted "Direct link to Test with all columns:")

```
SELECT *
FROM db1.table1
```

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

Test with limited allowed columns:

##### Test with limited allowed columns:[​](#test-limited-columns "Direct link to Test with limited allowed columns:")

```
SELECT
    id,
    column1
FROM db1.table1
```

```
Query id: 5e30b490-507a-49e9-9778-8159799a6ed0

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

## Troubleshooting[​](#troubleshooting "Direct link to Troubleshooting")

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

### Listing the grants and roles for a user[​](#listing-the-grants-and-roles-for-a-user "Direct link to Listing the grants and roles for a user")

```
SHOW GRANTS FOR row_and_column_user
```

```
Query id: 6a73a3fe-2659-4aca-95c5-d012c138097b

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

### List roles in ClickHouse[​](#list-roles-in-clickhouse "Direct link to List roles in ClickHouse")

```
SHOW ROLES
```

```
Query id: 1e21440a-18d9-4e75-8f0e-66ec9b36470a