Missing Permissions in MySQL

I’m not a big fan of SQL but with several things you DO need it. Make sure to meet the minimum requirements when using it, for example: change root’s password, remove anonymous accounts, drop root user and create a new database/user for wordpress (with all permissions).

Sometimes, when you drop the root user, you don’t have enough permissions to create users, create DB’s, drop tables and so on. I struggled a bit with this but there’s a good workaround to recover/change the permissions OR recreate the root account.

Stop the SQL server:

1
# /usr/local/etc/rc.d/mysql-server stop

Start SQL:

1
# mysqld_safe --skip-grant-tables &

Login as root and open the system DB:

1
2
# mysql -u root
mysql> use mysql;

To check if useraccount exists (you’ve created):

1
mysql> select * from user where User='USERACCOUNT';

Now let’s give back the correct permissions:

1
mysql> insert into user (Host, User, Password) values ('localhost','USERACCOUNT','');
1
mysql> update user set Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',Create_user_priv='Y' where user='USERACCOUNT';

If you just want to create (again) the root account because you accidently deleted it:

1
mysql> insert into user (Host, User, Password) values ('localhost','root','');
1
mysql> update user set Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',Create_user_priv='Y' where user='root';

Ready:

1
mysql> quit

Stop MySQL (you´ll see it being stopped with PID and skip-grant-tables):

1
# /usr/local/etc/rc.d/mysql-server stop

Start MySQL:

1
# /usr/local/etc/rc.d/mysql-server start

Check if it works with useraccount (or root):

1
# mysql -u root
1
mysql> grant all privileges on *.* to 'USERACCOUNT'@'localhost' with grant option;

Comments