[MDEV-33554] Upgrade from 11.2 to 11.3 changes root's privileges restricting GRANT ALL - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 11.3.2
Fix Version/s: 11.4.2
Component/s: Authentication and Privilege System, Upgrades
Labels:
None
Environment:
Docker container, x86_64 as well as arm64

Description

Freshly created MariaDB versions 11.2 and 11.3 works OK. Podman is used in the examples, but it's the same with the Docker. Images are from Docker Hub:

podman run -d -ti --env MARIADB_ROOT_PASSWORD=manager --name mariadb112 mariadb:11.2

podman exec -it mariadb112 bash

root@9d0b8edb089e:/# mariadb -pmanager

MariaDB [(none)]> show grants for root@'%';

+--------------------------------------------------------------------------------------------------------------------------------+

| Grants for root@%                                                                                                              |

+--------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |

| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION                                                                          |

+--------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.000 sec)

podman run -d -ti --env MARIADB_ROOT_PASSWORD=manager --name mariadb113 mariadb:11.3

podman exec -it mariadb113 bash

root@746a6dc0605e:/# mariadb -pmanager

MariaDB [(none)]> show grants for root@'%';

+--------------------------------------------------------------------------------------------------------------------------------+

| Grants for root@%                                                                                                              |

+--------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |

| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION                                                                          |

+--------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.000 sec)

However, upgrading from 11.2 to 11.3 causes this:

podman run -d -ti --env MARIADB_ROOT_PASSWORD=manager --name mariadb11 -v mariadb:/var/lib/mysql mariadb:11.2

podman exec -it mariadb11 bash

root@3f0e6649e7b1:/# mariadb -pmanager

MariaDB [(none)]> show grants for root@'%';

+--------------------------------------------------------------------------------------------------------------------------------+

| Grants for root@%                                                                                                              |

+--------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |

| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION                                                                          |

+--------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.000 sec)

Exit container and continue with:

podman rm -f mariadb11

podman run -d -ti --env MARIADB_ROOT_PASSWORD=manager --name mariadb11 -v mariadb:/var/lib/mysql mariadb:11.3

podman exec -it mariadb11 bash

root@ad62bd011ef3:/# mariadb -pmanager

MariaDB [(none)]> show grants for root@'%';

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for root@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| 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, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57' WITH GRANT OPTION |

| GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.000 sec)

This change stays even after running mariadb-upgrade command.

This is then causing errors such as

