MySQL: ERROR 1142 (42000) and ERROR 1064 (42000)

Question

I'm using windows 7. I've downloaded mysql-5.5.16-win32.zip and installed it. I started MySQL server successfully, but I get this error:

C:\Program Files\Mysql\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.16 MySQL Community Server (GPL)

mysql> select user, host, password from mysql.user;
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user
'
mysql> mysql -u root -p
    -> select user, host, password from mysql.user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'mysql
 -u root -p
select user, host, password from mysql.user' at line 1
mysql> mysql -u root -p root
    -> select user, host, password from mysql.user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'mysql
 -u root -p root
select user, host, password from mysql.user' at line 1
mysql>

How can set the required privileges, from Windows, to the mysql table users?

Answer 1

The mysql commandline error message:

ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'

Means you are logged into mysql with a default null user with just about zero privileges.

1. From the command line, run mysql as you did before:

   C:\Users\Charity>mysql
   

2. Which brings you to a mysql prompt, run the command select user();

   mysql> select user();
   +----------------+
   | user()         |
   +----------------+
   | ODBC@localhost |
   +----------------+
   1 row in set (0.00 sec)
   

   That output means you are connecting not as a user, but as some kind of non-user API connector. ODBC is not in the mysql.users table. It is the user that connects to MySQL when you don't specify a user.

3. Run another command:

   mysql> select user from mysql.user;
   ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for 
   table 'user'
   mysql>
   

   We didn't connect as any user so it's telling us access denied.

4. Run these commands:

   C:\Users\Charity>mysql -u this_is_not_a_user
   mysql> select user();
   +------------------------------+
   | user()                       |
   +------------------------------+
   | this_is_not_a_user@localhost |
   +------------------------------+
   1 row in set (0.00 sec)
   
   mysql>
   

   So we've logged in as another non-user of our definition. Would you expect this user we pulled out of a hat to have any privileges at all? No.

5. Stop fooling around and login as root:

   C:\Users\Charity>mysql -u root -p
   Enter password: **********
   mysql> select user();
   +----------------+
   | user()         |
   +----------------+
   | root@localhost |
   +----------------+
   1 row in set (0.00 sec)
   mysql>
   

   Now you are root, you can see everything.

6. Look at the mysql.user table:

   mysql> select user, host from mysql.user;
   +------+-----------+
   | user | host      |
   +------+-----------+
   |      | linux     |
   | root | linux     |
   |      | localhost |
   | pma  | localhost |
   | root | localhost |
   +------+-----------+
   5 rows in set (0.00 sec)
   

   You might want to create more users so you can let people use the database without giving them the rights to do anything to your database. Keep the barbarians out at the moat.

7. You don't want to be logging in as root all the time. So create a new user. Go to phpmyadmin, login to phpadmin as root. Click the "users" tab and create a new user. A new dialog shows, fill in the username, password and permissions. Then you can login as that user:

   C:\Users\Charity>mysql -u el -p
   Enter password: **********
   mysql> select user();
   +----------------+
   | user()         |
   +----------------+
   | el@localhost   |
   +----------------+
   1 row in set (0.00 sec)
   mysql>
   

   This user can do everything that you granted in the permissions defined in step 7.