diff options
Diffstat (limited to 'mysql-test/main/plugin_auth.result')
| -rw-r--r-- | mysql-test/main/plugin_auth.result | 491 |
1 files changed, 491 insertions, 0 deletions
diff --git a/mysql-test/main/plugin_auth.result b/mysql-test/main/plugin_auth.result new file mode 100644 index 00000000000..e470cc010c0 --- /dev/null +++ b/mysql-test/main/plugin_auth.result @@ -0,0 +1,491 @@ +SET GLOBAL SQL_MODE=""; +SET LOCAL SQL_MODE=""; +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 +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(60) COLLATE utf8_bin NOT NULL DEFAULT '', + `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', + `Proxied_host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', + `Proxied_user` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', + `With_grant` tinyint(1) NOT NULL DEFAULT 0, + `Grantor` char(141) 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`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User proxy privileges' +connect plug_con,localhost,plug,plug_dest; +connection plug_con; +select USER(),CURRENT_USER(); +USER() CURRENT_USER() +plug@localhost plug_dest@% +## test SET PASSWORD +SET PASSWORD = PASSWORD('plug_dest'); +connection default; +disconnect plug_con; +## 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 +connect plug_con_rightp,localhost,plug,plug_dest,,,,,auth_test_plugin; +connection plug_con_rightp; +select USER(),CURRENT_USER(); +USER() CURRENT_USER() +plug@localhost plug_dest@% +connection default; +disconnect plug_con_rightp; +## test no_auto_create_user sql mode with plugin users +SET @@sql_mode=no_auto_create_user; +GRANT INSERT ON TEST.* TO grant_user IDENTIFIED WITH 'test_plugin_server'; +SET @@sql_mode=""; +DROP USER grant_user; +## test utf-8 user name +CREATE USER `Ÿ` IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; +GRANT PROXY ON plug_dest TO `Ÿ`; +connect non_ascii,localhost,Ÿ,plug_dest; +connection non_ascii; +select USER(),CURRENT_USER(); +USER() CURRENT_USER() +Ÿ@localhost plug_dest@% +connection default; +disconnect non_ascii; +DROP USER `Ÿ`; +## test GRANT ... IDENTIFIED WITH/BY ... +CREATE DATABASE test_grant_db; +# create new user via GRANT WITH +GRANT ALL PRIVILEGES ON test_grant_db.* TO new_grant_user +IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; +GRANT PROXY ON plug_dest TO new_grant_user; +connect plug_con_grant,localhost,new_grant_user,plug_dest; +connection plug_con_grant; +select USER(),CURRENT_USER(); +USER() CURRENT_USER() +new_grant_user@localhost plug_dest@% +USE test_grant_db; +CREATE TABLE t1 (a INT); +DROP TABLE t1; +connection default; +disconnect plug_con_grant; +REVOKE ALL PRIVILEGES ON test_grant_db.* FROM new_grant_user; +# try re-create existing user via GRANT IDENTIFIED BY +GRANT ALL PRIVILEGES ON test_grant_db.* TO new_grant_user +IDENTIFIED BY 'new_password'; +connect(localhost,new_grant_user,plug_dest,test,MYSQL_PORT,MYSQL_SOCK); +connect plug_con_grant_deny,localhost,new_grant_user,plug_dest; +ERROR 28000: Access denied for user 'new_grant_user'@'localhost' (using password: YES) +connect plug_con_grant,localhost,new_grant_user,new_password; +connection plug_con_grant; +select USER(),CURRENT_USER(); +USER() CURRENT_USER() +new_grant_user@localhost new_grant_user@% +USE test_grant_db; +CREATE TABLE t1 (a INT); +DROP TABLE t1; +connection default; +disconnect plug_con_grant; +DROP USER new_grant_user; +# try re-create existing user via GRANT IDENTIFIED WITH +GRANT ALL PRIVILEGES ON test_grant_db.* TO plug +IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; +GRANT ALL PRIVILEGES ON test_grant_db.* TO plug_dest +IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; +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 MariaDB 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 MariaDB 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 MariaDB 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 +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; +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 MariaDB 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 MariaDB 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 '%' +connect grant_plug_dest_con,localhost,grant_plug_dest,grant_plug_dest_passwd; +connection grant_plug_dest_con; +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 +connection default; +in default connection +disconnect grant_plug_dest_con; +# 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 +GRANT PROXY ON ''@'%%' TO proxy_admin IDENTIFIED BY 'test' +WITH GRANT OPTION; +need USAGE : PROXY doesn't contain it. +GRANT USAGE on *.* TO proxy_admin; +connect proxy_admin_con,localhost,proxy_admin,test; +connection proxy_admin_con; +in proxy_admin_con; +should work : proxy_admin has proxy to ''@'%%' +GRANT PROXY ON future_user TO grant_plug; +connection default; +in default connection +disconnect proxy_admin_con; +SHOW GRANTS FOR grant_plug; +Grants for grant_plug@% +GRANT ALL PRIVILEGES ON *.* TO 'grant_plug'@'%' IDENTIFIED VIA test_plugin_server USING 'grant_plug_dest' +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 ALL PRIVILEGES ON *.* TO 'grant_plug'@'%' IDENTIFIED VIA test_plugin_server USING 'grant_plug_dest' +## 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 +connect plug_con,localhost,plug,plug_dest; +connection plug_con; +# in connection plug_con +SELECT @@LOCAL.proxy_user; +@@LOCAL.proxy_user +'plug'@'%' +connection default; +# in connection default +disconnect plug_con; +## 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 +connect plug_con,localhost,plug,plug_dest; +connection plug_con; +# in connection plug_con +SELECT @@LOCAL.external_user; +@@LOCAL.external_user +plug_dest +connection default; +# in connection default +disconnect plug_con; +## cleanup +DROP USER plug; +DROP USER plug_dest; +## END @@external_user tests +# +# Bug #56798 : Wrong credentials assigned when using a proxy user. +# +GRANT ALL PRIVILEGES ON *.* TO power_user; +GRANT USAGE ON anonymous_db.* TO ''@'%%' + IDENTIFIED WITH 'test_plugin_server' AS 'power_user'; +GRANT PROXY ON power_user TO ''@'%%'; +CREATE DATABASE confidential_db; +connect plug_con,localhost, test_login_user, power_user, confidential_db; +SELECT user(),current_user(),@@proxy_user; +user() current_user() @@proxy_user +test_login_user@localhost power_user@% ''@'%%' +connection default; +disconnect plug_con; +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 #57551 : Live upgrade fails between 5.1.52 -> 5.5.7-rc +# +CALL mtr.add_suppression("Missing system table mysql.proxies_priv."); +DROP TABLE mysql.proxies_priv; +# Must come back with mysql.proxies_priv absent. +SELECT * FROM mysql.proxies_priv; +ERROR 42S02: Table 'mysql.proxies_priv' doesn't exist +CREATE USER u1@localhost; +GRANT ALL PRIVILEGES ON *.* TO u1@localhost; +REVOKE ALL PRIVILEGES ON *.* FROM u1@localhost; +GRANT ALL PRIVILEGES ON *.* TO u1@localhost; +CREATE USER u2@localhost; +GRANT ALL PRIVILEGES ON *.* TO u2@localhost; +# access denied because of no privileges to root +GRANT PROXY ON u2@localhost TO u1@localhost; +ERROR 28000: Access denied for user 'root'@'localhost' +# access denied because of no privileges to root +REVOKE PROXY ON u2@localhost FROM u1@localhost; +ERROR 28000: Access denied for user 'root'@'localhost' +# go try graning proxy on itself, so that it will need the table +connect proxy_granter_con,localhost,u2,; +connection proxy_granter_con; +GRANT PROXY ON u2@localhost TO u1@localhost; +ERROR 42S02: Table 'mysql.proxies_priv' doesn't exist +REVOKE PROXY ON u2@localhost FROM u1@localhost; +ERROR 42S02: Table 'mysql.proxies_priv' doesn't exist +connection default; +disconnect proxy_granter_con; +# test if REVOKE works without the proxies_priv table +REVOKE ALL PRIVILEGES ON *.* FROM u1@localhost, u2@localhost; +# test if DROP USER work without the proxies_priv table +DROP USER u1@localhost,u2@localhost; +# test if FLUSH PRIVILEGES works without the proxies_priv table +FLUSH PRIVILEGES; +SELECT Host,User,Proxied_host,Proxied_user,With_grant FROM mysql.proxies_priv; +Host localhost +User root +Proxied_host +Proxied_user +With_grant 1 +FLUSH PRIVILEGES; +# +# Bug#58139 : default-auth option not recognized in MySQL standard +# command line clients +# +# Executing 'mysql' +1 +1 +# Executing 'mysqladmin' +mysqld is alive +# Executing 'mysqldump' +# Executing 'mysql_upgrade' +# +# 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) +connect cleartext_con,localhost,uplain,cleartext_test; +connection cleartext_con; +select USER(),CURRENT_USER(); +USER() CURRENT_USER() +uplain@localhost uplain@localhost +connection default; +disconnect cleartext_con; +DROP USER uplain@localhost; +# +# Bug #59038 : mysql.user.authentication_string column +# causes configuration wizard to fail +INSERT IGNORE INTO mysql.user( +Host, +User, +Password, +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'); +Warnings: +Warning 1364 Field 'authentication_string' doesn't have a default value +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 +NO 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 +NO 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 +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 +NO authentication_string +NO plugin +# +# Bug # 11766641: 59792: BIN/MYSQL -UUNKNOWN -PUNKNOWN +# .-> USING PASSWORD: NO +# +# shoud contain "using password=yes" +ERROR 1045 (28000): Access denied for user 'unknown'@'localhost' (using password: YES) +# shoud contain "using password=no" +ERROR 1045 (28000): Access denied for user 'unknown'@'localhost' (using password: NO) +# +# Bug #12610784: SET PASSWORD INCORRECTLY KEEP AN OLD EMPTY PASSWORD +# +CREATE USER bug12610784@localhost; +SET PASSWORD FOR bug12610784@localhost = PASSWORD('secret'); +ERROR 28000: Access denied for user 'bug12610784'@'localhost' (using password: NO) +connect b12610784,localhost,bug12610784,secret,test; +connection default; +disconnect b12610784; +DROP USER bug12610784@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; +connect bug12818542_con,localhost,bug12818542,bug12818542_dest; +connection bug12818542_con; +SELECT USER(),CURRENT_USER(); +USER() CURRENT_USER() +bug12818542@localhost bug12818542_dest@localhost +SET PASSWORD = PASSWORD('bruhaha'); +connection default; +disconnect bug12818542_con; +connect bug12818542_con2,localhost,bug12818542,bug12818542_dest; +connection bug12818542_con2; +SELECT USER(),CURRENT_USER(); +USER() CURRENT_USER() +bug12818542@localhost bug12818542_dest@localhost +connection default; +disconnect bug12818542_con2; +DROP USER bug12818542@localhost; +DROP USER bug12818542_dest@localhost; +SET GLOBAL SQL_MODE=default; +End of 5.5 tests |