Error granting privileges, invalid priv string: ALL , params: ('sometest', '%'), query: GRANT ALL ON `sometest`.* TO %s@%s , exception: (1044, \"Access denied for user 'root'@'%' to database 'sometest'\").

The issue was also discussed here: https://github.com/ansible-collections/community.mysql/issues/462

Attachments

Issue Links

is caused by

MDEV-29167 new db-level SHOW CREATE ROUTINE privilege

Closed

is duplicated by

MDEV-34851 privileges don't work with grant all privileges

Closed

Activity

Ascending order - Click to sort in descending order

Peter Schiffer created issue - 2024-02-27 21:09

Daniel Black added a comment - 2024-02-27 23:00

SHOW CREATE ROUTINE was added in 11.3. It was intentional that SHOW CREATE ROUTINE is not given automatically to users who had ALL PRIVILEGES in previous versions as users with ALL PRIVILEGES in previous versions weren't able to see the routine body, so they shouldn't after the upgrade either.

That was the rational at the time. It does make the granting of all privileges rather troublesome as you've discovered.

Daniel Black added a comment - 2024-02-27 23:00 SHOW CREATE ROUTINE was added in 11.3 . It was intentional that SHOW CREATE ROUTINE is not given automatically to users who had ALL PRIVILEGES in previous versions as users with ALL PRIVILEGES in previous versions weren't able to see the routine body, so they shouldn't after the upgrade either. That was the rational at the time. It does make the granting of all privileges rather troublesome as you've discovered.

Daniel Black made changes - 2024-02-27 23:01

Field	Original Value	New Value
Fix Version/s		11.3 [ 28565 ]

Daniel Black made changes - 2024-02-27 23:01

Component/s

Upgrades [ 15807 ]

Daniel Black made changes - 2024-02-27 23:01

Assignee

Daniel Black [ danblack ]

Oleksandr Byelkin added a comment - 2024-02-28 11:57

Actually, if root has rights to read mysql.proc (and in case all privileges for all DB it has) nothing changed for the root. root has that privilege vie other means.

Oleksandr Byelkin added a comment - 2024-02-28 11:57 Actually, if root has rights to read mysql.proc (and in case all privileges for all DB it has) nothing changed for the root. root has that privilege vie other means.

Sergei Golubchik made changes - 2024-02-28 19:26

Assignee

Daniel Black [ danblack ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2024-02-28 19:26

Priority

Major [ 3 ]

Blocker [ 1 ]

Sergei Golubchik made changes - 2024-02-28 19:27

Link

This issue blocks MENT-1983 [ MENT-1983 ]

Sergei Golubchik made changes - 2024-02-28 19:27

Link

This issue is caused by MENT-1983 [ MENT-1983 ]

Sergei Golubchik made changes - 2024-02-28 19:27

Link

This issue is caused by MENT-1983 [ MENT-1983 ]

Sergei Golubchik made changes - 2024-02-28 19:27

Link

This issue is caused by ~~MDEV-29167~~ [ ~~MDEV-29167~~ ]

Daniel Black made changes - 2024-02-29 02:01

Summary

Upgrade from 11.2 to 11.3 changes root's privileges

Upgrade from 11.2 to 11.3 changes root's privileges restricting GRANT ALL

Sergei Golubchik made changes - 2024-02-29 17:16

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2024-02-29 17:16

Assignee	Sergei Golubchik [ serg ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2024-02-29 19:26

Assignee

Oleksandr Byelkin [ sanja ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2024-02-29 19:26

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Daniel Black added a comment - 2024-03-01 00:57

serg, don't know you you considered your branch complete, but its currently missing the database level "all" privileges concept. There's no reason for it to be global only.

11.2
MariaDB [(none)]> create user bob@localhost identified by 'bob';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant all on test.* to bob@localhost;
Query OK, 0 rows affected (0.001 sec)

11.3-c5840805202d51abd0e7b18dcc3c0f66f85373da(bb-11.3-serg)) on same datadir
MariaDB [(none)]> show grants for bob@localhost\G
************************* 1. row *************************
Grants for bob@localhost: GRANT USAGE ON . TO `bob`@`localhost` IDENTIFIED BY PASSWORD '*61584B76F6ECE8FB9A328E7CF198094B2FAC55C7'
************************* 2. row *************************
Grants for bob@localhost: 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, DELETE HISTORY ON `test`.* TO `bob`@`localhost`
2 rows in set (0.000 sec)

This is the case even after mariadb-upgrade. The same applies to the PUBLIC role and the test database.

So should all privileges in the mysql.db, (except Delete_history_priv which has a special SUPER only upgrade path), be sufficient to give the PUBLIC,bob user SHOW CREATE ROUTINE duing mariadb-upgrade?

Daniel Black added a comment - 2024-03-01 00:57 serg , don't know you you considered your branch complete, but its currently missing the database level "all" privileges concept. There's no reason for it to be global only. 11.2 MariaDB [(none)]> create user bob@localhost identified by 'bob'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> grant all on test.* to bob@localhost; Query OK, 0 rows affected (0.001 sec) 11.3-c5840805202d51abd0e7b18dcc3c0f66f85373da(bb-11.3-serg)) on same datadir MariaDB [(none)]> show grants for bob@localhost\G *************************** 1. row *************************** Grants for bob@localhost: GRANT USAGE ON *.* TO `bob`@`localhost` IDENTIFIED BY PASSWORD '*61584B76F6ECE8FB9A328E7CF198094B2FAC55C7' *************************** 2. row *************************** Grants for bob@localhost: 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, DELETE HISTORY ON `test`.* TO `bob`@`localhost` 2 rows in set (0.000 sec) This is the case even after mariadb-upgrade. The same applies to the PUBLIC role and the test database. So should all privileges in the mysql.db, (except Delete_history_priv which has a special SUPER only upgrade path), be sufficient to give the PUBLIC,bob user SHOW CREATE ROUTINE duing mariadb-upgrade?

Sergei Golubchik added a comment - 2024-03-01 13:52

yes, correct, I'm fixing it now. that's why I took it back from In-Review.

Sergei Golubchik added a comment - 2024-03-01 13:52 yes, correct, I'm fixing it now. that's why I took it back from In-Review.

Sergei Golubchik made changes - 2024-03-01 15:01

Assignee	Sergei Golubchik [ serg ]	Oleksandr Byelkin [ sanja ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik added a comment - 2024-03-04 15:34

approved on slack

Sergei Golubchik added a comment - 2024-03-04 15:34 approved on slack

Sergei Golubchik made changes - 2024-03-04 15:34

Fix Version/s		11.4.2 [ 29633 ]
Fix Version/s	11.3 [ 28565 ]
Resolution		Fixed [ 1 ]
Status	In Review [ 10002 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2024-03-04 15:34

Component/s

Authentication and Privilege System [ 13101 ]

Ralf Gebhardt made changes - 2024-03-09 00:16

Link

This issue blocks MENT-1983 [ MENT-1983 ]

Archie Cobbs added a comment - 2024-03-25 18:01

Sorry to semi-hijack this issue, but I have three questions...

First, shouldn't this problem be noted in the release notes for 11.3.2?

Secondly, is it the official policy of MariaDB that the root user should always be able to GRANT ALL PRIVILEGES, even after an upgrade? If not then shouldn't it be?? Just want to make sure of that - it's not clear from these bug descriptions.

In our case, we have an application that runs, as the root user, a simple script at every startup containing:

GRANT ALL PRIVILEGES ON `pcom`.* TO 'pcom'@'localhost';

but this script started failing after upgrading to 11.3.2 with

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'pcom'

(i.e., this bug).

Thirdly, how the heck do I repair this now?? Is there some workaround that I can include in our automated script?

Currently the database is showing this:

MariaDB [(none)]> SHOW GRANTS for 'root'@'localhost';

*************************** 1. row ***************************

Grants for root@localhost: 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, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR ON *.* TO `root`@`localhost` WITH GRANT OPTION

*************************** 2. row ***************************

Grants for root@localhost: GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION

2 rows in set (0.000 sec)

I tried adding DELETE HISTORY (as you can see) but that didn't fix the problem.

Thanks.

Archie Cobbs added a comment - 2024-03-25 18:01 Sorry to semi-hijack this issue, but I have three questions... First, shouldn't this problem be noted in the release notes for 11.3.2? Secondly, is it the official policy of MariaDB that the root user should always be able to GRANT ALL PRIVILEGES , even after an upgrade? If not then shouldn't it be?? Just want to make sure of that - it's not clear from these bug descriptions. In our case, we have an application that runs, as the root user, a simple script at every startup containing: GRANT ALL PRIVILEGES ON `pcom`.* TO 'pcom'@'localhost'; but this script started failing after upgrading to 11.3.2 with ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'pcom' (i.e., this bug). Thirdly, how the heck do I repair this now?? Is there some workaround that I can include in our automated script? Currently the database is showing this: MariaDB [(none)]> SHOW GRANTS for 'root'@'localhost'; *************************** 1. row *************************** Grants for root@localhost: 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, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR ON *.* TO `root`@`localhost` WITH GRANT OPTION *************************** 2. row *************************** Grants for root@localhost: GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION 2 rows in set (0.000 sec) I tried adding DELETE HISTORY (as you can see) but that didn't fix the problem. Thanks.

Daniel Black added a comment - 2024-03-25 22:25 - edited

Hi archie172. Thanks for questions. I've answered your SO question on how to recovery.

> First, shouldn't this problem be noted in the release notes for 11.3.2?

Updating now.

> Secondly, is it the official policy of MariaDB that the root user should always be able to GRANT ALL PRIVILEGES, even after an upgrade? If not then shouldn't it be?? Just want to make sure of that - it's not clear from these bug descriptions.

Yes, the fix makes it such that those with ALL PRIVILEGES at a global level gain the privileges added by the server at global and below, and that ALL PRIVILEGES at database level is retained on upgrade.

> Thirdly, how the heck do I repair this now?? Is there some workaround that I can include in our automated script?

Per SO:

start with skip-grant-tables
FLUSH PRIVILEGES
GRANT ALL ON *.* TO root@localhost WITH GRANT OPTION

On later versions if you happened to transition through 11.3, mariadb-upgrade will re-add the privilege.

Daniel Black added a comment - 2024-03-25 22:25 - edited Hi archie172 . Thanks for questions. I've answered your SO question on how to recovery. > First, shouldn't this problem be noted in the release notes for 11.3.2? Updating now. > Secondly, is it the official policy of MariaDB that the root user should always be able to GRANT ALL PRIVILEGES, even after an upgrade? If not then shouldn't it be?? Just want to make sure of that - it's not clear from these bug descriptions. Yes, the fix makes it such that those with ALL PRIVILEGES at a global level gain the privileges added by the server at global and below, and that ALL PRIVILEGES at database level is retained on upgrade. > Thirdly, how the heck do I repair this now?? Is there some workaround that I can include in our automated script? Per SO: start with skip-grant-tables FLUSH PRIVILEGES GRANT ALL ON *.* TO root@localhost WITH GRANT OPTION On later versions if you happened to transition through 11.3, mariadb-upgrade will re-add the privilege.

Sergei Golubchik made changes - 2024-03-25 22:44

Comment

[ First, yes, may be, there's no well defined rule about what bugs we warn about in the release notes. It's a matter of opinions every time.

Second, there was no official policy about it, because this was the first case ever when we've added a truly new privilege for something that was not possible to do before. It seems that indeed, you're right, we'll want {{GRANT ALL PRIVILEGES}} to work after an upgrade — this was the lesson we've learned.

Third, yes, sure. Something like
{code:sql}
update mysql.global_priv set priv=json_replace(priv, '$.access', 18446744073709551615) where user = 'root' and host='localhost';
flush privileges;
{code} ]

Archie Cobbs added a comment - 2024-03-26 00:29

Hi @danblack,

Thanks for the quick response & clear directions. That solved the problem.

Archie Cobbs added a comment - 2024-03-26 00:29 Hi @danblack, Thanks for the quick response & clear directions. That solved the problem.

Sergei Golubchik made changes - 2024-10-07 10:33

Link

This issue is duplicated by ~~MDEV-34851~~ [ ~~MDEV-34851~~ ]

People

Assignee:: Oleksandr Byelkin

Reporter:: Peter Schiffer

Votes:: 0 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2024-02-27 21:09

Updated:: 2024-10-07 10:33

Resolved:: 2024-03-04 15:34

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration