[TiDB Usage Environment] Production Environment / Testing / POC
[TiDB Version] 6.1, 7.5
[Encountered Problem: Problem Phenomenon and Impact]
Our requirement background is: planning to configure two MySQL clients on the jump server, one for read-only, let’s call it mysql, and one for changes, let’s call it mysql_rw. This way, regardless of the account password used, we can default to prevent some unnecessary updates.
MySQL can set SET TRANSACTION READ ONLY=1 in init_connect to make the current session read-only, but TiDB has not implemented this system variable. Are there any other solutions? Adding a dedicated global read-only account is possible, but what if we want any account password to connect and default to read-only?
I’ve been searching for a long time and haven’t found a good method. If it doesn’t work, I might have to add a global read-only account in the end.
The best solution is to create a read-only account. Additionally, you can use middleware such as MySQL Proxy or MariaDB MaxScale to set the read-only mode at the proxy layer.
Thank you all for your suggestions, but this is to prevent DBAs from making mistakes on the command line. This is not the same scenario as opening read-only queries for developers to query.
If you use START TRANSACTION READ ONLY, it still allows updates upon commit. If you’re worried about accidental operations, you can use:
-U, --safe-updates Only allow UPDATE and DELETE that use keys.
-U, --i-am-a-dummy Synonym for option --safe-updates, -U.
Although it doesn’t completely prevent updates, it should block 90% of accidental operations.
Create Read-Only Account: Create a dedicated global account for read-only operations and set the permissions of this account to read-only. Although this is not the “default” behavior you want, it is a simple and effective solution for many users.
Use TiDB’s Permission System: You can use TiDB’s permission system to control the permissions of different accounts. For example, create a read-only role and grant this role to all users. This way, even if users try to perform write operations with other accounts, they will fail due to permission restrictions.
Middleware Control: If you are using TiDB middleware (such as TiDB Dashboard, Mydumper, Loader, etc.), you can implement read-only control at the middleware level. For example, configure the middleware to only forward read operation requests to the TiDB cluster.
Custom Proxy: Write a custom proxy service that intercepts all database requests and converts write requests to read requests or directly rejects them. This way, all connections through the proxy will be read-only by default.
TiDB Jump Server Configuration: On the jump server, you can configure firewall rules or use tools like iptables to intercept and modify database requests to achieve read-only.
Audit Plugin: Although TiDB’s audit plugin cannot directly prevent write operations, it can record all attempted write operations, helping you monitor and audit database activities.
TiDB Enterprise Edition: If you are using TiDB Enterprise Edition, consider using the fine-grained permission control features of the enterprise edition, which may provide more advanced permission management options.
Community Feedback and Contribution: If the above solutions do not meet your needs, you can consider submitting a feature request or contributing code to the TiDB community. TiDB is an open-source project, and contributions from community members are crucial to its development.
|
username: hzc989
|
username: 
TiDB hasn’t implemented this elegantly.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: