[MDEV-19948] 'show grants' return privileges individually Created: 2019-07-04  Updated: 2019-07-25  Resolved: 2019-07-25

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.3.15
Fix Version/s: 10.3.17, 10.4.7

Type: Bug Priority: Major
Reporter: TAO ZHOU Assignee: Anel Husakovic
Resolution: Fixed Votes: 0
Labels: None


 Description   

After upgrading from 10.2.24 to 10.3.15, I found that `show grants` does not return "ALL PRIVILEGES" but returns every single privilege individually.

Before upgrading it returns

GRANT ALL PRIVILEGES ON `app\_test`.* TO 'app_test'@'10.29.67.141' |

After:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `app\_test`.* TO 'app_test'@'10.29.67.141' |

I tried recreating the user and running 'grant all privileges' again, but still the same result.

 Comments   
Comment by Anel Husakovic [ 2019-07-04 ]

Hi laocius,

what happens after second time you start mysqld and run show grants for new user created on 10.3?
Again the same message or ALL PRIVILEGES?
I tried on > 10.3.15 and there is also such problem.

Comment by TAO ZHOU [ 2019-07-04 ]

I tried restarting mysqld and still got the same result.
It just happen to one of my servers and worked well on others.
I am not sure if it's just a configuration problem.

Comment by Anel Husakovic [ 2019-07-07 ]

Hi laocius,
I have investigated more deeply this situation and indeed there is a strange behavior in case when:

  1. you have not used mysql_upgrade on 10.2 datadir, or
  2. you have not used flush privileges.

General observation is that {DELETE_HISTORY_PRIV}} exists in 10.3 and is not used in 10.2. Temporary patch is done 2 days ago.
We have analysed additionally and found that updating rights which are happening in replace_user_table() from get_access() are not well formed in 10.2 and 10.3 while in 10.4 this problem is solved.
The test case in 10.3 is showing this example.
Today I pushed new patch 51c167f90ebf7.
cvicentiu here is result file obtained without the patch which is according to the comments in test file:

#
 
# MDEV-19948 'show grants' return privileges individually
 
#
 
SET @had_user_delete_history_priv := 0;
 
SELECT @had_user_delete_history_priv :=1 FROM mysql.user WHERE Delete_history_priv LIKE '%';
 
@had_user_delete_history_priv :=1
 
1
 
1
 
1
 
1
 
ALTER TABLE mysql.user DROP COLUMN Delete_history_priv;
 
FLUSH PRIVILEGES;
 
CREATE USER ten2;
 
GRANT ALL ON *.* TO ten2;
 
SHOW GRANTS FOR ten2;
 
Grants for ten2@%
 
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'ten2'@'%'
 
FLUSH PRIVILEGES;
 
SHOW GRANTS FOR ten2;
 
Grants for ten2@%
 
GRANT ALL PRIVILEGES ON *.* TO 'ten2'@'%'
 
DROP USER ten2;
 
ALTER TABLE mysql.user ADD Delete_history_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL DEFAULT 'N' after Create_tablespace_priv;
 
UPDATE mysql.user SET Delete_history_priv = Super_priv WHERE @had_user_delete_history_priv = 1;
 
main.anel                                [ pass ]      5

Comment by Anel Husakovic [ 2019-07-25 ]

Closed with 55d8ff0de8168d3b7d4

Updated in 10.2 with commit ee555f8fc5fed02503d45

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