WL#14087: Avoid locking reads from ACL tables
Affects: Server-8.0 — Status: Complete
The ACL tables (e.g. mysql.user and others) can be made read-accessible to user by granting them SELECT privilege by admin. This means that ACL table can be used for reading by commands: SELECT INSERT REPLACE UPDATE DELETE INSERT ... SELECT
INSERT ... SELECT UPDATE|DELETE WHERE (SELECT ...) multi-UPDATE and multi-DELETE Statements. CREATE ... SELECT LOAD DATA ... SET = LOAD XML ... SET = Reading of ACL table done by above statements can cause InnoDB row-level locks being acquired (depending on isolation mode and binary logging mode). This means that concurrent ACL DDL will be blocked, which is an issue, since such non-admin users can block ACL DLL. Revoking SELECT on ACL tables for such users might be not an option. For example, in MySQL Cloud user's admin account (which is separate from MySQL Cloud ociadmin@ account) needs this SELECT privilege to be able to dump/export ACL information from the the cloud. Still this user's account should not be able to interfere with ACL DDL done by MySQL Cloud's ociadmin@. This WL aims to do non-locking reads on ACL table for few commands. i.e., if SERIALIZABLE isolation level is in use, we should silently ignore it and continue doing non-locking reads on ACL tables for some of above commands.
-> Refers to tables listed in WL#14084 HLS Definition 1. ACL Tables. FR1: SELECT statements including SELECT ... FOR SHARE, but excluding SELECT ... FOR UPDATE on ACL tables should not acquire InnoDB row-locks in any isolation mode. This means that they should allow concurrent updates to ACL tables by DML or by ACL DDL (after WL#14084 is implemented). FR2: DML statements that read data from ACL tables but do not update them (e.g. by using subquery on ACL tables in WHERE clause) should not acquire InnoDB row-locks in any isolation mode. This means that the should allow concurrent updates to ACL tables by DML or by ACL DDL (after WL#14084 is implemented). FR3: Issue warning when DML reads ACL table in serializable isolation mode, stating that SE row locks are not acquired. FR4: DML that reads ACL tables which is written to binary log in statement mode would generate a warning, stating that SE row locks are not acquired and operation is not safe for statement logging. Also we need to ensure that in --binlog-format=mixed mode DML statements which read ACL tables are written to binary log using row events, and thus their logging is safe in mixed mode. FR5: Issue a warning when SELECT ... FOR SHARE reads ACL Tables in serializable isolation mode, stating that SE row locks are not acquired.
A. INTRODUCTION: As mentioned in HLD, a user reading
in a concurrent connection may cause ACL DDL and DML's to be blocked. The high-level idea of this WL is to implement non-locking reads on when it is appropriate. Simplifying a bit, we can say that reads from ACL tables become locking reads/acquire SE level locks in the following cases: 1) For commands asking for explicit locking like FLUSH TABLES ... WITH READ LOCK, LOCK TABLES ... READ/READ LOCAL or SELECT ... FOR SHARE/FOR UPDATE. 2) For any non-SELECT statements, to ensure that they are correctly serialized in binary log. (However, this requirement is relaxed for INSERT ... SELECT, REPLACE ... SELECT, UPDATE and CREATE TABLE SELECT statements in READ UNCOMMITTED/COMMITTED isolation modes). 3) In cases when binary logging is ON, uses statement or mixed modes, and we are executing SELECT statement which calls routine which might modify data. Again this is necessary to ensure that such statements are correctly serialized in binary log (in fact, when binlog is enabled and we use statement/mixed mode, we try to do this for all DML that can modify tables, but non-SELECT cases are covered by item 2)). 4) When SELECT is executed in non-autocommit mode in serializable isolation mode. This is necessary to implement semantics prescribed by SQL standard for the serializable mode. Of course, the same semantics requirement apply to other DML in serializable mode, however they are already handled by item 2). 5) When DML statement modifies table participating in a foreign key, constraint check acquires row locks on parent or child table. Cases described in 1) do not cause security concerns as they require extra privileges from users. Thus they should not be affected by this WL. However, SELECT ... FOR SHARE still might require special handling. See below. Requirement for reads to acquire SE locks to ensure correct binlog serialization (handled by cases 2) and 3)) can be relaxed for ACL tables if we sacrifice possibility for binary logging to work correctly in statement mode for DML that reads ACL tables. This looks like a reasonable compromise since: a) ACL tables can be considered special tables which are not manipulated directly that often b) statement/mixed binlog mode are non-default in modern MySQL versions c) Common cases when ACL tables might be manipulated directly using INSERT/UPDATE and DELETE and not ACL DDL won't be affected. Requirement for reads to acquire SE locks to ensure correct semantics of serializable isolation mode (handled by cases 2) and 4) above) can be relaxed for ACL tables if we take conscious step to break SQL-standard semantics of serializable mode for ACL tables. Again, this sounds like a reasonable compromise since: a) ACL tables can be considered special tables which should not be normally queried directly by users. Most important case for such access is probably being dump tools. But mysqldump/pump tools should not be affected by this change as they use REPEATABLE READ isolation mode (or even rely on LOCK TABLES ... READ). b) Serializable isolation level is not default. So most of users SELECTing from ACL tables should not notice anything. Case described in 5) does not cause security concern either as it requires extra privileges from user creating foreign key. Similarly to case 1) this case should not be affected by this WL. When DML reads ACL table in serializable isolation mode, we issue a warning that SE row locks are not acquired. B. DESIGN: The design of this WL is based on: WL#7464 - InnoDB: provide a way to do non-locking reads This WL uses the same interface introduced by above work, handler::extra(ha_extra_function::HA_EXTRA_SKIP_SERIALIZABLE_DD_VIEW) to convey InnoDB to skip acquiring row locks for read operation by this WL. This interface is invoked when opening the tables for a statement inside open_tables(). WL#7464 is implemented in InnoDB to enable skipping read lock and gap locks when 'handler' object is set with HA_EXTRA_SKIP_SERIALIZABLE_DD_VIEW function. These implementations did not take into account. This WL introduces changes to InnoDB code to support the same. These above changes enables execution of ACL DDL and DML that modify the to not block, when a concurrent connection is reading . The following section (C), lists specific commands during which this WL requests InnoDB to skip read locks for . C. COMMANDS AFFECTED: 1. This WL requests InnoDB to skip read row lock on for the following classes of commands: i. All SELECT statements and other read-only statements (e.g. DO/SET) that reference directly (from join list or through subquery), excluding SELECT ... FOR UPDATE, but including SELECT ... FOR SHARE (see below for rationale). ii. All data-modification statements that read directly (using join-list or through subquery). Some examples are: INSERT/REPLACE ... SELECT, UPDATE with subquery in WHERE or SET clauses, DELETE with subquery in WHERE clause, multi-UPDATE/DELETE, LOAD DATA with subquery, CREATE TABLE ... SELECT. iii. All SELECT-like and data-modification statements which read through stored function, or through trigger to be invoked by them. Note: Acquiring row locks when ACL table is read by SELECT ... FOR SHARE doesn't introduce security risk, since extra privileges are required in this case. However, this creates risk of deadlock in concurrently executed ACL DDL (SELECT ... FOR SHARE only acquires SR metadata locks and thus after WL#14084 allows concurrent execution of ACL DDL), which we would like to avoid (as ACL DDL is probably not yet ready to properly handle deadlocks on row level). Thus we ignore locking clause in this case as well. This seems to be reasonable compromise as user's should not normally do SELECT ... FOR SHARE over ACL tables. 2. Commands that modify the continue to acquire write row lock. For example: - INSERT/REPLACE, UPDATE, DELETE, LOAD DATA commands modifying rows in . - SELECT/SET/DO commands that modify through stored routines that they call. 3. The behavior will stay the same for statements that only read , Fro example: 1) Statements requesting explicit locking (except SELECT ... FOR SHARE) like LOCK TABLES ... READ [LOCAL] and SELECT ... FOR UPDATE. 2) Statements doing FK checks on ACL tables. 3) ALTER TABLE ALGORITHM=INPLACE, altering ACL tables. D. REPLICATION: Execution of commands C.1.ii/iii/iv in Row-Based-Replication setup would be recommended. In SBR mode the correct binlogging of DML relies on row-locking and thus might result in inconsistency without locking. Because of the above DML that reads ACL tables and is written to binary log in statement mode would generate a warning, stating SE locks are not acquired and that operation is not safe for binary log. Also we need to ensure that in --binlog-format=mixed mode DML statements which read ACL tables are written to binary log using row events, and thus their logging is safe in mixed mode. E. TOOLS: - MYSQLDUMP/PUMP tool are not affected by this WL. It uses the default isolation level REPEATABLE READ mode. - MYSQLBACKUP tools doing backup and restore operation is not affected by this WL. F. UPGRADE: This WL does not affect upgrade. G. DOCUMENTATION: Documentation should be updated to explicitly state the advantage of this WL, which enables to be not affected by concurrent connections reading . Also, it would be good to document following restrictions. The aim of doing following is to enable a concurrent ACL DDL to be executed without blocking. 1) The DML that reads ACL table will not honor serializable isolation mode. I.e., It would not acquire SE row locks. 2) DML that reads ACL tables which is written to binary log in statement mode would not acquire SE row locks. Note that this operation is not safe for statement logging. 3) The SELECT ... FOR SHARE reading ACL Tables would not acquire SE row locks, i.e. it would not honor FOR SHARE clause.
Notes: 1. This WL introduces a API interface like, 'bool is_acl_table(std::string db_name, std::string table_name);' which is used in open_tables() to determine if we should skip read locks for a
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.