Debian Codegraph @

mysql-test/suite/auth_sec/t/mysql_no_login.test

raw
1 --source include/have_mysql_no_login_plugin.inc
2
3 --replace_regex /\.dll/.so/
4 eval INSTALL PLUGIN mysql_no_login SONAME '$MYSQL_NO_LOGIN';
5
6 query_vertical SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_DESCRIPTION
7 FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'mysql_no_login';
8
9 --disable_query_log
10 CREATE DATABASE noauthdb;
11 CREATE TABLE noauthdb.t1 (a INT);
12 CREATE TABLE noauthdb.t2 (a INT);
13 INSERT INTO noauthdb.t1 VALUES (1), (2);
14 INSERT INTO noauthdb.t2 VALUES (3), (4);
15
16 --echo Creating users noauth, otheruser
17 CREATE USER noauth@localhost IDENTIFIED WITH 'mysql_no_login';
18 CREATE USER otheruser@localhost;
19 GRANT SELECT, UPDATE, INSERT ON noauthdb.* TO noauth@localhost;
20
21 --echo Creating view, procedure, function
22 CREATE DEFINER = noauth@localhost SQL SECURITY DEFINER VIEW noauthdb.v1 AS SELECT * FROM noauthdb.t1 WHERE a % 2 = 0;
23 CREATE DEFINER = noauth@localhost PROCEDURE noauthdb.p1 () CONTAINS SQL SQL SECURITY DEFINER UPDATE noauthdb.t2 SET a = 5 WHERE a = 3;
24 delimiter //;
25 CREATE DEFINER = noauth@localhost FUNCTION noauthdb.f1() RETURNS INT CONTAINS SQL SQL SECURITY DEFINER
26 BEGIN
27 DECLARE outp INT DEFAULT NULL;
28 SELECT MAX(a) INTO outp FROM noauthdb.t2;
29 RETURN outp;
30 END//
31 delimiter ;//
32
33 GRANT SELECT ON noauthdb.v1 TO otheruser@localhost;
34 GRANT EXECUTE ON noauthdb.* TO otheruser@localhost;
35 GRANT EXECUTE ON noauthdb.* TO noauth@localhost;
36
37 --enable_query_log
38
39 --echo # Connect as otheruser - should succeed.
40 --exec $MYSQL --user=otheruser -e "select user(), current_user(), @@proxy_user; SELECT * FROM noauthdb.v1; CALL noauthdb.p1(); SELECT noauthdb.f1();"
41
42 --echo # Attempt to access underlying tables directly using otheruser - should fail.
43 --error 1, ER_TABLEACCESS_DENIED_ERROR
44 --exec $MYSQL --user=otheruser -e "SELECT * FROM noauthdb.t1;"
45
46 --echo # Connect as noauth - should fail.
47 --error 1, ER_ACCESS_DENIED_ERROR
48 --exec $MYSQL --user=noauth -e "select user(), current_user(), @@proxy_user"
49
50 --echo #try to expire password of this plugin user - should warn
51 --error ER_CANNOT_USER
52 alter user noauth@localhost password expire;
53
54 --echo #uninstall plugin and try to login with this plugin user - should return error
55 uninstall plugin mysql_no_login;
56 --echo # Connect as noauth - should fail.
57 --error 1, ER_PLUGIN_IS_NOT_LOADED
58 --exec $MYSQL --user=noauth -e "select user(), current_user(), @@proxy_user"
59
60 # Cleanup
61 --disable_query_log
62
63 DROP DATABASE noauthdb;
64 DROP USER noauth@localhost;
65 DROP USER otheruser@localhost;
66
67 --enable_query_log
68 ##proxy related scenario
69 --replace_regex /\.dll/.so/
70 eval INSTALL PLUGIN mysql_no_login SONAME '$MYSQL_NO_LOGIN';
71 --disable_query_log
72 CREATE DATABASE noauthdb;
73 CREATE TABLE noauthdb.t1 (a INT);
74 CREATE TABLE noauthdb.t2 (a INT);
75 INSERT INTO noauthdb.t1 VALUES (1), (2);
76 INSERT INTO noauthdb.t2 VALUES (3), (4);
77
78 --echo Creating users noauth, otheruser
79 CREATE USER noauth@localhost IDENTIFIED WITH 'mysql_no_login';
80 CREATE USER otheruser@localhost;
81 GRANT PROXY ON 'noauth'@'localhost' TO 'otheruser'@'localhost';
82 GRANT SELECT, UPDATE, INSERT ON noauthdb.* TO noauth@localhost;
83
84 --echo Creating view, procedure, function
85 CREATE DEFINER = noauth@localhost SQL SECURITY DEFINER VIEW noauthdb.v1 AS SELECT * FROM noauthdb.t1 WHERE a % 2 = 0;
86 CREATE DEFINER = noauth@localhost PROCEDURE noauthdb.p1 () CONTAINS SQL SQL SECURITY DEFINER UPDATE noauthdb.t2 SET a = 5 WHERE a = 3;
87 delimiter //;
88 CREATE DEFINER = noauth@localhost FUNCTION noauthdb.f1() RETURNS INT CONTAINS SQL SQL SECURITY DEFINER
89 BEGIN
90 DECLARE outp INT DEFAULT NULL;
91 SELECT MAX(a) INTO outp FROM noauthdb.t2;
92 RETURN outp;
93 END//
94 delimiter ;//
95
96 GRANT SELECT ON noauthdb.v1 TO otheruser@localhost;
97 GRANT EXECUTE ON noauthdb.* TO otheruser@localhost;
98 GRANT EXECUTE ON noauthdb.* TO noauth@localhost;
99
100 --enable_query_log
101
102 --echo # Connect as otheruser - should succeed.
103 --exec $MYSQL --user=otheruser -e "select user(), current_user(), @@proxy_user; SELECT * FROM noauthdb.v1; CALL noauthdb.p1(); SELECT noauthdb.f1();"
104
105 --echo # Attempt to access underlying tables directly using otheruser - should fail.
106 --error 1, ER_TABLEACCESS_DENIED_ERROR
107 --exec $MYSQL --user=otheruser -e "SELECT * FROM noauthdb.t1;"
108
109 # Cleanup
110 --disable_query_log
111
112 DROP DATABASE noauthdb;
113 DROP USER noauth@localhost;
114 DROP USER otheruser@localhost;
115
116 UNINSTALL PLUGIN mysql_no_login;
117 --enable_query_log
118 --exit