Administration Management

IoTDB provides users with account privilege management operations, so as to ensure data security.

We will show you basic user privilege management operations through the following specific examples. Detailed SQL syntax and usage details can be found in SQL Documentation. At the same time, in the JAVA programming environment, you can use the Java JDBC to execute privilege management statements in a single or batch mode.

Basic Concepts

User

The user is the legal user of the database. A user corresponds to a unique username and has a password as a means of authentication. Before using a database, a person must first provide a legitimate username and password to make himself/herself a user.

Privilege

The database provides a variety of operations, and not all users can perform all operations. If a user can perform an operation, the user is said to have the privilege to perform the operation. privileges are divided into data management privilege (such as adding, deleting and modifying data) and authority management privilege (such as creation and deletion of users and roles, granting and revoking of privileges, etc.). Data management privilege often needs a path to limit its effective range. It is flexible that using path pattern to manage privileges.

Role

A role is a set of privileges and has a unique role name as an identifier. A user usually corresponds to a real identity (such as a traffic dispatcher), while a real identity may correspond to multiple users. These users with the same real identity tend to have the same privileges. Roles are abstractions that can unify the management of such privileges.

Default User

There is a default user in IoTDB after the initial installation: root, and the default password is root. This user is an administrator user, who cannot be deleted and has all the privileges. Neither can new privileges be granted to the root user nor can privileges owned by the root user be deleted.

Privilege Management Operation Examples

According to the sample dataAdministration - 图1open in new window, the sample data of IoTDB might belong to different power generation groups such as ln, sgcc, etc. Different power generation groups do not want others to obtain their own database data, so we need to have data privilege isolated at the group layer.

Create User

