CODEWITHSUNDEEP
mysqlprivilegessql-grant
the_basterd
the_basterd

Reputation: 633

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

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.

Upvotes: 2

Views: 1019

Answers (2)

the_basterd
the_basterd

Reputation: 633

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.

Upvotes: 1

marklong
marklong

Reputation: 191

Try this,

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

FLUSH PRIVILEGES;

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

Upvotes: 0

Related Questions