[ Avaa Bypassed ]

--source include/not_embedded.inc
--source include/have_validate_password_plugin.inc

call mtr.add_suppression("Dictionary file not specified");
let $MYSQL_ERRMSG_BASEDIR=`select @@lc_messages_dir`;

# plugin is not installed so even 'pass' (very weak)
# is accepted as a password
CREATE USER 'base_user'@'localhost' IDENTIFIED BY 'pass';
GRANT ALL ON mysql.* TO 'user1'@'localhost' IDENTIFIED BY 'pass';

--replace_regex /\.dll/.so/
eval INSTALL PLUGIN validate_password SONAME '$VALIDATE_PASSWORD';

# test for all the three password policy
# policy: LOW, MEDIUM, STRONG

--echo # password policy LOW (which only check for password length)
--echo # default case: password length should be minimum 8

SET @@global.validate_password_policy=LOW;
--error ER_NOT_VALID_PASSWORD
CREATE USER 'user'@'localhost' IDENTIFIED BY '';
--error ER_NOT_VALID_PASSWORD
SET PASSWORD FOR 'base_user'@'localhost'= PASSWORD('aweg');
SET PASSWORD FOR 'base_user'@'localhost'= PASSWORD('password3');
SET PASSWORD FOR 'base_user'@'localhost'= PASSWORD('password');
--error ER_NOT_VALID_PASSWORD
SET PASSWORD FOR 'base_user'@'localhost'= PASSWORD('passwor');
SET @@global.validate_password_length= 12;
--error ER_NOT_VALID_PASSWORD
UPDATE mysql.user SET PASSWORD= PASSWORD('password') WHERE user='base_user';
--error ER_NOT_VALID_PASSWORD
UPDATE mysql.user SET PASSWORD= PASSWORD('afrgtyhlp98') WHERE user='base_user';
UPDATE mysql.user SET PASSWORD= PASSWORD('iuyt567nbvfA') WHERE user='base_user';
GRANT USAGE ON *.* TO 'base_user'@'localhost' IDENTIFIED BY 'password1234';
SET @@global.validate_password_mixed_case_count= 0;
SET @@global.validate_password_number_count= 0;
SET @@global.validate_password_special_char_count= 0;
SET @@global.validate_password_length= 0;
UPDATE mysql.user SET PASSWORD= PASSWORD('') WHERE user='base_user';
UPDATE mysql.user SET PASSWORD= PASSWORD('p') WHERE user='base_user';
UPDATE mysql.user SET PASSWORD= PASSWORD('password') WHERE user='base_user';
SET @@global.validate_password_length= -2;
-- Error ER_WRONG_TYPE_FOR_VAR
SET @@global.validate_password_length= 3.5;

# test to check maximum value of password_length

SET @@global.validate_password_length= 2147483647;
SET @@global.validate_password_length= 2147483648;
PREPARE stmt1 FROM 'UPDATE mysql.user SET PASSWORD=(?) where USER = ?';
SET @a = REPEAT('a',2147483647);
SET @b = 'user@localhost';
EXECUTE stmt1 USING @a,@b;
DEALLOCATE PREPARE stmt1;
SET @@global.validate_password_length= 4294967295;
SET @@global.validate_password_length= 8;
PREPARE stmt1 FROM 'UPDATE mysql.user SET PASSWORD=(?) where USER = ?';
SET @a = REPEAT('a',1048576);
SET @b = 'user@localhost';
EXECUTE stmt1 USING @a,@b;
DEALLOCATE PREPARE stmt1;

--echo # password policy MEDIUM (check for mixed_case, digits, special_chars)
--echo # default case : atleast 1 mixed_case, 1 digit, 1 special_char

SET @@global.validate_password_mixed_case_count= 1;
SET @@global.validate_password_number_count= 1;
SET @@global.validate_password_special_char_count= 1;
SET @@global.validate_password_policy=MEDIUM;
SET @@global.validate_password_number_count= 0;
CREATE USER 'user'@'localhost' IDENTIFIED BY 'aedfoiASE$%';
--error ER_NOT_VALID_PASSWORD
SET PASSWORD FOR 'user'@'localhost'= PASSWORD('foiuiuytd78');
SET PASSWORD FOR 'user'@'localhost'= PASSWORD('pasretryFRGH&^98');
SET @@global.validate_password_mixed_case_count= 0;
--error ER_NOT_VALID_PASSWORD
CREATE USER 'user'@'localhost' IDENTIFIED BY 'aedSWEhjui';
SET PASSWORD FOR 'user'@'localhost'= PASSWORD('gruyuHOIU&*(');
SET PASSWORD FOR 'user'@'localhost'= PASSWORD('passwor0987**&');
SET @@global.validate_password_special_char_count= 0;
SET PASSWORD FOR 'base_user'@'localhost'= PASSWORD('piufgklol');
SET PASSWORD FOR 'base_user'@'localhost'= PASSWORD('password1A#');
SET @@global.validate_password_special_char_count= 1;
SET @@global.validate_password_number_count= 1;
SET @@global.validate_password_mixed_case_count= 1;
--error ER_NOT_VALID_PASSWORD
SET PASSWORD FOR 'base_user'@'localhost'= PASSWORD('erftuiik');
SET PASSWORD FOR 'base_user'@'localhost'= PASSWORD('password1A#');
SET @@global.validate_password_number_count= 2;
--error ER_NOT_VALID_PASSWORD
UPDATE mysql.user SET PASSWORD= PASSWORD('password1A#') WHERE user='base_user';
UPDATE mysql.user SET PASSWORD= PASSWORD('password12A#') WHERE user='base_user';
SET @@global.validate_password_number_count= 1;
SET @@global.validate_password_mixed_case_count= 2;
--error ER_NOT_VALID_PASSWORD
UPDATE mysql.user SET PASSWORD= PASSWORD('password1A#') WHERE user='base_user';
UPDATE mysql.user SET PASSWORD= PASSWORD('password1AB#') WHERE user='base_user';
SET @@global.validate_password_mixed_case_count= 1;
SET @@global.validate_password_special_char_count= 2;
--error ER_NOT_VALID_PASSWORD
GRANT USAGE ON *.* TO 'base_user'@'localhost' IDENTIFIED BY 'password1A#';
GRANT USAGE ON *.* TO 'base_user'@'localhost' IDENTIFIED BY 'password1A#$';
SET @@global.validate_password_special_char_count= 1;

--echo # No dictionary file present, no dictionary check
SET @@global.validate_password_policy=STRONG;
SET PASSWORD FOR 'base_user'@'localhost'= PASSWORD('password1A#');
UPDATE mysql.user SET PASSWORD= PASSWORD('password1A#') WHERE user='base_user';

--replace_result $MYSQL_ERRMSG_BASEDIR MYSQL_ERRMSG_BASEDIR
eval SET @@global.validate_password_dictionary_file="$MYSQL_ERRMSG_BASEDIR/dictionary.txt";

--echo # password policy strong
--echo # default_file : dictionary.txt

# file should contain 1 word per line
# error if substring of password is a dictionary word

SET @@global.validate_password_policy=STRONG;
--error ER_NOT_VALID_PASSWORD
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
--error ER_NOT_VALID_PASSWORD
SET PASSWORD FOR 'base_user'@'localhost'= PASSWORD('password1A#');
--error ER_NOT_VALID_PASSWORD
UPDATE mysql.user SET PASSWORD= PASSWORD('pass12345A#') WHERE user='base_user';
--error ER_NOT_VALID_PASSWORD
UPDATE mysql.user SET PASSWORD= PASSWORD('pass0000A#') WHERE user='base_user';
GRANT USAGE ON *.* TO 'base_user'@'localhost' IDENTIFIED BY 'PA00wrd!#';

--echo # test for password_validate_strength function

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT VALIDATE_PASSWORD_STRENGTH('password', 0);
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT VALIDATE_PASSWORD_STRENGTH();
SELECT VALIDATE_PASSWORD_STRENGTH('');
SELECT VALIDATE_PASSWORD_STRENGTH('pass');
SELECT VALIDATE_PASSWORD_STRENGTH('password');
SELECT VALIDATE_PASSWORD_STRENGTH('password0000');
SELECT VALIDATE_PASSWORD_STRENGTH('password1A#');
SELECT VALIDATE_PASSWORD_STRENGTH('PA12wrd!#');
SELECT VALIDATE_PASSWORD_STRENGTH('PA00wrd!#');

# Test for multibyte character set that have greater size when converted
# from uppercase to lowercase.
SET NAMES 'ujis';
SELECT VALIDATE_PASSWORD_STRENGTH('PA12wrd!#');

# default policy is set, all other plugin variables set to default
# Test to ensure that only the privileged user can access the plugin variables
SET @@global.validate_password_policy=MEDIUM;

# New connection
connect (plug_con,localhost,user1,pass);
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SET @@global.validate_password_policy=LOW;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SET @@global.validate_password_length= 4;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SET @@global.validate_password_special_char_count= 0;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
SET @@global.validate_password_mixed_case_count= 0;
# user has the update/create privilege but needs to satisfy password policy
# to update/create new account
--error ER_NOT_VALID_PASSWORD
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'PA00wrd!#';
--error ER_NOT_VALID_PASSWORD
UPDATE mysql.user SET PASSWORD= PASSWORD('password') WHERE user='user2';
UPDATE mysql.user SET PASSWORD= PASSWORD('PA00wrd!#') WHERE user='user2';
DROP USER 'user2'@'localhost';
disconnect plug_con;
--source include/wait_until_disconnected.inc

connection default;
DROP USER 'base_user'@'localhost';
DROP USER 'user1'@'localhost';
DROP USER 'user'@'localhost';
SET @@global.validate_password_length=default;
SET @@global.validate_password_number_count=default;
SET @@global.validate_password_mixed_case_count=default;
SET @@global.validate_password_special_char_count=default;
SET @@global.validate_password_policy=default;
SET @@global.validate_password_dictionary_file=default;

SELECT @@validate_password_length,
       @@validate_password_number_count,
       @@validate_password_mixed_case_count,
       @@validate_password_special_char_count,
       @@validate_password_policy,
       @@validate_password_dictionary_file;


--echo #
--echo # Bug#14588145 -	NEED ABILITY TO FLUSH PASSWORD VALIDATION DICTIONARY FILE
--echo #

SET @@global.validate_password_policy=STRONG;

--write_file $MYSQLTEST_VARDIR/tmp/dictionary.txt
password
validate
EOF

--write_file $MYSQLTEST_VARDIR/tmp/dictionary2.txt
password
validate
monkey
EOF

--echo # No dictionary file, password is accepted
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'passWORD123#';
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval SET @@global.validate_password_dictionary_file="$MYSQLTEST_VARDIR/tmp/dictionary2.txt";
# Dictionary file loaded

--echo # must return 3
SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS
  WHERE VARIABLE_NAME = 'validate_password_dictionary_file_words_count';

SELECT VARIABLE_VALUE into @ts1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
  WHERE VARIABLE_NAME = "validate_password_dictionary_file_last_parsed";

--echo # check format of the TS
--replace_regex /[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]/0000-00-00 00:00:00/
SELECT @ts1;

--echo # must return 19
SELECT LENGTH(@ts1);

--echo # must sleep for at least 1 sec so that the timestamps differ
--sleep 1

--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
eval SET @@global.validate_password_dictionary_file="$MYSQLTEST_VARDIR/tmp/dictionary.txt";

--echo # must return 2
SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS
  WHERE VARIABLE_NAME = 'validate_password_dictionary_file_words_count';

SELECT VARIABLE_VALUE into @ts2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
  WHERE VARIABLE_NAME = "validate_password_dictionary_file_last_parsed";

--echo # must return 1
SELECT @ts1 <> @ts2;

--error ER_NOT_VALID_PASSWORD
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'passWORD123#';

SET @@global.validate_password_dictionary_file=NULL;

--echo # Cache flushed and no dictionary file is loaded
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'passWORD123#';

--echo # Select commands to show that the validate_password lock is instrumented
SELECT NAME FROM performance_schema.setup_instruments WHERE NAME LIKE '%validate%';
SELECT NAME FROM performance_schema.rwlock_instances WHERE NAME LIKE '%validate%';

--echo # cleanup
DROP USER 'user1'@'localhost', 'user2'@'localhost';
SET @@global.validate_password_policy=DEFAULT;
remove_file $MYSQLTEST_VARDIR/tmp/dictionary.txt;
remove_file $MYSQLTEST_VARDIR/tmp/dictionary2.txt;

--echo #
--echo # Bug#21616496: CREATE USER ACCEPTS PLUGIN AND PASSWORD HASH,
--echo #               BUT IGNORES THE PASSWORD HASH
--echo #

--error ER_NOT_VALID_PASSWORD
CREATE USER 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password';

--error ER_NOT_VALID_PASSWORD
CREATE USER 'user1'@'localhost' IDENTIFIED WITH 'mysql_old_password';

--error ER_NOT_VALID_PASSWORD
CREATE USER 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E';

--error ER_NOT_VALID_PASSWORD
CREATE USER 'user1'@'localhost' IDENTIFIED WITH 'mysql_old_password' AS '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E';

--echo # clean up after the test
UNINSTALL PLUGIN validate_password;


--echo End of tests