1

I'm having trouble granting a user permission on AWS.

mysql> GRANT CREATE ON *.* TO 'hmrex_db'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR 'hmrex_db'@'%';
+------------------------------------------------------------------------------+
| Grants for hmrex_db@%                                                        |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hmrex_db'@'%' IDENTIFIED BY PASSWORD <secret>         |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `hmrex_db`.* TO 'hmrex_db'@'%' |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
  • The user I'm logged in as has GRANT ..., CREATE, ... ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD <secret> WITH GRANT OPTION
  • read_only is 0.

I can't work out why this isn't working.

I discovered I had made a typo and was setting the permission on a user that doesn't exist, however, this worked. When I fixed the typo, it still didn't work.

0

To user/privileges changes be reflected you need to run the following command:

FLUSH PRIVILEGES;

Then you can try SHOW GRANTS againg.

  • Unfortunately, this is incorrect. FLUSH PRIVILEGES; is only needed if you are manually UPDATEing the user permission table. When using GRANT ...; it's not needed. – CJ Dennis Sep 7 at 0:56
0

The answer is a combination of a typo, and misreading the output because of tiredness.

mysql> GRANT CREATE ON *.* TO 'hmrex_db'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR 'hmrex_db'@'%';
+------------------------------------------------------------------------------+
| Grants for hmrex_db@%                                                        |
+------------------------------------------------------------------------------+
| GRANT CREATE ON *.* TO 'hmrex_db'@'%' IDENTIFIED BY PASSWORD <secret>        |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `hmrex_db`.* TO 'hmrex_db'@'%' |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

You'll notice that the set of grants is still SELECT, INSERT, UPDATE, DELETE, DROP, the same as before. However, what you may not have noticed, and what I didn't notice, is that USAGE has changed to CREATE.

So, once the typo was fixed, the command was setting the permission correctly, just not where I was expecting to see it! There is a difference between

GRANT CREATE ON *.* TO 'hmrex_db'@'%';

and

GRANT CREATE ON `hmrex_db`.* TO 'hmrex_db'@'%';

which you can see in the correct output above in the first row, not in the second row where I was looking.

When I changed the command to GRANT CREATE ON `hmrex_db`.* TO 'hmrex_db'@'%';, I got the following output:

mysql> GRANT CREATE ON `hmrex_db`.* TO 'hmrex_db'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR 'hmrex_db'@'%';
+--------------------------------------------------------------------------------------+
| Grants for hmrex_db@%                                                                |
+--------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hmrex_db'@'%' IDENTIFIED BY PASSWORD <secret>                 |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `hmrex_db`.* TO 'hmrex_db'@'%' |
+--------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.