I have created user in mysql 5.1 and given the ALL privilege, details given below,

mysql> show GRANTS FOR test;
| Grants for test@%                                           |
| GRANT ALL PRIVILEGES ON `tt`.* TO 'test'@'%'                |
2 rows in set (0.00 sec)

But the show databases is not showing the databases on the mysql. It only shows as given below. How can give privilege for other DB s tables as well for the user 'test'?

mysql> show databases;
| Database           |
| information_schema |
| test               |

Error while I tried to use the mysql DB as user test:

mysql> use mysql;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
  • Are you sure the database exists? Check it out with root first. Jul 4 '12 at 6:51
  • Yes it has the additional tables for root user, not with the test user. mysql -utest -p -S /var/lib/mysql/mysql.sock and mysql -uroot -p -S /var/lib/mysql/mysql.sock
    – Ahn
    Jul 4 '12 at 6:55
  • The username in the grants and the username in the error don't match... Jul 4 '12 at 7:13
  • That is what I too seeing .... how it came, As I am logging to mysql with user 'test' and issuing the mysql> use mysql; and it gives the access denied error for ''@'localhost'.
    – Ahn
    Jul 4 '12 at 7:21
  • 1
    How did you create the user?
    – faker
    Jul 4 '12 at 8:41

in mysql.user table you can specify the different privileges to different users.If you are using query browser you can tools->mysql administrator then go to user and create new user with privileges or change its privileges.

After you change privileges don't forget to run "FLUSH PRIVILEGES" otherwise you changes wont affect.


You're not logged in as the user test@%, because you have the anonymous user account ''@'localhost' which is matching first. Either drop ''@'localhost' or create 'test'@'localhost'

You can also run mysql_secure_installation which will take care of a lot of this for you.

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.