Authority Management

IoTDB provides permission management capabilities to implement fine-grained access control for data and cluster systems, ensuring data and system security. This article introduces the core concepts of the permission module under the IoTDB Table Model, user specifications, permission governance, authentication logic, and practical use cases.

1. Core Concepts

1.1 User

A user refers to a legitimate database operator. Each user corresponds to a unique username and is authenticated by a password. To use the database, users must provide valid usernames and passwords stored in the system.

1.2 Privilege

The database supports a wide range of operations, but not all users are authorized to perform every action. A user is considered to have the corresponding privilege if permitted to execute a specific operation.

1.3 Role

A role is a collection of privileges identified by a unique role name. Roles correspond to actual job identities (e.g., traffic dispatchers), and multiple users may share the same identity. Users with identical job roles usually require consistent permissions. Roles serve as an abstraction for unified permission management among user groups.

1.4 Default Users and Roles

After initialization, IoTDB provides one default user named root with the default password root. As the administrator account, root owns all privileges permanently. Its permissions cannot be granted, revoked, or deleted, and it is the sole administrator user in the database. Newly created users and roles have no permissions by default.

2. User Specifications

Users with the SECURITY privilege can create users and roles, subject to the following constraints:

2.1 Username Rules

• Usernames must be 4 to 32 characters long, including uppercase and lowercase letters, digits, and special symbols (!@#$%^&*()_+-=). Creation of usernames identical to the administrator account is prohibited.
• If a username consists entirely of digits or contains special characters, it must be enclosed in double quotation marks "" during creation.

2.2 Password Rules

Passwords must be 4 to 32 characters long, including uppercase and lowercase letters, digits, and special symbols (!@#$%^&*()_+-=). A password cannot be the same as the associated username.

2.3 Role Name Rules

Role names must be 4 to 32 characters long, including uppercase and lowercase letters, digits, and special symbols (!@#$%^&*()_+-=). Creation of role names identical to the administrator account is prohibited.

3. Permission Management

Under the IoTDB Table Model, permissions are divided into two major categories: global privileges and data privileges.

3.1 Global Privileges

Global privileges include two types: SYSTEM and SECURITY:

• SYSTEM: Governs O&M operations and Data Definition Language (DDL) related privileges.
• SECURITY: Governs the management of users and roles, as well as privilege granting for other accounts.

Detailed descriptions of each global privilege are shown in the table below:

3.2 Data Privileges

Data privileges consist of privilege types and effective scopes.

• Privilege Types: CREATE, DROP, ALTER, SELECT, INSERT, DELETE.
• Scopes: ANY (system-wide), DATABASE (database-level), TABLE (single table).
  • Privileges with the ANY scope apply to all databases and tables.
  • Database-level privileges apply to the specified database and all tables within it.
  • Table-level privileges only take effect on the designated single table.
• Scope Matching Rules: When executing single-table operations, the system matches user privileges by scope level. For example, when writing data to DATABASE1.TABLE1, the system checks write permissions in sequence for ANY, DATABASE1, and DATABASE1.TABLE1 until a matching privilege is found or the check fails.

The logical relationship between privilege types, scopes and capabilities is shown below:

3.3 Privilege Granting and Revocation

In IoTDB, user privileges can be granted or revoked through three methods:

1. Direct granting or revocation by the super administrator.
2. Granting or revocation by common users with the GRANT OPTION privilege.
3. Granting or revocation via role assignment, operated by the super administrator or users with SECURITY privileges.

The following rules apply to privilege management under the IoTDB Table Model:

• No scope needs to be specified when granting or revoking global privileges.
• Data privilege operations require explicit privilege types and scopes. Revocation only takes effect on the specified scope and is not affected by hierarchical inclusion relationships.
• Pre-authorization is supported for databases and tables that have not yet been created.
• Repeated granting or revocation of privileges is allowed.
• WITH GRANT OPTION: Grants users the permission to manage privileges within their authorized scope, including granting and revoking corresponding permissions for other users.

4. Syntax and Usage Examples

4.1 User and Role Management

1. Create User (Requires SECURITY privilege)

CREATE USER <USERNAME> <PASSWORD>   
-- Example
CREATE USER user1 'passwd'

2. Modify Password Users can update their own passwords; modifying other users' passwords requires the SECURITY privilege.

ALTER USER <USERNAME> SET PASSWORD <password>
-- Example
ALTER USER tempuser SET PASSWORD 'newpwd'

3. Drop User (Requires SECURITY privilege)

DROP USER <USERNAME>
-- Example
DROP USER user1

4. Create Role (Requires SECURITY privilege)

CREATE ROLE <ROLENAME>
-- Example
CREATE ROLE role1

5. Drop Role (Requires SECURITY privilege)

DROP ROLE <ROLENAME>
-- Example
DROP ROLE role1

6. Grant Role to User (Requires SECURITY privilege)

GRANT ROLE <ROLENAME> TO <USERNAME>
-- Example
GRANT ROLE admin TO user1

7. Revoke Role from User (Requires SECURITY privilege)

REVOKE ROLE <ROLENAME> FROM <USERNAME>
-- Example
REVOKE ROLE admin FROM user1

8. List All Users (Requires SECURITY privilege)

LIST USER

9. List All Roles (Requires SECURITY privilege)

LIST ROLE

10. List Users Assigned to a Specified Role (Requires SECURITY privilege)

LIST USER OF ROLE <ROLENAME>
-- Example
LIST USER OF ROLE roleuser

11. List Roles of a Specified User Users can view their own roles; viewing other users' roles requires the SECURITY privilege.

LIST ROLE OF USER <USERNAME>
-- Example
LIST ROLE OF USER tempuser

12. List All Privileges of a User Users can view their own privileges; viewing other users' privileges requires the SECURITY privilege.

LIST PRIVILEGES OF USER <USERNAME>
-- Example
LIST PRIVILEGES OF USER tempuser

13. List All Privileges of a Role Users can view privileges of their assigned roles; viewing other roles' privileges requires the SECURITY privilege.

LIST PRIVILEGES OF ROLE <ROLENAME>
-- Example
LIST PRIVILEGES OF ROLE actor

4.2 Privilege Granting and Revocation

4.2.1 Grant Privileges

1. Grant user management privileges to a user

GRANT SECURITY TO USER <USERNAME>
-- Example
GRANT SECURITY TO USER TEST_USER

2. Grant database and table creation privileges with permission management rights

GRANT CREATE ON DATABASE <DATABASE> TO USER <USERNAME> WITH GRANT OPTION
-- Example
GRANT CREATE ON DATABASE TESTDB TO USER TEST_USER WITH GRANT OPTION

3. Grant database query privileges to a role

GRANT SELECT ON DATABASE <DATABASE> TO ROLE <ROLENAME>
-- Example
GRANT SELECT ON DATABASE TESTDB TO ROLE TEST_ROLE

4. Grant table query privileges to a user

GRANT SELECT ON <DATABASE>.<TABLENAME> TO USER <USERNAME>
-- Example
GRANT SELECT ON TESTDB.TESTTABLE TO USER TEST_USER

5. Grant global query privileges to a role

GRANT SELECT ON ANY TO ROLE <ROLENAME>
-- Example
GRANT SELECT ON ANY TO ROLE TEST_ROLE

6. ALL Syntax Sugar: ALL represents all privileges within the target scope

-- Grant all global privileges and full data privileges under ANY scope
GRANT ALL TO USER TESTUSER

-- Grant all data privileges under ANY scope
GRANT ALL ON ANY TO USER TESTUSER

-- Grant all data privileges under the specified database
GRANT ALL ON DATABASE TESTDB TO USER TESTUSER

-- Grant all data privileges under the specified table
GRANT ALL ON TABLE TESTTABLE TO USER TESTUSER

4.2.2 Revoke Privileges

1. Revoke user management privileges

REVOKE SECURITY FROM USER <USERNAME>
-- Example
REVOKE SECURITY FROM USER TEST_USER

2. Revoke database and table creation privileges

REVOKE CREATE ON DATABASE <DATABASE> FROM USER <USERNAME>
-- Example
REVOKE CREATE ON DATABASE TEST_DB FROM USER TEST_USER

3. Revoke table query privileges

REVOKE SELECT ON <DATABASE>.<TABLENAME> FROM USER <USERNAME>
-- Example
REVOKE SELECT ON TESTDB.TESTTABLE FROM USER TEST_USER

4. Revoke global query privileges

REVOKE SELECT ON ANY FROM USER <USERNAME>
-- Example
REVOKE SELECT ON ANY FROM USER TEST_USER

5. ALL Syntax Sugar for revocation

-- Revoke all global privileges and ANY-scoped data privileges
REVOKE ALL FROM USER TESTUSER

-- Only revoke ANY-scoped data privileges
REVOKE ALL ON ANY FROM USER TESTUSER

-- Only revoke all data privileges of the specified database
REVOKE ALL ON DATABASE TESTDB FROM USER TESTUSER

-- Only revoke all data privileges of the specified table
REVOKE ALL ON TABLE TESTDB FROM USER TESTUSER

4.3 View User Privileges

Each user has an independent privilege list recording all authorized permissions. Use the following statement to query privilege details:

LIST PRIVILEGES OF USER <USERNAME>

Output format:

ROLE	SCOPE	PRIVILEGE	WITH GRANT OPTION
	DB1.TB1	SELECT	FALSE
		SECURITY	TRUE
ROLE1	DB2.TB2	INSERT	TRUE
ROLE1	DB3.*	DELETE	FALSE
ROLE1	.	UPDATE	TRUE

• ROLE Column: Blank for self-owned user privileges; displays the role name if the privilege is inherited from a role.
• SCOPE Column: Table-level scope displayed as DB.TABLE, database-level scope as DB.*, and global ANY scope as *.*.
• PRIVILEGE Column: Lists specific authorized privilege types.
• WITH GRANT OPTION Column: TRUE means the user can regrant or revoke privileges within the corresponding scope.

Note: Users and roles can hold permissions for both the Tree Model and Table Model simultaneously. The system only displays permissions applicable to the currently connected model, while permissions for the other model are hidden.

5. Practical Scenario Example

Based on the Sample Data, data in different tables belongs to two independent data centers (bj and sh). To ensure data isolation, cross-center data access needs to be restricted through permission control.

5.1 Create Users

Use the CREATE USER statement to create new users. For example, the root administrator creates two dedicated write users for the BJ and SH data centers: bj_write_user and sh_write_user, with a unified password write_pwd.

CREATE USER bj_write_user 'write_pwd';
CREATE USER sh_write_user 'write_pwd';

Execute the user query statement:

LIST USER

Query result:

+------+-------------+-----------------+-----------------+
|UserId|         User|MaxSessionPerUser|MinSessionPerUser|
+------+-------------+-----------------+-----------------+
|     0|         root|               -1|                1|
| 10000|bj_write_user|               -1|               -1|
| 10001|sh_write_user|               -1|               -1|
+------+-------------+-----------------+-----------------+

5.2 Grant Privileges

Newly created users have no permissions by default and cannot perform database operations. For example, an insertion executed by bj_write_user will fail:

INSERT INTO table1(region, plant_id, device_id, model_id, maintenance, time, temperature, humidity, status, arrival_time) VALUES ('Beijing', '1001', '100', 'A', '180', '2025-03-26 13:37:00', 190.0, 30.1, false, '2025-03-26 13:37:34')

Error message after switching to the target database:

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 803: Access Denied: DATABASE database1

Grant table write privileges to bj_write_user via the root account:

GRANT INSERT ON database1.table1 TO USER bj_write_user

Retry data insertion after switching databases:

IoTDB> use database1
Msg: The statement is executed successfully.
IoTDB:database1> INSERT INTO table1(region, plant_id, device_id, model_id, maintenance, time, temperature, humidity, status, arrival_time) VALUES ('Beijing', '1001', '100', 'A', '180', '2025-03-26 13:37:00', 190.0, 30.1, false, '2025-03-26 13:37:34')
Msg: The statement is executed successfully.

5.3 Revoke Privileges

Use the REVOKE statement to reclaim granted permissions:

REVOKE INSERT ON database1.table1 FROM USER bj_write_user
REVOKE INSERT ON database1.table2 FROM USER sh_write_user

After revocation, the user loses corresponding write permissions and will receive a permission denied error when attempting to write data again:

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 803: Access Denied: No permissions for this operation, please add privilege INSERT ON database1.table1