Authority Management

IoTDB provides permission management functionality to implement fine-grained access control for data and cluster systems, ensuring data and system security. This document introduces the basic concepts, user definitions, permission management, authentication logic, and functional use cases of the permission module in IoTDB's table model.

1. Basic Concepts

1.1 User

A user is a legitimate database user. Each user is associated with a unique username and authenticated via a password. Before accessing the database, a user must provide valid credentials (a username and password that exist in the database).

1.2 Permission

A database supports multiple operations, but not all users can perform every operation. If a user is authorized to execute a specific operation, they are said to have the permission for that operation.

1.3 Role

A role is a collection of permissions, identified by a unique role name. Roles typically correspond to real-world identities (e.g., “traffic dispatcher”), where a single identity may encompass multiple users. Users sharing the same real-world identity often require the same set of permissions, and roles abstract this grouping for unified management.

1.4 Default User and Role

Upon initialization, IoTDB includes a default user:

• ​Username​: root
• ​Default password​: TimechoDB@2021 //before V2.0.6 it is root

The root user is the ​administrator​, inherently possessing all permissions. This user cannot be granted or revoked permissions and cannot be deleted. The database maintains only one administrator user. Newly created users or roles start with no permissions by default.

2. Permission List

In IoTDB's table model, there are two main types of permissions: Global Permissions and Data Permissions .

2.1 Global Permissions

Global permissions include user management and role management.

The following table describes the types of global permissions:

Permission Name	Description
MANAGE_USER	- Create users - Delete users - Modify user passwords - View user permission details - List all users
MANAGE_ROLE	- Create roles - Delete roles - View role permission details - Grant/revoke roles to/from users - List all roles

2.2 Data Permissions

Data permissions consist of permission types and permission scopes.

• Permission Types:
  • CREATE: Permission to create resources
  • DROP: Permission to delete resources
  • ALTER: Permission to modify definitions
  • SELECT: Permission to query data
  • INSERT: Permission to insert/update data
  • DELETE: Permission to delete data
• Permission Scopes:
  • ANY: System-wide (affects all databases and tables)
  • DATABASE: Database-wide (affects the specified database and its tables)
  • TABLE: Table-specific (affects only the specified table)
• Scope Enforcement Logic:

When performing table-level operations, the system matches user permissions with data permission scopes hierarchically. Example: If a user attempts to write data to DATABASE1.TABLE1, the system checks for write permissions in this order: 1. ANY scope → 2. DATABASE1 scope → 3. DATABASE1.TABLE1 scope. The check stops at the first successful match or fails if no permissions are found.

• Permission Type-Scope-Effect Matrix

3. User and Role Management

1. Create User (Requires MANAGE_USER Permission)

CREATE USER <USERNAME> <PASSWORD>   
eg: CREATE USER user1 'passwd'

Constraints:

