Authority Management

This document is the SQL manual for authority management starting from version V2.0.7. For detailed function usage, see Authority Management. For an introduction to authority management functions before version V2.0.7, refer to Authority Management

1. Privilege List

2. SQL Statements

2.1 User and Role Management

1. Create User (Requires SECURITY privilege)

CREATE USER <USERNAME> <PASSWORD>   
eg: CREATE USER user1 'Passwd@202604';

2. Change Password

Users can change their own passwords, but changing other users' passwords requires the SECURITY privilege.

ALTER USER <USERNAME> SET PASSWORD <password>
eg: ALTER USER tempuser SET PASSWORD 'Newpwd@202604';

3. Drop User (Requires SECURITY privilege)

DROP USER <USERNAME>
eg: DROP USER user1;

4. Create Role (Requires SECURITY privilege)

CREATE ROLE <ROLENAME>
eg: CREATE ROLE role1;

5. Drop Role (Requires SECURITY privilege)

DROP ROLE <ROLENAME>
eg: DROP ROLE role1;

6. Grant Role to User (Requires SECURITY privilege)

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

7. Revoke Role from User (Requires SECURITY privilege)

REVOKE ROLE <ROLENAME> FROM <USERNAME>
eg: 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 All Users Under a Specified Role (Requires SECURITY privilege)

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

11. List All Roles of a Specified User

Users can list their own roles, but listing other users' roles requires the SECURITY privilege.

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

12. List All Privileges of a User

Users can list their own privilege information, but listing other users' privileges requires the SECURITY privilege.

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

13. List All Privileges of a Role

Users can list the privilege information of roles they possess, but listing other roles' privileges requires the SECURITY privilege.

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

2.2 Privilege Management

2.2.1 Grant Privileges

1. Grant user management privileges to a user

GRANT SECURITY TO USER <USERNAME>
eg: GRANT SECURITY TO USER TEST_USER;

2. Grant a user the privilege to create databases and create tables within the database scope, and allow the user to manage privileges within 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 privilege 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 privilege 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 privilege 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 privileges within the object scope. You can use the ALL field to flexibly grant privileges.

GRANT ALL TO USER TESTUSER;
-- Grants all privileges available to the user, including global privileges and all data privileges in the ANY scope

GRANT ALL ON ANY TO USER TESTUSER;
-- Grants all data privileges available in the ANY scope to the user. After executing this statement, the user will have all data privileges on all databases

GRANT ALL ON DATABASE TESTDB TO USER TESTUSER;
-- Grants all data privileges available in the DB scope to the user. After executing this statement, the user will have all data privileges on this database

GRANT ALL ON TABLE TESTTABLE TO USER TESTUSER;
-- Grants all data privileges available in the TABLE scope to the user. After executing this statement, the user will have all data privileges on this table

2.2.2 Revoke Privileges

1. Revoke user management privileges from a user

REVOKE SECURITY FROM USER <USERNAME>
eg: REVOKE SECURITY FROM USER TEST_USER;

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

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

3. Revoke a user's privilege 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 privilege 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 privileges within the object scope. You can use the ALL field to flexibly revoke privileges.

REVOKE ALL FROM USER TESTUSER;
-- Revokes all global privileges and all data privileges in the ANY scope from the user

REVOKE ALL ON ANY FROM USER TESTUSER;
-- Revokes all data privileges in the ANY scope from the user, and does not affect DB-scope and TABLE-scope privileges

REVOKE ALL ON DATABASE TESTDB FROM USER TESTUSER;
-- Revokes all data privileges on the DB from the user, and does not affect TABLE privileges

REVOKE ALL ON TABLE TESTDB FROM USER TESTUSER;
-- Revokes all data privileges on the TABLE from the user

2.2.3 View User Privileges

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