Mysql: MySQL user has no permissions, and "GRANT" statement is not permitted

[kjaltana]

When creating a MySQL 5.7 container, the user is created but does not have any permissions. If you try to GRANT permissions, you receive the following errors:

0 row(s) affected, 2 warning(s): 1285 MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work 1287 Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation.

ALTER USER allows you to change everything except the user’s GRANTpermissions.

Test Docker Command:

docker run --name mysql-test -e MYSQL_ROOT_PASSWORD=my-secret-pw \
     -e MYSQL_DATABASE=mytestdb -e MYSQL_USER=testuser \
     -e MYSQL_PASSWORD=testpassword --publish 3307:3306 -d mysql:5.7

Attach to the container, login as root to mysql with credentials , switch to the “mysql” database and run:

select * from user where User = 'testuser'\G

The query returns:

*************************** 1. row ***************************
                  Host: %
                  User: testuser
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *9F69E47E519D9CA02116BF5796684F7D0D45F8FA
      password_expired: N
 password_last_changed: 2019-07-08 01:48:12
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

The same thing happens with this "docker-compose.yml" file:

version: "3.7"
services:
    mysql:
      image: mysql:5.7
      working_dir: /application
      volumes:
        - dbdata:/var/lib/mysql
      environment:
        - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
        - MYSQL_DATABASE=${MYSQL_DATABASE}
        - MYSQL_USER=${MYSQL_USER}
        - MYSQL_PASSWORD=${MYSQL_PASSWORD}
      networks:
        - web
      ports:
        - "${DB_PUBLIC_PORT}:3306"

    nginx:
      image: nginx:alpine
      restart: unless-stopped
      working_dir: /var/www
      tty: true
      volumes:
          - ./api:/var/www
          - ./phpdocker/nginx/nginx.conf:/etc/nginx/conf.d/default.conf
      networks:
        - web
      ports:
       - "${FRONT_END_PORT}:80"

    php-fpm:
      build: ./phpdocker/php-fpm
      working_dir: /var/www
      depends_on:
        - mysql
      volumes:
        - ./api:/var/www
        - ./phpdocker/php-fpm/php-ini-overrides.ini:/etc/php/7.2/fpm/conf.d/99-overrides.ini
      networks:
        - web

# Docker Networks
networks:
  web:
    driver: bridge

# Docker Volumes
volumes:
  dbdata: {}

[wglambert]

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 --name mysql-test -e MYSQL_ROOT_PASSWORD=my-secret-pw \
>      -e MYSQL_DATABASE=mytestdb -e MYSQL_USER=testuser \
>      -e MYSQL_PASSWORD=testpassword --publish 3307:3306 -d mysql:5.7
5780362b9403365928f3fa822ef527a417c270bec06d169a0702c948f4f35337

$ docker exec -it mysql-test mysql -u root -pmy-secret-pw
mysql: [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 2
Server version: 5.7.26 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 grants for 'testuser';
+--------------------------------------------------------+
| Grants for testuser@%                                  |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%'                   |
| GRANT ALL PRIVILEGES ON `mytestdb`.* TO 'testuser'@'%' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> grant ALL PRIVILEGES ON *.* TO 'testuser';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'testuser';
+--------------------------------------------------------+
| Grants for testuser@%                                  |
+--------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'%'          |
| GRANT ALL PRIVILEGES ON `mytestdb`.* TO 'testuser'@'%' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

[kjaltana]

This newbie wishes to ask the forgiveness of the maintainers and the community. The Lumen “.env” file I was using had the wrong host name (needed to be the name of the mysql container) and the wrong port number (needs to be the internal port, 3306).

Please mark this issue “Solved”.

[juniormayhe]

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 --name mysql-test -e MYSQL_ROOT_PASSWORD=my-secret-pw \
>      -e MYSQL_DATABASE=mytestdb -e MYSQL_USER=testuser \
>      -e MYSQL_PASSWORD=testpassword --publish 3307:3306 -d mysql:5.7
5780362b9403365928f3fa822ef527a417c270bec06d169a0702c948f4f35337

$ docker exec -it mysql-test mysql -u root -pmy-secret-pw
mysql: [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 2
Server version: 5.7.26 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 grants for 'testuser';
+--------------------------------------------------------+
| Grants for testuser@%                                  |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%'                   |
| GRANT ALL PRIVILEGES ON `mytestdb`.* TO 'testuser'@'%' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> grant ALL PRIVILEGES ON *.* TO 'testuser';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'testuser';
+--------------------------------------------------------+
| Grants for testuser@%                                  |
+--------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'%'          |
| GRANT ALL PRIVILEGES ON `mytestdb`.* TO 'testuser'@'%' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

These environment variables are interesting. The only thing missing was only a configuration in Windows Firewall to allow inbound connections for port 3306. Now I can connect from my host machine to a mysql running within a docker.

C:\WINDOWS\system32>docker run --name mymariadb -e MYSQL_DATABASE=finfollow -e MYSQL_USER=testuser -e MYSQL_PASSWORD=mypass -e MYSQL_ROOT_PASSWORD=mypass --publish 3306:3306 -d mariadb/server