Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13095

Implement user account locking

    XMLWordPrintable

Details

    • 10.4.0-1

    Description

      MariaDB should support locking or unlocking user accounts via theĀ·
      ACCOUNT LOCK and ACCOUNT UNLOCK options for the CREATE USER
      and ALTER USER statements.

      Given MySQL 5.7 already has this feature, we should preserve
      compatibility in terms of both API and datadir migration.

      We should support the following use cases:

          MariaDB [(none)]> CREATE USER user@localhost ACCOUNT LOCK;
          Query OK, 0 rows affected (0.00 sec)
      

          MariaDB [(none)]> CREATE USER user@localhost ACCOUNT UNLOCK;
          Query OK, 0 rows affected (0.00 sec)
      

          MariaDB [(none)]> ALTER USER user@localhost ACCOUNT LOCK;
          Query OK, 0 rows affected (0.00 sec)
      

          MariaDB [(none)]> SHOW CREATE USER user@localhost;
          +---------------------------------------------+
          | CREATE USER for user@localhost              |   
          +---------------------------------------------+
          | CREATE USER 'user'@'localhost' ACCOUNT LOCK |
          +---------------------------------------------+
          1 row in set (0.000 sec)
      

          MariaDB [(none)]> ALTER USER user@localhost ACCOUNT UNLOCK;
          Query OK, 0 rows affected (0.00 sec)
      

          MariaDB [(none)]> SHOW CREATE USER user@localhost;
          +-----------------------------------------------+
          | CREATE USER for user@localhost                |   
          +-----------------------------------------------+
          | CREATE USER 'user'@'localhost' ACCOUNT UNLOCK |
          +-----------------------------------------------+
          1 row in set (0.000 sec)
      

      When a new connection is attempted to a locked account, the server should
      return an ER_LOCKED_ACCOUNT error code.

      Regarding the required privileges for user account locking, there should be
      no additional privileges required except for what it is already required
      by the CREATE USER and ALTER USER statements.

      Note| The users are allowed to drop themselves or change their own password,
      we should follow a similar behavior in user account locking.

      Implementation details:

      • The locking state of an account should be kept in the JSON Priv column of
        mysql.global_priv. The User_table_json class will be enriched with accessors
        for reading/writing from/to the account_locked JSON field.

            MariaDB [(none)]> select user, host, Priv from mysql.global_priv where user='user';
            +-------+-----------+------------------------------+
            | user  | host      | Priv                         |
            +-------+-----------+------------------------------+
            | user  | localhost | {..., "account_locked":true} |
            +-------+-----------+------------------------------+
            1 row in set (0.001 sec)
        

      • To preserve the drop-in replacement property for MySQL 5.7 datadirs, we have to add
        similar accessors with the ones above to the User_table_tabular class which
        will read/write from/to the account_locked column in the mysql.user table.

      References:
      https://dev.mysql.com/doc/refman/5.7/en/account-locking.html

      Attachments

        Issue Links

          Activity

            People

              robertbindar Robert Bindar
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              2 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.