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.

Admin user

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:

CREATE USER IF NOT EXISTS clickhouse_admin
IDENTIFIED WITH sha256_password BY 'P!@ssword42!';

GRANT default_role TO clickhouse_admin;

Passwordless authentication

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.

Granular access control

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:

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:

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

Test admin privileges

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

All of these should succeed:

SHOW GRANTS FOR clickhouse_admin;

CREATE DATABASE db1

CREATE TABLE db1.table1 (id UInt64, column1 String) ENGINE = MergeTree() ORDER BY id;

INSERT INTO db1.table1 (id, column1) VALUES (1, 'abc');

SELECT * FROM db1.table1;

DROP TABLE db1.table1;

DROP DATABASE db1;

Non-admin users

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

Preparation

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

Creating a sample database, table, and rows

Create a test database

CREATE DATABASE db1;

Create a table

CREATE TABLE db1.table1 (
   id UInt64,
   column1 String,
   column2 String
)
ENGINE MergeTree
ORDER BY id;

Populate the table with sample rows

INSERT INTO db1.table1
   (id, column1, column2)
VALUES
   (1, 'A', 'abc'),
   (2, 'A', 'def'),
   (3, 'B', 'abc'),
   (4, 'B', 'def');

Verify the table

Querysql

SELECT *
FROM db1.table1

Responseresponse

Query id: 475015cc-6f51-4b20-bda2-3c9c41404e49

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

[object Object]

Create a regular user that will be used to demonstrate restrict access to certain columns:

CREATE USER column_user IDENTIFIED BY 'password';

[object Object]

Create a regular user that will be used to demonstrate restricting access to rows with certain values:

CREATE USER row_user IDENTIFIED BY 'password';

Creating roles

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.

[object Object]

CREATE ROLE column1_users;

[object Object]

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

[object Object]

GRANT column1_users TO column_user;

[object Object]

CREATE ROLE A_rows_users;

[object Object]

GRANT A_rows_users TO row_user;

[object Object]

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

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

CREATE ROW POLICY allow_other_users_filter 
ON db1.table1 FOR SELECT USING 1 TO clickhouse_admin, column1_users;

Verification

Testing role privileges with column restricted user

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     │
└────┴─────────┴─────────┘

[object Object]

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)

[object Object]

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

View rows available

SELECT *
FROM db1.table1

Query id: a79a113c-1eca-4c3f-be6e-d034f9a220fb

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

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

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

[object Object]

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

[object Object]

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

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:

SELECT
    id,
    column1
FROM db1.table1

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

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

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

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

SHOW ROLES

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

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

Display the policies

SHOW ROW POLICIES

Query id: f2c636e9-f955-4d79-8e80-af40ea227ebc

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

View how a policy was defined and current privileges

SHOW CREATE ROW POLICY A_row_filter ON db1.table1

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 │
└─────────────────────────────────────────────────────────────────────────────────────────────┘

Example commands to manage roles, policies, and users

The following commands can be used to:

delete privileges
delete policies
unassign users from roles
delete users and roles

Remove privilege from a role

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

Delete a policy

DROP ROW POLICY A_row_filter ON db1.table1;

Unassign a user from a role

REVOKE A_rows_users FROM row_user;

Delete a role

DROP ROLE A_rows_users;

Delete a user

DROP USER row_user;

Summary

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.