Bug #86546 Assigning a user as a role will change the user reach
Submitted: 1 Jun 2017 21:01 Modified: 13 Dec 2017 2:10
Reporter: Giuseppe Maxia (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Roles Severity:S1 (Critical)
Version:8.0.1 OS:Any
Assigned to: CPU Architecture:Any

[1 Jun 2017 21:01] Giuseppe Maxia
Description:
Let's start with the normal behavior. We have two hosts: mybox1 and mybox2.
From mybox2, we try to connect to root at mybox1:

# mysql -psecret -u root -h mybox1
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on 'mybox1' (111)

This is what we expect: since root is defined as "root@localhost", it can't be reached from other hosts.

Now, in mybox1, we create a new user, and assign root as if it were a role.

mysql> create user u1;
Query OK, 0 rows affected (0.01 sec)

mysql> grant root@'localhost' to u1;
Query OK, 0 rows affected (0.01 sec)

Back to mybox2:

# mysql -psecret -u root -h mybox1
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 9
Server version: 8.0.1-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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>

Now root@localhost, which was not changed, is also reachable from a remote server.

How to repeat:
#server2:
$ mysql -h server1 -u root -psecret 
(will fail)

# server1
create user u1;
grant root@'localhost' to u1;

#server2:
$ mysql -h server1 -u root -psecret 
(will succeed)
[2 Jun 2017 12:55] MySQL Verification Team
Hello Giuseppe,

Thank you for the report and feedback.
I just followed your steps as described but seeing the expected behavior at my end.

######
-- host1

bin/mysql -uroot -psecret
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 5
Server version: 8.0.1-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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.

root@localhost [(none)]>

-- host2

bin/mysql -psecret -h host1 -uroot
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host 'host2' is not allowed to connect to this MySQL server

-- host1

bin/mysql -uroot -psecret
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 5
Server version: 8.0.1-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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.

root@localhost [(none)]> create user u1;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> grant root@'localhost' to u1;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]>

-- host2

bin/mysql -psecret -h host1 -uroot
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'host2' (using password: YES)

#############

Anything else I'm missing? Please let us know.

regards,
umesh
[2 Jun 2017 13:57] Giuseppe Maxia
here is a script that reproduces the problem:

# --- CUT ---
docker run --name mybox1 --hostname mybox1 -e MYSQL_ROOT_PASSWORD=secret -d  --net my_net mysql/mysql-server:8.0

echo "Waiting 60 seconds for the server to be up"
for X in $(seq 1 6) ; do for N in $(seq 1 10) ; do sleep 1 ; echo -n '.' ; done ; echo -n ${X}0 ; done
echo ''
echo ''

echo "#Making sure the server responds"
docker exec -ti mybox1 mysql -u root -psecret -e 'select now(), version()'
echo ''

echo "#this should fail"
docker exec -ti mybox1 mysql -u root -psecret -h mybox1
echo ''

echo "#Setting the role"
docker exec -ti mybox1 mysql -u root -psecret -e 'create user u1; grant root@"localhost" to u1'
echo ''

echo "#this should succeed"
docker exec -ti mybox1 mysql -u root -psecret -h mybox1
# --- CUT ---

### SAMPLE run:

$ ./roles-docker.sh
613338c25869c05b8238e26a750bcb7437d14008eac85f2ba75dcff55cc14440
Waiting 60 seconds for the server to be up
..........10..........20..........30..........40..........50..........60

#Making sure the server responds
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------+-----------+
| now()               | version() |
+---------------------+-----------+
| 2017-06-02 13:38:04 | 8.0.1-dmr |
+---------------------+-----------+

#this should fail
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host '172.19.0.2' is not allowed to connect to this MySQL server

#Setting the role
mysql: [Warning] Using a password on the command line interface can be insecure.

#this should succeed
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 7
Server version: 8.0.1-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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>
[2 Jun 2017 13:59] Giuseppe Maxia
I forgot to put in the script a command to create the network.

docker network create my_net

You should execute this before running the script.
[5 Jun 2017 10:11] MySQL Verification Team
Thank you for the details, let me check with Docker procedure and get back to you.

regards,
Umesh
[6 Jun 2017 8:49] MySQL Verification Team
Thank you Giuseppe, verified as described in the docker environment(not sure why I couldn't see this with physical boxes though).

regards,
umesh
[6 Jun 2017 8:52] MySQL Verification Team
test results

Attachment: 86546.results (application/octet-stream, text), 12.55 KiB.

[17 Oct 2017 7:29] Terje Røsten
Posted by developer:
 
Docker image adds --skip-name-resolve and --skip-host-cache, please verify you add these options
to server when testing to reproduce.
[13 Dec 2017 2:10] Paul DuBois
Posted by developer:
 
Fixed in 8.0.4, 9.0.0.

Starting the server with the --skip-name-resolve option could cause
localhost in account entries to match non-localhost hosts.