MySql root privileges issue

MySQL & MariaDB Other MySQL® Questions
1

Some of my Percona servers shows restricted privileges to MySQL root user

mysql> SHOW GRANTS FOR ‘root’@‘localhost’;
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON . TO ‘root’@‘localhost’ IDENTIFIED BY PASSWORD ‘*D89ED13ADCA88BCF1C454C277E08545F8923D20B’ WITH GRANT OPTION |
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Still the privilege table shows full access

mysql> select * from mysql.user where User=‘root’;
±--------------------------------±-----±------------------------------------------±------------±------------±------------±------------±------------±----------±------------±--------------±-------------±----------±-----------±----------------±-----------±-----------±-------------±-----------±----------------------±-----------------±-------------±----------------±-----------------±-----------------±---------------±--------------------±-------------------±-----------------±-----------±-------------±---------±-----------±------------±-------------±--------------±------------±----------------±---------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
±--------------------------------±-----±------------------------------------------±------------±------------±------------±------------±------------±----------±------------±--------------±-------------±----------±-----------±----------------±-----------±-----------±-------------±-----------±----------------------±-----------------±-------------±----------------±-----------------±-----------------±---------------±--------------------±-------------------±-----------------±-----------±-------------±---------±-----------±------------±-------------±--------------±------------±----------------±---------------------+
| localhost | root | *xxxxxxxxxxxxxxxxxxxx | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 |

The sudden reset in root privileges creates issues, especuially for db backups . We are unable to create a user with full permissions as well

mysql> GRANT ALL PRIVILEGES ON . TO ‘cdpdbkp’@‘localhost’ with grant option;
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)
mysql>

I am able to access the server as mysql root from local machine without any authentication issues, but fails to grant privileges. It happens for multiple servers with different versions of MySQL . The server uses CloudLinux, cageFS,cPanel

Has there any recent restrictions are imposed?

Kindly

2

Hi aspiration,

Please share the output or version of mysql.
mysql> status ;

It is production or testing environment ?

3

Hello

Thank you for the response. Its a live server

mysql> status ;

mysql Ver 14.14 Distrib 5.5.33, for Linux (x86_64) using readline 5.1

Connection id: 609387
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ‘’
Using delimiter: ;
Server version: 5.5.33-31.1 Percona Server (GPL), Release rel31.1, Revision 566
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 1 day 16 hours 1 min 1 sec

Threads: 6 Questions: 65811107 Slow queries: 141 Opens: 787378 Flush tables: 1 Open tables: 2048 Queries per second avg: 456.828

mysql>

4

Hi,

Have you recently upgrade MySQL 5.5? If yes, I would suggest you to run mysql_upgrade on MySQL server again and then try. Because there are many changes between MySQL 5.1 and MySQL 5.5 in term of user/privileges. Check this.
[URL]mysql - Access denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges? - Stack Overflow

5

No , we have’t performed any recent updates. Same version on other servers works fine. The user table has identical entries with other servers

mysql> SELECT * FROM mysql.user WHERE User=‘root’\G
*************************** 1. row ***************************
Host: localhost
User: root
Password: *D89ED13ADCA88BCF1C454C277E08545F8923D20B
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
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0

6

Hi,

The sudden reset in root privileges creates issues, especially for db backups . We are unable to create a user with full permissions as well

mysql> GRANT ALL PRIVILEGES ON . TO ‘cdpdbkp’@‘localhost’ with grant option;
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)
mysql>

Please explain what changes/resetting done with root user !

Updated:
You can use same permission as granted in root user instead of “ALL PRIVILEGES” or reset the root privileges.
Permission required for backup user as per mysql documentation.
[B][url]http://dev.mysql.com/doc/mysql-enterprise-backup/3.6/en/mysqlbackup.privileges.html[/url][/B]

7

Was this database upgraded in the past (not just recently)? I think niljoshi was on the right track.

Normally your “show grants” statement for a user with all privs should look like the below:

mysql> show grants;
±---------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
±---------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO ‘root’@‘localhost’ IDENTIFIED BY PASSWORD ‘*AAAAAAAAAAAAAAAAAAAAAAA’ WITH GRANT OPTION |
±---------------------------------------------------------------------------------------------------------------------------------------+
1 rows in set (0.00 sec)

The fact that yours shows all the privs individually generally means that the server was updated at some point and it is not fully happy with the grant table structure. Your servers could have been working totally fine this whole time until either a change was made or this edge case was found.

The only other possible cause I can think of is that you have multiple entries for ROOT and you are authenticating against one that does not have all of the privs. So I would check the table to see if there are multiple ROOT entries and see if any of them have more restrictive permissions.

8

Hello Scott,

As mentioned earlier servers with same version works fine. In either case, a privilege table corruption is quite dangerous. We have multiple root access with different hostnames and none works with full privileges. Strangely privilege table shows all entries identical to a working server.

9

Try connecting to both the server that works and the server that does not work, and run:

select user(), current_user();
show grants;

That will tell you what user/host you logged in as, what you were authenticated as, and the applicable grants. This will tell us if you are at least authenticating as the same exact user/host combo with the same permissions.

10

Hello Scott,

Thank you for the suggestion. Below are the outputs

Working server :

mysql> select user(), current_user();
±---------------±---------------+
| user() | current_user() |
±---------------±---------------+
| root@localhost | root@localhost |
±---------------±---------------+
1 row in set (0.00 sec)

mysql> show grants;
±---------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
±---------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO ‘root’@‘localhost’ IDENTIFIED BY PASSWORD ‘*38F4873E5762F9F3C4E608EDD9781D6F79152E9D’ WITH GRANT OPTION |
±---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Problematic Server:

mysql> select user(), current_user();
±---------------±---------------+
| user() | current_user() |
±---------------±---------------+
| root@localhost | root@localhost |
±---------------±---------------+
1 row in set (0.00 sec)

mysql> show grants;
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON . TO ‘root’@‘localhost’ IDENTIFIED BY PASSWORD ‘*D89ED13ADCA88BCF1C454C277E08545F8923D20B’ WITH GRANT OPTION |
±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Kindly check the results and let me know your thoughts.

11

The MySQL Versions with which I experience issues are

Server version: 5.5.33-31.1
Server version: 5.5.31-30.3
Server version: 5.5.32-31.0

12

Hi,

I presume you are coming from an earlier version then update your current version?

If so, how did you upgrade? I would presume you do something like this,

  • run scripts/mysql_install_db --user=root
  • bin/mysql_upgrade --user=root --verbose

where mysql_upgrade checks for any incompatibilities that your tables against the new version. This will also upgrade the system tables so that means new privileges will be updated as well. In that case, this means that mysql_upgrade must be run each time you upgrade your version.

If you’re feeling insecure, always assure you have backups. You can use Percona Xtrabackup for ease of using backups and even try to ran the backup as a test to a separate server and check it over there so you could feel comfortable playing around with your data. To check for any modifications that running those commands, you can then run,

find /var/lib/mysql/data -mmin -5

to check those files that are modified w/in 5 minutes. Just replace the 5 from the time that lapse you expect it was ran and finished.

13

Aspiration;

What you are seeing is about what I expected. You can see how the working server returns the short nice version for your show grants command, while the non-working server lists out all the privileges separately. That is indicative of a server that was upgraded and the grant table is no longer inline with the version, which can yield unpredictable results. The server could have been upgraded a long time ago, and it could work great 99% of the time, while still not being in a perfect state. Have you tried running mysql_upgrade on the non-working server yet to see if it can fix the tables for you? I would start there for sure.

14

Hello

Thank you for the updtaes. Let me check it and will keep you posted.