Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3, 10.4, 10.5
-
None
Description
If a user with restricted privileges but including GRANT OPTION tries to revoke privileges from the user, which he has just created, the REVOKE ALL query fails with ERROR 1044 (42000) at line 1: Access denied for user 'admin'@'%' to database 'db'.
In my case Amazon RDS is used, where the master user has restricted access.
I created the script for reproducing the issue with docker:
#!/bin/bash -e
|
|
|
VERSION=${1:-"10.5.8"}
|
docker run -d --name mariadb -e MYSQL_ROOT_PASSWORD=secret -it mariadb:$VERSION
|
trap "docker rm -f mariadb" EXIT
|
|
|
while ! (
|
docker exec mariadb mysqladmin ping --silent && \
|
docker exec mariadb mysqladmin -uroot -psecret status --silent
|
); do
|
sleep 1
|
done
|
|
|
echo "Create 'admin' user"
|
docker exec -i mariadb mysql -uroot -psecret <<SQL
|
CREATE USER 'admin'@'%' IDENTIFIED BY 'secret';
|
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'admin'@'%' WITH GRANT OPTION;
|
SQL
|
|
|
echo "Check 'admin' can create 'user' and 'db'"
|
docker exec -i mariadb mysql -uadmin -psecret <<SQL
|
CREATE USER 'user'@'%';
|
CREATE DATABASE db;
|
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, ALTER, INDEX, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, EVENT, TRIGGER ON db.* TO 'user'@'%';
|
SQL
|
|
|
echo "Check 'admin' can detach 'user' from 'db'"
|
docker exec -i mariadb mysql -uadmin -psecret <<SQL
|
REVOKE ALL ON db.* FROM 'user'@'%';
|
SQL
|
If I change to "GRANT ALL ON . TO 'admin'@'%' WITH GRANT OPTION;", it works, but it is impossible for Amazon RDS.
If I change to "REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, ALTER, INDEX, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, EVENT, TRIGGER ON db.* FROM 'user'@'%';" it works, but it is inconvenient for my production code because I expect to revoke all previously granted privileges.
I checked the script with different versions and the issue reproduces starting from 10.3.4
The issue does not reproduce with MySQL image.