11

I successfully granted REQUIRE SSL to a single user by doing...

mysql -u"${targetMySqlUser}" -p"${targetMySqlPass}" -e "GRANT USAGE ON dbname.* TO 'dbusername'@'%' REQUIRE SSL;"

but im failing on REMOVING or REVOKING this flag from the user, using revoke. i guess im fighting with the syntax. Is there a proper way to remove it with the "revoke" command, without revoking the whole permission ?

MySQL 5.5 manual , this site and the interwebs didn't helped me yet finding a proper counter-way.

This SQL statement will work. 

UPDATE mysql.user SET ssl_type = '' WHERE ssl_type = 'any' ; FLUSH PRIVILEGES;

but I believe where is a GRANT REQUIRE SSL there must me a REVOKE REQUIRE SSL, isn't there ?

RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
Axel Werner
  • 255
  • 1
  • 3
  • 6

2 Answers2

11

What you are looking for does not exist in MySQL 5.5

Unfortunately, the ALTER USER command for MySQL 5.6 is limited. All you can do is

ALTER USER user@host PASSWORD EXPIRE;

In MySQL 5.7, you could run the ALTER USER command as follows

ALTER USER user@host REQUIRE NONE;

When it comes to MySQL 5.5, you did the most expedient way possible. Great !!!

A more politically correct way would have been to do the following:

DROP USER user@host;
CREATE USER user@host;
GRANT ... on ... TO user@host IDENTIFIED BY '...';

Otherwise, I commend you for doing what was needed.

RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
  • Good answer for various MySQL versions. For MySQL 5.6, [@Abey's solution](https://dba.stackexchange.com/a/182337/149697) does not require dropping and re-creating users. – mxxk Apr 22 '18 at 16:42
8

This was one of the google results when I wanted to remove REQUIRE SSL on a MySQL user that I enforced. What I did was do REQUIRE NONE on USAGE 

GRANT USAGE ON dbname.* TO 'dbusername'@'%' REQUIRE NONE;

Verify if the settings have been changed by running

SHOW GRANTS FOR 'dbusername'@'%' ;

Worked on MySQL 5.6

Abey
  • 181
  • 1
  • 2
  • Worked like a charm on MySQL 5.6. Elegant and simple :) – mxxk Apr 22 '18 at 16:35
  • What version of MySQL are you using? I'am using Mysql verison 5.7.22. SHOW GRANTs doesn't mention SSL - https://stackoverflow.com/questions/56203365/how-to-check-if-a-user-requires-ssl-in-mysql – committedandroider May 18 '19 at 22:39
  • `ALTER USER` is probably best for newer versions of MySQL/MariaDB but `GRANT ... REQUIRE NONE` is required on older versions. – Christopher Schultz Nov 12 '20 at 16:32