MySQL - Can't grant all privileges to myself

A while back I either renamed/replaced the "root" MySQL user with my username, in the name of extra security. Now I can't grant myself access to anything. I just have one user which doesn't have permission to "grant" access to the "mysql" database. So how can I edit users if I can't get into the "mysql" database?

To make matters worse, the only way I can get into the database through localhost is with "/usr/bin/mysqld_safe –skip-grant-tables&" BUT the catch is with "--skip-grant-tables" I can't use the grant command.

Fortunately it's not a catastrophic problem because my application has all the permission it needs to run the website. But I need to get this fixed because sooner or later I'll need full access.

This doesn't work either:

mysql> create user root@localhost IDENTIFIED BY 'blah';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Any ideas?

3 Replies

I'm making some progress. This worked:

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='me';

Now I can access the "mysql" table and I created a "root" user but I'm not sure I have full access yet.

grant all on *.* to root@localhost with grant option;
Query OK, 0 rows affected (0.12 sec)

I keep ketting 0 rows affected. Is that because these users already have full privileges?

Looks like I'm good to go now.

http://stackoverflow.com/questions/1746 … 7#17465047">http://stackoverflow.com/questions/17464116/how-do-i-know-a-mysql-user-has-the-maximum-privileges-possible/17465047#17465047

Basically you can check with "show grants;" and "select * from user;" if you have full privileges.

root@localhost should already have full privileges on all tables, and should already have a password. You should need only grant privileges to non-root users, and set a password ("identified by" part) for users which don't already have a password.

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct