CALL mtr.add_suppression("Plugin test_plugin_server reported: 'Wrong password supplied for plug_dest'");
SELECT PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_DESCRIPTION
FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='test_plugin_server';
PLUGIN_STATUS	ACTIVE
PLUGIN_TYPE	AUTHENTICATION
PLUGIN_DESCRIPTION	plugin API test plugin
SET @old_log_output=          @@global.log_output;
SET @old_general_log=         @@global.general_log;
SET GLOBAL log_output =       'TABLE';
SET GLOBAL general_log=       'ON';
CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest';
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
SELECT plugin,authentication_string FROM mysql.user WHERE User='plug';
plugin	authentication_string
test_plugin_server	plug_dest
## test plugin auth
ERROR 28000: Access denied for user 'plug'@'localhost' (using password: YES)
GRANT PROXY ON plug_dest TO plug;
test proxies_priv columns
SELECT * FROM mysql.proxies_priv WHERE user !='root';
Host	User	Proxied_host	Proxied_user	With_grant	Grantor	Timestamp
xx	plug	%	plug_dest	0	root@localhost	xx
test mysql.proxies_priv;
SHOW CREATE TABLE mysql.proxies_priv;
Table	Create Table
proxies_priv	CREATE TABLE `proxies_priv` (
  `Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  `Proxied_host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
  `Proxied_user` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  `With_grant` tinyint(1) NOT NULL DEFAULT '0',
  `Grantor` varchar(288) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`),
  KEY `Grantor` (`Grantor`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='User proxy privileges'
select USER(),CURRENT_USER();
USER()	CURRENT_USER()
plug@localhost	plug_dest@%
## test SET PASSWORD
SET PASSWORD = 'plug_dest';
ERROR HY000: SET PASSWORD has no significance for user 'plug'@'%' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.
## test bad credentials
ERROR 28000: Access denied for user 'plug'@'localhost' (using password: YES)
## test bad default plugin : As long as the server returns a compatible
## plugin any default plugin set by the client is ignored. Only if the
## client doesn't comprehend the servers plugin will the default be used.
## test correct default plugin
select USER(),CURRENT_USER();
USER()	CURRENT_USER()
plug@localhost	plug_dest@%
## test utf-8 user name
CREATE USER `Ÿ` IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest';
GRANT PROXY ON plug_dest TO `Ÿ`;
select USER(),CURRENT_USER();
USER()	CURRENT_USER()
Ÿ@localhost	plug_dest@%
DROP USER `Ÿ`;
CREATE DATABASE test_grant_db;
REVOKE SELECT on test_grant_db.* FROM joro
INDENTIFIED WITH 'test_plugin_server' AS 'plug_dest';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'' at line 2
REVOKE SELECT on test_grant_db.* FROM joro
INDENTIFIED BY 'plug_dest_passwd';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDENTIFIED BY 'plug_dest_passwd'' at line 2
REVOKE SELECT on test_grant_db.* FROM joro
INDENTIFIED BY PASSWORD 'plug_dest_passwd';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDENTIFIED BY PASSWORD 'plug_dest_passwd'' at line 2
DROP DATABASE test_grant_db;
## GRANT PROXY tests
SELECT @@global.check_proxy_users;
@@global.check_proxy_users
0
SELECT @@global.mysql_native_password_proxy_users;
@@global.mysql_native_password_proxy_users
0
SELECT @@global.sha256_password_proxy_users;
@@global.sha256_password_proxy_users
0
SET @@global.check_proxy_users=ON;
SET @@global.mysql_native_password_proxy_users=ON;
SET @@global.sha256_password_proxy_users=ON;
CREATE USER grant_plug IDENTIFIED WITH 'test_plugin_server'
  AS 'grant_plug_dest';
CREATE USER grant_plug_dest IDENTIFIED BY 'grant_plug_dest_passwd';
CREATE USER grant_plug_dest2 IDENTIFIED BY 'grant_plug_dest_passwd2';
# ALL PRIVILEGES doesn't include PROXY
GRANT ALL PRIVILEGES ON *.* TO grant_plug;
REVOKE SYSTEM_USER ON *.* FROM grant_plug;
ERROR 28000: Access denied for user 'grant_plug'@'localhost' (using password: YES)
GRANT ALL PRIVILEGES,PROXY ON grant_plug_dest TO grant_plug;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',PROXY ON grant_plug_dest TO grant_plug' at line 1
this should fail : can't combine PROXY
GRANT ALL SELECT,PROXY ON grant_plug_dest TO grant_plug;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT,PROXY ON grant_plug_dest TO grant_plug' at line 1
# this should fail : no such grant
REVOKE PROXY ON grant_plug_dest FROM grant_plug;
ERROR 42000: There is no such grant defined for user 'grant_plug' on host '%'
in grant_plug_dest_con
## testing what an ordinary user can grant
this should fail : no rights to grant all
GRANT PROXY ON ''@'' TO grant_plug;
ERROR 28000: Access denied for user 'grant_plug_dest'@'localhost'
this should fail : not the same user
GRANT PROXY ON grant_plug TO grant_plug_dest;
ERROR 28000: Access denied for user 'grant_plug_dest'@'localhost'
This is a valid grant
GRANT PROXY ON grant_plug_dest TO grant_plug;
REVOKE PROXY ON grant_plug_dest FROM grant_plug;
this should work : same user
GRANT PROXY ON grant_plug_dest TO grant_plug_dest2;
REVOKE PROXY ON grant_plug_dest FROM grant_plug_dest2;
this should fail : not the same user
GRANT PROXY ON grant_plug_dest@localhost TO grant_plug WITH GRANT OPTION;
ERROR 28000: Access denied for user 'grant_plug_dest'@'localhost'
this should fail : not the same user
REVOKE PROXY ON grant_plug_dest@localhost FROM grant_plug;
ERROR 28000: Access denied for user 'grant_plug_dest'@'localhost'
this should fail : can't create users
GRANT PROXY ON grant_plug_dest TO grant_plug@localhost;
ERROR 42000: You are not allowed to create a user with GRANT
in default connection
# test what root can grant
should work : root has PROXY to all users
GRANT PROXY ON ''@'' TO grant_plug;
REVOKE PROXY ON ''@'' FROM grant_plug;
should work : root has PROXY to all users
CREATE USER proxy_admin IDENTIFIED BY 'test';
GRANT PROXY ON ''@'' TO proxy_admin WITH GRANT OPTION;
need USAGE : PROXY doesn't contain it.
GRANT USAGE on *.* TO proxy_admin;
in proxy_admin_con;
should work : proxy_admin has proxy to ''@''
GRANT PROXY ON future_user TO grant_plug;
in default connection
SHOW GRANTS FOR grant_plug;
Grants for grant_plug@%
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `grant_plug`@`%`
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `grant_plug`@`%`
GRANT PROXY ON `future_user`@`%` TO `grant_plug`@`%`
REVOKE PROXY ON future_user FROM grant_plug;
SHOW GRANTS FOR grant_plug;
Grants for grant_plug@%
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `grant_plug`@`%`
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `grant_plug`@`%`
## testing drop user
CREATE USER test_drop@localhost;
GRANT PROXY ON future_user TO test_drop@localhost;
SHOW GRANTS FOR test_drop@localhost;
Grants for test_drop@localhost
GRANT USAGE ON *.* TO `test_drop`@`localhost`
GRANT PROXY ON `future_user`@`%` TO `test_drop`@`localhost`
DROP USER test_drop@localhost;
SELECT * FROM mysql.proxies_priv WHERE Host = 'test_drop' AND User = 'localhost';
Host	User	Proxied_host	Proxied_user	With_grant	Grantor	Timestamp
DROP USER proxy_admin;
DROP USER grant_plug,grant_plug_dest,grant_plug_dest2;
## END GRANT PROXY tests
## cleanup
DROP USER plug;
DROP USER plug_dest;
## @@proxy_user tests
CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest';
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
GRANT PROXY ON plug_dest TO plug;
SELECT USER(),CURRENT_USER(),@@LOCAL.proxy_user;
USER()	CURRENT_USER()	@@LOCAL.proxy_user
root@localhost	root@localhost	NULL
SELECT @@GLOBAL.proxy_user;
ERROR HY000: Variable 'proxy_user' is a SESSION variable
SELECT @@LOCAL.proxy_user;
@@LOCAL.proxy_user
NULL
SET GLOBAL proxy_user = 'test';
ERROR HY000: Variable 'proxy_user' is a read only variable
SET LOCAL proxy_user = 'test';
ERROR HY000: Variable 'proxy_user' is a read only variable
SELECT @@LOCAL.proxy_user;
@@LOCAL.proxy_user
NULL
# in connection plug_con
SELECT @@LOCAL.proxy_user;
@@LOCAL.proxy_user
'plug'@'%'
# in connection default
SET @@global.check_proxy_users=0;
SET @@global.mysql_native_password_proxy_users=0;
SET @@global.sha256_password_proxy_users=0;
## cleanup
DROP USER plug;
DROP USER plug_dest;
## END @@proxy_user tests
## @@external_user tests
CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest';
CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd';
GRANT PROXY ON plug_dest TO plug;
SELECT USER(),CURRENT_USER(),@@LOCAL.external_user;
USER()	CURRENT_USER()	@@LOCAL.external_user
root@localhost	root@localhost	NULL
SELECT @@GLOBAL.external_user;
ERROR HY000: Variable 'external_user' is a SESSION variable
SELECT @@LOCAL.external_user;
@@LOCAL.external_user
NULL
SET GLOBAL external_user = 'test';
ERROR HY000: Variable 'external_user' is a read only variable
SET LOCAL external_user = 'test';
ERROR HY000: Variable 'external_user' is a read only variable
SELECT @@LOCAL.external_user;
@@LOCAL.external_user
NULL
# in connection plug_con
SELECT @@LOCAL.external_user;
@@LOCAL.external_user
plug_dest
# in connection default
WL#5706 -- show the above got logged/rewritten correctly
SELECT argument FROM mysql.general_log WHERE argument LIKE CONCAT('CREATE USER %') AND
command_type NOT LIKE 'Prepare';
argument
CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'
CREATE USER 'plug_dest'@'%' IDENTIFIED BY <secret>
CREATE USER `Ÿ` IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'
CREATE USER grant_plug IDENTIFIED WITH 'test_plugin_server'
  AS 'grant_plug_dest'
CREATE USER 'grant_plug_dest'@'%' IDENTIFIED BY <secret>
CREATE USER 'grant_plug_dest2'@'%' IDENTIFIED BY <secret>
CREATE USER 'proxy_admin'@'%' IDENTIFIED BY <secret>
CREATE USER test_drop@localhost
CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'
CREATE USER 'plug_dest'@'%' IDENTIFIED BY <secret>
CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'
CREATE USER 'plug_dest'@'%' IDENTIFIED BY <secret>
## cleanup
DROP USER plug;
DROP USER plug_dest;
SET GLOBAL log_output=  @old_log_output;
SET GLOBAL general_log= @old_general_log;
TRUNCATE mysql.general_log;
## END @@external_user tests
#
#  Bug #56798 : Wrong credentials assigned when using a proxy user.
#
CREATE USER power_user;
GRANT ALL PRIVILEGES ON *.* TO power_user;
CREATE USER ''@'' IDENTIFIED WITH 'test_plugin_server' AS 'power_user';
GRANT USAGE ON anonymous_db.* TO ''@'';
GRANT PROXY ON power_user TO ''@'';
CREATE DATABASE confidential_db;
SELECT user(),current_user(),@@proxy_user;
user()	current_user()	@@proxy_user
test_login_user@localhost	power_user@%	''@''
DROP USER power_user;
DROP USER ''@'';
DROP DATABASE confidential_db;
# Test case #2 (crash with double grant proxy)
CREATE USER ''@'' IDENTIFIED WITH 'test_plugin_server' AS 'standard_user';
CREATE USER standard_user;
CREATE DATABASE shared;
GRANT ALL PRIVILEGES ON shared.* TO standard_user;
GRANT PROXY ON standard_user TO ''@'';
#should not crash
GRANT PROXY ON standard_user TO ''@'';
DROP USER ''@'';
DROP USER standard_user;
DROP DATABASE shared;
#
# Bug#58139 : default-auth option not recognized in MySQL standard
#             command line clients
#
# Executing 'mysql'
1
1
# Executing 'mysqladmin'
mysqld is alive
# Executing 'mysqldump'
#
# Bug #59657: Move the client authentication_pam plugin into the
#  server repository
#
CREATE USER uplain@localhost IDENTIFIED WITH 'cleartext_plugin_server'
  AS 'cleartext_test';
## test plugin auth
ERROR 28000: Access denied for user 'uplain'@'localhost' (using password: YES)
select USER(),CURRENT_USER();
USER()	CURRENT_USER()
uplain@localhost	uplain@localhost
DROP USER uplain@localhost;
#
# Bug #59038 : mysql.user.authentication_string column
#   causes configuration wizard to fail
INSERT INTO mysql.user(
Host,
User,
Select_priv,
Insert_priv,
Update_priv,
Delete_priv,
Create_priv,
Drop_priv,
Reload_priv,
Shutdown_priv,
Process_priv,
File_priv,
Grant_priv,
References_priv,
Index_priv,
Alter_priv,
Show_db_priv,
Super_priv,
Create_tmp_table_priv,
Lock_tables_priv,
Execute_priv,
Repl_slave_priv,
Repl_client_priv,
/*!50001
Create_view_priv,
Show_view_priv,
Create_routine_priv,
Alter_routine_priv,
Create_user_priv,
*/
ssl_type,
ssl_cipher,
x509_issuer,
x509_subject,
max_questions,
max_updates,
max_connections)
VALUES (
'localhost',
'inserttest',
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
'Y', 'Y',  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
/*!50001 'Y', 'Y', 'Y', 'Y', 'Y', */'', '', '', '', '0', '0', '0');
FLUSH PRIVILEGES;
DROP USER inserttest@localhost;
SELECT IS_NULLABLE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
COLUMN_NAME IN ('authentication_string', 'plugin') AND
TABLE_NAME='user' AND
TABLE_SCHEMA='mysql'
ORDER BY COLUMN_NAME;
IS_NULLABLE	COLUMN_NAME
YES	authentication_string
NO	plugin
#
# Bug #11936829: diff. between mysql.user (authentication_string)
#   in fresh and upgraded 5.5.11
#
SELECT IS_NULLABLE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= 'mysql' AND TABLE_NAME= 'user' AND
COLUMN_NAME IN ('plugin', 'authentication_string')
ORDER BY COLUMN_NAME;
IS_NULLABLE	COLUMN_NAME
YES	authentication_string
NO	plugin
ALTER TABLE mysql.user MODIFY plugin char(64) DEFAULT '' NOT NULL;
ALTER TABLE mysql.user MODIFY authentication_string TEXT NOT NULL;
Run mysql_upgrade on a 5.5.10 external authentication column layout
# restart:--upgrade=FORCE
SELECT IS_NULLABLE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= 'mysql' AND TABLE_NAME= 'user' AND
COLUMN_NAME IN ('plugin', 'authentication_string')
ORDER BY COLUMN_NAME;
IS_NULLABLE	COLUMN_NAME
YES	authentication_string
NO	plugin
#
# Bug #12610784: SET PASSWORD INCORRECTLY KEEP AN OLD EMPTY PASSWORD
#
CREATE USER bug12610784@localhost;
SET PASSWORD FOR bug12610784@localhost = 'secret';
ERROR 28000: Access denied for user 'bug12610784'@'localhost' (using password: NO)
DROP USER bug12610784@localhost;
#
# Bug # 11766641: 59792: BIN/MYSQL -UUNKNOWN -PUNKNOWN
#  .-> USING PASSWORD: NO
# should contain "using password=yes"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'unknown'@'localhost' (using password: YES)
# should contain "using password=no"
ERROR 1045 (28000): Access denied for user 'unknown'@'localhost' (using password: NO)
#
# Bug #12818542: PAM: ADDING PASSWORD FOR AN ACCOUNT DISABLES PAM
#   AUTHENTICATION SETTINGS
#
CREATE USER bug12818542@localhost
IDENTIFIED WITH 'test_plugin_server' AS 'bug12818542_dest';
CREATE USER bug12818542_dest@localhost
IDENTIFIED BY 'bug12818542_dest_passwd';
GRANT PROXY ON bug12818542_dest@localhost TO bug12818542@localhost;
SELECT USER(),CURRENT_USER();
USER()	CURRENT_USER()
bug12818542@localhost	bug12818542_dest@localhost
SET PASSWORD = 'bruhaha';
ERROR HY000: SET PASSWORD has no significance for user 'bug12818542'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.
SELECT USER(),CURRENT_USER();
USER()	CURRENT_USER()
bug12818542@localhost	bug12818542_dest@localhost
DROP USER bug12818542@localhost;
DROP USER bug12818542_dest@localhost;
End of 5.5 tests
#
# Bug #18057562: PROXY USERS LOCKED OUT WHEN UNDERLYING PROXIED USER
#                PASSWORD IS EXPIRED
#
# Restart server with disconnect_on_expired_password OFF;
# restart:--disconnect_on_expired_password=0
CREATE USER 'empl_external'@'localhost' IDENTIFIED WITH test_plugin_server AS 'employee';
CREATE USER 'employee'@'localhost' IDENTIFIED BY 'passkey';
GRANT PROXY ON 'employee'@'localhost' TO 'empl_external'@'localhost';
# Expiring the proxied user's password
ALTER USER employee@localhost PASSWORD EXPIRE;
# Connecting with the proxied user and executing a query after the
# proxied user's password is expired
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1820 (HY000) at line 1: You must reset your password using ALTER USER statement before executing this statement.
# Connecting with the proxy user and executing a query after the proxied
# user's password is expired. Here we are not supposed to get error.
mysql: [Warning] Using a password on the command line interface can be insecure.
current_user()
employee@localhost
user()
empl_external@localhost
DROP USER 'empl_external'@'localhost', 'employee'@'localhost';
#
# Bug #20537246: SERVER CRASH WHILE CONNECTING WITH CLEARTEXT-PLUGIN
#                USER WITH BLANK PWD
#
CREATE USER bug20537246@localhost
IDENTIFIED WITH 'cleartext_plugin_server' AS '';
## test connection
select USER(),CURRENT_USER();
USER()	CURRENT_USER()
bug20537246@localhost	bug20537246@localhost
DROP USER bug20537246@localhost;
#
# Bug #20599280 PASSWORD EXPIRED FLAG SET FOR PROXY USER SESSION WHEN
#               IT SET FOR PROXIED USER
#
CREATE USER 'empl_external'@'localhost' IDENTIFIED WITH test_plugin_server AS 'employee';
CREATE USER 'employee'@'localhost' IDENTIFIED BY 'passkey';
GRANT ALL ON *.* TO 'employee'@'localhost';
GRANT PROXY ON 'employee'@'localhost' TO 'empl_external'@'localhost';
SELECT USER(), CURRENT_USER, @@PROXY_USER;
USER()	CURRENT_USER	@@PROXY_USER
empl_external@localhost	employee@localhost	'empl_external'@'localhost'
ALTER USER 'employee'@'localhost' PASSWORD EXPIRE;
SELECT USER(), CURRENT_USER, @@PROXY_USER;
USER()	CURRENT_USER	@@PROXY_USER
empl_external@localhost	employee@localhost	'empl_external'@'localhost'
SELECT USER(), CURRENT_USER, @@PROXY_USER;
USER()	CURRENT_USER	@@PROXY_USER
empl_external@localhost	employee@localhost	'empl_external'@'localhost'
SELECT USER(), CURRENT_USER, @@PROXY_USER;
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
DROP USER 'employee'@'localhost', 'empl_external'@'localhost';
#
# WL#2284: Increase the length of a user name
#
# Testing 32 proxy users
CREATE USER user_name_len_22_01234 IDENTIFIED WITH 'test_plugin_server' AS 'user_name_len_22_0dest';
CREATE USER user_name_len_22_0dest IDENTIFIED BY 'plug_dest_passwd';
SELECT plugin,authentication_string FROM mysql.user WHERE User='plug';
plugin	authentication_string
## test plugin auth
ERROR 28000: Access denied for user 'user_name_len_22_01234'@'localhost' (using password: YES)
GRANT PROXY ON user_name_len_22_0dest TO user_name_len_22_01234;
test proxies_priv columns
SELECT * FROM mysql.proxies_priv WHERE user !='root';
Host	User	Proxied_host	Proxied_user	With_grant	Grantor	Timestamp
xx	user_name_len_22_01234	%	user_name_len_22_0dest	0	root@localhost	xx
test mysql.proxies_priv;
SHOW CREATE TABLE mysql.proxies_priv;
Table	Create Table
proxies_priv	CREATE TABLE `proxies_priv` (
  `Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  `Proxied_host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
  `Proxied_user` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  `With_grant` tinyint(1) NOT NULL DEFAULT '0',
  `Grantor` varchar(288) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`),
  KEY `Grantor` (`Grantor`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='User proxy privileges'
SELECT USER(),CURRENT_USER();
USER()	CURRENT_USER()
user_name_len_22_01234@localhost	user_name_len_22_0dest@%
SET NAMES utf8;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
CREATE USER очень_очень_очень_длинный_юзер__ IDENTIFIED WITH 'test_plugin_server' AS 'очень_очень_очень_длинный_дест__';
CREATE USER очень_очень_очень_длинный_дест__ IDENTIFIED BY 'plug_dest_passwd';
SELECT plugin,authentication_string FROM mysql.user WHERE User='plug';
plugin	authentication_string
## test plugin auth
ERROR 28000: Access denied for user 'очень_очень_очень_длинный_'@'localhost' (using password: YES)
GRANT PROXY ON очень_очень_очень_длинный_дест__ TO очень_очень_очень_длинный_юзер__;
test proxies_priv columns
SELECT * FROM mysql.proxies_priv WHERE user !='root';
Host	User	Proxied_host	Proxied_user	With_grant	Grantor	Timestamp
xx	user_name_len_22_01234	%	user_name_len_22_0dest	0	root@localhost	xx
xx	очень_очень_очень_длинный_юзер__	%	очень_очень_очень_длинный_дест__	0	root@localhost	xx
test mysql.proxies_priv;
SHOW CREATE TABLE mysql.proxies_priv;
Table	Create Table
proxies_priv	CREATE TABLE `proxies_priv` (
  `Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  `Proxied_host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
  `Proxied_user` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  `With_grant` tinyint(1) NOT NULL DEFAULT '0',
  `Grantor` varchar(288) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`),
  KEY `Grantor` (`Grantor`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='User proxy privileges'
SELECT USER(),CURRENT_USER();
USER()	CURRENT_USER()
очень_очень_очень_длинный_юзер__@localhost	очень_очень_очень_длинный_дест__@%
# Testing 32 proxy users cleanup
DROP USER user_name_len_22_01234;
DROP USER user_name_len_22_0dest;
DROP USER очень_очень_очень_длинный_юзер__;
DROP USER очень_очень_очень_длинный_дест__;
SET NAMES default;
# Testing 32 users with different authentication plugins
CREATE USER user_name_len_22_01234@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string';
SELECT CURRENT_USER();
CURRENT_USER()
user_name_len_22_01234@localhost
DROP USER user_name_len_22_01234@localhost;
CREATE USER user_name_len_32_012345678901234@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
SELECT CURRENT_USER();
CURRENT_USER()
user_name_len_32_012345678901234@localhost
DROP USER user_name_len_32_012345678901234@localhost;
CREATE USER user_name_len_32_012345678901234@localhost IDENTIFIED WITH 'cleartext_plugin_server' AS 'auth_string';
SELECT CURRENT_USER();
CURRENT_USER()
user_name_len_32_012345678901234@localhost
DROP USER user_name_len_32_012345678901234@localhost;
# Testing 32 users with different authentication plugins WITH REQUIRE SSL
CREATE USER user_name_len_22_01234@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' REQUIRE SSL;
SELECT CURRENT_USER();
CURRENT_USER()
user_name_len_22_01234@localhost
DROP USER user_name_len_22_01234@localhost;
CREATE USER user_name_len_32_012345678901234@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string' REQUIRE SSL;
SELECT CURRENT_USER();
CURRENT_USER()
user_name_len_32_012345678901234@localhost
DROP USER user_name_len_32_012345678901234@localhost;
CREATE USER user_name_len_32_012345678901234@localhost IDENTIFIED WITH 'cleartext_plugin_server' AS 'auth_string' REQUIRE SSL;
SELECT CURRENT_USER();
CURRENT_USER()
user_name_len_32_012345678901234@localhost
DROP USER user_name_len_32_012345678901234@localhost;
# Testing 32 utf users with different authentication plugins
SET NAMES utf8;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
CREATE USER очень_очень_очень_длинный_юзер__@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string';
SELECT CURRENT_USER();
CURRENT_USER()
очень_очень_очень_длинный_юзер__@localhost
DROP USER очень_очень_очень_длинный_юзер__@localhost;
CREATE USER очень_очень_очень_длинный_юзер__@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
SELECT CURRENT_USER();
CURRENT_USER()
очень_очень_очень_длинный_юзер__@localhost
DROP USER очень_очень_очень_длинный_юзер__@localhost;
CREATE USER очень_очень_очень_длинный_юзер__@localhost IDENTIFIED WITH 'cleartext_plugin_server' AS 'auth_string';
SELECT CURRENT_USER();
CURRENT_USER()
очень_очень_очень_длинный_юзер__@localhost
DROP USER очень_очень_очень_длинный_юзер__@localhost;
# Testing 32 utf users with different authentication plugins WITH REQUIRE SSL
CREATE USER очень_очень_очень_длинный_юзер__@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' REQUIRE SSL;
SELECT CURRENT_USER();
CURRENT_USER()
очень_очень_очень_длинный_юзер__@localhost
DROP USER очень_очень_очень_длинный_юзер__@localhost;
CREATE USER очень_очень_очень_длинный_юзер__@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string' REQUIRE SSL;
SELECT CURRENT_USER();
CURRENT_USER()
очень_очень_очень_длинный_юзер__@localhost
DROP USER очень_очень_очень_длинный_юзер__@localhost;
CREATE USER очень_очень_очень_длинный_юзер__@localhost IDENTIFIED WITH 'cleartext_plugin_server' AS 'auth_string' REQUIRE SSL;
SELECT CURRENT_USER();
CURRENT_USER()
очень_очень_очень_длинный_юзер__@localhost
DROP USER очень_очень_очень_длинный_юзер__@localhost;
SET NAMES default;
# restart: