Gecko [ www.jdcpatala.org ]

Name	Size	Permission
binlog_anonymous_ownership.tes...	11.78 KB	-rw-r--r--
binlog_bug23533.test	1.48 KB	-rw-r--r--
binlog_bug36391-master.opt	32 B	-rw-r--r--
binlog_bug36391.test	1.18 KB	-rw-r--r--
binlog_cache_write_failure-mas...	97 B	-rw-r--r--
binlog_cache_write_failure.tes...	5.4 KB	-rw-r--r--
binlog_check_flush_log_asserti...	1.68 KB	-rw-r--r--
binlog_checksum.test	1.08 KB	-rw-r--r--
binlog_crash_safe_master_check...	51 B	-rw-r--r--
binlog_crash_safe_master_check...	2.6 KB	-rw-r--r--
binlog_database.test	370 B	-rw-r--r--
binlog_delete_and_flush_index....	3.53 KB	-rw-r--r--
binlog_dmls_on_tmp_tables_read...	2.38 KB	-rw-r--r--
binlog_drop_if_exists.test	3.47 KB	-rw-r--r--
binlog_drop_temp_table_on_disc...	2 KB	-rw-r--r--
binlog_drop_temp_table_on_disc...	1.75 KB	-rw-r--r--
binlog_enforce_gtid_consistenc...	379 B	-rw-r--r--
binlog_enforce_gtid_consistenc...	378 B	-rw-r--r--
binlog_enforce_gtid_consistenc...	1.46 KB	-rw-r--r--
binlog_enforce_gtid_consistenc...	3.75 KB	-rw-r--r--
binlog_enforce_gtid_consistenc...	314 B	-rw-r--r--
binlog_enforce_gtid_consistenc...	313 B	-rw-r--r--
binlog_enforce_gtid_consistenc...	739 B	-rw-r--r--
binlog_enforce_gtid_consistenc...	409 B	-rw-r--r--
binlog_error_action-master.opt	88 B	-rw-r--r--
binlog_error_action.test	17.12 KB	-rw-r--r--
binlog_flush_logs_error.test	1.72 KB	-rw-r--r--
binlog_foreign_key_parent_tabl...	44 B	-rw-r--r--
binlog_foreign_key_parent_tabl...	1.08 KB	-rw-r--r--
binlog_format_switch_in_tmp_ta...	2.77 KB	-rw-r--r--
binlog_grant.test	1.83 KB	-rw-r--r--
binlog_grant_alter_user-master...	32 B	-rw-r--r--
binlog_grant_alter_user.test	39.59 KB	-rw-r--r--
binlog_group_commit_flush_cras...	51 B	-rw-r--r--
binlog_group_commit_flush_cras...	2.17 KB	-rw-r--r--
binlog_group_commit_gtid_order...	4.6 KB	-rw-r--r--
binlog_group_commit_indefinite...	1.51 KB	-rw-r--r--
binlog_group_commit_sync_delay...	1.84 KB	-rw-r--r--
binlog_group_commit_sync_delay...	1.23 KB	-rw-r--r--
binlog_group_commit_sync_no_de...	3.72 KB	-rw-r--r--
binlog_gtid_automatic_using_ro...	2.02 KB	-rw-r--r--
binlog_gtid_cache-master.opt	117 B	-rw-r--r--
binlog_gtid_cache.test	635 B	-rw-r--r--
binlog_gtid_errors-master.opt	149 B	-rw-r--r--
binlog_gtid_errors.test	9.1 KB	-rw-r--r--
binlog_gtid_exhausted-master.o...	77 B	-rw-r--r--
binlog_gtid_exhausted.test	3.3 KB	-rw-r--r--
binlog_gtid_implicit_commit.te...	121 B	-rw-r--r--
binlog_gtid_innodb.test	132 B	-rw-r--r--
binlog_gtid_mix_ok_packet_all_...	131 B	-rw-r--r--
binlog_gtid_mix_ok_packet_all_...	1.63 KB	-rw-r--r--
binlog_gtid_mix_response_packe...	99 B	-rw-r--r--
binlog_gtid_mix_response_packe...	1.82 KB	-rw-r--r--
binlog_gtid_mode_set_gtid_next...	4.66 KB	-rw-r--r--
binlog_gtid_mysqlbinlog_row.te...	197 B	-rw-r--r--
binlog_gtid_mysqlbinlog_row_in...	707 B	-rw-r--r--
binlog_gtid_mysqlbinlog_row_my...	661 B	-rw-r--r--
binlog_gtid_mysqlbinlog_start_...	444 B	-rw-r--r--
binlog_gtid_mysqldump.test	2.09 KB	-rw-r--r--
binlog_gtid_next_begin_caused_...	948 B	-rw-r--r--
binlog_gtid_next_partially_fai...	2.93 KB	-rw-r--r--
binlog_gtid_next_partially_fai...	9.45 KB	-rw-r--r--
binlog_gtid_next_single_stmt_t...	968 B	-rw-r--r--
binlog_gtid_next_temporary_tab...	2.73 KB	-rw-r--r--
binlog_gtid_next_xa.test	241 B	-rw-r--r--
binlog_gtid_ps_reprepare.test	6.56 KB	-rw-r--r--
binlog_gtid_purge_binlog_at_st...	3.72 KB	-rw-r--r--
binlog_gtid_rbr_only_flag.test	2.51 KB	-rw-r--r--
binlog_gtid_reset_consistency_...	2.61 KB	-rw-r--r--
binlog_gtid_row_ctype_ucs.test	265 B	-rw-r--r--
binlog_gtid_simple_recovery.te...	6 KB	-rw-r--r--
binlog_gtid_state_update_deadl...	2.62 KB	-rw-r--r--
binlog_gtid_stm_ctype_ucs.test	280 B	-rw-r--r--
binlog_gtid_unknown_xid.test	5.39 KB	-rw-r--r--
binlog_gtid_utils.test	3.56 KB	-rw-r--r--
binlog_gtids_table_gcov.test	2.86 KB	-rw-r--r--
binlog_implicit_commit.test	290 B	-rw-r--r--
binlog_incident.test	1.39 KB	-rw-r--r--
binlog_incident_ignore-master....	24 B	-rw-r--r--
binlog_incident_ignore.test	152 B	-rw-r--r--
binlog_index-master.opt	16 B	-rw-r--r--
binlog_index.test	16.86 KB	-rw-r--r--
binlog_innodb-master.opt	25 B	-rw-r--r--
binlog_innodb.test	139 B	-rw-r--r--
binlog_innodb_row.test	2.72 KB	-rw-r--r--
binlog_killed.test	9.71 KB	-rw-r--r--
binlog_killed_simulate-master....	39 B	-rw-r--r--
binlog_killed_simulate.test	2.7 KB	-rw-r--r--
binlog_max_extension.test	2.61 KB	-rw-r--r--
binlog_max_flush_queue_time_wa...	32 B	-rw-r--r--
binlog_max_flush_queue_time_wa...	850 B	-rw-r--r--
binlog_mixed_cache_stat.test	277 B	-rw-r--r--
binlog_mixed_load_data.test	535 B	-rw-r--r--
binlog_mts_logical_clock.test	2.35 KB	-rw-r--r--
binlog_mts_logical_clock_gtid....	318 B	-rw-r--r--
binlog_mysqlbinlog-cp932-maste...	23 B	-rw-r--r--
binlog_mysqlbinlog-cp932.test	1.11 KB	-rw-r--r--
binlog_mysqlbinlog_base64.test	2.64 KB	-rw-r--r--
binlog_mysqlbinlog_concat.test	4.28 KB	-rw-r--r--
binlog_mysqlbinlog_filter.test	2.07 KB	-rw-r--r--
binlog_mysqlbinlog_intvar.test	1.59 KB	-rw-r--r--
binlog_mysqlbinlog_raw.test	3.53 KB	-rw-r--r--
binlog_mysqlbinlog_rewrite_db....	6.58 KB	-rw-r--r--
binlog_mysqlbinlog_row.test	204 B	-rw-r--r--
binlog_mysqlbinlog_row_innodb....	714 B	-rw-r--r--
binlog_mysqlbinlog_row_myisam....	669 B	-rw-r--r--
binlog_mysqlbinlog_row_trans.t...	4.07 KB	-rw-r--r--
binlog_mysqlbinlog_start_stop....	452 B	-rw-r--r--
binlog_mysqlbinlog_start_stop_...	1.95 KB	-rw-r--r--
binlog_nogtid_mix_response_pac...	480 B	-rw-r--r--
binlog_noop_dml_with_readonly....	2.4 KB	-rw-r--r--
binlog_old_versions.test	5.22 KB	-rw-r--r--
binlog_purge_binary_logs_stall...	70 B	-rw-r--r--
binlog_purge_binary_logs_stall...	1.36 KB	-rw-r--r--
binlog_purge_binary_logs_to.te...	3.45 KB	-rw-r--r--
binlog_query_filter_rules-mast...	40 B	-rw-r--r--
binlog_query_filter_rules.test	855 B	-rw-r--r--
binlog_rbr_only_savepoint.test	2.83 KB	-rw-r--r--
binlog_reset_connection_not_re...	3.49 KB	-rw-r--r--
binlog_reset_master.test	996 B	-rw-r--r--
binlog_rewrite.test	2.99 KB	-rw-r--r--
binlog_rewrite_db_noleak.test	2.65 KB	-rw-r--r--
binlog_rewrite_order.test	1.07 KB	-rw-r--r--
binlog_rewrite_suppress_use.te...	2.25 KB	-rw-r--r--
binlog_rotate_bgc_sync.test	3.37 KB	-rw-r--r--
binlog_rotate_deadlock-master....	181 B	-rw-r--r--
binlog_rotate_deadlock.test	1.75 KB	-rw-r--r--
binlog_row_binlog-master.opt	55 B	-rw-r--r--
binlog_row_binlog.test	783 B	-rw-r--r--
binlog_row_cache_stat.test	274 B	-rw-r--r--
binlog_row_create_select_crash...	1.35 KB	-rw-r--r--
binlog_row_ctype_cp932.test	268 B	-rw-r--r--
binlog_row_ctype_ucs.test	272 B	-rw-r--r--
binlog_row_delete_all_rows.tes...	688 B	-rw-r--r--
binlog_row_drop_tbl.test	215 B	-rw-r--r--
binlog_row_drop_tmp_tbl.test	233 B	-rw-r--r--
binlog_row_innodb_stat-master....	26 B	-rw-r--r--
binlog_row_insert_select.test	708 B	-rw-r--r--
binlog_row_kill_create_select....	845 B	-rw-r--r--
binlog_row_mix_innodb_myisam-m...	97 B	-rw-r--r--
binlog_row_mix_innodb_myisam.t...	585 B	-rw-r--r--
binlog_row_mysqlbinlog_db_filt...	4.36 KB	-rw-r--r--
binlog_row_mysqlbinlog_verbose...	5.59 KB	-rw-r--r--
binlog_row_query_log_events.te...	942 B	-rw-r--r--
binlog_server_id.test	794 B	-rw-r--r--
binlog_server_start_options-ma...	54 B	-rw-r--r--
binlog_server_start_options.te...	1.14 KB	-rw-r--r--
binlog_set_gtid_mode_ongoing_t...	6.96 KB	-rw-r--r--
binlog_set_trans_dependency_de...	2.24 KB	-rw-r--r--
binlog_sf.test	2.67 KB	-rw-r--r--
binlog_shutdown_hang.test	1.14 KB	-rw-r--r--
binlog_simulate_crash_and_erro...	4.24 KB	-rw-r--r--
binlog_spurious_ddl_errors-mas...	20 B	-rw-r--r--
binlog_spurious_ddl_errors.tes...	4.16 KB	-rw-r--r--
binlog_sql_mode.test	4.75 KB	-rw-r--r--
binlog_start_comment.test	1.01 KB	-rw-r--r--
binlog_start_slave_heartbeat-s...	16 B	-rw-r--r--
binlog_start_slave_heartbeat.t...	1.33 KB	-rw-r--r--
binlog_stm_binlog-master.opt	55 B	-rw-r--r--
binlog_stm_binlog.test	741 B	-rw-r--r--
binlog_stm_blackhole.test	456 B	-rw-r--r--
binlog_stm_cache_stat.test	280 B	-rw-r--r--
binlog_stm_ctype_cp932.test	318 B	-rw-r--r--
binlog_stm_ctype_ucs.test	287 B	-rw-r--r--
binlog_stm_delete_all_rows.tes...	712 B	-rw-r--r--
binlog_stm_do_db-master.opt	38 B	-rw-r--r--
binlog_stm_do_db.test	2.8 KB	-rw-r--r--
binlog_stm_drop_tbl.test	230 B	-rw-r--r--
binlog_stm_drop_tmp_tbl.test	248 B	-rw-r--r--
binlog_stm_innodb_stat-master....	26 B	-rw-r--r--
binlog_stm_insert_select.test	489 B	-rw-r--r--
binlog_stm_mix_innodb_myisam-m...	145 B	-rw-r--r--
binlog_stm_mix_innodb_myisam.t...	552 B	-rw-r--r--
binlog_stm_ps.test	939 B	-rw-r--r--
binlog_stm_row.test	3.38 KB	-rw-r--r--
binlog_stm_unsafe_warning-mast...	51 B	-rw-r--r--
binlog_stm_unsafe_warning.test	5.65 KB	-rw-r--r--
binlog_stm_user_variables.test	3.4 KB	-rw-r--r--
binlog_switch_inside_trans.tes...	7.63 KB	-rw-r--r--
binlog_temporary_tables_user_v...	1.14 KB	-rw-r--r--
binlog_tmp_table.test	4.53 KB	-rw-r--r--
binlog_transaction_dependency_...	90 B	-rw-r--r--
binlog_transaction_dependency_...	8.21 KB	-rw-r--r--
binlog_transaction_dependency_...	2.2 KB	-rw-r--r--
binlog_transaction_write_set_s...	44 B	-rw-r--r--
binlog_transaction_write_set_s...	1.98 KB	-rw-r--r--
binlog_transaction_write_set_s...	44 B	-rw-r--r--
binlog_transaction_write_set_s...	1.33 KB	-rw-r--r--
binlog_transaction_write_set_s...	44 B	-rw-r--r--
binlog_transaction_write_set_s...	1.21 KB	-rw-r--r--
binlog_transaction_write_set_s...	44 B	-rw-r--r--
binlog_transaction_write_set_s...	4.15 KB	-rw-r--r--
binlog_truncate_kill.test	1.96 KB	-rw-r--r--
binlog_truncate_myisam.test	348 B	-rw-r--r--
binlog_unsafe-master.opt	65 B	-rw-r--r--
binlog_unsafe.test	25.95 KB	-rw-r--r--
binlog_unsafe_stmt_capable_eng...	41 B	-rw-r--r--
binlog_unsafe_stmt_capable_eng...	12.55 KB	-rw-r--r--
binlog_use_gtid_skip.test	1.03 KB	-rw-r--r--
binlog_user_if_exists.test	9.22 KB	-rw-r--r--
binlog_variables_log_bin-maste...	16 B	-rw-r--r--
binlog_variables_log_bin.test	136 B	-rw-r--r--
binlog_variables_log_bin_index...	70 B	-rw-r--r--
binlog_variables_log_bin_index...	136 B	-rw-r--r--
binlog_variables_log_statement...	2.18 KB	-rw-r--r--
binlog_wait_for_executed_gtid_...	879 B	-rw-r--r--
binlog_wait_for_executed_gtid_...	2.45 KB	-rw-r--r--
binlog_wait_for_executed_gtid_...	633 B	-rw-r--r--
binlog_wait_for_executed_gtid_...	801 B	-rw-r--r--
binlog_write_error.test	2.7 KB	-rw-r--r--
binlog_writeset_history_protec...	3.06 KB	-rw-r--r--
binlog_wrong_last_committed.te...	3.07 KB	-rw-r--r--
binlog_xa_handling.test	3.69 KB	-rw-r--r--
binlog_xa_modify_gtid_executed...	2.93 KB	-rw-r--r--
binlog_xa_prepare_failure.test	2.05 KB	-rw-r--r--
binlog_xa_prepared_disconnect....	628 B	-rw-r--r--
binlog_xa_rollback_failure.tes...	1.4 KB	-rw-r--r--
binlog_xa_select_gtid_executed...	2.41 KB	-rw-r--r--
binlog_xa_trx_gtid_response_pa...	98 B	-rw-r--r--
binlog_xa_trx_gtid_response_pa...	1.1 KB	-rw-r--r--
binlog_xa_trx_split_across_bin...	2.51 KB	-rw-r--r--
disabled.def	404 B	-rw-r--r--
log_builtin_as_identified_by_p...	39 B	-rw-r--r--
log_builtin_as_identified_by_p...	128 B	-rw-r--r--
show_binlog_events_no_lock.tes...	2.27 KB	-rw-r--r--

Code Editor : binlog_grant_alter_user.test

################### suite/binlog/t/binlog_grant_alter_user.test ###################
# Replication Testing CREATE/ALTER/GRANT USER new syntax with New variable        #
# "log-builtin-as-identified-by-password" default value "OFF"                     #
#                                                                                 # 
# Test for WL#6409                                                                #
# created :2015-01-02                                                             #
# Modified:2015-02-04  WL#6054                                                    #
# Author  : Lalit Choudhary                                                       #
#                                                                                 #
###################################################################################

--source include/not_embedded.inc
# Save the initial number of concurrent sessions
--source include/count_sessions.inc
--source include/master-slave.inc
--connection master

SHOW GLOBAL VARIABLES LIKE 'log_builtin_as_identified_by_password';
--echo #
--echo # WL#6409: CREATE/ALTER USER
--echo #

--echo # CREATE USER
--echo
CREATE USER user1;
query_vertical SELECT host,user,plugin,authentication_string
FROM mysql.user WHERE user = 'user1';

--echo
--error ER_PARSE_ERROR
CREATE USER user3@%;

# Sequence of the attributes. provide password first and auth_plugin later on
--error ER_PARSE_ERROR
CREATE USER user3@localhost BY 'auth_string' WITH 'mysql_native_password';

