Audit and external authentication in MySQL

    Today I will tell you how to make your MySQL DBMS closer to PCI DSS standards. To get started, here's what we get:
    Console admin user mcshadow
    mcshadow: ~ $ mysql --user = mcshadow --password = mike
    mysql> select current_user ();
    + ---------------- +
    | current_user () |
    + ---------------- +
    | mike @ localhost |
    + ---------------- +
    mcshadow: ~ $ mysql --user = mcshadow --password = root
    mysql> select current_user ();
    + ---------------- +
    | current_user () |
    + ---------------- +
    | root @ localhost |
    + ---------------- +

    Access is possible both with root rights and with the rights of the mortal user mike.


    Console mortal user mike
    mike: ~ $ mysql --user = mcshadow --password = mike
    ERROR 1698 (28000): Access denied for user 'mcshadow' @ 'localhost'

    Access to the database under the administrator is not possible.


    Meanwhile in syslog
    mysqld: User: mcshadow TRY access from: localhost with privileges: mike
    mysqld: User: mcshadow SUCCESS access from: localhost with privileges: mike
    mysql: SYSTEM_USER: 'mcshadow', MYSQL_USER: 'mcshadow', CONNECTION_ID: 5, DB_SERVER: '- - ', DB:' - ', COMMAND_RESULT: SUCCESS, QUERY:' select current_user (); '
    mysqld: User: mcshadow TRY access from: localhost with privileges: root
    mysqld: User: mcshadow SUCCESS access from: localhost with privileges: root
    mysql: SYSTEM_USER: 'mcshadow', MYSQL_USER: 'mcshadow', CONNECTION_ID: 6, DB_SERVER: '- - ', DB:' - ', COMMAND_RESULT: SUCCESS, QUERY:' select current_user (); '
    mysqld: User: mcshadow TRY access from: localhost with privileges:
    access from: localhost with privileges: mike


    Introduction


    The post is recommended to those who really need it, there was the phrase "today I will be brief", but at the end of writing the article I realized that it did not work out.
    If you use MySQL as a DBMS in a large company, you will encounter some problems, such as: MySQL does not have a user-defined password policy, i.e. you will not be able to set up a tricky scheme for expiring the password used, control new passwords to meet the standards adopted in your organization, use the SSO system to connect to the database, etc. It would also be extremely convenient to log all successful and unsuccessful attempts to connect to the database and the actions of users with DBA rights to the security officer’s console. In addition to this, I often really want to go to the database under my login, but with the rights of another user, for example, to perform an installation or perform a certain set of actions to get an error. Moreover, it is advisable not to know the password of this user, as well as to do so, so that all actions are correctly reflected in the security logs. Other databases allow you to make part of these items, of course, not all. MySQL starting with version 5.5.7+ will allow you to execute any of them without creating unnecessary load on the database.
    The article is educational in nature, the use of these solutions for any purpose, including those described, is solely on your conscience.

    Theory


    Now let's talk about what and where to get it. As you know, in order for this to work, you need to build an external library. Well, we, as “experienced” applicants, will not write anything ourselves, we just take the finished one and simply transfer from one source to another.
    First: we take logging for the MySQL client from perkona. If we compare the sources of MySQL 5.5.X and Percona 5.5.X, then the difference is solely in the fact that the Perkon client can log everything in syslog, but it does this optionally. Actually, we just need to drag part of these source codes. And make this the default setting. You can just take the source MySQL client for perkona if you are afraid to mess up with copy-paste.
    Second: logging of attempts to enter the database, as you understand, will have to be done on the server. Here we have little choice. How to understand what needs to be logged? Everything is simple in the MySQL sources there is a log.cc file - it is responsible for general_log . This log successfully records everything that happens on the database, including successful and unsuccessful connection attempts. Everything would be fine, but it works very slowly - I strongly advise you not to include it on an industrial database. We need this log to understand what to look for and where. According to this file at the moment there are only two implementations that allow recording in the general_log_print and general_log_write logs . Here you have to sweat and carefully look at what to change and where.
    Third: and perhaps the most interesting thing for us is the new feature of MySQL 5.5.7 GRANT PROXY .
    GRANT PROXY
      ON 'priv_user' @ 'localhost'
      TO 'real_user' @ 'localhost';

    for this to work, the user real_user must be created in a special way
    CREATE USER 'real_user' @ 'localhost'
      IDENTIFIED WITH 'auth_plugin_xxx' AS 'auth_string';

    Now, when connecting to the real_user user , the password can be checked not just by the muscle itself, but put it on a third-party plugin - auth_plugin_xxx . You can write this plugin yourself, tynt , well, it's for developers, we will not do such things yet, because: for testing purposes and as an example, MySQL has already written a couple of plugins on which to experiment. We will take them as a basis to play around. The most important thing why this plugin is needed is that it can replace, based on its internal logic, the username field whose privileges will be applied to the session. And if our real_user has proxy rightsunder the substituted user - MySQL will successfully provide us with all the rights of the priv_user user . It is in this plug-in that you can push the call to SSO according to your internal protocol or to the ldap server, and make a bunch of other logic.
    For now, enough theory - download MySQL 5.5.15 in the source code.

    Practice


    The first is logging administrator actions from the local machine. We believe that the database administrator is not a server administrator and has access to the database only from the console using a socket or over TCP - this is not important. In order to administer the server this is more than enough. In mysql.cc we need to add the following lines:
    #include  // после этой строки для Linux систем включаем syslog
    #ifndef __WIN__
    #include "syslog.h"
    #endif
    ...
    void tee_putc(int c, FILE *file); // после этой декларируем внутреннюю функцию логирования
    void write_syslog(String *buffer);
    ...
    // в конец файла добавляем имплементацию из Percona слегка её изменив чтобы логировать успешность выполения комманды. Стек ошибок мускуль хранит тут: mysql_error(&mysql)[0]
    void write_syslog(String *line){
    #ifndef __WIN__
      uint length= line->length();
      uint chunk_len= min(MAX_SYSLOG_MESSAGE, length);
      char *ptr= line->c_ptr_safe();
      char buff[MAX_SYSLOG_MESSAGE + 1];
      for (;
           length;
           length-= chunk_len, ptr+= chunk_len, chunk_len= min(MAX_SYSLOG_MESSAGE,
                                                               length))
      {
        char *str;
        if (length == chunk_len)
          str= ptr;                                 // last chunk => skip copy
        else
        {
          memcpy(buff, ptr, chunk_len);
          buff[chunk_len]= '\0';
          str= buff;
        }
        syslog(LOG_INFO,
               "SYSTEM_USER:'%s', MYSQL_USER:'%s', CONNECTION_ID:%lu, "
               "DB_SERVER:'%s', DB:'%s', COMMAND_RESULT:%s, QUERY:'%s'",
               getenv("SUDO_USER") ? getenv("SUDO_USER") : 
               getenv("USER") ? getenv("USER") : "--",
               current_user ? current_user : "--",
               mysql_thread_id(&mysql),
               current_host ? current_host : "--",
               current_db ? current_db : "--",
               mysql_error(&mysql)[0]?"FAILED":"SUCCESS",
    		   str);
      }
    #endif
    }
    ...
    #endif /*HAVE_READLINE*/  // логирование сомманды выполняем после этой строки 
    #ifndef __WIN__
      if (buffer->length() && connect_flag == CLIENT_INTERACTIVE){
        write_syslog(buffer);
      }
    #endif
    

    The second one. The main module that handles server-side login attempts is sql_acl.cc . After calling all general_log_print - we need to add our own. As you understand, general_log is disabled, but the tip is very good. The second command ( general_log_write ) is not currently called when trying to connect a user to the database. It turned out like this (new blocks are allocated by the PCI DSS patch):
    // PCI DSS patch
    #ifndef __WIN__
    #include "syslog.h"
    #endif
    // end PCI DSS patch
    ... // функция login_failed_error - вызывается если аутентификция не прошла
        general_log_print(thd, COM_CONNECT, ER(ER_ACCESS_DENIED_NO_PASSWORD_ERROR),
                          mpvio->auth_info.user_name,
                          mpvio->auth_info.host_or_ip);
    // PCI DSS patch
    	syslog(LOG_WARNING, "User:%s FAILED access from:%s with privileges:%s", mpvio->auth_info.user_name, mpvio->auth_info.host_or_ip, mpvio->auth_info.authenticated_as);
    // end PCI DSS patch
    ...
        general_log_print(thd, COM_CONNECT, ER(ER_ACCESS_DENIED_ERROR),
                          mpvio->auth_info.user_name,
                          mpvio->auth_info.host_or_ip,
                          passwd_used ? ER(ER_YES) : ER(ER_NO));
    // PCI DSS patch
    	syslog(LOG_WARNING, "User:%s FAILED access from:%s with privileges:%s", mpvio->auth_info.user_name, mpvio->auth_info.host_or_ip, mpvio->auth_info.authenticated_as);
    // end PCI DSS patch
    ... // функция secure_auth - это проверка на свежесть протокола испольщуемого клиентом
      if (mpvio->client_capabilities & CLIENT_PROTOCOL_41)
      {
        my_error(ER_SERVER_IS_IN_SECURE_AUTH_MODE, MYF(0),
                 mpvio->auth_info.user_name,
                 mpvio->auth_info.host_or_ip);
        general_log_print(thd, COM_CONNECT, ER(ER_SERVER_IS_IN_SECURE_AUTH_MODE),
                          mpvio->auth_info.user_name,
                          mpvio->auth_info.host_or_ip);
    // PCI DSS patch
    	syslog(LOG_WARNING, "User:%s FAILED access from:%s with privileges:%s", mpvio->auth_info.user_name, mpvio->auth_info.host_or_ip, mpvio->auth_info.authenticated_as);
    // end PCI DSS patch
      }
      else
      {
        my_error(ER_NOT_SUPPORTED_AUTH_MODE, MYF(0));
        general_log_print(thd, COM_CONNECT, ER(ER_NOT_SUPPORTED_AUTH_MODE));
    // PCI DSS patch
    	syslog(LOG_WARNING, "Auth mode not supported");
    // end PCI DSS patch
      }
    ... // функция send_plugin_request_packet - эта функция заставляет клиента ввести дополнительные данные если аутентификационный плагин этого требует
        general_log_print(current_thd, COM_CONNECT, ER(ER_NOT_SUPPORTED_AUTH_MODE));
    // PCI DSS patch
    	syslog(LOG_WARNING, "Auth mode not supported");
    // end PCI DSS patch
    ... // find_mpvio_user
        general_log_print(current_thd, COM_CONNECT, ER(ER_NOT_SUPPORTED_AUTH_MODE));
    // PCI DSS patch
    	syslog(LOG_WARNING, "Auth mode not supported");
    // end PCI DSS patch
    ... // функция acl_authenticate - ну это само ядро аутентификации main так сказать
        if (strcmp(mpvio.auth_info.authenticated_as, mpvio.auth_info.user_name))
        {
          general_log_print(thd, command, "%s@%s as %s on %s",
                            mpvio.auth_info.user_name, mpvio.auth_info.host_or_ip,
                            mpvio.auth_info.authenticated_as ? 
                              mpvio.auth_info.authenticated_as : "anonymous",
                            mpvio.db.str ? mpvio.db.str : (char*) "");
    // PCI DSS patch
    	syslog(LOG_WARNING, "User:%s TRY access from:%s with privileges:%s", mpvio.auth_info.user_name, mpvio.auth_info.host_or_ip, mpvio.auth_info.authenticated_as);
    // end PCI DSS patch
        }
        else
          {
    	  general_log_print(thd, command, (char*) "%s@%s on %s",
                            mpvio.auth_info.user_name, mpvio.auth_info.host_or_ip,
                            mpvio.db.str ? mpvio.db.str : (char*) "");
    // PCI DSS patch
    	syslog(LOG_WARNING, "User:%s TRY access from:%s with privileges:%s", mpvio.auth_info.user_name, mpvio.auth_info.host_or_ip, mpvio.auth_info.authenticated_as);
    // end PCI DSS patch
          }
    ...
      if (res > CR_OK && mpvio.status != MPVIO_EXT::SUCCESS)
      {
        DBUG_ASSERT(mpvio.status == MPVIO_EXT::FAILURE);
        if (!thd->is_error())
          login_failed_error(&mpvio, mpvio.auth_info.password_used);
        DBUG_RETURN (1);
      }
    // PCI DSS patch
      else
    	syslog(LOG_WARNING, "User:%s SUCCESS access from:%s with privileges:%s", mpvio.auth_info.user_name, mpvio.auth_info.host_or_ip, mpvio.auth_info.authenticated_as);
    // end PCI DSS patch
    

    Unfortunately that's not all. It turns out that authentication is performed in 2 more places. The first is when you try to run the use database command . The sql_db.cc module is responsible for this ; the mysql_change_db function is in it after calling the general log, and as luckily , we add our lines.
        general_log_print(thd, COM_INIT_DB, ER(ER_DBACCESS_DENIED_ERROR),
                          sctx->priv_user, sctx->priv_host, new_db_file_name.str);
    // PCI DSS patch
    	syslog(LOG_WARNING, "User:%s FAILED access from:%s with privileges:%s", sctx->proxy_user, sctx->priv_host, sctx->priv_user);
    // end PCI DSS patch
    

    And the last thing that remains for us to do is to log in the moment the user views the database information that is not available to him. The sql_show.cc module is responsible for this . The procedure with the eloquent name mysqld_show_create_db . Add:
        general_log_print(thd,COM_INIT_DB,ER(ER_DBACCESS_DENIED_ERROR),
                          sctx->priv_user, sctx->host_or_ip, dbname);
    // PCI DSS patch
    	syslog(LOG_WARNING, "User:%s FAILED access from:%s with privileges:%s", sctx->proxy_user, sctx->priv_host, sctx->priv_user);
    // end PCI DSS patch
    

    According to POSIX, the syslog function supports multithreading; it should not fall. We did not get into critical sections, it should not slow down much.
    Excellent! Now we can read in the logs how and where our rights are changing. It remains the case for small. Zayuzat new feature. We climb into the plugins directory of the source codes and see as many as two authentication plugins, this is a gift. The first auth_socket.c - allows you to log in to the database as a user of the operating system, if a socket is used. Well, for lack of a better one we will use it - like this is our SSO. The next plugin - test_plugin.c - works as follows. When creating a user, you specify the mystical line AS 'auth_string'after the name of the plugin. The plugin compares the password with this line. If no matches are found, it throws an error; if everything went well, then the privileges of the user whose name is ' auth_string ' are assigned to the session . The plugin, as you yourself understand the test and is just for the purpose of checking that the mechanism is working.
    According to the documentation, the plugin can only change the username, or rather write it with the new info-> authenticated_as field, specially designated for this, and set the password
    #define PASSWORD_USED_NO         0
    #define PASSWORD_USED_YES        1
    #define PASSWORD_USED_NO_MENTION 2
    

    I had problems with changing the host, so I did not take risks. All only with LAN.
    We make one of two functions and shove it into a test authentication plugin (suddenly it is being used by another system ...)
    static int auth_test_plugin(MYSQL_PLUGIN_VIO *vio, MYSQL_SERVER_AUTH_INFO *info)
    {
      unsigned char *pkt;
      int pkt_len;
      MYSQL_PLUGIN_VIO_INFO vio_info;
      struct ucred cred;
      socklen_t cred_len= sizeof(cred);
      struct passwd pwd_buf, *pwd;
      char buf[1024];
      /* запрашиваем пароль */
      if (vio->write_packet(vio, (const unsigned char *) PASSWORD_QUESTION, 1))
        return CR_ERROR;
      /* считываем пароль */
      if ((pkt_len= vio->read_packet(vio, &pkt)) < 0)
        return CR_ERROR;
      /* указываем что на пароль можно забить */
      info->password_used= PASSWORD_USED_NO_MENTION;
      /* копируем полученный пароль в качестве имени пользователя */
      strcpy (info->authenticated_as, (const char *) pkt);
      vio->info(vio, &vio_info);
      if (vio_info.protocol != MYSQL_VIO_SOCKET)
        return CR_ERROR;
      /* get the UID of the client process */
      if (getsockopt(vio_info.socket, SOL_SOCKET, SO_PEERCRED, &cred, &cred_len))
        return CR_ERROR;
      if (cred_len != sizeof(cred))
        return CR_ERROR;
      /* and find the username for this uid */
      getpwuid_r(cred.uid, &pwd_buf, buf, sizeof(buf), &pwd);
      if (pwd == NULL)
        return CR_ERROR;
      /* проверяем что пользователь ОС совпадает с пользователем MySQL */
      return strcmp(pwd->pw_name, info->user_name) ? CR_ERROR : CR_OK;
    }
    

    Next we are going
    cmake -DCMAKE_INSTALL_PREFIX = / opt / mysql-5.5.15 - this is the folder where we are put, you do not want to kill your muscle.
    make
    make install
    

    We configure the database, log in as root and perform the following actions:
    install plugin test_plugin_server soname 'auth_test_plugin.so';
    show plugins;
    + ----------------------- + -------- + ---------------- ---- + --------------------- + --------- +
    | Name | Status | Type | Library | License |
    + ----------------------- + -------- + ---------------- ---- + --------------------- + --------- +
    ...
    | test_plugin_server | ACTIVE | AUTHENTICATION | auth_test_plugin.so | GPL |
    + ----------------------- + -------- + ---------------- ---- + --------------------- + --------- +
    create user 'mike' @ 'localhost';
    create user 'mcshadow' @ 'localhost' identified with 'test_plugin_server' as 'volki';
    grant proxy on 'root' @ 'localhost' to 'mcshadow' @ 'localhost';
    grant proxy on 'mike' @ 'localhost' to 'mcshadow' @ 'localhost';
    select * from mysql.proxies_priv;
    + ----------- + ---------- + ----------------- + -------- ------ + ------------ + ---------------- + ------------- -------- +
    | Host | User | Proxied_host | Proxied_user | With_grant | Grantor | Timestamp |
    + ----------- + ---------- + ----------------- + -------- ------ + ------------ + ---------------- + ------------- -------- +
    ...
    | localhost | mcshadow | localhost | root | 0 | root @ localhost | 2011-08-17 01:15:09 |
    | localhost | mcshadow | localhost | mike | 0 | root @ localhost | 2011-08-17 01:30:35 |
    + ----------- + ---------- + ----------------- + -------- ------ + ------------ + ---------------- + ------------- -------- +
    

    That’s all, actually - now you can log in to the database under your account with root privileges or any other user to whom proxying is allowed. As you can see from the table, you can still set with grant option , but purely IMHO this is already superfluous.

    Conclusion


    So far, of course, the solution is damp, because it is not clear what works and what does not. For example, everything that I tried worked for me, but there is no complete confidence in it yet. For a better understanding, you can read the listing of unit tests - plugin_auth.result , which, as it turns out, is in mysql-test \ r folder (thanks for the hint by svetasmirnova ), but in general there is very little information on the topic GRANT PROXY . Based on the results of these works, we split diff-Nur and collect RPMs - we will try to drive this solution (after review by real programmers) on an industrial database. I hope not let you down (pah-pah-pah).
    Study! Join now! Share your experience!

    Also popular now: