MySQL: ERROR 1227 (42000): Access denied - Cannot CREATE USER

Question

I'm using MySQL 5.5.16 noinstall Zip Archive on Win7.

After I started the server, the command show databases showed me a list of 2 databases: information_schema and test. The latter is empty.

Where is the table user?

I tried to create a new user through this command create user newUser; and got the following error message: ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

What should I do to create, databases, tables, and do all the operations I want to do? I don't know if the fact that I'm using MySQL 5.5.16 noinstall Zip Archive has something to do with the error message?

Answer 1

First thing to do is run this:

SHOW GRANTS; 

You will quickly see you were assigned the anonymous user to authenticate into mysql.

Instead of logging into mysql with

mysql 

login like this:

mysql -uroot 

By default, root@localhost has all rights and no password.

If you cannot login as root without a password, do the following:

Step 01) Add the two options in the mysqld section of my.ini:

[mysqld] skip-grant-tables skip-networking 

Step 02) Restart mysql

net stop mysql <wait 10 seconds> net start mysql 

Step 03) Connect to mysql

mysql 

Step 04) Create a password from root@localhost

UPDATE mysql.user SET password=password('whateverpasswordyoulike') WHERE user='root' AND host='localhost'; exit 

Step 05) Restart mysql

net stop mysql <wait 10 seconds> net start mysql 

Step 06) Login as root with password

mysql -u root -p 

You should be good from there.

CAVEAT: Please remove anonymous users !!!