New to databases and accidentally removed privileges from root user; only privileges I currently have are USAGE and PROXY. This is a MySQL database that I access through HeidiSQL. Currently cannot access my data tables; is there a way to get these privileges restored? Did not create any other user accounts.
Mysql – Removed privileges from root user and can’t access database
MySQLpermissions
Related Solutions
I have dealt with this issue before.
When you ran
select count(1) UserTableColumnCount from information_schema.columns
where table_schema='mysql' and table_name='user';
you should have gotten 42. That's how many columns MySQL 5.5 has for mysql.user
. Since you got 39, that means you must have upgraded from MySQL 5.1. That has 39 columns.
I wrote an earlier post about the number of columns in mysql.user
in different versions : MySQL service stops after trying to grant privileges to a user
Here is the post where I dealt with this : mysql: Restore All privileges to admin user
Hopefully you could run
# mysql_upgrade --upgrade-system-tables
to realign mysql.user
and have it autofill missing permissions with Y
.
Give it a Try !!!
If you want to try to fix the mysql.user manually, here are the steps:
#
# Backup the mysql.user table
#
CREATE TABLE mysql.user_backup LIKE mysql.user;
INSERT INTO mysql.user_backup SELECT * FROM mysql.user;
#
# Add Missing Columns
#
ALTER TABLE mysql.user
ADD COLUMN Create_tablespace_priv enum('N','Y') NOT NULL DEFAULT 'N'
AFTER Trigger_Priv
;
ALTER TABLE mysql.user ADD COLUMN plugin char(64);
ALTER TABLE mysql.user ADD COLUMN authentication_string text DEFAULT NULL;
#
# Give root user all privileges
#
UPDATE mysql.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',Event_priv='Y',Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user='root';
FLUSH PRIVILEGES;
That's it !!!
This works:
As root:
mysql> CREATE USER 'myuser'@'%' IDENTIFIED BY 'billy';
Query OK, 0 rows affected (0.04 sec)
Then, on the sport
schema, grant ALL
to myuser
mysql> GRANT ALL ON sport.* TO 'myuser'@'%';
Query OK, 0 rows affected (0.02 sec)
Exit and then log on as the new user myuser
mysql> exit;
Bye
[pol@localhost inst]$ ./bin/mysql -S ./mysql.sock -u myuser -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.22-log Source distribution
Show all the schemas myuser can see
mysql> show schemas;
+--------------------+
| Database |
+--------------------+
| information_schema |
| sport |
| test |
+--------------------+
3 rows in set (0.00 sec)
There are many more schemas on this server - it's just that myuser can't see them.
mysql> use sport;
Database changed
Then, try to create a user as myuser.
mysql> create user 'fred'@'%' IDENTIFIED BY 'mary';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
Fails.
But, I can do this.
mysql> create table test (a int, b varchar(10));
Query OK, 0 rows affected (0.53 sec)
mysql> insert into test values(3, 'testvalue');
Query OK, 1 row affected (0.04 sec)
Take a look at the three answers here, here and here for a better idea of what's going on. Always test your users after creation - it's very easy to give users too much power.
You can also do it this way (a more elegant solution perhaps)
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
INDEX, ALTER, CREATE TEMPORARY TABLES
ON sport.* TO 'username'@'localhost' IDENTIFIED BY 'password';
Finally, you could just remove the CREATE USER
privilege (and, of course, the WITH GRANT OPTION
) from this answer to have a "super user" who can do everything except create new users.
I think @sqlbot may have figured out the root of your problem.
Best Answer
You can use following steps to regrant root priviledge.
mysqld
.mysqld
with--skip-grant-tables
.mysql
to connectmysqld
. (Typemysql
only, No need to input password-p
, maybe No need to input usernameroot
, either)UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;
mysqld
normally.