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.
616 lines
28 KiB
616 lines
28 KiB
5 months ago
|
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 utf8_bin NOT NULL DEFAULT '',
|
||
|
|
`Proxied_host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
|
||
|
|
`Proxied_user` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
|
||
|
|
`With_grant` tinyint(1) NOT NULL DEFAULT '0',
|
||
|
|
`Grantor` varchar(288) COLLATE utf8_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=utf8 COLLATE=utf8_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
|
||
|
|
Setting password is allowed but it won't affect the authentication mechanism.
|
||
|
|
SET PASSWORD = 'plug_dest';
|
||
|
|
Warnings:
|
||
|
|
Note 1699 SET PASSWORD has no significance for user 'plug'@'%' as authentication plugin does not support it.
|
||
|
|
## test bad credentials
|
||
|
|
ERROR 28000: Access denied for user 'plug'@'localhost' (using password: YES)
|
||
|
|
## test bad default plugin : should get CR_AUTH_PLUGIN_CANNOT_LOAD
|
||
|
|
## 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_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_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_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_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;
|
||
|
|
## 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';
|
||
|
|
Warnings:
|
||
|
|
Note 1699 SET PASSWORD has no significance for user 'bug12818542'@'localhost' as authentication plugin does not support it.
|
||
|
|
SELECT USER(),CURRENT_USER();
|
||
|
|
USER() CURRENT_USER()
|
||
|
|
bug12818542@localhost bug12818542_dest@localhost
|
||
|
|
DROP USER bug12818542@localhost;
|
||
|
|
DROP USER bug12818542_dest@localhost;
|
||
|
|
#
|
||
|
|
# 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';
|
||
|
|
Warnings:
|
||
|
|
Note 1699 SET PASSWORD has no significance for user 'bug12818542'@'localhost' as authentication plugin does not support it.
|
||
|
|
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 utf8_bin NOT NULL DEFAULT '',
|
||
|
|
`Proxied_host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
|
||
|
|
`Proxied_user` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
|
||
|
|
`With_grant` tinyint(1) NOT NULL DEFAULT '0',
|
||
|
|
`Grantor` varchar(288) COLLATE utf8_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=utf8 COLLATE=utf8_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 utf8_bin NOT NULL DEFAULT '',
|
||
|
|
`Proxied_host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
|
||
|
|
`Proxied_user` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
|
||
|
|
`With_grant` tinyint(1) NOT NULL DEFAULT '0',
|
||
|
|
`Grantor` varchar(288) COLLATE utf8_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=utf8 COLLATE=utf8_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;
|