MySQL granting privileges for a new user removes ability to log in

2
votes

The issue occurring is MySQL is not letting me create a user then grant its privileges. Following the directions from the site:

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'

->     WITH GRANT OPTION;

The result when looking up its grant is this:

mysql> 
SHOW GRANTS FOR 'monty'@'localhost';

+-------------------------------------------+
| Grants for monty@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO 'monty'@'localhost' |
+-------------------------------------------+

I am able to log in to monty, but without any privileges, no CREATE, SELECT, etc.

(Back to root user) I try 'FLUSH PRIVILEGES' thinking it would 'flush' the grant tables, but nothing occurs nor is the grant table updated. Now when I try logging into monty again I get this error:

ERROR 1524 (HY000): Plugin '*BF06A06D69EC935E85659FCDED1F6A80426ABD3B' is not loaded

(caused by the FLUSH PRIVILEGES command)

Any suggestions to why this is happening? I'm thinking it's something wrong with my setup but I am not sure.

Extra bits of information:

When using USER() or CURRENT_USER(), it shows root@localhost. SHOW GRANTS displays "GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' WITH GRANT OPTION" and "GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION", as expected. So all privilege granting is there.

When I try "SELECT user, password FROM mysql.user;", nothing shows up for 'monty' under the password column.

EDIT: SOLVED check my solution down below.

2
mysqlprivilegesgrant

2 Answers

1
votes

The issue was although I installed the newest version of MySQL, it was for some reason still using the old MySQL table for mysql.user. By running mysql.upgrade, it updated the mysql.user tables which can be seen by the removal of the 'password' column.

0
votes

Try this,

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' WITH GRANT OPTION;