CREATE USER "user2"@'%';
query_vertical SELECT host,user,plugin,authentication_string
FROM mysql.user WHERE user='user2';

--echo
CREATE USER u1@localhost;
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string FROM mysql.user WHERE USER='u1';

CREATE USER u2@localhost IDENTIFIED BY 'auth_string' ACCOUNT LOCK;
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string FROM mysql.user WHERE USER='u2';

--echo
--error ER_PARSE_ERROR
CREATE USER user5@localhost IDENTIFIED  AS 'auth_string';

CREATE USER u3@localhost IDENTIFIED WITH 'mysql_native_password';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string FROM mysql.user WHERE USER='u3';

CREATE USER u4@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string FROM mysql.user WHERE USER='u4';

# Testing connection
--connect(con1, localhost, u4,'auth_string')
SELECT USER();
connection default;
disconnect con1;

--echo
CREATE USER user4@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string FROM mysql.user WHERE USER='user4';
--echo
CREATE USER user6@localhost IDENTIFIED WITH 'mysql_native_password';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string FROM mysql.user WHERE USER='user6';
--echo
CREATE USER user7@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y'
                            PASSWORD EXPIRE NEVER ACCOUNT UNLOCK;
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string FROM mysql.user WHERE USER='user7';
--echo
# Testing connection
--connect(con1, localhost, user7, 'auth_string#%y')
SELECT USER();
connection default;
disconnect con1;

CREATE USER user8@localhost IDENTIFIED WITH 'mysql_native_password'
                            AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string FROM mysql.user WHERE USER='user8';

# Testing connection
--connect(con1, localhost, user8,'auth_string')
SELECT USER();
connection default;
disconnect con1;

# Tesing with mysql_native_password plugin.
CREATE USER tu1@localhost IDENTIFIED WITH 'mysql_native_password';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,password_expired,
password_expired  FROM mysql.user WHERE USER='tu1';

CREATE USER tu2@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_@13*';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,
password_expired FROM mysql.user WHERE USER='tu2';

--echo
CREATE USER tu3@localhost IDENTIFIED WITH 'mysql_native_password' BY '%auth_O0s-tring';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,password_expired
FROM mysql.user WHERE USER='tu3';

--echo

CREATE USER u5@localhost REQUIRE SSL;
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type
FROM mysql.user WHERE USER='u5';

CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509;
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type
FROM mysql.user WHERE USER='u6';

CREATE USER tu4@localhost IDENTIFIED WITH 'mysql_native_password' BY 'djgsj743$' REQUIRE SSL;
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type FROM mysql.user WHERE USER='tu4';

CREATE USER tu5@localhost IDENTIFIED WITH 'mysql_native_password' BY 'dwh@#ghd$!' REQUIRE X509;
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type FROM mysql.user WHERE USER='tu5';

CREATE USER u7@localhost IDENTIFIED WITH 'mysql_native_password'
            REQUIRE CIPHER 'cipher';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,
x509_issuer,x509_subject FROM mysql.user WHERE USER='u7';

CREATE USER u8@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
            REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,
x509_issuer,x509_subject FROM mysql.user WHERE USER='u8';

CREATE USER u9@localhost REQUIRE SUBJECT 'sub';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,
x509_issuer,x509_subject FROM mysql.user WHERE USER='u9';

CREATE USER u10@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#y'
            REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
            SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
            ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA";
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,
x509_issuer,x509_subject FROM mysql.user WHERE USER='u10';

# Testing connection
--connect(con1, localhost, u10,'auth_string#y',,,,SSL)
SELECT USER();
connection default;
disconnect con1;

CREATE USER tu6@localhost IDENTIFIED WITH 'mysql_native_password' BY '#hGrt0O6'
            REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
            SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
            ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
            WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,x509_issuer,
x509_subject,max_questions,max_user_connections FROM mysql.user WHERE USER='tu6';

CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2;
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,max_questions
FROM mysql.user WHERE USER='u11';

CREATE USER u12@localhost IDENTIFIED BY 'auth_string'  WITH MAX_QUERIES_PER_HOUR 2;
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,max_questions
FROM mysql.user WHERE USER='u12';

CREATE USER u13@localhost IDENTIFIED WITH 'mysql_native_password'
            WITH MAX_CONNECTIONS_PER_HOUR 2;
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,max_connections
FROM mysql.user WHERE USER='u13';

CREATE USER u14@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
            WITH MAX_USER_CONNECTIONS 2 ACCOUNT LOCK;
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,max_user_connections
FROM mysql.user WHERE USER='u14';

CREATE USER u15@localhost,
            u16@localhost IDENTIFIED BY 'auth_string',
            u17@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string FROM mysql.user WHERE USER='u15';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string FROM mysql.user WHERE USER='u16';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string FROM mysql.user WHERE USER='u17';
CREATE USER u18@localhost,
            u19@localhost IDENTIFIED BY 'auth_string',
            u20@localhost IDENTIFIED WITH 'mysql_native_password',
            u21@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string',
            u22@localhost IDENTIFIED WITH 'mysql_native_password',
            u23@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_&string'
            REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
            WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2 ACCOUNT UNLOCK;
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,
x509_issuer,x509_subject,max_questions,max_user_connections FROM mysql.user WHERE USER='u18';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,
x509_issuer,x509_subject,max_questions,max_user_connections FROM mysql.user WHERE USER='u19';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,
x509_issuer,x509_subject,max_questions,max_user_connections FROM mysql.user WHERE USER='u20';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,
x509_issuer,x509_subject,max_questions,max_user_connections FROM mysql.user WHERE USER='u22';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,
x509_issuer,x509_subject,max_questions,max_user_connections FROM mysql.user WHERE USER='u23';

DROP USER tu1@localhost,tu2@localhost,tu3@localhost,tu4@localhost,tu5@localhost,
          tu6@localhost,user1@'%',user2@'%',user4@localhost,user6@localhost,
          user7@localhost,user8@localhost, u1@localhost,u2@localhost,u3@localhost,
          u4@localhost,u5@localhost,u6@localhost, u7@localhost, u8@localhost,
          u9@localhost, u10@localhost,u11@localhost, u12@localhost,u13@localhost,
          u14@localhost,u15@localhost,u16@localhost,u17@localhost,u18@localhost,
          u19@localhost, u20@localhost,u21@localhost,u22@localhost,u23@localhost;

--echo # ALTER USER

CREATE USER u1@localhost;
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime
FROM mysql.user WHERE USER='u1';

--echo # Its a no op
ALTER USER u1@localhost;
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime
FROM mysql.user WHERE USER='u1';

CREATE USER u2@localhost IDENTIFIED BY 'password_string';
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u2';
ALTER USER u2@localhost IDENTIFIED BY 'new_auth_string';
# Look for auth_string and password last changed field
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u2';
# Testing connection
--connect(con1, localhost, u2, new_auth_string)
SELECT USER();
connection default;
disconnect con1;
--echo
CREATE USER u3@localhost IDENTIFIED WITH 'mysql_native_password' ACCOUNT LOCK;
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u3';

ALTER USER u3@localhost IDENTIFIED WITH 'mysql_native_password'
                        AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
                        PASSWORD EXPIRE NEVER ACCOUNT UNLOCK;
# Look for plugin,auth_string and password expired field
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u3';

# Testing connection
--connect(con1, localhost, u3, 'auth_string')
SELECT USER();
connection default;
disconnect con1;

ALTER USER u3@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u3';

ALTER USER u3@localhost
           REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
           WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2
           PASSWORD EXPIRE NEVER;

--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u3';

CREATE USER u4@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
            ACCOUNT LOCK PASSWORD EXPIRE DEFAULT;
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u4';
ALTER USER u4@localhost IDENTIFIED WITH 'mysql_native_password'
           BY 'auth_string' ACCOUNT UNLOCK;
# Look for plugin,auth_string field
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u4';

CREATE USER u5@localhost REQUIRE SSL;
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u5';

ALTER USER u5@localhost IDENTIFIED WITH 'mysql_native_password';
# Look for plugin,auth_string, password expired field
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u5';

# Testing connection
--connect(con1, localhost, u5,,,,,SSL)
SET PASSWORD='new_auth_string';
SELECT USER();
connection default;
disconnect con1;

CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509 ACCOUNT UNLOCK ACCOUNT LOCK PASSWORD EXPIRE INTERVAL 80 DAY;
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u6';
ALTER USER u6@localhost IDENTIFIED WITH 'mysql_native_password' BY 'new_auth_string' REQUIRE SSL ACCOUNT UNLOCK;
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u6';

CREATE USER u7@localhost IDENTIFIED WITH 'mysql_native_password'
            BY 'auth_string' REQUIRE CIPHER 'DHE-RSA-AES256-SHA';
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u7';
ALTER USER u7@localhost IDENTIFIED WITH 'mysql_native_password' BY 'new_auth_string'
            REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA' ACCOUNT UNLOCK;
# Look for plugin,auth_string, password expired, SSL type field
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u7';

# Testing connection
--connect(con1, localhost, u7,'new_auth_string',,,,SSL)
SELECT USER();
connection default;
disconnect con1;

CREATE USER u8@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
            REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA';
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u8';
ALTER USER u8@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
            REQUIRE CIPHER "DHE-RSA-AES256-SHA";
# Look for plugin,auth_string, password expired, SSL fields
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u8';
# Testing connection
--connect(con1, localhost, u8,'auth_string',,,,SSL)
SELECT USER();
connection default;
disconnect con1;

CREATE USER tu1@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
            REQUIRE ISSUER 'issuer';
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='tu1';
ALTER USER tu1@localhost IDENTIFIED WITH 'mysql_native_password'
            REQUIRE CIPHER "DHE-RSA-AES256-SHA";
# Look for plugin,auth_string, password expired, SSL fields
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='tu1';

CREATE USER u9@localhost REQUIRE SUBJECT 'sub';
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u9';
ALTER USER u9@localhost REQUIRE ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA";
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u9';

ALTER USER u9@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string77hg'
                        REQUIRE ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA";
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u9';

CREATE USER u10@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
            REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND
            SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
            ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA";
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u10';
ALTER USER u10@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
            REQUIRE SSL;
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u10';

# Testing connection
--connect(con1, localhost, u10,'auth_string',,,,SSL)
SELECT USER();
connection default;
disconnect con1;

CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2;
--replace_column 7 # 9 #
query_vertical SELECT user,max_questions FROM mysql.user WHERE USER='u11';
ALTER USER u11@localhost WITH MAX_QUERIES_PER_HOUR 6;
--replace_column 7 # 9 #
query_vertical SELECT user,max_questions FROM mysql.user WHERE USER='u11';

CREATE USER u12@localhost IDENTIFIED BY 'auth_string'
                          WITH MAX_QUERIES_PER_HOUR 2;
--replace_column 7 # 9 #
query_vertical SELECT user,max_questions FROM mysql.user WHERE USER='u12';
ALTER USER u12@localhost IDENTIFIED WITH 'mysql_native_password'
                         WITH MAX_QUERIES_PER_HOUR 8;
--replace_column 7 # 9 #
query_vertical SELECT user,max_questions FROM mysql.user WHERE USER='u12';

ALTER USER u12@localhost IDENTIFIED WITH 'mysql_native_password'
                         WITH MAX_QUERIES_PER_HOUR 1000;
--replace_column 7 # 9 #
query_vertical SELECT user,max_questions FROM mysql.user WHERE USER='u12';

CREATE USER u13@localhost IDENTIFIED WITH 'mysql_native_password'
                          WITH MAX_CONNECTIONS_PER_HOUR 2;
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u13';
ALTER USER u13@localhost PASSWORD EXPIRE;
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u13';

CREATE USER u14@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
            WITH MAX_USER_CONNECTIONS 2;
--replace_column 7 # 10 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u14';
ALTER USER u14@localhost WITH MAX_USER_CONNECTIONS 12
                         PASSWORD EXPIRE INTERVAL 365 DAY;
--replace_column 7 # 10 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u14';

CREATE USER tu2@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
            WITH MAX_USER_CONNECTIONS 2 ;
--replace_column 7 # 11 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,max_questions,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='tu2';
ALTER USER tu2@localhost WITH MAX_USER_CONNECTIONS 12 MAX_QUERIES_PER_HOUR 543
                         PASSWORD EXPIRE INTERVAL 365 DAY;
--replace_column 7 # 11 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,max_questions,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='tu2';

# Testing connection
--connect(con1, localhost, tu2,'auth_string')
SELECT USER();
connection default;
disconnect con1;

CREATE USER u15@localhost,
            u16@localhost IDENTIFIED WITH 'mysql_native_password',
            u17@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u15';
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u16';
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u17';
ALTER USER u15@localhost IDENTIFIED WITH 'mysql_native_password',
           u16@localhost,
           u17@localhost IDENTIFIED BY 'new_auth_string'
           PASSWORD EXPIRE DEFAULT;
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u15';
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u16';
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u17';

CREATE USER u18@localhost,
            u19@localhost IDENTIFIED BY 'auth_string',
            u20@localhost IDENTIFIED WITH 'mysql_native_password',
            u21@localhost IDENTIFIED WITH 'mysql_native_password' BY '!Y_TOdh6)',
            u22@localhost IDENTIFIED WITH 'mysql_native_password',
            u23@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_&string'
            REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
            WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
--replace_column 7 # 11 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,
max_questions,password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u18';
--replace_column 7 # 11 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,
max_questions,password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u19';
--replace_column 7 # 11 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,
max_questions,password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u20';
--replace_column 7 # 11 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,
max_questions,password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u21';
--replace_column 7 # 11 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,
max_questions,password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u22';
--replace_column 7 # 11 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,
max_questions,password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u23';

ALTER USER u18@localhost, u19@localhost,
           u20@localhost, u21@localhost,
           u22@localhost, u23@localhost
           REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
           WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2
           PASSWORD EXPIRE NEVER;
--replace_column 7 # 11 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,
max_questions,password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u18';
--replace_column 7 # 11 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,
max_questions,password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u19';
--replace_column 7 # 11 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,
max_questions,password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u20';
--replace_column 7 # 11 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,
max_questions,password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u21';
--replace_column 7 # 11 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,
max_questions,password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u22';
--replace_column 7 # 11 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,max_user_connections,
max_questions,password_last_changed,password_lifetime FROM mysql.user
WHERE USER='u23';

DROP USER tu1@localhost,tu2@localhost,u1@localhost, u2@localhost, u3@localhost,
          u4@localhost,u5@localhost,u6@localhost, u7@localhost, u8@localhost,
          u9@localhost,u10@localhost,u11@localhost,u12@localhost,u13@localhost,
          u14@localhost,u15@localhost,u16@localhost,u17@localhost,u18@localhost,
          u19@localhost,u20@localhost,u21@localhost,u22@localhost,u23@localhost;

--echo # ALTER USER with current user

CREATE USER u1@localhost IDENTIFIED BY 'abc';
--connect(con1, localhost, u1, abc)
SELECT USER();
connection default;
ALTER USER u1@localhost PASSWORD EXPIRE;
disconnect con1;

--connect(con1, localhost, u1, abc)
--error ER_MUST_CHANGE_PASSWORD
SELECT USER();
--enable_warnings
SET PASSWORD = 'def';
--disable_warnings
disconnect con1;
--enable_warnings

--connect(con1, localhost, u1, def)
SELECT USER();
connection default;
ALTER USER u1@localhost PASSWORD EXPIRE;
disconnect con1;

--connect(con1, localhost, u1, def)
--error ER_MUST_CHANGE_PASSWORD
SELECT USER();
# Password set to current user
--error ER_PARSE_ERROR
ALTER USER IDENTIFIED BY 'npwd';
ALTER USER USER() IDENTIFIED BY 'abc';
disconnect con1;

--connect(con1, localhost, u1, abc)
SELECT USER();
connection default;
ALTER USER u1@localhost PASSWORD EXPIRE;
disconnect con1;

--connect(con1, localhost, u1, abc)
--error ER_MUST_CHANGE_PASSWORD
SELECT USER();
connection default;
ALTER USER u1@localhost IDENTIFIED BY 'def';
disconnect con1;

--connect(con1, localhost, u1, def)
SELECT USER();
connection default;
DROP USER u1@localhost;
disconnect con1;

--echo # ALTER USER with current user is allowed to set only credential information

CREATE USER u1@localhost, u2@localhost IDENTIFIED BY 'abc';
GRANT ALL ON *.* TO u2@localhost;

--connect(con1, localhost, u2, abc)
--error ER_PARSE_ERROR
ALTER USER USER() IDENTIFIED WITH 'sha256_password';
--error ER_PARSE_ERROR
ALTER USER USER() IDENTIFIED BY 'def', u2@localhost PASSWORD EXPIRE;
--error ER_PARSE_ERROR
ALTER USER USER() IDENTIFIED BY 'def' PASSWORD EXPIRE;
--error ER_PARSE_ERROR
ALTER USER ;
ALTER USER u2@localhost IDENTIFIED BY 'auth_string' PASSWORD EXPIRE INTERVAL 45 DAY;
--replace_column 7 # 9 #
query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
plugin,authentication_string,password_expired,
password_last_changed,password_lifetime FROM mysql.user WHERE USER='u2';
connection default;
disconnect con1;
DROP USER u1@localhost, u2@localhost;

--echo # SHOW CREATE USER

CREATE USER u1@localhost;
SHOW CREATE USER u1@localhost;

CREATE USER u2@localhost IDENTIFIED BY 'auth_string';
SHOW CREATE USER u2@localhost;
--echo # DDL output for SHOW CREATE USER u2@localhost and recreating user
DROP USER u2@localhost;
CREATE USER 'u2'@'localhost' IDENTIFIED WITH 'mysql_native_password'
            AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' REQUIRE NONE;

--connect(con1,localhost,u2,auth_string)
SELECT USER();
connection default;
disconnect con1;
--echo

CREATE USER u3@localhost IDENTIFIED WITH 'mysql_native_password';
SHOW CREATE USER u3@localhost;

CREATE USER u4@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
--replace_regex /AS .* REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
SHOW CREATE USER u4@localhost;

CREATE USER user1@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
--replace_regex /AS .* REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
SHOW CREATE USER user1@localhost;

--echo

CREATE USER user2@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
--replace_regex /AS .* REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
SHOW CREATE USER user2@localhost;
--echo
CREATE USER u5@localhost REQUIRE SSL;
SHOW CREATE USER u5@localhost;

CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509;
SHOW CREATE USER u6@localhost;

CREATE USER u7@localhost IDENTIFIED WITH 'mysql_native_password'
            REQUIRE CIPHER 'cipher';
SHOW CREATE USER u7@localhost;

CREATE USER u8@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
            REQUIRE ISSUER 'issuer';
--replace_regex /AS .* REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
SHOW CREATE USER u8@localhost;

CREATE USER u9@localhost REQUIRE SUBJECT 'sub';
SHOW CREATE USER u9@localhost;

CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2;
SHOW CREATE USER u11@localhost;

CREATE USER u12@localhost IDENTIFIED BY 'auth_string'  WITH MAX_QUERIES_PER_HOUR 2;
SHOW CREATE USER u12@localhost;

CREATE USER u13@localhost IDENTIFIED WITH 'mysql_native_password'
            WITH MAX_CONNECTIONS_PER_HOUR 2;
SHOW CREATE USER u13@localhost;

CREATE USER u14@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
            WITH MAX_USER_CONNECTIONS 2;
--replace_regex /AS .* WITH/AS '<non-deterministic-password-hash>' WITH/
SHOW CREATE USER u14@localhost;

CREATE USER u15@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
            REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
            ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
            CIPHER 'cipher' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
--replace_regex /AS .* REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
SHOW CREATE USER u15@localhost;

DROP USER user1@localhost,u1@localhost,u2@localhost,u3@localhost,u4@localhost,
          u5@localhost,u6@localhost, u7@localhost, u8@localhost,u9@localhost,
          u10@localhost,u11@localhost,u12@localhost,u13@localhost,u14@localhost,
          u15@localhost;
--echo
--echo # GRANT
--echo
CREATE USER user1@localhost IDENTIFIED WITH 'mysql_native_password'
            AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
            REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
            ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
            WITH MAX_CONNECTIONS_PER_HOUR 1000 MAX_USER_CONNECTIONS 20
                 MAX_QUERIES_PER_HOUR 60 MAX_UPDATES_PER_HOUR 100;

--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type,ssl_cipher,
x509_issuer,x509_subject,max_user_connections,
max_questions,max_updates,max_connections FROM mysql.user
WHERE USER='user1';

--echo
GRANT SELECT ON *.* TO user1@localhost;
GRANT SELECT,INSERT,UPDATE ON mysql.* TO user1@localhost;

--echo
SHOW GRANTS FOR user1@localhost;
--echo
REVOKE INSERT,UPDATE ON mysql.* FROM user1@localhost;

SHOW GRANTS FOR user1@localhost;
--echo

GRANT SELECT,INSERT,UPDATE ON *.* TO user2@localhost IDENTIFIED BY 'admin'
      REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
      ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
      WITH MAX_CONNECTIONS_PER_HOUR 1000 MAX_USER_CONNECTIONS 20
           MAX_QUERIES_PER_HOUR 60 MAX_UPDATES_PER_HOUR 100;

CREATE USER user3@localhost IDENTIFIED BY 'auth_string';

GRANT SELECT ON mysql.* TO user3@localhost
      IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
      REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
      ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
      WITH MAX_CONNECTIONS_PER_HOUR 1000 MAX_USER_CONNECTIONS 20
           MAX_QUERIES_PER_HOUR 60 MAX_UPDATES_PER_HOUR 100;

--echo # Cleanup
DROP USER user1@localhost,user2@localhost,user3@localhost;

--echo
--echo # SET PASSWORD Tests
--echo
CREATE USER user1@localhost IDENTIFIED BY 'auth_string';
CREATE USER user2@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string1';

SELECT PASSWORD('plaintext_pwd');

SET PASSWORD FOR user1@localhost=PASSWORD('auth_xyz@');
SET PASSWORD FOR user2@localhost=PASSWORD('gd636@gj');

--echo
CREATE USER user4@localhost;
ALTER USER user4@localhost PASSWORD EXPIRE;
SET PASSWORD FOR user4@localhost='';

CREATE USER user3@localhost IDENTIFIED  BY 'auth_string';
CREATE USER user5@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string1';

SET PASSWORD FOR user3@localhost='auth_abc';
GRANT SELECT ON *.* TO user3@localhost;

--connect(con1, localhost, user3, auth_abc)
SELECT USER();
SET PASSWORD='auth_xyz';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type,password_expired,
password_lifetime FROM mysql.user WHERE user='user3';
connection default;
disconnect con1;

--echo
SET PASSWORD FOR user4@localhost='auth_xyz';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type,password_expired,
password_lifetime FROM mysql.user WHERE user='user4';
--echo
SET PASSWORD FOR user5@localhost='auth_dhsga5';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,ssl_type,password_expired,
password_lifetime FROM mysql.user WHERE user='user5';

--echo
CREATE USER user6@localhost IDENTIFIED BY 'auth_string';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,password_expired
FROM mysql.user WHERE USER='user6';

ALTER USER user6@localhost IDENTIFIED WITH 'mysql_native_password';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string,password_expired
FROM mysql.user WHERE USER='user6';

SET PASSWORD FOR user6@localhost='plaintext_password';
--replace_column 3 #
query_vertical SELECT user,plugin,authentication_string
FROM mysql.user WHERE USER='user6';

# Testing connection
--connect(con1, localhost, user6, 'plaintext_password')
SELECT USER();
connection default;
disconnect con1;

--echo
--echo # Cleanup
DROP USER user1@localhost,user2@localhost,user3@localhost,
          user4@localhost,user5@localhost,user6@localhost;
--echo
--echo # Testing log_builtin_as_identified_by_password variable scope GLOBAL Dynamic

# log_builtin_as_identified_by_password variable change dynamically
SHOW GLOBAL VARIABLES LIKE 'log_builtin_as_identified_by_password';
--echo
SET GLOBAL log_builtin_as_identified_by_password = ON;
SHOW GLOBAL VARIABLES LIKE 'log_builtin_as_identified_by_password';
--echo
SET GLOBAL log_builtin_as_identified_by_password = OFF;
SHOW GLOBAL VARIABLES LIKE 'log_builtin_as_identified_by_password';
--echo
--error ER_GLOBAL_VARIABLE
SET SESSION log_builtin_as_identified_by_password = ON;
SHOW  VARIABLES LIKE 'log_builtin_as_identified_by_password';
--echo
--error ER_GLOBAL_VARIABLE
SET @@log_builtin_as_identified_by_password = OFF;
SHOW VARIABLES LIKE 'log_builtin_as_identified_by_password';
--echo
SET @@global.log_builtin_as_identified_by_password = ON;
SHOW GLOBAL VARIABLES LIKE 'log_builtin_as_identified_by_password';
--echo
--error ER_GLOBAL_VARIABLE
SET @@session.log_builtin_as_identified_by_password = ON;
--echo # GLOBAL variable value
SHOW GLOBAL VARIABLES LIKE 'log_builtin_as_identified_by_password';
SET GLOBAL log_builtin_as_identified_by_password=OFF;
SHOW VARIABLES LIKE 'log_builtin_as_identified_by_password';

--source include/sync_slave_sql_with_master.inc
--echo [On Slave]
--source include/show_binlog_events.inc
--source include/rpl_end.inc

--echo
--echo End of 5.7 tests!
--echo

${this.title}

Code Editor : binlog_grant_alter_user.test