Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
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
- is blocked by
-
-
- Closed
-
- relates to
-
MDEV-19482 Document account locking
-
- Closed
-
Activity
I would be interested in tackling this. For a start, what is the desired syntax for implementing this? I looked briefly into the SQL standard and there's no definition for that. Should we use the existing ALTER USER method or add something new similar to LOCK TABLE?
Regarding the actual locking mechanism, are we looking at a new user flag or manipulate the hash as suggested on the mailing list (like linux does with passwd/shadow files)? The latter might not be a good approach due to the different hashing methods/libraries that are or might be used in the future, as Vicentiu has hinted.
PS: Let me know if these questions would be better directed at the maria-discuss list.
I'm just a bystander, but I'd like to note that IMHO it might make sense to design this feature similar to what already exists in recent MySQL versions:
https://stackoverflow.com/questions/40604087/how-to-lock-a-users-account-in-mysql-5-7
https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/account-locking.html
Another use-case: I'm trying to write a script to automate restoring a database on our webserver which hosts multiple different websites - each of course with their own user/scheme. To restore the database properly, it needs to be wiped first. Unfortunately, after I deleted all tables, sometimes new website calls create new data/tables which subsequently prevent the restore operation. I dont want to stop the whole webserver because other websites should continue running. It would be really great if I could just LOCK the corresponding account to prevent new calls from coming in and wait until old calls have ceased. Then I knew I could easily restore the schema without any problems/interruptions and UNLOCK the account afterwards. I search if such a method is available and found that MySQL has it but not my much preferred MariaDB.