Authority Management

April 3, 2025About 8 min

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: 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

Permission Type	Scope(Hierarchy)	Effect
CREATE	ANY	Create any table/database
	DATABASE	Create tables in the specified database; create a database with the specified name
	TABLE	Create a table with the specified name
DROP	ANY	Delete any table/database
	DATABASE	Delete the specified database or its tables
	TABLE	Delete the specified table
ALTER	ANY	Modify definitions of any table/database
	DATABASE	Modify definitions of the specified database or its tables
	TABLE	Modify the definition of the specified table
SELECT	ANY	Query data from any table in any database
	DATABASE	Query data from any table in the specified database
	TABLE	Query data from the specified table
INSERT	ANY	Insert/update data in any table
	DATABASE	Insert/update data in any table within the specified database
	TABLE	Insert/update data in the specified table
DELETE	ANY	Delete data from any table
	DATABASE	Delete data from tables within the specified database
	TABLE	Delete data from the specified table

3. User and Role Management

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 MD5 hash by default.

Update Password

Users can update their own passwords. Updating others' passwords requires MANAGE_USER.

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

Delete User (Requires MANAGE_USER)

DROP USER <USERNAME>
eg: DROP USER user1

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.

Delete Role (Requires MANAGE_ROLE)

DROP ROLE <ROLENAME>
eg: DROP ROLE role1

Assign Role to User (Requires MANAGE_ROLE)

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

Revoke Role from User (Requires MANAGE_ROLE)

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

List All Users (Requires MANAGE_USER)

LIST USER

List All Roles (Requires MANAGE_ROLE)

LIST ROLE

List Users in a Role (Requires MANAGE_USER)

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

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

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

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

Grant a user the permission to manage users

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

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

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

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

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

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

Revoke a user's permission to manage users

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

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

Revoke a user's permission to query a table

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

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

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