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

Privilege Type	Privilege Name	Scope of Effect	Description
Global Privileges	SYSTEM	Global	Allows users to create, modify, and delete databases.
			Allows users to create, modify, and delete tables and table views.
			Allows users to create, delete, and view user-defined functions.
			Allows users to create, start, stop, delete, and view PIPEs. Allows users to create, delete, and view PIPEPLUGINS.
			Allows users to query and cancel queries. Allows users to view variables. Allows users to view cluster status.
			Allows users to create, delete, and view deep learning models.
	SECURITY	Global	Allows users to create users.
			Allows users to delete users.
			Allows users to modify user passwords.
			Allows users to view user privilege information.
			Allows users to list all users.
			Allows users to create roles.
			Allows users to delete roles.
			Allows users to view role privilege information.
			Allows users to grant a role to a user or revoke it.
			Allows users to list all roles.
	AUDIT	Global	Allows users to maintain audit log rules and view audit logs.
Data Privileges	CREATE	ANY	Allows creating any table and any database.
		Database	Allows users to create tables under this database; allows users to create a database with this name.
		Table	Allows users to create a table with this name.
	ALTER	ANY	Allows modifying the definition of any table and any database.
		Database	Allows users to modify the definition of a database and the definitions of tables under that database.
		Table	Allows users to modify the definition of a table.
	SELECT	ANY	Allows querying data from any table in any database in the system.
		Database	Allows users to query data from any table in this database.
		Table	Allows users to query data in this table. When executing multi-table queries, the database only displays data that the user has permission to access.
	INSERT	ANY	Allows inserting/updating data into any table in any database.
		Database	Allows users to insert/update data into any table within the scope of this database.
		Table	Allows users to insert/update data into this table.
	DELETE	ANY	Allows deleting data from any table.
		Database	Allows users to delete data within the scope of this database.
		Table	Allows users to delete data from this table.

2. SQL Statements

2.1 User and Role Management

1. Create User (Requires SECURITY privilege)

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

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';

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