• Username: 4-32 characters (letters, numbers, special chars: !@#$%^&*()_+-=). Cannot duplicate the admin (root) username.
• Password: 4-32 characters (letters, numbers, special chars). Stored as SHA-256 hash by default.

2. Modify Password

Users can modify their own passwords. Modifying others' passwords requires MANAGE_USER.

ALTER USER <USERNAME> SET PASSWORD <password>
eg: ALTER USER tempuser SET PASSWORD 'newpwd'

3. Delete User (Requires MANAGE_USER)

DROP USER <USERNAME>
eg: DROP USER user1

4. Create Role (Requires MANAGE_ROLE)

CREATE ROLE <ROLENAME>
eg: CREATE ROLE role1

Constraints:

• Role Name: 4-32 characters (letters, numbers, special chars). Cannot duplicate the admin role name.

5. Delete Role (Requires MANAGE_ROLE)

DROP ROLE <ROLENAME>
eg: DROP ROLE role1

6. Assign Role to User (Requires MANAGE_ROLE)

GRANT ROLE <ROLENAME> TO <USERNAME>
eg: GRANT ROLE admin TO user1

7. Revoke Role from User (Requires MANAGE_ROLE)

REVOKE ROLE <ROLENAME> FROM <USERNAME>
eg: REVOKE ROLE admin FROM user1

8. List All Users (Requires MANAGE_USER)

LIST USER

9. List All Roles (Requires MANAGE_ROLE)

LIST ROLE

10. List Users in a Role (Requires MANAGE_USER)

LIST USER OF ROLE <ROLENAME>
eg: LIST USER OF ROLE roleuser

11. List Roles of a User

• Users can list their own permissions.
• Listing others' permissions requires MANAGE_USER.

LIST ROLE OF USER <USERNAME>
eg: LIST ROLE OF USER tempuser

12. List User Permissions

• Users can list their own permissions.
• Listing others' permissions requires MANAGE_USER.

LIST PRIVILEGES OF USER <USERNAME>
eg: LIST PRIVILEGES OF USER tempuser

13. List Role Permissions

• Users can list permissions of roles they have.
• Listing other roles' permissions requires MANAGE_ROLE.

LIST PRIVILEGES OF ROLE <ROLENAME>
eg: LIST PRIVILEGES OF ROLE actor

4. Permission Management

IoTDB supports granting and revoking permissions through the following three methods:

• Direct assignment/revocation by a super administrator
• Assignment/revocation by users with the GRANT OPTION privilege
• Assignment/revocation via roles (managed by super administrators or users with MANAGE_ROLE permissions)

In the IoTDB Table Model, the following principles apply when granting or revoking permissions:

• Global permissions can be granted/revoked without specifying a scope.
• Data permissions require specifying both the permission type and permission scope. When revoking, only the explicitly defined scope is affected, regardless of hierarchical inclusion relationships.
• Preemptive permission planning is allowed—permissions can be granted for databases or tables that do not yet exist.
• Repeated granting/revoking of permissions is permitted.
• WITH GRANT OPTION: Allows users to manage permissions within the granted scope. Users with this option can grant or revoke permissions for other users in the same scope.

4.1 Granting Permissions

1. Grant a user the permission to manage users

GRANT MANAGE_USER TO USER <USERNAME>
eg: GRANT MANAGE_USER TO USER TEST_USER

2. Grant a user the permission to create databases and tables within the database, and allow them to manage permissions in that scope

GRANT CREATE ON DATABASE <DATABASE> TO USER <USERNAME> WITH GRANT OPTION
eg: GRANT CREATE ON DATABASE TESTDB TO USER TEST_USER WITH GRANT OPTION

3. Grant a role the permission to query a database

GRANT SELECT ON DATABASE <DATABASE>TO ROLE <ROLENAME>
eg: GRANT SELECT ON DATABASE TESTDB TO ROLE TEST_ROLE

4. Grant a user the permission to query a table

GRANT SELECT ON <DATABASE>.<TABLENAME> TO USER <USERNAME>
eg: GRANT SELECT ON TESTDB.TESTTABLE TO USER TEST_USER

5. Grant a role the permission to query all databases and tables

GRANT SELECT ON ANY TO ROLE <ROLENAME>
eg: GRANT SELECT ON ANY TO ROLE TEST_ROLE

6. ALL Syntax Sugar: ALL represents all permissions within a given scope, allowing flexible permission granting.

GRANT ALL TO USER TESTUSER
-- Grants all possible permissions to the user, including global permissions and all data permissions under ANY scope.

GRANT ALL ON ANY TO USER TESTUSER
-- Grants all data permissions under the ANY scope. After execution, the user will have all data permissions across all databases.

GRANT ALL ON DATABASE TESTDB TO USER TESTUSER
-- Grants all data permissions within the specified database. After execution, the user will have all data permissions on that database.

GRANT ALL ON TABLE TESTTABLE TO USER TESTUSER
-- Grants all data permissions on the specified table. After execution, the user will have all data permissions on that table.

4.2 Revoking Permissions

1. Revoke a user's permission to manage users

REVOKE MANAGE_USER FROM USER <USERNAME>
eg: REVOKE MANAGE_USER FROM USER TEST_USER

2. Revoke a user's permission to create databases and tables within the database

REVOKE CREATE ON DATABASE <DATABASE> FROM USER <USERNAME>
eg: REVOKE CREATE ON DATABASE TEST_DB FROM USER TEST_USER

3. Revoke a user's permission to query a table

REVOKE SELECT ON <DATABASE>.<TABLENAME> FROM USER <USERNAME>
eg: REVOKE SELECT ON TESTDB.TESTTABLE FROM USER TEST_USER

4. Revoke a user's permission to query all databases and tables

REVOKE SELECT ON ANY FROM USER <USERNAME>
eg: REVOKE SELECT ON ANY FROM USER TEST_USER

5. ALL Syntax Sugar: ALL represents all permissions within a given scope, allowing flexible permission revocation.

REVOKE ALL FROM USER TESTUSER
-- Revokes all global permissions and all data permissions under ANY scope.

REVOKE ALL ON ANY FROM USER TESTUSER
-- Revokes all data permissions under the ANY scope, without affecting DB or TABLE-level permissions.

REVOKE ALL ON DATABASE TESTDB FROM USER TESTUSER
-- Revokes all data permissions on the specified database, without affecting TABLE-level permissions.

REVOKE ALL ON TABLE TESTDB FROM USER TESTUSER
-- Revokes all data permissions on the specified table.

4.3 Viewing User Permissions

Each user has an access control list that identifies all the permissions they have been granted. You can use the LIST PRIVILEGES OF USER <USERNAME> statement to view the permission information of a specific user or role. The output format is as follows:

ROLE	SCOPE	PRIVIVLEGE	WITH GRANT OPTION
	DB1.TB1	SELECT	FALSE
		MANAGE_ROLE	TRUE
ROLE1	DB2.TB2	UPDATE	TRUE
ROLE1	DB3.*	DELETE	FALSE
ROLE1	*.*	UPDATE	TRUE

• ​ROLE column​: If empty, it indicates the user's own permissions. If not empty, it means the permission is derived from a granted role.
• ​SCOPE column​: Represents the permission scope of the user/role. Table-level permissions are denoted as DB.TABLE, database-level permissions as DB.*, and ANY-level permissions as *.*.
• ​PRIVILEGE column​: Lists the specific permission types.
• ​WITH GRANT OPTION column​: If TRUE, it means the user can grant their own permissions to others.
• A user or role can have permissions in both the tree model and the table model, but the system will only display the permissions relevant to the currently connected model. Permissions under the other model will not be shown.

5. Example

Using the content from the Sample Data as an example, the data in the two tables may belong to the bj and sh data centers, respectively. To prevent each center from accessing the other's database data, we need to implement permission isolation at the data center level.

5.1 Creating Users

Use CREATE USER <USERNAME> <PASSWORD> to create users. For example, the root user with all permissions can create two user roles for the ln and sgcc groups, named bj_write_user and ​sh_write_user​, both with the password ​write_pwd​. The SQL statements are:

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

To display the users, use the following SQL statement:

LIST USER

The result will show the two newly created users, as follows:

+-------------+
|         User|
+-------------+
|bj_write_user|
|         root|
|sh_write_user|
+-------------+

5.2 Granting User Permissions

Although the two users have been created, they do not yet have any permissions and thus cannot perform database operations. For example, if the bj_write_user attempts to write data to ​table1​, the SQL statement would be:

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

The system will deny the operation and display an error:

IoTDB> INSERT INTO table1(region, plant_id, device_id, model_id, maintenance, time, temperature, humidity, status, arrival_time) VALUES   ('北京', '1001', '100', 'A', '180', '2025-03-26 13:37:00', 190.0, 30.1, false, '2025-03-26 13:37:34') 
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: database is not specified
IoTDB> use database1
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 803: Access Denied: DATABASE database1

The root user can grant bj_write_user write permissions for table1 using the GRANT <PRIVILEGES> ON <DATABASE.TABLE> TO USER <USERNAME> statement, for example:

GRANT INSERT ON database1.table1 TO USER bj_write_user

After granting permissions, bj_write_user can successfully write data:

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   ('北京', '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 Revoking User Permissions

After granting permissions, the root user can revoke them using the REVOKE <PRIVILEGES> ON <DATABASE.TABLE> FROM USER <USERNAME> statement. For example:

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

Once permissions are revoked, bj_write_user will no longer have write access to ​table1​:

IoTDB:database1> INSERT INTO table1(region, plant_id, device_id, model_id, maintenance, time, temperature, humidity, status, arrival_time) VALUES   ('北京', '1001', '100', 'A', '180', '2025-03-26 13:37:00', 190.0, 30.1, false, '2025-03-26 13:37:34')
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 803: Access Denied: No permissions for this operation, please add privilege INSERT ON database1.table1