Skip to main content
Version: 2.4.x

DCL

Is used to control the access rights of database users.

Only users with system permissions can add users and tenants.

Only a user who holds the role of owner under a tenant or a user with system permissions can add a role under a tenant and give a user a role.

CREATE TENANT

CREATE TENANT is used to create a new tenant in CnosDB.CnosDB allows a single instance to serve multiple clients (i.e. tenants) at the same time, with each client being treated as an independent tenant.

CREATE TENANT [IF NOT EXISTS] tenant_name
[WITH [comment = <comment>],
[drop_after = duration],
[_limiter = <limiter_config>]];
OptionsDescription
drop_afterDelete tenant delay time, default is immediate deletion, supports: d, h, m, when no unit is provided, default is d
_limiterLimit tenant resource usage, for more details please refer to Tenant Resources
View example
CREATE TENANT test;

CREATE USER

CREATE USER is used to create a new user.

By using the CREATE USER statement, administrators can create new users in CnosDB and assign them corresponding permissions and roles.New users can be used to access databases, execute queries, update data, etc. The specific permissions depend on the permission level assigned to the user by the administrator.

CREATE USER [IF NOT EXISTS] user_name
[WITH [PASSWORD='',]
[MUST_CHANGE_PASSWORD=true,]
[RSA_PUBLIC_KEY='']
[GRANTED_ADMIN=true,] [COMMENT = '']];
OptionsDescription
MUST_CHANGE_PASSWORDWhether to change the password on the first login, default is false.
GRANTED_ADMINWhether the user is a admin user, or admin is used for all of the instances.
RSA_PUBLIC_KEYUpload the public key of the user RSA algorithm for login authentication.
View example
CREATE USER IF NOT EXISTS tester WITH PASSWORD='xxx', MUST_CHANGE_PASSWORD=true, COMMENT = 'test';

CREATE ROLE

CREATE ROLE is used to create new roles.

By using the CREATE ROLE statement, administrators can define new roles and assign permissions to these roles.Roles can be used in the database for organizing users and granting a specific set of permissions to simplify the administration and control access levels.

CREATE ROLE [IF NOT EXISTS] role_name INHERIT {owner | member};
OptionsDescription
ownerDefault role for tenants. You must inherit owner or member when creating a new role.
memberDefault role for tenants. You must inherit owner or member when creating a new role.
View example
CREATE ROLE owner_role INHERIT owner;

ALTER TENANT

The ALTER TENANT is used to modify the tenant properties or configuration.

With the ALTER TENANT command, the tenant properties can be modified, such as changing the tenant's configuration, adjusting resource limits, etc.

ALTER TENANT tenant_name {SET sql_option | UNSET option_name };

sql_option: option_name = value
option: {COMMENT/DROP_AFTER/_LIMITER}
OptionsDescription
SETAdd or modify attributes for tenants.
UNSETRevoke the configuration or properties within the tenant.
View example
ALTER TENANT test SET COMMENT = 'abc';

ALTER USER

ALTER USER is used to modify statements for existing users.

By using the ALTER USER statement, administrators can change a user's properties, permissions, and configurations.This includes operations such as modifying user passwords, changing user roles, adjusting user permissions, etc.

ALTER USER user_name {SET sql_option};

sql_option: option_name = option_value
option_name: {COMMENT | MUST_CHANGE_PASSWORD | PASSWORD | RSA_PUBLIC_KEY}
OptionsDescription
SETAdd or modify attributes for tenants.
View example
ALTER USER tester SET PASSWORD = 'aaa';

DROP TENANT

DROP TENANT is used to delete a tenant and its related data and configuration.

Through the DROP TENANT command, you can delete a specific tenant, including all data, configurations, users, and other content owned by the tenant.

Before executing the DROP TENANT operation, it is usually necessary to consider it carefully, as this operation will permanently delete the tenant and all its related data.

DROP TENANT tenant_name [AFTER duration];
OptionsDescription
ALTERDelete tenant delay time, default immediate deletion, support: d, h, m, when no unit is specified, default is d, during deletion period the tenant will be disabled, the priority of ALTER is higher than DROP_AFTER in CREATE TANANT.
View example
DROP TENANT test AFTER '7d';

DROP USER

DROP USER is used to delete an existing user.

By using the DROP USER statement, a database administrator can permanently delete specific users from the database, including the user's login credentials, permissions, and configuration information.

DROP USER [IF EXISTS] user_name;
View example
DROP USER IF EXISTS tester;

DROP ROLE

DROP ROLE is used to delete existing roles.

By using the DROP ROLE statement, administrators can permanently delete specific roles from the database, including the permissions and configuration information associated with the role.When deleting a role, the permissions assigned to users may be revoked.

tip

When a role is deleted, the permissions of the corresponding tenant members will be revoked at the same time. However, the binding relationship between tenant members and their roles will not be synchronized deletion (only roles will become invalid).

DROP ROLE role_name;
View example
DROP USER IF EXISTS tester;

GRANT

GRANT is used to grant permissions to users or roles.

By using the GRANT command, the database administrator can grant specific permissions to users or roles, thus controlling their access and operation permissions on database objects.

The granularity supported by permissions is as follows

OptionsDescription
READPermission of reading the database.
WRITEPermission of reading and writing the database.
ALLPermission of adding, deleting, modifying, and querying the database.
GRANT {READ | WRITE | ALL} ON DATABASE database_name TO ROLE role_name;
View example

Create a character named rrr.

CREATE ROLE rrr INHERIT member;

Grant the role rrr the permission to read the database air.**

GRANT READ ON DATABASE air TO ROLE rrr;

Grant the role rrr permission to read and write the wind database.**

GRANT WRITE ON DATABASE wind TO ROLE rrr;

Grant the role rrr all permissions regarding the database sea.**

GRANT ALL ON DATABASE sea TO ROLE rrr;

REVOKE

REVOKE is used in database management systems to revoke user or role permissions.

By using the REVOKE command, a database administrator can revoke previously granted permissions from users or roles to restrict their access or operation permissions on database objects.The REVOKE command is commonly used in conjunction with the GRANT command to manage and adjust the permissions of users or roles.

REVOKE {WRITE | READ | FULL} ON DATABASE database_name FROM role_name;
View example

Revoke the permission to read database air for rrr.**

REVOKE READ ON DATABASE air FROM rrr;