Root access to database denied

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Root access to database denied

Post by pickle »

Hi everyone,

I can login to my MySQL server just fine as root. I can create a database just fine. But when I try to grant privileges to said database, I get a 1044 error "Access denied for user 'root'@'localhost' to database 'session2'.

Checking the `mysql` database, the root user has the grant privilege both in the `user` and `db` tables.

The query I'm trying to run is:

Code: Select all

GRANT ALL ON `myDB`.* TO 'myUser'@'localhost' IDENTIFIED BY PASSWORD `myPass'
Any ideas? I'm suspecting a brain fart.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Root access to database denied

Post by Christopher »

Have you done a "FLUSH PRIVILEGES" to make the changes live?
(#10850)
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Root access to database denied

Post by pickle »

I have, but I don't think it applies.

Permissions haven't changed, as the root user has rights to '%' databases, so when I make a new database, the privileges automatically apply.

Regardless, I have done that & there was no change.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Root access to database denied

Post by Christopher »

That does sound strange. Are there any entries in the mysql host table
(#10850)
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Root access to database denied

Post by pickle »

Nope.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Root access to database denied

Post by VladSun »

What about
[sql]GRANT ALL ON `myDB`.* TO myUser@localhost IDENTIFIED BY 'myPass'[/sql]

I.e.
- remove the quotes from user/host
- use text password instead of hash
- ` fixed to ' in `mypass'
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Root access to database denied

Post by pickle »

Same deal.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Root access to database denied

Post by VladSun »

Try to run mysql_fix_privilege_tables
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Root access to database denied

Post by pickle »

Ran that, took a few seconds, and it didn't fix anything.

One thing that strikes me as odd is it's saying I'm denied access to the database 'session'. I thought the GRANT query just affected mysql.user and mysql.db? Those are the two tables I change when I manually create permission.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Root access to database denied

Post by VladSun »

Yes, it's odd.
Do you have a 'session2' database indeed?

Try to restart MySQL.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Root access to database denied

Post by pickle »

Yes, the database exists. I create it immediately before trying to grant privileges.

I can't restart MySQL now - we've got lots of apps running on it. I'll try this evening & see what happens.

Thanks.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply