MMM Community Blog Multi-Master Replication Manager for MySQL

2Oct/09Off

Centralized authentication with mysql

There is no centralized authentication for mysql. You have to set up your user accounts individually for each of your mysql servers. You can use replication, but that would mean, your master servers must have the same authentication privileges as your slaves have. What about security?

So, here is a little workaround that I did and I can't find the same anywhere.

How does it work?

This is an unofficial and unsupported solution. Let's have a master and one or more slave hosts. On your master, you have to create a database lets say slave_auth. Create the same tables in this database as you have in the 'mysql' database and needed for authentication (more details below), and set up a user who can read/write those tables.

When you are done, create a temporary database on your slaves lets say auth_temp with the exact structure of the tables on your master, but with federated engine connected to your master with the user you added previously. Shut down mysql, overwrite the mysql system tables with those you got in auth_temp db (or don't shut down, just overwrite), flush privileges, and there you go, its done. Funny humm?

What are the requirements?

  • It needs federated engine supported on your slaves

(no, there is no more requirements)

How to make it work:

Let's move on to some direct working example:

1. Moves on your master(s):

1.1 Creating tables for slaves:

mysql>create database auth_slaves;
mysql>create table auth_slaves.columns_priv like mysql.columns_priv;
mysql>create table auth_slaves.db like mysql.db;
mysql>create table auth_slaves.tables_priv like mysql.tables_priv;
mysql>create table auth_slaves.user like mysql.user;

(If you already have different privileges on your slaves, I would recommend a mysql dump instead)

1.2 Create a user for your slaves:

mysql>grant select,insert,update on auth_slaves.* to 'auth_slave_user'@'192.168.1.%' identified by 'auth_4_sl4v3s';

You are able to set up different kind of auth users, for example if you only wan't on some of your slaves to be just read only, don't grant other rights, but select. Up to you.

All done on  your master..

2. Moves on your slave(s):

2.1 Create a temporary database

mysql>create database dropme_auth;

2.2 Create the tables

Now, I don't paste all the table definitions, but execute show create table on each table printed above, copy the result to a txt or something and modify the command to work with federated like in this example:

mysql#host:master#db:auth_slaves>show create table user\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
......
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

Now all you have to do is modify the engine like: engine = federated CONNECTION='mysql://auth_slave_user:auth_4_sl4v3s@master.host:3306/auth_slaves/user'

So, just create a table driven by federated engine connected to your masters auth_slaves db with the same table names with the same table structure.

3. Replace the system tables:

In this scenerio you must overwrite the tables in the mysql database folder (for example /var/lib/mysql/mysql) with the tables from your temporary database where you built the federated tables (for example /var/lib/mysql/dropme_auth) like this:  cp /var/lib/mysql/dropme_auth/*.frm /var/lib/mysql/mysql/ .

Don't use '*' in the cp, it's even better if you copy every table one by one.
Take care of the privileges after the copy.

4. Execute a simple select query like select * from mysql.user. If you can see the same what is on your master, you are done. If not, hmm. Thats a kind of a problem :)

What if connection broken?

Nothing will happen unless you execute flush privileges. Because as we all know, mysql cache the authentication tables and doesn't load the content again until flushing of privileges.

What about flush privileges?

The question you will consider soon if you build this up: Ok wait, how can I modify authentication settings now?! The answer is so simple, just make it on any of your slaves and it will affect the tables stored on your master. When you done, if the documentation correct, execute flush privileges command on your master and all your slaves will reload the modified tables.

Benefits:

The most important is what used to be when you move on to centralized authentication.

  • You only need to maintain the privileges on one of your slaves instead of a lots.
  • In this case, you don't have to keep the same privileges for your masters and slaves and you don't have to replicate the mysql database.
Comments (7) Trackbacks (0)
  1. Though it’s not officially supported you could also place most of the grants tables into NDB cluster, Things that won’t work in the cluster are stored procedures, triggers and events. These still would need to be created in each mysqld node individually.

    ## NDB GRANTS – RUN ON ALL SQL NODES ##

    RENAME TABLE mysql.user TO mysql.user_myi;
    CREATE TABLE IF NOT EXISTS mysql.user LIKE mysql.user_myi;
    ALTER TABLE mysql.user ENGINE=NDBCLUSTER;
    INSERT IGNORE INTO mysql.user SELECT * FROM mysql.user_myi;

    RENAME TABLE mysql.db TO mysql.db_myi;
    CREATE TABLE IF NOT EXISTS mysql.db LIKE mysql.db_myi;
    ALTER TABLE mysql.db ENGINE=NDBCLUSTER;
    INSERT IGNORE INTO mysql.db SELECT * FROM mysql.db_myi;

    RENAME TABLE mysql.host TO mysql.host_myi;
    CREATE TABLE IF NOT EXISTS mysql.host LIKE mysql.host_myi;
    ALTER TABLE mysql.host ENGINE=NDBCLUSTER;
    INSERT IGNORE INTO mysql.host SELECT * FROM mysql.host_myi;

    RENAME TABLE mysql.tables_priv TO mysql.tables_priv_myi;
    CREATE TABLE IF NOT EXISTS mysql.tables_priv LIKE mysql.tables_priv_myi;
    ALTER TABLE mysql.tables_priv ENGINE=NDBCLUSTER;
    INSERT IGNORE INTO mysql.tables_priv SELECT * FROM mysql.tables_priv_myi;

    RENAME TABLE mysql.columns_priv TO mysql.columns_priv_myi;
    CREATE TABLE IF NOT EXISTS mysql.columns_priv LIKE mysql.columns_priv_myi;
    ALTER TABLE mysql.columns_priv ENGINE=NDBCLUSTER;
    INSERT IGNORE INTO mysql.columns_priv SELECT * FROM mysql.columns_priv_myi;

    SET GLOBAL event_scheduler=1;
    SELECT “REMEMBER TO set event_scheduler=1 in the my.cnf” as WARNING;
    CREATE EVENT `mysql`.`flush_priv_tables` ON SCHEDULE EVERY 30 second ON COMPLETION PRESERVE DO FLUSH PRIVILEGES;

    # Revert to default MYISAM GRANTS tables

    DROP TABLE IF EXISTS mysql.user, mysql.db, mysql.host, mysql.tables_priv, mysql.columns_priv;
    RENAME TABLE mysql.user_my TO mysql.user;
    RENAME TABLE mysql.db_my TO mysql.db;
    RENAME TABLE mysql.host_my TO mysql.host;
    RENAME TABLE mysql.tables_priv_my TO mysql.tables_priv;
    RENAME TABLE mysql.columns_priv_my TO mysql.columns_priv;
    DROP EVENT `mysql`.`flush_priv_tables`;
    SET GLOBAL event_scheduler=0;

  2. However, there is the potential for some profound badness with in either of these approaches. The grants system does not use SQL to query the grants tables and load the results into the cache, rather it uses HANDLER calls which is really are only safe with MyISAM. Perhaps Sinisa could/should elaborate.

  3. @Matthew

    Exactly, I already had some issues with grants system myself. I always find a way to workaround. Would be great if mysql would support this.

    And I also forget to mention to upload the tables in my example like you did :)

  4. Does the MMM tool support changing the federated table definition when you promote a slave to the master (say, if you’re doing a rolling maintenance?)

  5. @Sheeri

    No it’s not, but why would you need it if you point in the connection part of the federated table’s definition to the VIP of the masters?

  6. @istvan good point but it is possible. Read again to see what I mean.

  7. I have some great news. A year and half after this thread started, the first release with officially supported privilege tables stored in NDBCLUSTER engine is available!

    Please check it out!
    http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-privilege-distribution.html


Trackbacks are disabled.