This topic describes how to execute a GRANT statement to grant permissions to a standard account for ApsaraDB for ClickHouse. The account is created and managed by running SQL statements.

Prerequisites

  • The cluster whose account you want to grant permissions is an ApsaraDB for ClickHouse 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 permissions to only standard accounts that are created and managed by running SQL statements.

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.

Example

  • 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 permission 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 permission 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 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';