You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
384 lines
12 KiB
384 lines
12 KiB
drop table if exists t1,t2;
|
|
show tables where Tables_in_mysql != 'ndb_binlog_index';
|
|
Tables_in_mysql
|
|
columns_priv
|
|
component
|
|
db
|
|
default_roles
|
|
engine_cost
|
|
func
|
|
general_log
|
|
global_grants
|
|
gtid_executed
|
|
help_category
|
|
help_keyword
|
|
help_relation
|
|
help_topic
|
|
innodb_index_stats
|
|
innodb_table_stats
|
|
password_history
|
|
plugin
|
|
procs_priv
|
|
proxies_priv
|
|
role_edges
|
|
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 test@127.0.0.1 identified by "gambling";
|
|
grant ALL on *.* to test@127.0.0.1;
|
|
show tables where Tables_in_mysql != 'ndb_binlog_index';
|
|
Tables_in_mysql
|
|
columns_priv
|
|
component
|
|
db
|
|
default_roles
|
|
engine_cost
|
|
func
|
|
general_log
|
|
global_grants
|
|
gtid_executed
|
|
help_category
|
|
help_keyword
|
|
help_relation
|
|
help_topic
|
|
innodb_index_stats
|
|
innodb_table_stats
|
|
password_history
|
|
plugin
|
|
procs_priv
|
|
proxies_priv
|
|
role_edges
|
|
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)
|
|
drop user test@localhost;
|
|
drop user test@127.0.0.1;
|
|
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;
|
|
SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE user = 'event_scheduler' AND command = 'Daemon';
|
|
COUNT(*) = 1
|
|
1
|
|
|
|
# -- 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;
|
|
|
|
# -- 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
|
|
# -- 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
|
|
SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE user = 'event_scheduler' AND command = 'Daemon';
|
|
COUNT(*) = 1
|
|
1
|
|
|
|
# -- Closing new connection...
|
|
|
|
# -- 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
|
|
N
|
|
SELECT USER();
|
|
USER()
|
|
must_change@localhost
|
|
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 '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)
|
|
Pattern "Access denied for user 'newuser'@'localhost' \(using password: NO\)" found
|
|
|
|
# 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)
|
|
Pattern "Access denied for user 'root'@'localhost' \(using password: YES\)" found
|
|
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'
|
|
Pattern "Access denied for user 'new1'@'localhost' to database 'test1'" found
|
|
|
|
# Case 4: SSL connection attempt without necessary certificates
|
|
Pattern "Got an error reading communication packets" found
|
|
SHOW STATUS LIKE 'Aborted_connects';
|
|
Variable_name Value
|
|
Aborted_connects 4
|
|
DROP USER 'new1'@'localhost';
|
|
DROP DATABASE test1;
|
|
|