[MDEV-17658] change the structure of mysql.user table Created: 2018-11-10  Updated: 2023-09-21  Resolved: 2018-12-11

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Fix Version/s: 10.4.1

Type: Task Priority: Critical
Reporter: Sergei Golubchik Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-7597 Expiration of user passwords Closed
blocks MDEV-11340 Allow multiple alternative authentica... Closed
blocks MDEV-13095 Implement user account locking Closed
PartOf
includes MDEV-12715 remove mysql.user.password column Closed
Problem/Incident
causes MDEV-21486 Implement option for mysql_install_db... Closed
causes MDEV-21487 Implement option for mysql_upgrade th... Closed
causes MDEV-29542 The first character in column name ha... Closed
causes MDEV-32209 mariadb cannot import 10.3 or older m... Open

 Description   

The suggestion:

Create new table

CREATE TABLE mysql.global_priv (
 
  Host char(60) binary DEFAULT '' NOT NULL,
 
  User char(80) binary DEFAULT '' NOT NULL,
 
  data JSON,
 
  UNIQUE(host,user)
 
)

The data might looks like

{
 
  "SELECT":"Y",
 
  "SUPER":"N",
 
  ...
 
 "plugin":"mysql_native_password",
 
  ...
 
}

and a view:

CREATE VIEW mysql.user AS SELECT
 
  Host, User,
 
  IF(JSON_VALUE(data,"plugin") IN ("mysql_native_password", "mysql_old_password"),
 
     JSON_VALUE(data, "authentication_string"),"") as Password
 
  ...
 
  FROM mysql.global_priv;

the view will show the same data that old mysql.user table had.

The script mysql_fix_system_tables.sql should create new table and move data over.
Also it should convert grants on mysql.user to grants on mysql.global_priv.

 Comments   
Comment by Ralf Gebhardt [ 2018-11-10 ]

serg, can you give a rough effort for this one? As type JSON will be used, wouldn't it make sense to give MDEV-13916 priority critical to assure JSON format by default?

Comment by Sergei Golubchik [ 2018-11-11 ]

No, the JSON column from this task will only be modified by the server, not by users. There's no need to provide an additional CHECK constraint in this case.

Comment by Daniël van Eeden [ 2019-01-08 ]

It would be very helpful to be able to link an account to some external system (e.g. a Request ID or a Ticket ID). This would make some compliance processes much easier. Allowing users to add to this JSON (or part of the JSON) would allow this. Maybe something that abstracts this like ALTER USER myuser COMMENT 'request_id: 1234'.

Generated at Thu Feb 08 08:38:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.