3

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 *.* TO 'test'@'%' WITH GRANT OPTION |
| 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'
6
  • Are you sure the database exists? Check it out with root first. Jul 4, 2012 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, 2012 at 6:55
  • The username in the grants and the username in the error don't match... Jul 4, 2012 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, 2012 at 7:21
  • 1
    How did you create the user?
    – faker
    Jul 4, 2012 at 8:41

2 Answers 2

0

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.

2

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .