diff options
author | Oleksandr Byelkin <sanja@mariadb.com> | 2021-08-19 13:30:45 +0200 |
---|---|---|
committer | Oleksandr Byelkin <sanja@mariadb.com> | 2021-09-09 15:25:39 +0200 |
commit | cf3c58e85a2dc7fff12f43b5e95577c22829b317 (patch) | |
tree | ce2ec3162fc8136085e5d022ae574b0496e68f8c | |
parent | 271afbc88e48307299c7a38abfb74dc372c7eb2c (diff) | |
download | mariadb-git-bb-10.7-MDEV-9245-4.tar.gz |
MDEV-9245 password "reuse prevention" validation pluginbb-10.7-MDEV-9245-4
-rw-r--r-- | mysql-test/suite/plugins/r/password_reuse_check.result | 53 | ||||
-rw-r--r-- | mysql-test/suite/plugins/t/password_reuse_check.test | 58 | ||||
-rw-r--r-- | plugin/password_reuse_check/CMakeLists.txt | 4 | ||||
-rw-r--r-- | plugin/password_reuse_check/password_reuse_check.c | 233 |
4 files changed, 348 insertions, 0 deletions
diff --git a/mysql-test/suite/plugins/r/password_reuse_check.result b/mysql-test/suite/plugins/r/password_reuse_check.result new file mode 100644 index 00000000000..5d797922c16 --- /dev/null +++ b/mysql-test/suite/plugins/r/password_reuse_check.result @@ -0,0 +1,53 @@ +install soname "password_reuse_check"; +set @save_password_reuse_check_interval= @@password_reuse_check_interval; +set global password_reuse_check_interval= 0; +# Default value (sould be unlimited i.e. 0) +SHOW GLOBAL VARIABLES like "password_reuse_check%"; +Variable_name Value +password_reuse_check_interval 0 +# insert user +grant select on *.* to user_name@localhost identified by 'test_pwd'; +grant select on *.* to user_name@localhost identified by 'test_pwd'; +ERROR HY000: Your password does not satisfy the current policy requirements +show warnings; +Level Code Message +Error 1819 Your password does not satisfy the current policy requirements +alter user user_name@localhost identified by 'test_pwd'; +ERROR HY000: Operation ALTER USER failed for 'user_name'@'localhost' +show warnings; +Level Code Message +Error 1819 Your password does not satisfy the current policy requirements +Error 1396 Operation ALTER USER failed for 'user_name'@'localhost' +# check exparation +set global password_reuse_check_interval= 10; +alter user user_name@localhost identified by 'test_pwd'; +ERROR HY000: Operation ALTER USER failed for 'user_name'@'localhost' +show warnings; +Level Code Message +Error 1819 Your password does not satisfy the current policy requirements +Error 1396 Operation ALTER USER failed for 'user_name'@'localhost' +select hex(hash) from mysql.password_reuse_check_history; +hex(hash) +6276C87127F2B65FC6B24E94E324A02FF0D393D7FB7DEAF6F5F49F0A8AB006711D5C6EF67E36A251AB6337E7E20D312F9ED66D70EB699A6EC85B1E0BC7F376C0 +# emulate old password +update mysql.password_reuse_check_history set time= date_sub(now(), interval +11 day); +alter user user_name@localhost identified by 'test_pwd'; +show warnings; +Level Code Message +drop user user_name@localhost; +show create table mysql.password_reuse_check_history; +Table Create Table +password_reuse_check_history CREATE TABLE `password_reuse_check_history` ( + `hash` binary(64) NOT NULL, + `time` timestamp NOT NULL DEFAULT current_timestamp(), + PRIMARY KEY (`hash`), + KEY `tm` (`time`) +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +select count(*) from mysql.password_reuse_check_history; +count(*) +1 +drop table mysql.password_reuse_check_history; +set @save_password_reuse_check_interval= @@password_reuse_check_interval; +set global password_reuse_check_interval= @save_password_reuse_check_interval; +uninstall plugin password_reuse_check; diff --git a/mysql-test/suite/plugins/t/password_reuse_check.test b/mysql-test/suite/plugins/t/password_reuse_check.test new file mode 100644 index 00000000000..d99920407af --- /dev/null +++ b/mysql-test/suite/plugins/t/password_reuse_check.test @@ -0,0 +1,58 @@ +--source include/not_embedded.inc + +if (!$PASSWORD_REUSE_CHECK_SO) { + skip No PASSWORD_REUSE_CHECK plugin; +} + +install soname "password_reuse_check"; + +set @save_password_reuse_check_interval= @@password_reuse_check_interval; + +set global password_reuse_check_interval= 0; + +--echo # Default value (sould be unlimited i.e. 0) +SHOW GLOBAL VARIABLES like "password_reuse_check%"; + +--echo # insert user +grant select on *.* to user_name@localhost identified by 'test_pwd'; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to user_name@localhost identified by 'test_pwd'; +show warnings; + +--error ER_CANNOT_USER +alter user user_name@localhost identified by 'test_pwd'; +show warnings; + +# Plugin does not work for it +#--error ER_NOT_VALID_PASSWORD +#SET PASSWORD FOR user_name@localhost = PASSWORD('test_pwd'); + +--echo # check exparation + +set global password_reuse_check_interval= 10; + +--error ER_CANNOT_USER +alter user user_name@localhost identified by 'test_pwd'; +show warnings; +select hex(hash) from mysql.password_reuse_check_history; + +--echo # emulate old password +update mysql.password_reuse_check_history set time= date_sub(now(), interval +11 day); + +alter user user_name@localhost identified by 'test_pwd'; +show warnings; + +drop user user_name@localhost; + +show create table mysql.password_reuse_check_history; +select count(*) from mysql.password_reuse_check_history; + +drop table mysql.password_reuse_check_history; + +set @save_password_reuse_check_interval= @@password_reuse_check_interval; + +set global password_reuse_check_interval= @save_password_reuse_check_interval; + +uninstall plugin password_reuse_check; diff --git a/plugin/password_reuse_check/CMakeLists.txt b/plugin/password_reuse_check/CMakeLists.txt new file mode 100644 index 00000000000..ab80f3c57ad --- /dev/null +++ b/plugin/password_reuse_check/CMakeLists.txt @@ -0,0 +1,4 @@ + +ADD_DEFINITIONS(-DMYSQL_SERVER) + +MYSQL_ADD_PLUGIN(password_reuse_check password_reuse_check.c) diff --git a/plugin/password_reuse_check/password_reuse_check.c b/plugin/password_reuse_check/password_reuse_check.c new file mode 100644 index 00000000000..bdbf7d358f9 --- /dev/null +++ b/plugin/password_reuse_check/password_reuse_check.c @@ -0,0 +1,233 @@ +/* Copyright (c) 2021, Oleksandr Byelkin and MariaDB + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ + +#include <stdio.h> // for snprintf +#include <string.h> // for memset +#include <mysql/plugin_password_validation.h> +#include <mysqld_error.h> + +#define HISTORY_DB_NAME "password_reuse_check_history" + +#define SQL_BUFF_LEN 2048 + +#define STRING_WITH_LEN(X) (X), ((size_t) (sizeof(X) - 1)) + +// 0 - unlimited, otherwise number of days to check +static unsigned interval= 0; + +// helping string for bin_to_hex512 +static char digits[]= "0123456789ABCDEF"; + + +/** + Convert string of 512 bits (64 bytes) to hex representation + + @param to pointer to the result puffer + (should be at least 64*2 bytes) + @param str pointer to 512 bits (64 bytes string) +*/ + +static void bin_to_hex512(char *to, const unsigned char *str) +{ + const unsigned char *str_end= str + (512/8); + for (; str != str_end; ++str) + { + *to++= digits[((unsigned char) *str) >> 4]; + *to++= digits[((unsigned char) *str) & 0x0F]; + } +} + + +/** + Send SQL error as ER_UNKNOWN_ERROR for information + + @param mysql Connection handler +*/ + +static void report_sql_error(MYSQL *mysql) +{ + my_printf_error(ER_UNKNOWN_ERROR, "password_reuse_check:[%d] %s", ME_WARNING, + mysql_errno(mysql), mysql_error(mysql)); +} + + +/** + Create the history of passwords table for this plugin. + + @param mysql Connection handler + + @retval 1 - Error + @retval 0 - OK +*/ + +static int create_table(MYSQL *mysql) +{ + if (mysql_real_query(mysql, + // 512/8 = 64 + STRING_WITH_LEN("CREATE TABLE mysql." HISTORY_DB_NAME + " ( hash binary(64)," + " time timestamp default current_timestamp," + " primary key (hash), index tm (time) )" + " ENGINE=Aria"))) + { + report_sql_error(mysql); + return 1; + } + return 0; +} + + +/** + Run this query and create table if needed + + @param mysql Connection handler + @param query The query to run + @param len length of the query text + + @retval 1 - Error + @retval 0 - OK +*/ + +static int run_query_with_table_creation(MYSQL *mysql, const char *query, + size_t len) +{ + if (mysql_real_query(mysql, query, len)) + { + unsigned int rc= mysql_errno(mysql); + if (rc != ER_NO_SUCH_TABLE) + { + // suppress this error in case of try to add the same password twice + if (rc != ER_DUP_ENTRY) + report_sql_error(mysql); + return 1; + } + if (create_table(mysql)) + return 1; + if (mysql_real_query(mysql, query, len)) + { + report_sql_error(mysql); + return 1; + } + } + return 0; +} + + +/** + Password validator + + @param username User name (part of whole login name) + @param password Password to validate + @param hostname Host name (part of whole login name) + + @retval 1 - Password is not OK or an error happened + @retval 0 - Password is OK +*/ + +static int validate(const MYSQL_CONST_LEX_STRING *username, + const MYSQL_CONST_LEX_STRING *password, + const MYSQL_CONST_LEX_STRING *hostname) +{ + MYSQL *mysql= NULL; + size_t key_len= username->length + password->length + hostname->length; + size_t buff_len= (key_len > SQL_BUFF_LEN ? key_len : SQL_BUFF_LEN); + size_t len; + char *buff= malloc(buff_len); + unsigned char hash[512/8]; + char escaped_hash[512/8*2 + 1]; + if (!buff) + return 1; + + mysql= mysql_init(NULL); + if (!mysql) + { + free(buff); + return 1; + } + + memcpy(buff, hostname->str, hostname->length); + memcpy(buff + hostname->length, username->str, username->length); + memcpy(buff + hostname->length + username->length, password->str, + password->length); + buff[key_len]= 0; + memset(hash, 0, sizeof(hash)); + my_sha512(hash, buff, key_len); + if (mysql_real_connect_local(mysql, NULL, NULL, NULL, 0) == NULL) + goto sql_error; + + if (interval) + { + // trim the table + len= snprintf(buff, buff_len, + "DELETE FROM mysql." HISTORY_DB_NAME + " WHERE time < DATE_SUB(NOW(), interval %d day)", + interval); + if (run_query_with_table_creation(mysql, buff, len)) + goto sql_error; + } + + bin_to_hex512(escaped_hash, hash); + escaped_hash[512/8*2]= '\0'; + len= snprintf(buff, buff_len, + "INSERT INTO mysql." HISTORY_DB_NAME "(hash) " + "values (x'%s')", + escaped_hash); + if (run_query_with_table_creation(mysql, buff, len)) + goto sql_error; + + free(buff); + mysql_close(mysql); + return 0; // OK + +sql_error: + free(buff); + if (mysql) + mysql_close(mysql); + return 1; // Error +} + +static MYSQL_SYSVAR_UINT(interval, interval, PLUGIN_VAR_RQCMDARG, + "Password history retention period in days (0 means unlimited)", NULL, NULL, + 0, 0, 365*100, 1); + + +static struct st_mysql_sys_var* sysvars[]= { + MYSQL_SYSVAR(interval), + NULL +}; + +static struct st_mariadb_password_validation info= +{ + MariaDB_PASSWORD_VALIDATION_INTERFACE_VERSION, + validate +}; + +maria_declare_plugin(password_reuse_check) +{ + MariaDB_PASSWORD_VALIDATION_PLUGIN, + &info, + "password_reuse_check", + "Oleksandr Byelkin", + "Prevent password reuse", + PLUGIN_LICENSE_GPL, + NULL, + NULL, + 0x0100, + NULL, + sysvars, + "1.0", + MariaDB_PLUGIN_MATURITY_ALPHA +} +maria_declare_plugin_end; |