CODEWITHSUNDEEP
mysql
freento
freento

Reputation: 2949

SHOW GRANTS FOR CURRENT_USER works strange

I tried

SHOW GRANTS FOR CURRENT_USER

and it shows me:

GRANT USAGE ON *.* TO 'test_user'@'localhost' IDENTIFIED BY PASSWORD 'pass'

but this user has grants for select for test_script database.

Here is show grants by root:

SHOW GRANTS FOR test_user

GRANT USAGE ON *.* TO 'test_user'@'%'
GRANT SELECT ON `test_script`.* TO 'test_user'@'%'

Why SHOW GRANTS FOR CURRENT_USER doesn't show me grants for test_script table?

Upvotes: 3

Views: 1283

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270637

This looks to be because CURRENT_USER (the logged-in user) is 'test_user'@'localhost', who has different grants from 'test_user'@'%'.

SHOW GRANTS FOR 'test_user'@'%'
SHOW GRANTS FOR 'test_user'@'localhost'

If you login via TCP with mysql -h 127.0.0.1 -u test_user -p, you should now be using the 'test_user'@'%' grants.

When you execute a SHOW GRANTS statement for a username alone, without specifying the host, MySQL will attempt to list grants for the user @'%'. If that specification didn't exist at all, you would get 

ERROR 1141 (42000): There is no such grant defined for user 'test_user' on host '%'

In your case, that specification existed, but with different grants than the test_user@localhost.

Upvotes: 4

Related Questions