drop table if exists t1,t2;
show tables;
Tables_in_mysql
columns_priv
db
engine_cost
event
func
general_log
gtid_executed
help_category
help_keyword
help_relation
help_topic
innodb_index_stats
innodb_table_stats
ndb_binlog_index
plugin
proc
procs_priv
proxies_priv
server_cost
servers
slave_master_info
slave_relay_log_info
slave_worker_info
slow_log
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
show tables;
Tables_in_test
connect(localhost,root,z,test2,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'root'@'localhost' (using password: YES)
connect(localhost,root,z,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'root'@'localhost' (using password: YES)
create user test@localhost identified by "gambling";
grant ALL on *.* to test@localhost;
create user [email protected] identified by "gambling";
grant ALL on *.* to [email protected];
show tables;
Tables_in_mysql
columns_priv
db
engine_cost
event
func
general_log
gtid_executed
help_category
help_keyword
help_relation
help_topic
innodb_index_stats
innodb_table_stats
ndb_binlog_index
plugin
proc
procs_priv
proxies_priv
server_cost
servers
slave_master_info
slave_relay_log_info
slave_worker_info
slow_log
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
show tables;
Tables_in_test
connect(localhost,test,,test2,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO)
connect(localhost,test,,"",MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO)
connect(localhost,test,zorro,test2,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES)
connect(localhost,test,zorro,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES)
connect(localhost,test,,test2,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO)
connect(localhost,test,,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO)
delete from mysql.user where user=_binary"test";
flush privileges;
create table t1 (id integer not null auto_increment primary key);
create temporary table t2(id integer not null auto_increment primary key);
set @id := 1;
delete from t1 where id like @id;
drop table t1;
# ------------------------------------------------------------------
# -- End of 4.1 tests
# ------------------------------------------------------------------

# -- Bug#33507: Event scheduler creates more threads than max_connections
# -- which results in user lockout.

CREATE USER mysqltest_u1@localhost;

SET GLOBAL max_connections = 3;
SET GLOBAL event_scheduler = ON;

# -- Waiting for Event Scheduler to start...

# -- Disconnecting default connection...

# -- Check that we allow exactly three user connections, no matter how
# -- many threads are running.

# -- Connecting (1)...
# -- Establishing connection 'con_1' (user: mysqltest_u1)...
# -- Connection 'con_1' has been established.

# -- Connecting (2)...
# -- Establishing connection 'con_2' (user: mysqltest_u1)...
# -- Connection 'con_2' has been established.

# -- Connecting (3)...
# -- Establishing connection 'con_3' (user: mysqltest_u1)...
# -- Connection 'con_3' has been established.

# -- Connecting (4) [should fail]...
# -- Establishing connection 'con_4' (user: mysqltest_u1)...
# -- Error: can not establish connection 'con_4'.

# -- Check that we allow one extra SUPER-user connection.

# -- Connecting super (1)...
# -- Establishing connection 'con_super_1' (user: root)...
# -- Connection 'con_super_1' has been established.

# -- Connecting super (2) [should fail]...
# -- Establishing connection 'con_super_2' (user: root)...
# -- Error: can not establish connection 'con_super_2'.

# -- Ensure that we have Event Scheduler thread, 3 ordinary user
# -- connections and one extra super-user connection.
SELECT user FROM information_schema.processlist ORDER BY id;
user
event_scheduler
mysqltest_u1
mysqltest_u1
mysqltest_u1
root

# -- Resetting variables...
SET GLOBAL max_connections = 151;

# -- Stopping Event Scheduler...
SET GLOBAL event_scheduler = OFF;

# -- Waiting for Event Scheduler to stop...

# -- That's it. Closing connections...

# -- Restoring default connection...

# -- Waiting for connections to close...

DROP USER mysqltest_u1@localhost;

# -- End of Bug#33507.

# -- Bug#35074: max_used_connections is not correct.

FLUSH STATUS;

SHOW STATUS LIKE 'max_used_connections';
Variable_name	Value
Max_used_connections	1

# -- Starting Event Scheduler...
SET GLOBAL event_scheduler = ON;
# -- Waiting for Event Scheduler to start...

# -- Opening a new connection to check max_used_connections...

# -- Check that max_used_connections hasn't changed.
SHOW STATUS LIKE 'max_used_connections';
Variable_name	Value
Max_used_connections	2

# -- Closing new connection...

# -- Stopping Event Scheduler...
SET GLOBAL event_scheduler = OFF;
# -- Waiting for Event Scheduler to stop...

# -- End of Bug#35074.

# ------------------------------------------------------------------
# -- End of 5.1 tests
# ------------------------------------------------------------------
#
# WL#2392: Change Password at next login
#
CREATE USER must_change@localhost IDENTIFIED BY 'aha';
SELECT password_expired FROM mysql.user 
WHERE user='must_change' and host = 'localhost';
password_expired
N
SELECT USER();
USER()
must_change@localhost
CREATE TABLE t1 (A INT);
CREATE PROCEDURE TEST_t1(new_a INT) INSERT INTO t1 VALUES (new_a);
CREATE FUNCTION last_t1() RETURNS INT RETURN (SELECT MAX(A) FROM t1);
# Initialize the table
CALL test_t1(1);
UPDATE mysql.user SET password_expired='Y'
  WHERE user='must_change' and host = 'localhost';
# without FLUSH the field has no effect
# must not throw an error
SELECT USER();
USER()
must_change@localhost
# must not throw an error
SELECT last_t1();
last_t1()
1
# must not throw an error
CALL test_t1(last_t1() + 1);
FLUSH PRIVILEGES;
# existing connections continue as before even after flush
# must not throw an error
SELECT USER();
USER()
must_change@localhost
# must not throw an error
SELECT last_t1();
last_t1()
2
# must not throw an error
CALL test_t1(last_t1() + 1);
# new connections are blocked until a password reset
SELECT USER();
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
SELECT last_t1();
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
CALL test_t1(last_t1() + 1);
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
# setting a password unlocks it
ALTER USER must_change@localhost IDENTIFIED BY 'aha2';
# must not throw an error
SELECT USER();
USER()
must_change@localhost
# must not throw an error
SELECT last_t1();
last_t1()
3
# must not throw an error
CALL test_t1(last_t1() + 1);
# check if SET PASSWORD resets the column
SELECT password_expired FROM mysql.user
WHERE user='must_change' and host = 'localhost';
password_expired
N
UPDATE mysql.user SET password_expired='Y'
  WHERE user='must_change' and host = 'localhost';
FLUSH PRIVILEGES;
SELECT USER();
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
SELECT last_t1();
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
CALL test_t1(last_t1() + 1);
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
# setting a password with a user name is ok if it exactly matches the
# authenticated user.
ALTER USER must_change@localhost IDENTIFIED BY 'aha3';
SELECT USER();
USER()
must_change@localhost
SELECT last_t1();
last_t1()
4
CALL test_t1(last_t1() + 1);
# setting a password for the current user works
ALTER USER must_change@localhost IDENTIFIED BY 'aha3';
SELECT USER();
USER()
must_change@localhost
SELECT last_t1();
last_t1()
5
CALL test_t1(last_t1() + 1);
# testing the ALTER USER command
# try a single user
ALTER USER must_change@localhost PASSWORD EXPIRE;
SELECT password_expired FROM mysql.user
WHERE user='must_change' and host = 'localhost';
password_expired
Y
SELECT USER();
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
ALTER USER must_change@localhost IDENTIFIED BY 'aha4';
# try a valid+invalid user combo
ALTER USER
invalid_user@localhost,
must_change@localhost PASSWORD EXPIRE;
ERROR HY000: Operation ALTER USER failed for 'invalid_user'@'localhost'
SELECT password_expired FROM mysql.user
WHERE user='must_change' and host = 'localhost';
password_expired
Y
SELECT USER();
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
ALTER USER must_change@localhost IDENTIFIED BY 'aha5';
SELECT USER();
USER()
must_change@localhost
# Password change must be persistent when reconnecting
SELECT USER();
USER()
must_change@localhost
# cleanup
DROP PROCEDURE test_t1;
DROP FUNCTION last_t1;
DROP TABLE t1;
DROP USER must_change@localhost;
#
# WL#6587:  Protocol support for password expiration
#
CREATE USER wl6587@localhost IDENTIFIED BY 'wl6587';
ALTER USER wl6587@localhost PASSWORD EXPIRE;
# non-interactive mysql should fail
mysql: [Warning] Using a password on the command line interface can be insecure.
Please use --connect-expired-password option or invoke mysql in interactive mode.
# mysqladmin non-password should fail
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Your password has expired. To log in you must change it using a client that supports expired passwords.'
# mysqladmin password should work
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
DROP USER wl6587@localhost;
# ------------------------------------------------------------------
# -- End of 5.6 tests
# ------------------------------------------------------------------
#
# BUG#27539838: NOT ALL ABORTED CONNECTS ARE REPORTED TO ERROR.LOG
#               PROPERLY
#
# restart: --log-error=LOG_ERR --log-error-verbosity=3
SHOW STATUS LIKE 'Aborted_connects';
Variable_name	Value
Aborted_connects	0

# Case 1: Connection attempt by an invalid user
connect(localhost,newuser,,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'newuser'@'localhost' (using password: NO)

# Case 2: Connection attempt by a valid user with incorrect password
connect(localhost,root,1234,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'root'@'localhost' (using password: YES)
CREATE DATABASE test1;
CREATE USER 'new1'@'localhost';

# Case 3: Connection attempt by a valid user with no privileges to access a database
connect(localhost,new1,,test1,MASTER_PORT,MASTER_SOCKET);
ERROR 42000: Access denied for user 'new1'@'localhost' to database 'test1'

# Case 4: SSL connection attempt without necessary certificates
SHOW STATUS LIKE 'Aborted_connects';
Variable_name	Value
Aborted_connects	4
DROP USER 'new1'@'localhost';
DROP DATABASE test1;