Execute the GRANT statement to grant permissions to a standard account for ApsaraDB for ClickHouse - ApsaraDB for ClickHouse

This topic describes how to execute the GRANT statement to grant permissions to a standard account for ApsaraDB for ClickHouse.

Prerequisites

The cluster whose account you want to grant permissions is an ApsaraDB for ClickHouse Community-compatible Edition cluster.
Your database account is a privileged account or a standard account that has the GRANT permission.

Limits

You can execute the GRANT statement to grant only DML permissions to standard accounts.

Syntax

GRANT [ON CLUSTER default]
    priv_type [(column_list [,...])] [,...]    
    ON priv_level
    TO {user | CURRENT_USER} [,...]
    [WITH GRANT OPTION]

Parameters

ON CLUSTER default: specifies that permissions are granted on all nodes. Set the value to ON CLUSTER default.
priv_type: specifies the type of permission to be granted. For more information about valid values, see Privileges.
column_list: optional. If you specify a parameter value, the permission is granted on the specified column. If you do not specify a parameter value, the permission is granted on all columns.
For example, if the priv_type parameter is set to SELECT and the column_list parameter is set to name, the account is granted the SELECT permission on the name column.
priv_level: specifies the level of the permission.
- *.*: specifies the cluster level.
- db_name.*: specifies the database level.
- db_name.table_name or table_name: specifies the table level.
WITH GRANT OPTION: specifies that the GRANT permission is granted to the account. When an account has the GRANT permission, the account can grant its permissions to other accounts.

Examples

Grant the all permission at the cluster level to account2.
```
GRANT ON CLUSTER default all ON *.* TO 'account2';
```
Grant the all permission on the ck_demo database to account3.
```
GRANT ON CLUSTER default all ON ck_demo.* TO 'account3';
```

Grant the SELECT and INSERT permissions to account1 and account2.

GRANT ON CLUSTER default SELECT,INSERT ON *.* TO 'account1','account2'

Create an account by executing the CREATE USER statement, and grant permissions to the created account by executing the GRANT statement.

Create an account and grant the DML permissions at the cluster level to the account.

CREATE USER 'test' ON CLUSTER default IDENTIFIED WITH sha256_password BY 'Testpassword1';
GRANT ON CLUSTER default INSERT,SELECT,ALTER,DROP on *.* to 'test';

Create an account and grant the DML permissions on the ck_demo database to the account.

CREATE USER 'test123' ON CLUSTER default IDENTIFIED WITH sha256_password BY 'Testpassword123';
GRANT ON CLUSTER default INSERT,SELECT,ALTER,DROP on ck_demo.* to 'test123';

Create an account and grant the account with the SELECT permission on the customer_id and gender columns in the customer table.

CREATE USER 'test321' ON CLUSTER default IDENTIFIED WITH sha256_password BY 'Testpassword321';
GRANT ON CLUSTER default SELECT (customer_id, gender) ON customer to 'test321';