How to forcibly create/modify MySQL user grants, without restarting the database service

Question

I am familiar with how to start MySQL server with the "--init-file" option, or the "--skip-grant-tables" options.

However I have a nagios based mysql monitor, which needs to query a table, and therefore I need to configure a user (GRANT SELECT ON heartbeart to 'nagios-monitor'@'123.123.123.123') for that operation with appropriate permissions granted to the nagios server host, or nrpe script host.

I'd like to have an automated installer script, to configure the table. Hence I would like to be able to carry out these operations automated, with little risk to the underlying application uptime.

1. It is not always clear what the mysql root user password is. (targets are arbitrarily used developer and test boxes, and they change the passwords)
2. I want to be able to add monitoring without causing any downtime
3. Other strategies to manage users/passwords are fine, but again, I still need to apply those grants without restarting the database.

Notes

1. If I install the mysql from scratch, then I add a "dba" type user similar to 'debian-sys-maint' in addition to the root user, with a complex password, which sidesteps the above problem. - this is not the use-case that i am considering here.

Possible Solutions

1. suggestion: Spin up a second mysqld process on port 3307 (with the --init-file option to create my nagios-monitor user), pointed at the same /var/lib/mysql, but masked to only open the "mysql" database

consequences?: probably corrupt everything - could pause

sudo kill -STOP $(cat /var/run/mysqld/mysqld.pid)

and then start -

sudo kill -CONT $(cat /var/run/mysqld/mysqld.pid)

How to force the main mysql to re-read the tables from disk?

1. some sort of scheduled task that is run by mysqld? could append my commands to that, as its not particularly urgent to run the grants.

Multiple mysqld

http://dev.mysql.com/doc/refman/5.5/en/mysqld-multi.html

Beware of the dangers of using multiple mysqld servers with the same data directory. Use separate data directories, unless you know what you are doing. Starting multiple servers with the same data directory does not give you extra performance in a threaded system.

http://dev.mysql.com/doc/refman/5.5/en/multiple-data-directories.html

5.3.1. Setting Up Multiple Data Directories

Normally, you should never have two servers that update data in the same databases. Even when the preceding precautions are observed, this kind of setup works only with MyISAM and MERGE tables, and not with any of the other storage engines.

mysql> SHOW TABLE STATUS where Name = 'user'\G
*************************** 1. row ***************************
           Name: user
         **Engine: MyISAM**
        Version: 10
     Row_format: Dynamic
           Rows: 27
 Avg_row_length: 91
    Data_length: 2476
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2012-04-12 11:46:21
    Update_time: 2013-01-22 00:16:00
     Check_time: 2013-01-19 20:52:09
      Collation: utf8_bin
       Checksum: NULL
 Create_options: 
        Comment: Users and global privileges
1 row in set (0.00 sec)

So I spun up a 2nd mysqld on port 3307, with a masked datadir like so;

# mkdir /var/lib/mysql2/
# ln -s /var/lib/mysql/mysql /var/lib/mysql2/mysql

# /usr/sbin/mysqld --basedir=/usr \
--datadir=/var/lib/mysql2 \
--plugin-dir=/usr/lib64/mysql/plugin \
--user=mysql \
--log-error=/var/lib/mysql2/server-64654.err \
--pid-file=/var/lib/mysql2/server-64654.pid \
--socket=/var/lib/mysql2/mysql.sock \
--port=3307 \
--init-file=/var/cache/chef/grants.sql \
--datadir=/var/lib/mysql2/

with a grants.sql;

GRANT ALL ON *.* TO 'nagios-user'@'127.0.0.1' IDENTIFIED BY 'xxx' WITH GRANT OPTION;
FLUSH PRIVILEGES;

mysql --port=3307 -h 127.0.0.1 -e 'select User from mysql.user'
+-------------+
| User        |
+-------------+
| root |
| nagios_user |

But the original mysql,doesn;t pick it up until it has been restarted! gah!

mysql --port=3306 -h 127.0.0.1 -e 'select User from mysql.user'
+-------------+
| User        |
+-------------+
| root |

Answer 1

You could copy just the the "mysql" database away to another location and start another daemon on it. Get the SHA1 or DES hash stored in the user table for a user with SUPER privs (usually root, but sometimes renamed for security through obscurity).

Then connect to the mysql using a modified version of the client library that makes mysql_real_connect() support using a pre-hashed password instead of having it take the password plaintext. This should be trivial.

You won't ever know the actual password, but with the hash and a modified client you'll be able to log in anyway.

You can then make any modifications to permissions, create necessary schema and tables and flush privileges.

I'll leave the security implications of such practices up to you.

Answer 2

If you want to do this

GRANT SELECT ON heartbeart.* to 'nagios-monitor'@'123.123.123.123'

without any restart tricks, only root@localhost can do that as follows:

INSERT INTO mysql.user SET
    user='nagios-monitor',
    host='123.123.123.123',
    password=PASSWORD('whateverpassword');
INSERT INTO mysql.db SET
    user='nagios-monitor',
    host='123.123.123.123',
    db='heartbeat',
    select_priv='Y';
FLUSH PRIVILEGES;

When done, run show grants for 'nagios-monitor'@'123.123.123.123'; and you should see this:

mysql> show grants for 'nagios-monitor'@'123.123.123.123';
+----------------------------------------------------------------------+
| Grants for [email protected]                            |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'nagios-monitor'@'123.123.123.123'             |
| GRANT SELECT ON `heartbeart`.* TO 'nagios-monitor'@'123.123.123.123' |
+----------------------------------------------------------------------+
2 rows in set (0.03 sec)

mysql> 

This, of course, assumes the you are accessing a database called heartbeat.

Now if you want to do a table called heartbeat, let's pretend the heartbeat table is in the nagiosdb database. As root@localhost, you would run this:

INSERT INTO mysql.user SET
    user='nagios-monitor',
    host='123.123.123.123',
    password=PASSWORD('whateverpassword');
INSERT INTO mysql.tables_priv SET
    user='nagios-monitor',
    host='123.123.123.123',
    db='nagiosdb',
    grantor='root@localhost',
    table_priv='Select';
FLUSH PRIVILEGES;

When done, run show grants for 'nagios-monitor'@'123.123.123.123'; and you should see this:

mysql> show grants for 'nagios-monitor'@'123.123.123.123';
+------------------------------------------------------------------------------+
| Grants for [email protected]                                    |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'nagios-monitor'@'123.123.123.123'                     |
| GRANT SELECT ON `nagiosdb`.`heartbeat` TO 'nagios-monitor'@'123.123.123.123' |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Give it a Try !!!

UPDATE 2013-01-22 13:44 EDT

Here is untested BlackOps approach:

STEP01) Make a copy of the mysql schema into a database called mysql_orig

mkdir /var/lib/mysql/mysql_schema
cd /var/lib/mysql/mysql_schema
cp /var/lib/mysql/mysql/* .
chown -R mysql:mysql /var/lib/mysql/mysql_schema

STEP02) Make a copy of the mysql schema into a database called mysql_nagios

mkdir /var/lib/mysql/mysql_nagios
cd /var/lib/mysql/mysql_nagios
cp /var/lib/mysql/mysql/* .
chown -R mysql:mysql /var/lib/mysql/mysql_nagios

STEP03) Create database for Schema Injection

mkdir /var/lib/mysql/mysql_injection
chown -R mysql:mysql /var/lib/mysql/mysql_injection

STEP04) Insert the nagios user

INSERT INTO mysql_nagios.user SET
    user='nagios-monitor',
    host='123.123.123.123',
    password=PASSWORD('whateverpassword');
INSERT INTO mysql_nagios.tables_priv SET
    user='nagios-monitor',
    host='123.123.123.123',
    db='nagiosdb',
    grantor='root@localhost',
    table_priv='Select';

STEP05) As root@localhost, activate the event scheduler

SET GLOBAL event_scheduler = 1;

STEP06) Slide the new stuff into mysql_injection

rm -f /var/lib/mysql/mysql_injection/*
cp /var/lib/mysql/mysql_nagios/* /var/lib/mysql/mysql_injection/.

STEP07) As root@localhost, automate the injection of the new tables (one time usage)

use mysql_injection
DELIMITER $$
CREATE EVENT ev_schema_inject
    ON SCHEDULE
      EVERY 1 MINUTE
      STARTS (NOW() + INTERVAL 1 MINUTE)
    DO
    BEGIN
        REPLACE INTO mysql.user SELECT * FROM mysql_injection.user;
        REPLACE INTO mysql.tables_priv SELECT * FROM mysql_injection.tables_priv;
        FLUSH PRIVILEGES;
    END $$
DELIMITER ;

THERE IS A CATCH : Someone with root@localhost privileges needs to run Steps 5-7.

In the event you want to put back everything as it was before, just run this:

DELETE FROM mysql.user;
DELETE FROM mysql.tables_priv;
INSERT INTO mysql.user SELECT * FROM mysql_orig.user;
INSERT INTO mysql.tables_priv SELECT * FROM mysql_orig.tables_priv;
FLUSH PRIVILEGES;
SET GLOBAL event_scheduler = 0;

Answer 3

So a while back I found this solution/work-around, which seems pretty reliable (though I am not 100% sure about using it in production...);

mysqld queries a function called check_scramble(*args) which returns 0 if authentication is successful. gdb can be used to temporarily hook that function and return anything you want, so this script causes gdb to intercept the call to check_scamble once, and then detaches from the process.

Solution obviously requires gdb to be installed.

So starting with an unknown root password;

# echo "select version()" | mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

1). create a file called gdbscript containing the following;

  tbreak check_scramble
  commands
  silent
  return (long int)0
  end
  cont

2) call gdb in one console;

# gdb -p $(cat /var/run/mysqld/mysqld.pid) -batch \
    -x gdbscript

this will hook into the mysqld process;

[New LWP 21273]
[New LWP 21272]
[Thread debugging using libthread_db enabled]
0x00007f6849d4a383 in poll () from /lib64/libc.so.6
Temporary breakpoint 1 at 0x660204: file /export/home/pb2/build/sb_0-26265460-1512805762.25/rpm/BUILD/mysql-5.6.39/mysql-5.6.39/sql/password.c, line 556.

3) in another console. login to mysql with any user/pass combination;

# mysql -uroot -p123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 153
mysql>  UPDATE mysql.user SET Password=PASSWORD('SOMENEWPASSWORD') WHERE USER='root';
mysql>  flush privileges;

4) gdb will detach, and auth will be back, and root password reset;

Temporary breakpoint 1 at 0x660204: file /export/home/pb2/build/sb_0-26265460-1512805762.25/rpm/BUILD/mysql-5.6.39/mysql-5.6.39/sql/password.c, line 556.
[Switching to Thread 0x7f68209f3700 (LWP 22242)]

then you can login with the new root password...

[~]# echo "SELECT VERSION() | mysql -uroot -pSOMENEWPASSWORD
version()
5.6.39