WL#9045: Make user management DDLs atomic

CREATE/ALTER/DROP USER and GRANT/REVOKE are used to manage users' credentials and privileges. These statements allow modification for multiple users. However, modifications performed are not atomic. It is possible that a user management DDL can successfully process some of the users in the list but produces error for others. This in turn creates problems in replication setup as well.

Goal of this worklog is to make sure that DDLs related to user management behave in atomic fashion.

• FR1 : CREATE USER should either execute successfully for all supplied users or no new users should be created.
  • FR1.1 : Similar reasoning should apply to CREATE ROLE which will be introduced as a part of WL#988.

• FR2 : ALTER USER should either execute successfully for all supplied users which exist or none of the users should be altered.

• FR3 : RENAME USER should either execute successfully for all supplied user pairs or none of the users should be renamed.

• FR4 : DROP USER should either execute successfully for all supplied users or none of the users should be dropped.
  • FR4.1 : Similar reasoning should apply to DROP ROLE which will be introduced as a part of WL#988.

• FR5 : GRANT should either execute successfully for all supplied users or no grants should take place.

• FR6 : REVOKE should either execute successfully for all supplied users or no privileges should be revoked.

• FR7 : For statements mentioned in FR1 to FR6, a binlog entry will be created only if there are no errors at the time of execution.

• FR8 : CREATE USER IF NOT EXISTS should either execute successfully for all supplied users which do not exist or no new users should be created.
  • FR8.1 : If CREATE USER IF NOT EXISTS is successful, binlog entry should contain all supplied users - Regardless of number of users created.
  • FR8.2 : While writing a binlog entry for CREATE USER IF NOT EXISTS, if user already exists but is part of CREATE USER IF NOT EXISTS, default authentication plugin should be used to rewrite authentication information if same is not supplied explicitly.
  • FR8.3 : If binlog entry is made for existing users, corresponding warning should be logged in server error log.

• FR9 : ALTER USER IF EXISTS should either execute successfully for all supplied users which do exist or no users should be altered.
  • FR9.1 : If ALTER USER EXISTS is successful, binlog entry should contain all supplied users - Regardless of number of users altered.
  • FR9.2 : While writing a binlog entry for ALTER USER IF EXISTS, if user does not exist but is specified in the ALTER USER IF EXISTS, default authentication plugin should be used to rewrite authentication information if same is not supplied explicitly.
  • FR9.3 : If binlog entry is made for non-existing users, corresponding warning should be logged in server error log.

• FR10 : DROP USER IF EXISTS should execute successfully for all supplied users which exist.
  • FR10.1 : If DROP USER IF EXISTS is successful, binlog entry should contain all supplied users - regardless of number of users actually dropped.

HLS

• I1 : A new MDL_key - ACL_CACHE will be introduced to support locking of ACL tables and caches using MDL.
  • I1.1 : ACL_CACHE will be locked either in MDL_SHARED or MDL_EXCLUSIVE mode based on type of operation.
  • I1.2 : ACL_CACHE will be locked with lock duration MDL_EXPLICIT.
  • I1.3 : Instrumentation for new key type will be added to performance schema
  • I1.4 : We will use "ACL_CACHE_LOCK_NAMESPACE" as namespace value while obtaining MDL for MDL_key::ACL_CACHE
  • I1.5 : In order to retain WL#8355's functionality, we will have 32 partitions for ACL cache. Thus key will be "ACL_CACHE_LOCK_NAMESPACE <partition_number>". Where 0 <= <partition_number> <= 31.
    • I2 : Usage of acl_cache->lock and LOCK_grant will be replaced by taking MDL on ACL_CACHE.

• I3 : For each operation which can modify ACL tables and caches, MDL on ACL_CACHE will be taken in MDL_EXCLUSIVE mode after opening/locking required system tables. Lock will be release only after commit or rollback is done and caches are in consistent state.
  • I3.1 : This covers each operation mentioned in FRs.
  • I3.2 : In addition, for FLUSH PRIVILEGES operation ACL_CACHE will be locked in MDL_EXCLUSIVE mode because FLUSH PRIVILEGES populates caches from system tables.
  • I3.3 : For operations requiring MDL_SHARED type of lock, one of the 32 partitioned will be locked in MDL_SHARED mode.
  • I3.4 : For operations requiring MDL_EXCLUSIVE type of lock, all partitioned will be locked in MDL_EXCLUSIVE mode.

• I4 : For each operation which may read ACL caches, MDL on ACL_CACHE will be taken in MDL_SHARED mode.

• I5 : A new class Acl_cache_lock_guard will be introduced to support locking/unlocking of MDL with ACL_CACHE key.

• I6 : A new helper class Release_acl_cache_locks will be introduced to support unlocking of MDL with ACL_CACHE key.

• I7 : This worklog will result into change in behavior in ACL DDLs.
  • I7.1 : No partial execution will be supported. A DDL will either succeed fully or none of the changes are applied.
  • I7.2 : This means, partial execution + warnings will no longer be logged in binlog. Only successful statements will be logged.

• I8 : If an ACL DDL fails, all intermediate changes done to ACL tables and caches will be rolled back. ACL tables and caches will be in the same state as they were before the execution of the failed DDL.

• I9 : Failure to acquire required locks on ACL caches would result into new error type : ER_CANNOT_LOCK_USER_MANAGEMENT_CACHES