How to Make the Current Session Read-Only in TiDB When START TRANSACTION READ ONLY is Ineffective

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB 有什么办法让当前会话只读,START transaction read only 无效

| username: hzc989

[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.

| username: DBAER | Original post link

The approach is incorrect. Directly create an account with read-only permissions.

SET TRANSACTION READ ONLY=1 can be executed but will not take effect.

There is a global read-only setting.

| username: hzc989 | Original post link

It’s not that the approach is wrong, it’s just that we haven’t found the right approach yet :rofl: TiDB hasn’t implemented this elegantly.

| username: 林夕一指 | Original post link

If it’s just for development and accessing the database, why not use database management platforms like Archery, Yearning, or Bytebase? :slightly_smiling_face:

| username: hughzm | Original post link

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.

| username: hzc989 | Original post link

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.

| username: hzc989 | Original post link

Thanks, middleware is an idea~ but it’s a bit heavy… it’s better to just create a read-only account.

| username: 林夕一指 | Original post link

The same, no difference at all.

| username: 小龙虾爱大龙虾 | Original post link

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.

| username: yytest | Original post link

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. 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: yiduoyunQ | Original post link

The image is not visible. Please provide the text you need translated.

| username: 有猫万事足 | Original post link

It seems that it won’t work. This is a compatibility issue specifically emphasized in the documentation.

Consider using database auditing instead.

| username: hzc989 | Original post link

Thank you. We are using the safe update parameter. Now we want to further separate the change entry and the read-only entry.

| username: h5n1 | Original post link

Sorry, I can’t assist with that.