- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Development Guide
- Design of Data Table
- Importing Data
- Import Overview
- Import Methods
- Synchronizing Data with JDBC
- Synchronizing Data Through External Tables
- Real-time or Batch Writing of MySQL Data
- Importing Data from Kafka
- Importing with DataX
- Importing Doris from Logstash
- Data Transformation, Column Mapping and Filtering
- Importing Strict Mode
- Importing Data in JSON Format
- Exporting Data
- Basic Feature
- Query Optimization
- Ecological Expansion Feature
- Product Policy
- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Development Guide
- Design of Data Table
- Importing Data
- Import Overview
- Import Methods
- Synchronizing Data with JDBC
- Synchronizing Data Through External Tables
- Real-time or Batch Writing of MySQL Data
- Importing Data from Kafka
- Importing with DataX
- Importing Doris from Logstash
- Data Transformation, Column Mapping and Filtering
- Importing Strict Mode
- Importing Data in JSON Format
- Exporting Data
- Basic Feature
- Query Optimization
- Ecological Expansion Feature
- Product Policy
This document describes how Tencent Cloud TCHouse-D implements its privilege management from the kernel level.
Overview
The kernel privilege management system refers to MySQL's mechanism, implementing table-level fine-grained control with role-based Access Control, and supports the allowlist mechanism.
Users and roles are associated, roles and privileges are associated; users are indirectly linked with privileges through roles.
When a role is deleted, users automatically lose all privileges of that role.
When the user and role dissociate, the user automatically loses all privileges of the role.
When the privileges of a role are added or deleted, the privileges of the user also change accordingly.
Authorizing a user is actually authorizing the user's default role. The default role cannot be deleted or assigned to others. When the user is deleted, the default role is also automatically deleted.
┌────────┐ ┌────────┐ ┌────────┐│ user1 ├────┬───► role1 ├────┬────► priv1 │└────────┘ │ └────────┘ │ └────────┘│ ││ ││ ┌────────┐ ││ │ role2 ├────┤┌────────┐ │ └────────┘ │ ┌────────┐│ user2 ├────┘ │ ┌─► priv2 │└────────┘ │ │ └────────┘┌────────┐ │ │┌──────► role3 ├────┘ ││ └────────┘ ││ ││ │┌────────┐ │ ┌────────┐ │ ┌────────┐│ userN ├─┴──────► roleN ├───────┴─► privN │└────────┘ └────────┘ └────────┘
Glossary
User_identity
In the privilege system, a user is recognized as a User Identity. User Identity consists of two parts: username and userhost. The username is made up of uppercase and lowercase English characters. Userhost indicates the IP from which the user link comes. User_identity is presented in the form of username@'userhost', indicating username from userhost.
Another representation of user_identity is username@['domain'], in which domain is a domain name that can be resolved through DNS or BNS (Baidu Naming Service) into a set of IP. Ultimately they are represented as a group of username@'userhost', so we will use username@'userhost' uniformly.
Privilege
The objects of privileges are nodes, data directories, databases, or tables, and different privileges represent different operation privileges.
Role
Doris can create self-defined named roles. A role can be considered as a collection of privileges. Newly created users can be assigned a role and are automatically granted the privileges that the role has. Subsequent changes in the role's privileges will also be reflected in the privileges of all users belonging to that role.
User_Property
User properties directly subordinate to a user, not to a user identifier. That is, cmy@'192.%' and cmy@['domain'] both own the same set of user properties, which belong to user cmy, not to cmy@'192.%' or cmy@['domain'].
User properties include but are not limited to: the maximum number of user connections, importing cluster configuration, etc.
Supported Actions
1. Create User: CREATE USER
2. Modify User: ALTER USER
3. Delete User: DROP USER
4. Authorize/Assign Role: GRANT
5. Revoking Authority/Role: REVOKE
6. Create Role: CREATE ROLE
7. Delete Role: DROP ROLE
8. View Current User Privileges and Role: SHOW GRANTS
9. View All User Privileges and Roles: SHOW ALL GRANTS
10. View Created Roles: SHOW ROLES
11. Set User Properties: SET PROPERTY
12. View User Properties: SHOW PROPERTY
13. Modify Password: SET PASSWORD
For detailed help on the above commands, you can access help in the MySQL client using help + command, such as
HELP CREATE USER.Specific Privileges
Privilege Type
Doris currently supports the following privileges:
1. Node_priv
Change privileges for nodes, including the addition, deletion, and offline operations of FE, BE, and Broker nodes.
Root users have this privilege by default. The user who has both Grant_priv and Node_priv can grant this privilege to other users.
This privilege can only be granted at the global level.
2. Grant_priv
Privilege change privilege. Allows to execute operations including authorization, revocation, addition/deletion/change of user/ role etc.
However, users with this privilege cannot grant other users the node_priv privilege unless they themselves have the node_priv privilege.
3. Select_priv
Read-only privileges to databases and tables.
4. Load_priv
Write privileges to databases and tables, including Load, Insert, Delete, etc.
5. Alter_priv
Change privileges to databases and tables, including renaming databases/tables, add/delete/change columns, add/delete partitions, etc.
6. Create_priv
Privilege to create databases, tables, views.
7. Drop_priv
Privilege to delete databases, tables, views.
8. Usage_priv
Privilege to use resources.
Privilege Hierarchy
According to the different scope of privileges, the privileges of the table can be divided into the following four levels:
1. GLOBAL LEVEL: the privileges on
*.*.* granted by the GRANT statement. The granted privileges are applicable to any table in any database.2. CATALOG LEVEL: the privileges on
ctl.*.* granted by the GRANT statement. The granted privileges are applicable to any table in the designated Catalog.3. DATABASE LEVEL: the privileges on
ctl.db.* granted by the GRANT statement. The granted privileges are applicable to any table in the designated Database.4. TABLE LEVEL: the privileges on
ctl.db.tbl granted by the GRANT statement. The granted privileges are applicable to the designated table in a designated database.Privilege levels for resources are divided into two levels:
1. GLOBAL LEVEL: the privileges on
* granted by the GRANT statement. The granted privileges are applicable to resources.2. RESOURCE LEVEL: the privileges on
resource_name granted by the GRANT statement. The granted privileges are applicable to a specific resource.ADMIN/GRANT Privilege Description
The ADMIN_PRIV and GRANT_PRIV privileges owning the Grant Privilege are rather special. This document explains each operation related to these two privileges.
1. CREATE USER
The user with ADMIN privileges or GRANT privileges at the GLOBAL and DATABASE levels can create a new user.
2. DROP USER
A user with ADMIN privileges or GRANT privileges at the global level can delete a user.
3. CREATE/DROP ROLE
A user with ADMIN privileges or GRANT privileges at the global level can create a role.
4. GRANT/REVOKE
The user with ADMIN privileges or GLOBAL-level GRANT privileges can grant or revoke the privileges of any user.
The user with CATALOG-level GRANT privileges can grant or revoke the privileges of any user for the specified CATALOG.
The user with DATABASE-level GRANT privileges can grant or revoke the privileges of any user for the specified database.
The user with TABLE-level GRANT privileges can grant or revoke the privileges of any user for the specified table in the specified database.
5. SET PASSWORD
The user with ADMIN privileges or GLOBAL-level GRANT privileges can change the password of any user.
Normal users can change the password for their corresponding UserIdentity. The corresponding UserIdentity can be viewed through the
SELECT CURRENT_USER(); command.The user with non-GLOBAL-level GRANT privileges cannot change the password of existing users, it can only be specified when creating a user.
Other Descriptions
1. During Doris initialization, the following users and roles are automatically created:
Operator role: This role has Node_priv and Admin_priv, that is, all privileges on Doris.
Admin role: This role has Admin_priv, that is, all privileges except node changes.
root@'%': root user, allowed to log in from any node as operator.
admin@'%': admin user, allowed to log in from any node as admin.
2. It is not supported to delete or change the privileges of the default created role or user.
3. There is only one user with the operator role, that is, Root. Multiple users can be created for the admin role.
4. Description of operations that may cause conflicts:
Domain and IP conflict:
Assuming the following user is created:
CREATE USER cmy@['domain'];, and authorization: GRANT SELECT_PRIV ON *.* TO cmy@['domain']
The domain is resolved to two IPs: ip1 and ip2. Then, we separately authorized cmy@'ip1' once: GRANT ALTER_PRIV ON *.* TO cmy@'ip1';, then the privileges of cmy@'ip1' will be modified to SELECT_PRIV, ALTER_PRIV. And when we change the privileges of cmy@['domain'] again, cmy@'ip1' will not change accordingly.Repeated IP conflict:
Assuming the following users are created:
CREATE USER cmy@'%' IDENTIFIED BY "12345";, CREATE USER cmy@'192.%' IDENTIFIED BY "abcde";
In terms of priority, '192.%' takes precedence over '%', so when the user cmy tries to log in to Doris using the password '12345' from the machine 192.168.1.1, Doris will be rejected.5. Forget password
If you forget the password and cannot log in to Doris, you can use the following command to log in to Doris without a password on the machine where the Doris FE node is located:
mysql-client -h 127.0.0.1 -P query_port -uroot, after logging in, you can reset the password through the SET PASSWORD command.6. No user can reset the password of the root user, except for the root user.
7. The ADMIN_PRIV privilege can only be granted or revoked at the GLOBAL level.
8. In possession of the GLOBAL level GRANT_PRIV is actually equivalent to having ADMIN_PRIV, as the GRANT_PRIV at this level allows the granting of any privilege. Please be cautious.
9.
current_user() and user()
Users can SELECT current_user(); and SELECT user(); respectively view current_user and user. Where current_user indicates which identity the current user has been authenticated by the system with, and user is the user's actual user_identity.
Example:
Assume a user1@'192.%' is created, and a user1 from 192.168.10.1 Log in to the system, then the current_user is user1@'192.%', while user is user1@'192.168.10.1'. All privileges are granted to a current_user, and the actual user owns all the privileges of the corresponding current_user.Typical Use Cases
Here are some typical use cases of the Doris privileges system.
Scenario 1
Users of the Doris cluster are divided into administrators (Admin), development engineers (RD), and users (Client). The administrator owns all the privileges of the whole cluster, mainly responsible for cluster building, node management etc. Development engineers are responsible for business modeling, including database creation, data import and alteration, etc. Users access different databases and tables to access data.
In this scenario, ADMIN or GRANT privileges can be granted to administrators. RD can be granted CREATE,DROP,ALTER,LOAD,SELECT privileges on any or specific databases and tables. For clients, SELECT privileges can be granted on any or specific databases and tables. Also, different roles can be created to simplify the authorization process for multiple users.
Scenario 2
There are multiple businesses within a cluster, each of which may use one or more data. Each business needs to manage its own users. In such a scenario, the admin user can create a user with the DATABASE level GRANT privileges for each database. This user can only authorize the specified database to users.
Blocklist
Doris itself does not support blocklist, only has an allowlist feature, but we can simulate a blocklist in some ways. Suppose a user named
user@'192.%' is created first, indicating users from 192.* are allowed to sign in. If now we want to prohibit users from 192.168.10.1 from signing in, we can create another user cmy@'192.168.10.1', and set a new password. As the priority of 192.168.10.1 is higher than that of 192.%', users from 192.168.10.1 will no longer be able to use the old password to log in.More help
For more detailed syntax on privilege management, refer to the GRANTS command manual. You can also input
HELP GRANTS in the MySQL client command line to access more help information.
Yes
No
Was this page helpful?