Manage the permissions to access clusters - ApsaraDB for SelectDB

An ApsaraDB for SelectDB instance can contain multiple compute clusters, which are similar to computing resource groups or compute queues in a distributed system. Compute clusters can support different workloads, such as isolating read traffic from write traffic and isolating online traffic from offline traffic, to meet the requirements for isolating business. This topic describes how to manage the permissions to access clusters in an ApsaraDB for SelectDB instance.

Important

In the production environment, do not modify the names of clusters unless necessary. This is because the cluster names are used in queries. If you modify the cluster names, the permissions to access the clusters may become invalid.

Query clusters

You can query the compute clusters of an ApsaraDB for SelectDB instance.

Syntax

SHOW CLUSTERS;

Example

SHOW CLUSTERS;

The following results are returned:

+---------------+------------+-------------+
| cluster       | is_current | users       |
+---------------+------------+-------------+
| test_cluster  | FALSE      | admin, jack |
| test_cluster1 | FALSE      |             |
+---------------+------------+-------------+

Switch a cluster

You can specify the database and compute cluster that you want to use.

Syntax

USE { [catalog_name.]database_name[@cluster_name] | @cluster_name }

Parameters

Parameter	Description
catalog_name	The name of the catalog.
database_name	The name of the database.
cluster_name	The name of the cluster.

Note

