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

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

Prerequisites

Your database account is a privileged account or a standard account that has the GRANT permission.

Limits

The GRANT statement grants 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: Executes the grant operation on every node. This parameter has a fixed value of ON CLUSTER default.
priv_type: Specifies the type of permission to grant. For more information, see Privileges.
column_list: Optional. Specifies the columns on which the permissions are granted. If you omit this parameter, the permissions are granted on all columns.
For example, if you set priv_type to SELECT and column_list to name, the account is granted the SELECT permission on the name column.
priv_level: Specifies the level at which the permissions are granted.
- *.*: Grants permissions at the cluster level.
- db_name.*: Grants permissions at the database level.
- db_name.table_name or table_name: Grants permissions at the table level.
WITH GRANT OPTION: Grants the GRANT permission to the account. An account with the GRANT permission can grant its own 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 using the CREATE USER statement, and then grant permissions to the account using the GRANT statement.

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

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 it DML permissions on the ck_demo database.

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 it 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';