Grant permissions to standard accounts with the GRANT statement - ApsaraDB for ClickHouse

Grants DML permissions to a standard account in ApsaraDB for ClickHouse.

Prerequisites

Before you begin, ensure that you have:

A privileged account, or a standard account with the GRANT permission

Limitations

GRANT 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

Parameter	Description
`ON CLUSTER default`	Applies the grant operation to every node in the cluster. The value is fixed as `ON CLUSTER default`.
`priv_type`	The type of permission to grant. For the full list of supported privileges, see Privileges.
`column_list`	(Optional) The columns on which to grant permissions. If omitted, permissions apply to all columns. For example, setting `priv_type` to `SELECT` and `column_list` to `name` grants `SELECT` on the `name` column only.
`priv_level`	The scope at which permissions are granted: `.` for cluster level, `db_name.*` for database level, or `db_name.table_name` / `table_name` for table level.
`WITH GRANT OPTION`	Grants the `GRANT` permission to the account. An account with this permission can grant its own permissions to other accounts.

Examples

Grant permissions to an existing account

Grant all at the cluster level to account2:

GRANT ON CLUSTER default all ON *.* TO 'account2';

Grant all on the ck_demo database to account3:

GRANT ON CLUSTER default all ON ck_demo.* TO 'account3';

Grant SELECT and INSERT to account1 and account2:

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

Create an account and grant permissions

Create an account using CREATE USER, then grant permissions with GRANT.

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

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

Grant SELECT on specific columns (customer_id and gender) 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';