If the database name or cluster name is a reserved keyword, you must enclose the database name or cluster name with backticks ('). Sample code:

USE @`interval`

Examples

Example 1: Use the test_cluster cluster.
```
USE @test_cluster;
```
Example 2: Use the test_database database and the test_cluster compute cluster.
```
USE test_database@test_cluster;
```
Example 3: Specify the test_database database and test_cluster compute cluster in a Java Database Connectivity (JDBC) URL.
```
jdbc:mysql://selectdb-cn-****:9030/test_database@test_cluster
```

Grant a user the permissions to access clusters

You can grant a user the permissions to access clusters.

Syntax

GRANT USAGE_PRIV ON CLUSTER {cluster_name} TO {user}

Parameters

Parameter	Description
cluster_name	The name of the cluster.
user	The username.

Examples

Example 1:

Grant the user named jack the permissions to access the test_cluster cluster.
```
GRANT USAGE_PRIV ON CLUSTER test_cluster TO jack;
```

Query the permissions granted to the user named jack to access clusters.

SHOW GRANTS FOR jack\G;

The following results are returned:

 UserIdentity: 'jack'@'%'
     Password: Yes
  GlobalPrivs: Admin_priv  (false)
 CatalogPrivs: NULL
DatabasePrivs: internal.information_schema: Select_priv  (false)
   TablePrivs: NULL
ResourcePrivs: NULL
 CloudCluster: test_cluster: Usage_priv  (false)
   CloudStage: NULL

Example 2:

Grant the user named jack the permissions to access the not_exist_cluster cluster.
```
GRANT USAGE_PRIV ON CLUSTER not_exist_cluster TO jack;
```

Query the permissions granted to the user named jack to access clusters.

SHOW GRANTS FOR jack\G;

The following results are returned:

UserIdentity: 'jack'@'%'
     Password: Yes
  GlobalPrivs: Admin_priv  (false)
 CatalogPrivs: NULL
DatabasePrivs: internal.information_schema: Select_priv  (false)
   TablePrivs: NULL
ResourcePrivs: NULL
 CloudCluster: not_exist_cluster: Usage_priv  (false)
   CloudStage: NULL

Use the not_exist_cluster cluster.

USE information_schema@not_exist_cluster;

The following results are returned:

No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***
ERROR 5091 (42000): Cluster not_exist_cluster not exist

Revoke the permissions to access clusters from a user

You can revoke the permissions to access clusters from a user.

Syntax

REVOKE USAGE_PRIV ON CLUSTER {cluster_name} FROM {user}

Parameters

Parameter	Description
cluster_name	The name of the cluster.
user	The username.

Example

Revoke the permissions to access the test_cluster cluster from the user named jack and query the permissions of the user.

REVOKE USAGE_PRIV ON CLUSTER test_cluster FROM jack;
SHOW grants for jack\G

The following results are returned:

 UserIdentity: 'jack'@'%'
     Password: Yes
  GlobalPrivs: Admin_priv  (false)
 CatalogPrivs: NULL
DatabasePrivs: internal.information_schema: Select_priv  (false)
   TablePrivs: NULL
ResourcePrivs: NULL
 CloudCluster: NULL
   CloudStage: NULL

Specify a default cluster for a user

In ApsaraDB for SelectDB, you can specify a default cluster for a user. This allows the user to access resources and perform operations in ApsaraDB for SelectDB in an easier and more convenient way. After you specify a default cluster for a user, the user can perform operations such as queries without the need to specify a cluster. The system automatically uses the default cluster to process the operations. This reduces the configuration steps that are required for each operation and improves work efficiency. In addition, this helps the user focus on data analysis and tasks by freeing the user from cluster management.

Note

If you do not specify a default cluster for a user, the system automatically specifies a cluster that has an active backend and that the user has permissions to access as the default cluster of the user. In the same session, the default cluster that is specified by the system remains unchanged. In different sessions, the default cluster that is specified by the system may be changed in the following cases:

The user has no permissions to access the previous default cluster that is specified by the system.
Clusters are created or existing clusters are deleted.
The previous default cluster that is specified by the system does not have an active backend.

In the first and second cases, the default cluster that is specified by the system must be changed. In the third case, the default cluster that is specified by the system may be changed. Therefore, we recommend that you periodically check your permissions to access clusters. This ensures that you can access the resources that you require as expected.

Syntax

-- Specify a default cluster.
SET PROPERTY 'default_cloud_cluster' = '{clusterName}';
-- Specify a default cluster for a user.
SET PROPERTY FOR {user} 'default_cloud_cluster' = '{clusterName}';

Parameters

Parameter	Description
cluster_name	The name of the cluster.
user	The username. Important If you want to specify a user, you must have the permissions of the admin user.

Examples

Example 1:

Specify a default cluster for the current user.

SET PROPERTY 'default_cloud_cluster' = 'test_cluster';

Query the default cluster that the user can access.

SHOW PROPERTY;

The following results are returned:

+------------------------+-------------------------------+
| Key                    | Value                         |
+------------------------+-------------------------------+
| cpu_resource_limit     | -1                            |
| default_cloud_cluster  | test_cluster                  |
| exec_mem_limit         | -1                            |
| load_mem_limit         | -1                            |
| max_query_instances    | -1                            |
| max_user_connections   | 100                           |
| quota.high             | 800                           |
| quota.low              | 100                           |
| quota.normal           | 400                           |
| resource.cpu_share     | 1000                          |
| resource.hdd_read_iops | 80                            |
| resource.hdd_read_mbps | 30                            |
| resource.io_share      | 1000                          |
| resource.ssd_read_iops | 1000                          |
| resource.ssd_read_mbps | 30                            |
| resource_tags          |                               |
| sql_block_rules        |                               |
+------------------------+-------------------------------+

Example 2:

Create a user named jack and assign the admin role to the user.

CREATE USER jack IDENTIFIED BY '123456' DEFAULT ROLE "admin";

Query the default cluster that the user named jack can access.

SHOW PROPERTY FOR jack;

The following results are returned:

+------------------------+-------------------------------+
| Key                    | Value                         |
+------------------------+-------------------------------+
| cpu_resource_limit     | -1                            |
| default_cloud_cluster  | test_cluster1                 |
| exec_mem_limit         | -1                            |
| load_mem_limit         | -1                            |
| max_query_instances    | -1                            |
| max_user_connections   | 100                           |
| quota.high             | 800                           |
| quota.low              | 100                           |
| quota.normal           | 400                           |
| resource.cpu_share     | 1000                          |
| resource.hdd_read_iops | 80                            |
| resource.hdd_read_mbps | 30                            |
| resource.io_share      | 1000                          |
| resource.ssd_read_iops | 1000                          |
| resource.ssd_read_mbps | 30                            |
| resource_tags          |                               |
| sql_block_rules        |                               |
+------------------------+-------------------------------+

Example 3:
1. If the default cluster that you want to specify does not exist in the current instance, an error is reported and prompts you to execute the SHOW CLUSTERS; statement to query all available clusters in the current instance.
```
SET PROPERTY 'default_cloud_cluster' = 'not_exist_cluster';
```
  The following results are returned:
```
ERROR 5091 (42000): errCode = 2, detailMessage = Cluster not_exist_cluster not exist, use SQL 'SHOW CLUSTERS' to get a valid cluster
```
2. Query all available clusters in the current instance.
```
SHOW CLUSTERS;
```
  The following results are returned:
```
+---------------+------------+-------------+
| cluster       | is_current | users       |
+---------------+------------+-------------+
| test_cluster  | FALSE      | admin, jack |
| test_cluster1 | FALSE      |             |
+---------------+------------+-------------+
```
  The cluster column indicates the name of the cluster. The is_current column indicates whether the current user uses the cluster. The users column indicates that the cluster in the current row can be specified as the default cluster for the users.