Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Using CentOS 6, logged in to MySQL as root, entering the command:

create user 'user123' identified by 'pass123';

works fine. But when I try and give that user super user privileges with:

grant all on *.* to 'user123' identified by 'pass123';

I get the error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Then

select * from mysql.user;

shows that root has Y in all columns, so should have all privileges.

I'd be very grateful if anyone could help me find why root is unable to grant privileges as I can't see why it wouldn't be working.

Thanks

share|improve this question
add comment

2 Answers 2

up vote 0 down vote accepted

Had to circumvent the grant tables in the end. See here:

http://stackoverflow.com/questions/10130296/mysql5-as-root-cant-create-database-or-do-anything-access-denied

on how to do that

share|improve this answer
add comment

Privileges in MySQL are tied to both a 'username' and a 'host'. The host can be a domain name, an IP address or 'localhost'. Any of these can include the wildcard %. If you connect from the wrong place, even with the right username and password, you will still get a permission denied error.

Each of these different users can have different access to different databases, tables, rows and commands.

It's also worth noting that '127.0.0.1' is not the same as 'localhost' as using the mysql client on the command line will use the socket rather than IP to connect. If you want to use IP to connect when using the mysql client on the MySQL server, you can specify -P <IP address>.

To see all of the different 'users':

SELECT user,host,password FROM mysql.user;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.