Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
11.3.2
-
None
-
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
-
-
- Closed
-
Activity
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.
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?
yes, correct, I'm fixing it now. that's why I took it back from In-Review.
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.
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.
Hi @danblack,
Thanks for the quick response & clear directions. That solved the problem.
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.