MySQL Bugs: #74685: Scope for SSL options in GRANT statement is misleading and not proper documented

Bug #74685	Scope for SSL options in GRANT statement is misleading and not proper documented
Submitted:	4 Nov 2014 15:10	Modified:	8 May 2018 11:18
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S4 (Feature request)
Version:	5.6.21 -	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I think the headline bove and *how to* below will explain.

How to repeat:
SET sql_mode = ''; -- in order to ensure that NO_AUTO_CREATE_USER is not set.

GRANT SELECT ON sakila.* TO someone@localhost REQUIRE SSL;
GRANT SELECT ON world.* TO someone@localhost;

SHOW GRANTS FOR someone@localhost;
/* returns 

Grants for someone@localhost                             
---------------------------------------------------------
GRANT USAGE ON *.* TO 'someone'@'localhost' REQUIRE SSL  
GRANT SELECT ON `sakila`.* TO 'someone'@'localhost'      
GRANT SELECT ON `world`.* TO 'someone'@'localhost'
*/

DROP USER someone@localhost;
CREATE USER someone@localhost REQUIRE SSL; -- syntax error

Suggested fix:
Allow only "REQUIRE SSL" (etc.) in GRANT USAGE and CREATE USER.

The current syntax confuses some people thinking that  "REQUIRE SSL" only has effect when reading the sakila table. 

I also think it is a relic from old days before CREATE USER was introduced in MySQL 5.0x.¨SSL options should have been added thwre, IMO.

This is not a 'strict bug' I realize, but I have noticed some cleanup efforts affecting sql_modes,variables and options in recent releases (appreciated BTW!) and this could be considered.

Besides I don't find the current behaviour clearly documented. 3 things could happen actually:
1) (what happens now) if only "REQUIRE SSL" was specified with one GRANT it applies to user globally
2) "REQUIRE SSL" could be specific for databases and tables where it was specified. This is actually what the GRANT syntax implies semantically (to me, at least) and some people get it wrong for same reason (I know personally that this is not possible, but that does not matter). Executing "REVOKE SELECT ON sakila.* FROM someone@localhost;" also does not remove "REQUIRE SSL" even though it was specified in that particlar GRANT-context.
3) "REQUIRE SSL" could be revoked from user globally with the second statement npt specifying "REQUIRE SSL".

(we had a few customer reports from users expecting both 2 and 3 to happen, actually)

Sorry for to be late.

Your MySQL connection id is 2
Server version: 5.7.23 Source distribution 2018-APR-29

Copyright (c) 2000, 2018, 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 5.7 > create database sakila;
Query OK, 1 row affected (0,00 sec)

mysql 5.7 > SET sql_mode = ''; -- in order to ensure that NO_AUTO_CREATE_USER is not set.
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql 5.7 >
mysql 5.7 > GRANT SELECT ON sakila.* TO someone@localhost REQUIRE SSL;
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql 5.7 > GRANT SELECT ON world.* TO someone@localhost;
Query OK, 0 rows affected (0,00 sec)

mysql 5.7 >
mysql 5.7 > SHOW GRANTS FOR someone@localhost;
+-----------------------------------------------------+
| Grants for someone@localhost                        |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'someone'@'localhost'         |
| GRANT SELECT ON `sakila`.* TO 'someone'@'localhost' |
| GRANT SELECT ON `world`.* TO 'someone'@'localhost'  |
+-----------------------------------------------------+
3 rows in set (0,01 sec)

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.41 Source distribution 2018-APR-29

Copyright (c) 2000, 2018, 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 5.6 > create database sakila;
Query OK, 1 row affected (0,00 sec)

mysql 5.6 > SET sql_mode = ''; -- in order to ensure that NO_AUTO_CREATE_USER is not set.
Query OK, 0 rows affected (0,00 sec)

mysql 5.6 > GRANT SELECT ON sakila.* TO someone@localhost REQUIRE SSL;
Query OK, 0 rows affected (0,00 sec)

mysql 5.6 > GRANT SELECT ON world.* TO someone@localhost;
Query OK, 0 rows affected (0,00 sec)

mysql 5.6 > SHOW GRANTS FOR someone@localhost;
+---------------------------------------------------------+
| Grants for someone@localhost                            |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'someone'@'localhost' REQUIRE SSL |
| GRANT SELECT ON `sakila`.* TO 'someone'@'localhost'     |
| GRANT SELECT ON `world`.* TO 'someone'@'localhost'      |
+---------------------------------------------------------+
3 rows in set (0,00 sec)