MySQL Bugs: #86546: Assigning a user as a role will change the user reach

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

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)

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

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>

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.

Thank you for the details, let me check with Docker procedure and get back to you.

regards,
Umesh

Thank you Giuseppe, verified as described in the docker environment(not sure why I couldn't see this with physical boxes though).

regards,
umesh

test results

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

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.

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.