We use CREATE USER <userName> <password> to create users. For example, we can use root user who has all privileges to create two users for ln and sgcc groups, named ln_write_user and sgcc_write_user, with both passwords being write_pwd. It is recommended to wrap the username in backtick(`). The SQL statement is:

  1. CREATE USER `ln_write_user` 'write_pwd'
  2. CREATE USER `sgcc_write_user` 'write_pwd'

Then use the following SQL statement to show the user:

  1. LIST USER

As can be seen from the result shown below, the two users have been created:

  1. IoTDB> CREATE USER `ln_write_user` 'write_pwd'
  2. Msg: The statement is executed successfully.
  3. IoTDB> CREATE USER `sgcc_write_user` 'write_pwd'
  4. Msg: The statement is executed successfully.
  5. IoTDB> LIST USER
  6. +---------------+
  7. | user|
  8. +---------------+
  9. | ln_write_user|
  10. | root|
  11. |sgcc_write_user|
  12. +---------------+
  13. Total line number = 3
  14. It costs 0.157s

Grant User Privilege

At this point, although two users have been created, they do not have any privileges, so they can not operate on the database. For example, we use ln_write_user to write data in the database, the SQL statement is:

  1. INSERT INTO root.ln.wf01.wt01(timestamp,status) values(1509465600000,true)

The SQL statement will not be executed and the corresponding error prompt is given as follows:

  1. IoTDB> INSERT INTO root.ln.wf01.wt01(timestamp,status) values(1509465600000,true)
  2. Msg: 602: No permissions for this operation, please add privilege INSERT_TIMESERIES.

Now, we use root user to grant the two users write privileges to the corresponding databases.

We use GRANT USER <userName> PRIVILEGES <privileges> ON <nodeName> to grant user privileges(ps: grant create user does not need path). For example:

  1. GRANT USER `ln_write_user` PRIVILEGES INSERT_TIMESERIES on root.ln.**
  2. GRANT USER `sgcc_write_user` PRIVILEGES INSERT_TIMESERIES on root.sgcc1.**, root.sgcc2.**
  3. GRANT USER `ln_write_user` PRIVILEGES CREATE_USER

The execution result is as follows:

  1. IoTDB> GRANT USER `ln_write_user` PRIVILEGES INSERT_TIMESERIES on root.ln.**
  2. Msg: The statement is executed successfully.
  3. IoTDB> GRANT USER `sgcc_write_user` PRIVILEGES INSERT_TIMESERIES on root.sgcc1.**, root.sgcc2.**
  4. Msg: The statement is executed successfully.
  5. IoTDB> GRANT USER `ln_write_user` PRIVILEGES CREATE_USER
  6. Msg: The statement is executed successfully.

Next, use ln_write_user to try to write data again.

  1. IoTDB> INSERT INTO root.ln.wf01.wt01(timestamp, status) values(1509465600000, true)
  2. Msg: The statement is executed successfully.

Revoker User Privilege

After granting user privileges, we could use REVOKE USER <userName> PRIVILEGES <privileges> ON <nodeName> to revoke the granted user privileges(ps: revoke create user does not need path). For example, use root user to revoke the privilege of ln_write_user and sgcc_write_user:

  1. REVOKE USER `ln_write_user` PRIVILEGES INSERT_TIMESERIES on root.ln.**
  2. REVOKE USER `sgcc_write_user` PRIVILEGES INSERT_TIMESERIES on root.sgcc1.**, root.sgcc2.**
  3. REVOKE USER `ln_write_user` PRIVILEGES CREATE_USER

The execution result is as follows:

  1. REVOKE USER `ln_write_user` PRIVILEGES INSERT_TIMESERIES on root.ln.**
  2. Msg: The statement is executed successfully.
  3. REVOKE USER `sgcc_write_user` PRIVILEGES INSERT_TIMESERIES on root.sgcc1.**, root.sgcc2.**
  4. Msg: The statement is executed successfully.
  5. REVOKE USER `ln_write_user` PRIVILEGES CREATE_USER
  6. Msg: The statement is executed successfully.

After revoking, ln_write_user has no permission to writing data to root.ln.**

  1. INSERT INTO root.ln.wf01.wt01(timestamp, status) values(1509465600000, true)
  2. Msg: 602: No permissions for this operation, please add privilege INSERT_TIMESERIES.

SQL Statements

Here are all related SQL statements:

  • Create User
  1. CREATE USER <userName> <password>;
  2. Eg: IoTDB > CREATE USER `thulab` 'pwd';
  • Delete User
  1. DROP USER <userName>;
  2. Eg: IoTDB > DROP USER `xiaoming`;
  • Create Role
  1. CREATE ROLE <roleName>;
  2. Eg: IoTDB > CREATE ROLE `admin`;
  • Delete Role
  1. DROP ROLE <roleName>;
  2. Eg: IoTDB > DROP ROLE `admin`;
  • Grant User Privileges
  1. GRANT USER <userName> PRIVILEGES <privileges> ON <nodeNames>;
  2. Eg: IoTDB > GRANT USER `tempuser` PRIVILEGES INSERT_TIMESERIES, DELETE_TIMESERIES on root.ln.**, root.sgcc.**;
  3. Eg: IoTDB > GRANT USER `tempuser` PRIVILEGES CREATE_ROLE;
  • Grant User All Privileges
  1. GRANT USER <userName> PRIVILEGES ALL;
  2. Eg: IoTDB > GRANT USER `tempuser` PRIVILEGES ALL;
  • Grant Role Privileges
  1. GRANT ROLE <roleName> PRIVILEGES <privileges> ON <nodeNames>;
  2. Eg: IoTDB > GRANT ROLE `temprole` PRIVILEGES INSERT_TIMESERIES, DELETE_TIMESERIES ON root.sgcc.**, root.ln.**;
  3. Eg: IoTDB > GRANT ROLE `temprole` PRIVILEGES CREATE_ROLE;
  • Grant Role All Privileges
  1. GRANT ROLE <roleName> PRIVILEGES ALL ON <nodeNames>;
  2. Eg: IoTDB > GRANT ROLE `temprole` PRIVILEGES ALL;
  • Grant User Role
  1. GRANT <roleName> TO <userName>;
  2. Eg: IoTDB > GRANT `temprole` TO tempuser;
  • Revoke User Privileges
  1. REVOKE USER <userName> PRIVILEGES <privileges> ON <nodeNames>;
  2. Eg: IoTDB > REVOKE USER `tempuser` PRIVILEGES DELETE_TIMESERIES on root.ln.**;
  3. Eg: IoTDB > REVOKE USER `tempuser` PRIVILEGES CREATE_ROLE;
  • Revoke User All Privileges
  1. REVOKE USER <userName> PRIVILEGES ALL;
  2. Eg: IoTDB > REVOKE USER `tempuser` PRIVILEGES ALL;
  • Revoke Role Privileges
  1. REVOKE ROLE <roleName> PRIVILEGES <privileges> ON <nodeNames>;
  2. Eg: IoTDB > REVOKE ROLE `temprole` PRIVILEGES DELETE_TIMESERIES ON root.ln.**;
  3. Eg: IoTDB > REVOKE ROLE `temprole` PRIVILEGES CREATE_ROLE;
  • Revoke All Role Privileges
  1. REVOKE ROLE <roleName> PRIVILEGES ALL;
  2. Eg: IoTDB > REVOKE ROLE `temprole` PRIVILEGES ALL;
  • Revoke Role From User
  1. REVOKE <roleName> FROM <userName>;
  2. Eg: IoTDB > REVOKE `temprole` FROM tempuser;
  • List Users
  1. LIST USER
  2. Eg: IoTDB > LIST USER
  • List User of Specific Role
  1. LIST USER OF ROLE <roleName>;
  2. Eg: IoTDB > LIST USER OF ROLE `roleuser`;
  • List Roles
  1. LIST ROLE
  2. Eg: IoTDB > LIST ROLE
  • List Roles of Specific User
  1. LIST ROLE OF USER <username> ;
  2. Eg: IoTDB > LIST ROLE OF USER `tempuser`;
  • List All Privileges of Users
  1. LIST PRIVILEGES USER <username> ;
  2. Eg: IoTDB > LIST PRIVILEGES USER `tempuser`;
  • List Related Privileges of Users(On Specific Paths)
  1. LIST PRIVILEGES USER <username> ON <paths>;
  2. Eg: IoTDB> LIST PRIVILEGES USER `tempuser` ON root.ln.**, root.ln.wf01.**;
  3. +--------+-----------------------------------+
  4. | role| privilege|
  5. +--------+-----------------------------------+
  6. | | root.ln.** : ALTER_TIMESERIES|
  7. |temprole|root.ln.wf01.** : CREATE_TIMESERIES|
  8. +--------+-----------------------------------+
  9. Total line number = 2
  10. It costs 0.005s
  11. IoTDB> LIST PRIVILEGES USER `tempuser` ON root.ln.wf01.wt01.**;
  12. +--------+-----------------------------------+
  13. | role| privilege|
  14. +--------+-----------------------------------+
  15. | | root.ln.** : ALTER_TIMESERIES|
  16. |temprole|root.ln.wf01.** : CREATE_TIMESERIES|
  17. +--------+-----------------------------------+
  18. Total line number = 2
  19. It costs 0.005s
  • List All Privileges of Roles
  1. LIST PRIVILEGES ROLE <roleName>
  2. Eg: IoTDB > LIST PRIVILEGES ROLE `actor`;
  • List Related Privileges of Roles(On Specific Paths)
  1. LIST PRIVILEGES ROLE <roleName> ON <paths>;
  2. Eg: IoTDB> LIST PRIVILEGES ROLE `temprole` ON root.ln.**, root.ln.wf01.wt01.**;
  3. +-----------------------------------+
  4. | privilege|
  5. +-----------------------------------+
  6. |root.ln.wf01.** : CREATE_TIMESERIES|
  7. +-----------------------------------+
  8. Total line number = 1
  9. It costs 0.005s
  10. IoTDB> LIST PRIVILEGES ROLE `temprole` ON root.ln.wf01.wt01.**;
  11. +-----------------------------------+
  12. | privilege|
  13. +-----------------------------------+
  14. |root.ln.wf01.** : CREATE_TIMESERIES|
  15. +-----------------------------------+
  16. Total line number = 1
  17. It costs 0.005s
  • Alter Password
  1. ALTER USER <username> SET PASSWORD <password>;
  2. Eg: IoTDB > ALTER USER `tempuser` SET PASSWORD 'newpwd';

Other Instructions

The Relationship among Users, Privileges and Roles

A Role is a set of privileges, and privileges and roles are both attributes of users. That is, a role can have several privileges and a user can have several roles and privileges (called the user’s own privileges).

At present, there is no conflicting privilege in IoTDB, so the real privileges of a user is the union of the user’s own privileges and the privileges of the user’s roles. That is to say, to determine whether a user can perform an operation, it depends on whether one of the user’s own privileges or the privileges of the user’s roles permits the operation. The user’s own privileges and privileges of the user’s roles may overlap, but it does not matter.

It should be noted that if users have a privilege (corresponding to operation A) themselves and their roles contain the same privilege, then revoking the privilege from the users themselves alone can not prohibit the users from performing operation A, since it is necessary to revoke the privilege from the role, or revoke the role from the user. Similarly, revoking the privilege from the users’s roles alone can not prohibit the users from performing operation A.

At the same time, changes to roles are immediately reflected on all users who own the roles. For example, adding certain privileges to roles will immediately give all users who own the roles corresponding privileges, and deleting certain privileges will also deprive the corresponding users of the privileges (unless the users themselves have the privileges).

List of Privileges Included in the System

List of privileges Included in the System

privilege NameInterpretationExample
CREATE_DATABASEcreate database; set/unset database ttl; path dependentEg1: CREATE DATABASE root.ln;
Eg2:set ttl to root.ln 3600000;
Eg3:unset ttl to root.ln;
DELETE_DATABASEdelete databases; path dependentEg: delete database root.ln;
CREATE_TIMESERIEScreate timeseries; path dependentEg1: create timeseries
create timeseries root.ln.wf02.status with datatype=BOOLEAN,encoding=PLAIN;
Eg2: create aligned timeseries
create aligned timeseries root.ln.device1(latitude FLOAT encoding=PLAIN compressor=SNAPPY, longitude FLOAT encoding=PLAIN compressor=SNAPPY);
INSERT_TIMESERIESinsert data; path dependentEg1: insert into root.ln.wf02(timestamp,status) values(1,true);
Eg2: insert into root.sg1.d1(time, s1, s2) aligned values(1, 1, 1)
ALTER_TIMESERIESalter timeseries; path dependentEg1: alter timeseries root.turbine.d1.s1 ADD TAGS tag3=v3, tag4=v4;
Eg2: ALTER timeseries root.turbine.d1.s1 UPSERT ALIAS=newAlias TAGS(tag2=newV2, tag3=v3) ATTRIBUTES(attr3=v3, attr4=v4);
READ_TIMESERIESquery data; path dependentEg1: SHOW DATABASES;
Eg2: show child paths root.ln, show child nodes root.ln;
Eg3: show devices;
Eg4: show timeseries root.;
Eg5: show schema templates;
Eg6: show all ttl
Eg7: Query-Data(The query statements under this section all use this permission)
Eg8: CVS format data export
./export-csv.bat -h 127.0.0.1 -p 6667 -u tempuser -pw root -td ./
Eg9: Performance Tracing Tool
tracing select * from root.

Eg10: UDF-Query
select example() from root.sg.d1
Eg11: Triggers-Query
show triggers
Eg12: Count-Query
count devices
DELETE_TIMESERIESdelete data or timeseries; path dependentEg1: delete timeseries
delete timeseries root.ln.wf01.wt01.status
Eg2: delete data
delete from root.ln.wf02.wt02.status where time < 10
Eg3: use drop semantic
`drop timeseries root.ln.wf01.wt01.status
CREATE_USERcreate users; path independentEg: create user thulab ‘passwd’;
DELETE_USERdelete users; path independentEg: drop user xiaoming;
MODIFY_PASSWORDmodify passwords for all users; path independent; (Those who do not have this privilege can still change their own asswords. )Eg: alter user tempuser SET PASSWORD ‘newpwd’;
LIST_USERlist all users; list all user of specific role; list a user’s related privileges on speciific paths; path independentEg1: list user;
Eg2: list user of role ‘wirte_role’;
Eg3: list privileges user admin;
Eg4: list privileges user ‘admin’ on root.sgcc.;
GRANT_USER_PRIVILEGEgrant user privileges; path independentEg: grant user tempuser privileges DELETE_TIMESERIES on root.ln.;
REVOKE_USER_PRIVILEGErevoke user privileges; path independentEg: revoke user tempuser privileges DELETE_TIMESERIES on root.ln.;
GRANT_USER_ROLEgrant user roles; path independentEg: grant temprole to tempuser;
REVOKE_USER_ROLErevoke user roles; path independentEg: revoke temprole from tempuser;
CREATE_ROLEcreate roles; path independentEg: create role admin;
DELETE_ROLEdelete roles; path independentEg: drop role admin;
LIST_ROLElist all roles; list all roles of specific user; list a role’s related privileges on speciific paths; path independentEg1: list role
Eg2: list role of user ‘actor’;
Eg3: list privileges role wirte_role;
Eg4: list privileges role wirte_role ON root.sgcc;
GRANT_ROLE_PRIVILEGEgrant role privileges; path independentEg: grant role temprole privileges DELETE_TIMESERIES ON root.ln.;
REVOKE_ROLE_PRIVILEGErevoke role privileges; path independentEg: revoke role temprole privileges DELETE_TIMESERIES ON root.ln.*;
CREATE_FUNCTIONregister UDFs; path independentEg: create function example AS ‘org.apache.iotdb.udf.UDTFExample’;
DROP_FUNCTIONderegister UDFs; path independentEg: drop function example
CREATE_TRIGGERcreate triggers; path dependentEg1: CREATE TRIGGER <TRIGGER-NAME> BEFORE INSERT ON <FULL-PATH> AS <CLASSNAME>
Eg2: CREATE TRIGGER <TRIGGER-NAME> AFTER INSERT ON <FULL-PATH> AS <CLASSNAME>
DROP_TRIGGERdrop triggers; path dependentEg: drop trigger ‘alert-listener-sg1d1s1’
CREATE_CONTINUOUS_QUERYcreate continuous queries; path independentEg: select s1, s1 into t1, t2 from root.sg.d1
DROP_CONTINUOUS_QUERYdrop continuous queries; path independentEg1: DROP CONTINUOUS QUERY cq3
Eg2: DROP CQ cq3
SHOW_CONTINUOUS_QUERIESshow continuous queries; path independentEg1: SHOW CONTINUOUS QUERIES
Eg2: SHOW cqs
UPDATE_TEMPLATEcreate and drop schema template; path independentEg1: create schema template t1(s1 int32)
Eg2: drop schema template t1
READ_TEMPLATEshow schema templates and show nodes in schema template; path independentEg1: show schema templates
Eg2: show nodes in template t1
APPLY_TEMPLATEset, unset and activate schema template; path dependentEg1: set schema template t1 to root.sg.d
Eg2: unset schema template t1 from root.sg.d
Eg3: create timeseries of schema template on root.sg.d
Eg4: delete timeseries of schema template on root.sg.d
READ_TEMPLATE_APPLICATIONshow paths set and using schema template; path independentEg1: show paths set schema template t1
Eg2: show paths using schema template t1

Note that path dependent privileges can only be granted or revoked on root.**;

Note that the following SQL statements need to be granted multiple permissions before they can be used:

  • Import data: Need to assign READ_TIMESERIESINSERT_TIMESERIES two permissions.。
  1. Eg: IoTDB > ./import-csv.bat -h 127.0.0.1 -p 6667 -u renyuhua -pw root -f dump0.csv
  • Query Write-back (SELECT INTO)
    • READ_TIMESERIES permission of source sequence in all select clauses is required
    • INSERT_TIMESERIES permission of target sequence in all into clauses is required
  1. Eg: IoTDB > select s1, s1 into t1, t2 from root.sg.d1 limit 5 offset 1000

Username Restrictions

IoTDB specifies that the character length of a username should not be less than 4, and the username cannot contain spaces.

Password Restrictions

IoTDB specifies that the character length of a password should have no less than 4 character length, and no spaces. The password is encrypted with MD5.

Role Name Restrictions

IoTDB specifies that the character length of a role name should have no less than 4 character length, and no spaces.

Path pattern in Administration Management

A path pattern’s result set contains all the elements of its sub pattern’s result set. For example, root.sg.d.* is a sub pattern of root.sg.*.*, while root.sg.** is not a sub pattern of root.sg.*.*. When a user is granted privilege on a pattern, the pattern used in his DDL or DML must be a sub pattern of the privilege pattern, which guarantees that the user won’t access the timeseries exceed his privilege scope.

Permission cache

In distributed related permission operations, when changing permissions other than creating users and roles, all the cache information of dataNode related to the user (role) will be cleared first. If any dataNode cache information is clear and fails, the permission change task will fail.

Operations restricted by non root users

At present, the following SQL statements supported by iotdb can only be operated by the root user, and no corresponding permission can be given to the new user.

TsFile Management
  • Load TsFiles
  1. Eg: IoTDB > load '/Users/Desktop/data/1575028885956-101-0.tsfile'
  • remove a tsfile
  1. Eg: IoTDB > remove '/Users/Desktop/data/data/root.vehicle/0/0/1575028885956-101-0.tsfile'
  • unload a tsfile and move it to a target directory
  1. Eg: IoTDB > unload '/Users/Desktop/data/data/root.vehicle/0/0/1575028885956-101-0.tsfile' '/data/data/tmp'
Delete Time Partition (experimental)
  • Delete Time Partition (experimental)
  1. Eg: IoTDB > DELETE PARTITION root.ln 0,1,2
Continuous Query,CQ
  • Continuous Query,CQ
  1. Eg: IoTDB > CREATE CONTINUOUS QUERY cq1 BEGIN SELECT max_value(temperature) INTO temperature_max FROM root.ln.*.* GROUP BY time(10s) END
Maintenance Command
  • FLUSH
  1. Eg: IoTDB > flush
  • MERGE
  1. Eg: IoTDB > MERGE
  2. Eg: IoTDB > FULL MERGE
  • CLEAR CACHE
  1. Eg: IoTDB > CLEAR CACHE
  • SET STSTEM TO READONLY / WRITABLE
  1. Eg: IoTDB > SET STSTEM TO READONLY / WRITABLE
  • SCHEMA SNAPSHOT
  1. Eg: IoTDB > CREATE SNAPSHOT FOR SCHEMA
  • Query abort
  1. Eg: IoTDB > KILL QUERY 1
Watermark Tool
  • Watermark new users
  1. Eg: IoTDB > grant watermark_embedding to Alice
  • Watermark Detection
  1. Eg: IoTDB > revoke watermark_embedding from Alice