Running version 5.6.46 MYSQL_USER is not granted to create a new schema

[m-revetria]

After upgrading to version 5.6.46, the user MYSQL_USER is not allowed to create a new schema. This has been working since a long time for us, even with previous version 5.6.45.

The statement below throws the error Error Code: 1044. Access denied for user 'myUser'@'%' to database 'test' when running 5.6.46.

CREATE SCHEMA IF NOT EXISTS test;

MySQL container is spec in docker-file like this:

  mysql:
    image: mysql:5.6
    container_name: mysql.docker
    environment:
      MYSQL_ROOT_PASSWORD: "password"
      MYSQL_USER: "myUser"
      MYSQL_PASSWORD: "password"
      MYSQL_DATABASE: "sample_db"
    ports:
      - 3306:3306

The privileges for the user MYSQL_USER seems the same in both MySQL versions:

5.6.46:

show grants for 'myUser';

-- Grants for myUser@%
-- 'GRANT USAGE ON *.* TO \'myUser\'@\'%\' IDENTIFIED BY PASSWORD <secret>'
-- 'GRANT ALL PRIVILEGES ON `sample_db`.* TO \'myUser\'@\'%\''

SELECT * FROM information_schema.user_privileges;

-- GRANTEE, TABLE_CATALOG, PRIVILEGE_TYPE, IS_GRANTABLE
-- '\'myUser\'@\'%\'', 'def', 'USAGE', 'NO'

SELECT * FROM information_schema.schema_privileges;

-- # GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, PRIVILEGE_TYPE, IS_GRANTABLE
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'SELECT', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'INSERT', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'UPDATE', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'DELETE', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'CREATE', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'DROP', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'REFERENCES', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'INDEX', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'ALTER', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'CREATE TEMPORARY TABLES', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'LOCK TABLES', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'EXECUTE', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'CREATE VIEW', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'SHOW VIEW', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'CREATE ROUTINE', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'ALTER ROUTINE', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'EVENT', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'TRIGGER', 'NO'

5.6.45:

show grants for 'myUser';

-- # Grants for myUser@%
-- 'GRANT USAGE ON *.* TO \'myUser\'@\'%\' IDENTIFIED BY PASSWORD <secret>'
-- 'GRANT ALL PRIVILEGES ON `sample_db`.* TO \'myUser\'@\'%\''

SELECT * FROM information_schema.user_privileges;

-- # GRANTEE, TABLE_CATALOG, PRIVILEGE_TYPE, IS_GRANTABLE
-- '\'myUser\'@\'%\'', 'def', 'USAGE', 'NO'

SELECT * FROM information_schema.schema_privileges;

-- # GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, PRIVILEGE_TYPE, IS_GRANTABLE
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'SELECT', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'INSERT', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'UPDATE', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'DELETE', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'CREATE', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'DROP', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'REFERENCES', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'INDEX', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'ALTER', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'CREATE TEMPORARY TABLES', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'LOCK TABLES', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'EXECUTE', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'CREATE VIEW', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'SHOW VIEW', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'CREATE ROUTINE', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'ALTER ROUTINE', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'EVENT', 'NO'
-- '\'myUser\'@\'%\'', 'def', 'sample_db', 'TRIGGER', 'NO'

Is this change expected? If so, what should I do to grant MYSQL_USER privileges to create new schemas?

[wglambert]

That coincides with the incorporation of docker-library/official-images#6768 in 5.6.46

I'm not exactly sure what change would cause this given that permissions look to be the same

[wglambert]

Actually this is essentially working as intended, your specified user should only have full access to the specified database sample_db. However the user has permissions for creating the specific test database in 5.6.45, and then in 5.6.46 this was taken away

https://github.com/docker-library/docs/tree/master/mysql#mysql_user-mysql_password

This user will be granted superuser permissions (see above) for the database specified by the MYSQL_DATABASE variable.

$ docker run -d --rm --name mysql -e MYSQL_USER=myUser -e MYSQL_ROOT_PASSWORD=password -e MYSQL_PASSWORD=password -e MYSQL_DATABASE=sample_db mysql:5.6.45                                                                                                                                                                       
57a29ee173fa96b29e5be28f4ac626ac00c395dba453aa265cc02a0330618f59

$ grep connections <(docker logs -f mysql 2>&1)                                                                                                      
2019-10-21 19:38:20 86 [Note] mysqld: ready for connections.
2019-10-21 19:38:29 1 [Note] mysqld: ready for connections.
^C


$ docker exec -it mysql mysql -umyUser -ppassword                                                                                                    
Warning: Using a password on the command line interface can be insecure.                                                                                                    
Welcome to the MySQL monitor.  Commands end with ; or \g.                                                                                                                   
Your MySQL connection id is 1                                                                                                                                               
Server version: 5.6.45 MySQL Community Server (GPL)                                                                                                                         
                                                                                                                                                                            
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.                                                                                                
                                                                                                                                                                            
Oracle is a registered trademark of Oracle Corporation and/or its                                                                                                           
affiliates. Other names may be trademarks of their respective                                                                                                               
owners.                                                                                                                                                                     
                                                                                                                                                                            
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.                                                                                              
                                                                                                                                                                            
mysql> show databases;                                                                                                                                                      
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sample_db          |
+--------------------+
2 rows in set (0.00 sec)

mysql> create schema mydb;
ERROR 1044 (42000): Access denied for user 'myUser'@'%' to database 'mydb'                                                                                                  

mysql> mysql> create schema test1;
ERROR 1044 (42000): Access denied for user 'myUser'@'%' to database 'test1'       
                                                                                          
mysql> create schema test;
Query OK, 1 row affected (0.00 sec)                                                                                                                                         
                                                                                                                                                                            
mysql> show databases;                                                                                                                                                      
+--------------------+                                                                                                                                                      
| Database           |                                                                                                                                                      
+--------------------+                                                                                                                                                      
| information_schema |                                                                                                                                                      
| sample_db          |                                                                                                                                                      
| test               |                                                                                                                                                      
+--------------------+                                                                                                                                                      
3 rows in set (0.00 sec)

mysql> DELETE FROM mysql.db WHERE Db='test';
ERROR 1142 (42000): DELETE command denied to user 'myUser'@'localhost' for table 'db'

mysql> show grants for 'myUser';
+--------------------------------------------------------------------+
| Grants for myUser@%                                                |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'myUser'@'%' IDENTIFIED BY PASSWORD <secret> |
| GRANT ALL PRIVILEGES ON `sample_db`.* TO 'myUser'@'%'              |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

5.6.46:

$ docker run -d --rm --name mysql -e MYSQL_USER=myUser -e MYSQL_ROOT_PASSWORD=password -e MYSQL_PASSWORD=password -e MYSQL_DATABASE=sample_db mysql:5.6.46
d90f0d4112af483f3ba2adfcd77e91dc627c72a4b225b0ce095f8638502784c0

$ grep connections <(docker logs -f mysql 2>&1)                                                                                                     
2019-10-21 19:44:48 98 [Note] mysqld: ready for connections.
2019-10-21 19:44:55 1 [Note] mysqld: ready for connections.
^C

$ docker exec -it mysql mysql -umyUser -ppassword
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.46 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sample_db          |
+--------------------+
2 rows in set (0.00 sec)

mysql> create schema test1;
ERROR 1044 (42000): Access denied for user 'myUser'@'%' to database 'test1'

mysql> create schema test;
ERROR 1044 (42000): Access denied for user 'myUser'@'%' to database 'test'

mysql> show grants for 'myUser';
+--------------------------------------------------------------------+
| Grants for myUser@%                                                |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'myUser'@'%' IDENTIFIED BY PASSWORD <secret> |
| GRANT ALL PRIVILEGES ON `sample_db`.* TO 'myUser'@'%'              |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

[m-revetria]

I see, so this never should have worked, we were really lucky using that schema before 🤣 This was needed for us to run some tests against a real MySQL database, anyway, I was able to change the code a little bit to not need to create a new schema each time.

Thanks for clarifying what's going on here.

I'm closing the issue as it's working as designed

[yosifkit]

Just going to note that the cause would be #479.