Not the answer you need?
Register and ask your own question!

MySql root privileges issue

aspirationaspiration EntrantCurrent User Role Beginner
Some of my Percona servers shows restricted privileges to MySQL root user

mysql> SHOW GRANTS FOR 'root'@'localhost';
+
+
| Grants for [email protected] |
+
+
| 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

Comments

  • deadmanalivedeadmanalive Contributor Current User Role Beginner
    Hi aspiration,

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

    It is production or testing environment ?
  • aspirationaspiration Entrant Current User Role Beginner
    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: [email protected]
    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>
  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    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.
    http://stackoverflow.com/questions/8484722/access-denied-for-user-rootlocalhost-while-attempting-to-grant-privileges
  • aspirationaspiration Entrant Current User Role Beginner
    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
  • deadmanalivedeadmanalive Contributor Current User Role Beginner
    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.
    http://dev.mysql.com/doc/mysql-enterprise-backup/3.6/en/mysqlbackup.privileges.html
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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 [email protected] |
    +
    +
    | 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.
  • aspirationaspiration Entrant Current User Role Beginner
    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.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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.
  • aspirationaspiration Entrant Current User Role Beginner
    Hello Scott,

    Thank you for the suggestion. Below are the outputs

    Working server :
    mysql> select user(), current_user();
    +
    +
    +
    | user() | current_user() |
    +
    +
    +
    | [email protected] | [email protected] |
    +
    +
    +
    1 row in set (0.00 sec)

    mysql> show grants;
    +
    +
    | Grants for [email protected] |
    +
    +
    | 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() |
    +
    +
    +
    | [email protected] | [email protected] |
    +
    +
    +
    1 row in set (0.00 sec)

    mysql> show grants;
    +
    +
    | Grants for [email protected] |
    +
    +
    | 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.
  • aspirationaspiration Entrant Current User Role Beginner
    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
  • paul.namuagpaul.namuag Entrant Current User Role Beginner
    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.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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.
  • aspirationaspiration Entrant Current User Role Beginner
    Hello

    Thank you for the updtaes. Let me check it and will keep you posted.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.