Audit and external authentication in MySQL
Today I will tell how to make your MySQL DBMS closer to PCI DSS standards. To begin with, this is what we get:
Console admin user mcshadow
Access is possible with both root and mortal user rights mike.
Mike mortal console
Access to the database under the administrator is not possible.
Meanwhile in syslog
The post is recommended to those who really need it, there was a phrase “I will be brief today,” but after I finished writing this 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: there is no user-defined password policy in MySQL, i.e. You can’t set up a cunning scheme for the use of a password, control new passwords for compliance with standards adopted by your organization, use SSO 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 user actions with DBA rights to the security officer’s console. In addition to this, you often want to log into the database under your login, but with the rights of another user, for example, to install or perform a specific set of actions to get an error. What is desirable to not know the password of this user, as well as to make sure that all actions are correctly reflected in the security logs. Other databases allow you to make some of these items, of course, not all. MySQL starting from version 5.5.7+ will allow you to perform any of them, without creating undue burden on the database.
The article is educational in nature, the use of these solutions for any purpose, including those described, solely on your conscience.
Now let's talk what and where to get. As you understand, in order for this to work, you need to build an external library. Well, we are not going to write anything ourselves as “experienced” siblings, but simply take the finished one and just transfer from one source to another.
First: logging for the MySQL client is taken from the percones. If you compare the sources of MySQL 5.5.X and Percona 5.5.X, the difference in them is only in the fact that the Perconian client is able to log everything in a syslog, but it does this optionally. Actually, we just need to drag a part of these source codes. And make this the default setting. You can simply take the source MySQL client for perkony, if you are afraid to mess with copy-paste.
Second: logging attempts to log into the database, as you understand, will have to be done on the server. Here we have a small selection. How to understand what you need to log? Everything is simple in the MySQL source code there is a file log.cc - it is responsible for the 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 against including it on the 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 allowing to write to the general_log_print and general_log_write logs. There will have to sweat and carefully review what to change and where.
Third: and for us, perhaps the most interesting is the new MySQL 5.5.7 GRANT PROXY feature.
for this to work the user real_user must be created in a special way
Now, when connecting a real_user user , the password can be checked not only by the means of the muscle itself, but put it on a third-party plugin - auth_plugin_xxx . You can write this plugin yourself, well, this is for labor developers, we are not going to do such things yet, because: for testing purposes and as an example, MySQL has already written a couple of plug-ins on which to experiment. We will take them as a basis to play around. The most important thing for which this plugin is needed is that, based on its internal logic, it can replace the username field whose privileges will be applied to the session. And if our real_user has proxy rights under a spoofed user, MySQL successfully gives us all the rights of the priv_user user. It is in this plug-in that you can cram a call to SSO using your internal protocol or ldap server, and heap up any heap of other logic.
While the theory suffices - we swing MySQL 5.5.15 in source codes.
The first is logging administrator actions from the local machine. We believe that the database administrator is not the server administrator and has access to the database only from the console using a socket or via TCP - this is not important. In order to administer the server is more than enough. In mysql.cc we need to add the following lines:
The second. The main module that handles server user 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 currently not called when attempting to connect a user to the database. I did it like this (new blocks are allocated to the PCI DSS patch):
Unfortunately, this is not all. It turns out the authentication is performed in 2 places. The first is when you try to use the use database command. For this, the sql_db.cc module, the mysql_change_db function, is responsible for it, after calling the general log, as well, we add our lines.
And the last thing left for us to do is to log the moment when the user views information on the database that is not available to him. The module sql_show.cc is responsible for this. The procedure with the eloquent title mysqld_show_create_db . Add:
According to POSIX, the syslog function supports multithreading, it should not fall. We did not fit into the critical sections, it should not be too slow.
Fine! Now we can read in the logs how and where we change rights. It remains the case for small. Zayuzat new feature. We climb into the plugins directory of source codes and see as many as two plugins for authentication, like this a gift. The first auth_socket.c allows you to log in to the database under the operating system user if a socket is used. Well, in the absence of a better use of it - like this is our SSO. The next plugin, test_plugin.c , works as follows. When creating a user, you specify the mystical string AS 'auth_string' after the name of the plugin. The plugin compares the password with this line. If no match is found, it throws out an error, but if everything went well, then you are assigned to the session the privileges of the user whose name is ' auth_string '. A plugin, as you yourself understand the test and serves simply for the purpose of checking that the mechanism works.
According to the documentation, the plug-in can only change the user name, or rather, write it with a new specially allocated field info-> authenticated_as and set the need to enter a password
I had problems with changing the host, so I didn’t risk it. All just lokalki.
We make one of two functions and shove it into a test authentication plugin (all of a sudden it uses another system ...)
Next we are going
We configure the database, log in as root and perform the following actions:
That's all - now you can log in to the database using your account as root or any other user to whom proxying is allowed. As can be seen from the table, you can still set with grant option , but purely IMHO, this is already superfluous.
')
While the solution is certainly damp, for it is not clear what works and what does not. For example, everything that I tried worked for me, but I don’t have full confidence in it yet. For more understanding, you can read the listing unit tests - plugin_auth.result , which as it turns out is in the samples in the daddy mysql-test \ r (thanks for the hint svetasmirnova ), but in general very little information on the topic GRANT PROXY . Following the results of these works, we made diff -Nur and collect RPM - 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).
Learn! Join now! Share your experience!
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 with both root and mortal user rights mike.
Mike mortal console
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: mike
mysqld: User: mcshadow FAILED access from: localhost with privileges: mike
Introduction
The post is recommended to those who really need it, there was a phrase “I will be brief today,” but after I finished writing this 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: there is no user-defined password policy in MySQL, i.e. You can’t set up a cunning scheme for the use of a password, control new passwords for compliance with standards adopted by your organization, use SSO 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 user actions with DBA rights to the security officer’s console. In addition to this, you often want to log into the database under your login, but with the rights of another user, for example, to install or perform a specific set of actions to get an error. What is desirable to not know the password of this user, as well as to make sure that all actions are correctly reflected in the security logs. Other databases allow you to make some of these items, of course, not all. MySQL starting from version 5.5.7+ will allow you to perform any of them, without creating undue burden on the database.
The article is educational in nature, the use of these solutions for any purpose, including those described, solely on your conscience.
Theory
Now let's talk what and where to get. As you understand, in order for this to work, you need to build an external library. Well, we are not going to write anything ourselves as “experienced” siblings, but simply take the finished one and just transfer from one source to another.
First: logging for the MySQL client is taken from the percones. If you compare the sources of MySQL 5.5.X and Percona 5.5.X, the difference in them is only in the fact that the Perconian client is able to log everything in a syslog, but it does this optionally. Actually, we just need to drag a part of these source codes. And make this the default setting. You can simply take the source MySQL client for perkony, if you are afraid to mess with copy-paste.
Second: logging attempts to log into the database, as you understand, will have to be done on the server. Here we have a small selection. How to understand what you need to log? Everything is simple in the MySQL source code there is a file log.cc - it is responsible for the 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 against including it on the 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 allowing to write to the general_log_print and general_log_write logs. There will have to sweat and carefully review what to change and where.
Third: and for us, perhaps the most interesting is the new MySQL 5.5.7 GRANT PROXY feature.
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 a real_user user , the password can be checked not only by the means of the muscle itself, but put it on a third-party plugin - auth_plugin_xxx . You can write this plugin yourself, well, this is for labor developers, we are not going to do such things yet, because: for testing purposes and as an example, MySQL has already written a couple of plug-ins on which to experiment. We will take them as a basis to play around. The most important thing for which this plugin is needed is that, based on its internal logic, it can replace the username field whose privileges will be applied to the session. And if our real_user has proxy rights under a spoofed user, MySQL successfully gives us all the rights of the priv_user user. It is in this plug-in that you can cram a call to SSO using your internal protocol or ldap server, and heap up any heap of other logic.
While the theory suffices - we swing MySQL 5.5.15 in source codes.
Practice
The first is logging administrator actions from the local machine. We believe that the database administrator is not the server administrator and has access to the database only from the console using a socket or via TCP - this is not important. In order to administer the server is more than enough. In mysql.cc we need to add the following lines:
#include <violite.h> // 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. The main module that handles server user 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 currently not called when attempting to connect a user to the database. I did it like this (new blocks are allocated to 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, this is not all. It turns out the authentication is performed in 2 places. The first is when you try to use the use database command. For this, the sql_db.cc module, the mysql_change_db function, is responsible for it, after calling the general log, as well, 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 left for us to do is to log the moment when the user views information on the database that is not available to him. The module sql_show.cc is responsible for this. The procedure with the eloquent title 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 fit into the critical sections, it should not be too slow.
Fine! Now we can read in the logs how and where we change rights. It remains the case for small. Zayuzat new feature. We climb into the plugins directory of source codes and see as many as two plugins for authentication, like this a gift. The first auth_socket.c allows you to log in to the database under the operating system user if a socket is used. Well, in the absence of a better use of it - like this is our SSO. The next plugin, test_plugin.c , works as follows. When creating a user, you specify the mystical string AS 'auth_string' after the name of the plugin. The plugin compares the password with this line. If no match is found, it throws out an error, but if everything went well, then you are assigned to the session the privileges of the user whose name is ' auth_string '. A plugin, as you yourself understand the test and serves simply for the purpose of checking that the mechanism works.
According to the documentation, the plug-in can only change the user name, or rather, write it with a new specially allocated field info-> authenticated_as and set the need to enter a 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 didn’t risk it. All just lokalki.
We make one of two functions and shove it into a test authentication plugin (all of a sudden it uses 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 put, you do not want to kill your muscular. 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 - now you can log in to the database using your account as root or any other user to whom proxying is allowed. As can be seen from the table, you can still set with grant option , but purely IMHO, this is already superfluous.
')
Conclusion
While the solution is certainly damp, for it is not clear what works and what does not. For example, everything that I tried worked for me, but I don’t have full confidence in it yet. For more understanding, you can read the listing unit tests - plugin_auth.result , which as it turns out is in the samples in the daddy mysql-test \ r (thanks for the hint svetasmirnova ), but in general very little information on the topic GRANT PROXY . Following the results of these works, we made diff -Nur and collect RPM - 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).
Learn! Join now! Share your experience!
Source: https://habr.com/ru/post/126519/
All Articles