Announcement

Announcement Module
Collapse
No announcement yet.

MySql root privileges issue

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySql root privileges issue

    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 ?

    Comment


    • #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>

      Comment


      • #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.
        http://stackoverflow.com/questions/8...ant-privileges

        Comment


        • #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

          Comment


          • #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.
            http://dev.mysql.com/doc/mysql-enter...rivileges.html


            Last edited by deadmanalive; 09-17-2013, 12:20 PM.

            Comment


            • #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.

              Comment


              • #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.

                Comment


                • #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.

                  Comment


                  • #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.

                    Comment


                    • #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

                      Comment


                      • #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.

                        Comment


                        • #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.

                          Comment


                          • #14
                            Hello

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

                            Comment

                            Working...
                            X