diff options
Diffstat (limited to 'mysql-test/main/grant2.result')
| -rw-r--r-- | mysql-test/main/grant2.result | 948 |
1 files changed, 948 insertions, 0 deletions
diff --git a/mysql-test/main/grant2.result b/mysql-test/main/grant2.result new file mode 100644 index 00000000000..2ccc01d225a --- /dev/null +++ b/mysql-test/main/grant2.result @@ -0,0 +1,948 @@ +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; +SET NAMES binary; +drop database if exists mysqltest; +drop database if exists mysqltest_1; +delete from mysql.user where user like 'mysqltest\_%'; +delete from mysql.db where user like 'mysqltest\_%'; +delete from mysql.tables_priv where user like 'mysqltest\_%'; +delete from mysql.columns_priv where user like 'mysqltest\_%'; +flush privileges; +grant all privileges on `my\_1`.* to mysqltest_1@localhost with grant option; +grant create user on *.* to mysqltest_1@localhost; +create user mysqltest_2@localhost; +connect user_a,localhost,mysqltest_1,,; +connection user_a; +grant select on `my\_1`.* to mysqltest_2@localhost; +grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass'; +ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysql' +disconnect user_a; +connection default; +grant update on mysql.* to mysqltest_1@localhost; +connect user_b,localhost,mysqltest_1,,; +connection user_b; +grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass'; +grant select on `my\_1`.* to mysqltest_3@localhost; +disconnect user_b; +connection default; +grant insert on mysql.* to mysqltest_1@localhost; +connect user_c,localhost,mysqltest_1,,; +connection user_c; +grant select on `my\_1`.* to mysqltest_3@localhost; +grant select on `my\_1`.* to mysqltest_4@localhost identified by 'pass'; +disconnect user_c; +connection default; +delete from mysql.user where user like 'mysqltest\_%'; +delete from mysql.db where user like 'mysqltest\_%'; +delete from mysql.tables_priv where user like 'mysqltest\_%'; +delete from mysql.columns_priv where user like 'mysqltest\_%'; +flush privileges; +grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option; +grant create user on *.* to mysqltest_1@localhost; +connect user1,localhost,mysqltest_1,,; +connection user1; +select current_user(); +current_user() +mysqltest_1@localhost +grant all privileges on `my\_1`.* to mysqltest_2@localhost with grant option; +grant all privileges on `my_%`.* to mysqltest_3@localhost with grant option; +ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'my_%' +set @@sql_mode='NO_AUTO_CREATE_USER'; +select @@sql_mode; +@@sql_mode +NO_AUTO_CREATE_USER +grant select on `my\_1`.* to mysqltest_4@localhost with grant option; +ERROR 28000: Can't find any matching row in the user table +grant select on `my\_1`.* to mysqltest_4@localhost identified by 'mypass' +with grant option; +disconnect user1; +connection default; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT CREATE USER ON *.* TO 'mysqltest_1'@'localhost' +GRANT ALL PRIVILEGES ON `my\_%`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +show grants for mysqltest_2@localhost; +Grants for mysqltest_2@localhost +GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost' +GRANT ALL PRIVILEGES ON `my\_1`.* TO 'mysqltest_2'@'localhost' WITH GRANT OPTION +show grants for mysqltest_3@localhost; +ERROR 42000: There is no such grant defined for user 'mysqltest_3' on host 'localhost' +delete from mysql.user where user like 'mysqltest\_%'; +delete from mysql.db where user like 'mysqltest\_%'; +flush privileges; +create database mysqltest_1; +grant all privileges on `mysqltest\_1`.* to mysqltest_1@localhost with grant option; +connect user2,localhost,mysqltest_1,,; +connection user2; +select current_user(); +current_user() +mysqltest_1@localhost +show databases; +Database +information_schema +mysqltest_1 +test +grant all privileges on `mysqltest_1`.* to mysqltest_1@localhost with grant option; +ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest_1' +disconnect user2; +connection default; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT ALL PRIVILEGES ON `mysqltest\_1`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +delete from mysql.user where user like 'mysqltest\_%'; +delete from mysql.db where user like 'mysqltest\_%'; +drop database mysqltest_1; +flush privileges; +create database mysqltest; +grant INSERT, SELECT on mysqltest.* to mysqltest_1@localhost; +flush privileges; +use mysqltest; +create table t1 (id int primary key, data varchar(255)); +connect mrbad, localhost, mysqltest_1,,mysqltest; +connection mrbad; +show grants for current_user(); +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' +GRANT SELECT, INSERT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +insert into t1 values (1, 'I can''t change it!'); +update t1 set data='I can change it!' where id = 1; +ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 't1' +insert into t1 values (1, 'XXX') on duplicate key update data= 'I can change it!'; +ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 't1' +select * from t1; +id data +1 I can't change it! +disconnect mrbad; +connection default; +drop table t1; +delete from mysql.user where user like 'mysqltest\_%'; +delete from mysql.db where user like 'mysqltest\_%'; +flush privileges; +create table t1 (a int, b int); +grant select (a) on t1 to mysqltest_1@localhost with grant option; +connect mrugly, localhost, mysqltest_1,,mysqltest; +connection mrugly; +grant select (a,b) on t1 to mysqltest_2@localhost; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't1' +grant select on t1 to mysqltest_3@localhost; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 't1' +disconnect mrugly; +connection default; +drop table t1; +delete from mysql.user where user like 'mysqltest\_%'; +delete from mysql.db where user like 'mysqltest\_%'; +delete from mysql.tables_priv where user like 'mysqltest\_%'; +delete from mysql.columns_priv where user like 'mysqltest\_%'; +flush privileges; +drop database mysqltest; +use test; +create user mysqltest_1@host1; +create user mysqltest_2@host2; +create user mysqltest_3@host3; +create user mysqltest_4@host4; +create user mysqltest_5@host5; +create user mysqltest_6@host6; +create user mysqltest_7@host7; +flush privileges; +drop user mysqltest_3@host3; +connect con8,127.0.0.1,root,,test,$MASTER_MYPORT,; +disconnect con8; +connection default; +drop user mysqltest_1@host1, mysqltest_2@host2, mysqltest_4@host4, +mysqltest_5@host5, mysqltest_6@host6, mysqltest_7@host7; +connect con9,127.0.0.1,root,,test,$MASTER_MYPORT,; +disconnect con9; +connection default; +create database mysqltest_1; +grant select, insert, update on `mysqltest\_1`.* to mysqltest_1@localhost; +connect con10,localhost,mysqltest_1,,; +connection con10; +set sql_log_off = 1; +ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation +set sql_log_bin = 0; +ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation +disconnect con10; +connection default; +delete from mysql.user where user like 'mysqltest\_1'; +delete from mysql.db where user like 'mysqltest\_1'; +drop database mysqltest_1; +flush privileges; +set sql_mode='maxdb'; +drop table if exists t1, t2; +create table t1(c1 int); +create table t2(c1 int, c2 int); +create user 'mysqltest_1'; +create user 'mysqltest_1'; +ERROR HY000: Operation CREATE USER failed for 'mysqltest_1'@'%' +create user 'mysqltest_2' identified by 'Mysqltest-2'; +create user 'mysqltest_3' identified by password 'fffffffffffffffffffffffffffffffffffffffff'; +grant select on *.* to 'mysqltest_2'; +grant insert on test.* to 'mysqltest_2'; +grant update on test.t1 to 'mysqltest_2'; +grant update (c2) on test.t2 to 'mysqltest_2'; +select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; +host user password +% mysqltest_1 +% mysqltest_2 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 +% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff +select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; +host db user +% test mysqltest_2 +select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; +host db user table_name +% test mysqltest_2 t1 +% test mysqltest_2 t2 +select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' order by host,db,user,table_name,column_name; +host db user table_name column_name +% test mysqltest_2 t2 c2 +show grants for 'mysqltest_1'; +Grants for mysqltest_1@% +GRANT USAGE ON *.* TO 'mysqltest_1'@'%' +show grants for 'mysqltest_2'; +Grants for mysqltest_2@% +GRANT SELECT ON *.* TO 'mysqltest_2'@'%' IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1' +GRANT INSERT ON "test".* TO 'mysqltest_2'@'%' +GRANT UPDATE (c2) ON "test"."t2" TO 'mysqltest_2'@'%' +GRANT UPDATE ON "test"."t1" TO 'mysqltest_2'@'%' +drop user 'mysqltest_1'; +select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; +host user password +% mysqltest_2 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 +% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff +select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; +host db user +% test mysqltest_2 +select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; +host db user table_name +% test mysqltest_2 t1 +% test mysqltest_2 t2 +select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' order by host,db,user,table_name,column_name; +host db user table_name column_name +% test mysqltest_2 t2 c2 +show grants for 'mysqltest_1'; +ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%' +rename user 'mysqltest_2' to 'mysqltest_1'; +select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; +host user password +% mysqltest_1 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 +% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff +select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user; +host db user +% test mysqltest_1 +select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name; +host db user table_name +% test mysqltest_1 t1 +% test mysqltest_1 t2 +select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' order by host,db,user,table_name,column_name; +host db user table_name column_name +% test mysqltest_1 t2 c2 +show grants for 'mysqltest_1'; +Grants for mysqltest_1@% +GRANT SELECT ON *.* TO 'mysqltest_1'@'%' IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1' +GRANT INSERT ON "test".* TO 'mysqltest_1'@'%' +GRANT UPDATE (c2) ON "test"."t2" TO 'mysqltest_1'@'%' +GRANT UPDATE ON "test"."t1" TO 'mysqltest_1'@'%' +drop user 'mysqltest_1', 'mysqltest_3'; +drop user 'mysqltest_1'; +ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%' +drop table t1, t2; +insert into mysql.db set user='mysqltest_1', db='%', host='%'; +flush privileges; +show grants for 'mysqltest_1'; +ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%' +revoke all privileges, grant option from 'mysqltest_1'; +ERROR HY000: Can't revoke all privileges for one or more of the requested users +drop user 'mysqltest_1'; +select host,db,user from mysql.db where user = 'mysqltest_1' order by host,db,user; +host db user +insert into mysql.tables_priv set host='%', db='test', user='mysqltest_1', table_name='t1'; +flush privileges; +show grants for 'mysqltest_1'; +ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%' +drop user 'mysqltest_1'; +select host,db,user,table_name from mysql.tables_priv where user = 'mysqltest_1' order by host,db,user,table_name; +host db user table_name +insert into mysql.columns_priv set host='%', db='test', user='mysqltest_1', table_name='t1', column_name='c1'; +flush privileges; +show grants for 'mysqltest_1'; +ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%' +drop user 'mysqltest_1'; +select host,db,user,table_name,column_name from mysql.columns_priv where user = 'mysqltest_1' order by host,db,user,table_name,column_name; +host db user table_name column_name +create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; +drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; +create user 'mysqltest_1', 'mysqltest_2' identified by 'Mysqltest-2', 'mysqltest_3' identified by password 'fffffffffffffffffffffffffffffffffffffffff'; +rename user 'mysqltest_1' to 'mysqltest_1a', 'mysqltest_2' TO 'mysqltest_2a', 'mysqltest_3' TO 'mysqltest_3a'; +drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; +ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%','mysqltest_2'@'%','mysqltest_3'@'%' +drop user 'mysqltest_1a', 'mysqltest_2a', 'mysqltest_3a'; +create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; +create user 'mysqltest_1a', 'mysqltest_2', 'mysqltest_3a'; +ERROR HY000: Operation CREATE USER failed for 'mysqltest_2'@'%' +rename user 'mysqltest_1a' to 'mysqltest_1b', 'mysqltest_2a' TO 'mysqltest_2b', 'mysqltest_3a' TO 'mysqltest_3b'; +ERROR HY000: Operation RENAME USER failed for 'mysqltest_2a'@'%' +drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; +drop user 'mysqltest_1b', 'mysqltest_2b', 'mysqltest_3b'; +ERROR HY000: Operation DROP USER failed for 'mysqltest_2b'@'%' +create user 'mysqltest_2' identified by 'Mysqltest-2'; +drop user 'mysqltest_2' identified by 'Mysqltest-2'; +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 'identified by 'Mysqltest-2'' at line 1 +drop user 'mysqltest_2'; +create user '%@b'@'b'; +show grants for '%@b'@'b'; +Grants for %@b@b +GRANT USAGE ON *.* TO '%@b'@'b' +grant select on mysql.* to '%@b'@'b'; +show grants for '%@b'@'b'; +Grants for %@b@b +GRANT USAGE ON *.* TO '%@b'@'b' +GRANT SELECT ON "mysql".* TO '%@b'@'b' +rename user '%@b'@'b' to '%@a'@'a'; +show grants for '%@b'@'b'; +ERROR 42000: There is no such grant defined for user '%@b' on host 'b' +show grants for '%@a'@'a'; +Grants for %@a@a +GRANT USAGE ON *.* TO '%@a'@'a' +GRANT SELECT ON "mysql".* TO '%@a'@'a' +drop user '%@a'@'a'; +create user mysqltest_2@localhost; +grant create user on *.* to mysqltest_2@localhost; +connect user3,localhost,mysqltest_2,,; +connection user3; +select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; +ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 'user' +create user mysqltest_A@'%'; +rename user mysqltest_A@'%' to mysqltest_B@'%'; +drop user mysqltest_B@'%'; +disconnect user3; +connection default; +drop user mysqltest_2@localhost; +create user mysqltest_3@localhost; +grant INSERT,DELETE,UPDATE on mysql.* to mysqltest_3@localhost; +connect user4,localhost,mysqltest_3,,; +connection user4; +show grants; +Grants for mysqltest_3@localhost +GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost' +GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO 'mysqltest_3'@'localhost' +select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password; +ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 'user' +insert into mysql.user set host='%', user='mysqltest_B'; +Warnings: +Warning 1364 Field 'ssl_cipher' doesn't have a default value +Warning 1364 Field 'x509_issuer' doesn't have a default value +Warning 1364 Field 'x509_subject' doesn't have a default value +Warning 1364 Field 'authentication_string' doesn't have a default value +create user mysqltest_A@'%'; +rename user mysqltest_B@'%' to mysqltest_C@'%'; +drop user mysqltest_C@'%'; +drop user mysqltest_A@'%'; +disconnect user4; +connection default; +drop user mysqltest_3@localhost; +set @@sql_mode=''; +create database mysqltest_1; +create table mysqltest_1.t1 (i int); +insert into mysqltest_1.t1 values (1),(2),(3); +GRANT ALL ON mysqltest_1.t1 TO mysqltest_1@'127.0.0.0/255.0.0.0'; +connect n1,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,$MASTER_MYSOCK; +connection n1; +show grants for current_user(); +Grants for mysqltest_1@127.0.0.0/255.0.0.0 +GRANT USAGE ON *.* TO 'mysqltest_1'@'127.0.0.0/255.0.0.0' +GRANT ALL PRIVILEGES ON `mysqltest_1`.`t1` TO 'mysqltest_1'@'127.0.0.0/255.0.0.0' +select * from t1; +i +1 +2 +3 +disconnect n1; +connection default; +REVOKE ALL ON mysqltest_1.t1 FROM mysqltest_1@'127.0.0.0/255.0.0.0'; +delete from mysql.user where user like 'mysqltest\_1'; +flush privileges; +drop table mysqltest_1.t1; +grant all on mysqltest_1.* to mysqltest_1@'127.0.0.1'; +connect b12302,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,; +connection b12302; +select current_user(); +current_user() +mysqltest_1@127.0.0.1 +set password = password('changed'); +disconnect b12302; +connection default; +select host, length(password) from mysql.user where user like 'mysqltest\_1'; +host length(password) +127.0.0.1 41 +revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.1'; +delete from mysql.user where user like 'mysqltest\_1'; +flush privileges; +grant all on mysqltest_1.* to mysqltest_1@'127.0.0.0/255.0.0.0'; +connect b12302_2,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,; +connection b12302_2; +select current_user(); +current_user() +mysqltest_1@127.0.0.0/255.0.0.0 +set password = password('changed'); +disconnect b12302_2; +connection default; +select host, length(password) from mysql.user where user like 'mysqltest\_1'; +host length(password) +127.0.0.0/255.0.0.0 41 +revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.0/255.0.0.0'; +delete from mysql.user where user like 'mysqltest\_1'; +flush privileges; +drop database mysqltest_1; +connect n5,localhost,test,,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection n5; +set password = password("changed"); +ERROR 42000: You are using MariaDB as an anonymous user and anonymous users are not allowed to modify user settings +disconnect n5; +connection default; +connect con2root,localhost,root,,; +connect con3root,localhost,root,,; +connection default; +lock table mysql.user write; +connection con2root; +flush privileges; +connection con3root; +grant all on *.* to 'mysqltest_1'@'localhost'; +connection default; +unlock tables; +connection con2root; +connection con3root; +connection default; +lock table mysql.user write; +connection con2root; +set password for 'mysqltest_1'@'localhost' = password(''); +connection con3root; +revoke all on *.* from 'mysqltest_1'@'localhost'; +connection default; +unlock tables; +connection con2root; +connection con3root; +connection default; +drop user 'mysqltest_1'@'localhost'; +disconnect con2root; +disconnect con3root; +create database TESTDB; +create table t2(a int); +create temporary table t1 as select * from mysql.user; +delete from mysql.user where host='localhost'; +INSERT INTO mysql.user (host, user, password) VALUES +('%','mysqltest_1',password('password')); +Warnings: +Warning 1364 Field 'ssl_cipher' doesn't have a default value +Warning 1364 Field 'x509_issuer' doesn't have a default value +Warning 1364 Field 'x509_subject' doesn't have a default value +Warning 1364 Field 'authentication_string' doesn't have a default value +INSERT INTO mysql.db (host, db, user, select_priv) VALUES +('%','TESTDB','mysqltest_1','Y'); +FLUSH PRIVILEGES; +connect con1,localhost,mysqltest_1,password,TESTDB; +create database TEStdb; +Got one of the listed errors +connection default; +disconnect con1; +delete from mysql.user; +delete from mysql.db where host='%' and user='mysqltest_1' and db='TESTDB'; +insert into mysql.user select * from t1; +drop table t1, t2; +drop database TESTDB; +flush privileges; +SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; +SET GLOBAL log_bin_trust_function_creators = 1; +GRANT ALL PRIVILEGES ON test.* TO `a@`@localhost; +GRANT EXECUTE ON * TO `a@`@localhost; +connect bug13310,localhost,'a@',,test; +connection bug13310; +CREATE TABLE t2 (s1 INT); +INSERT INTO t2 VALUES (1); +DROP FUNCTION IF EXISTS f2; +CREATE FUNCTION f2 () RETURNS INT +BEGIN DECLARE v INT; SELECT s1 FROM t2 INTO v; RETURN v; END// +SELECT f2(); +f2() +1 +DROP FUNCTION f2; +DROP TABLE t2; +disconnect bug13310; +connection default; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM `a@`@localhost; +DROP USER `a@`@localhost; +SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; +drop database if exists mysqltest_1; +drop database if exists mysqltest_2; +drop user mysqltest_u1@localhost; +create database mysqltest_1; +create database mysqltest_2; +grant all on mysqltest_1.* to mysqltest_u1@localhost; +use mysqltest_2; +create table t1 (i int); +connect user1,localhost,mysqltest_u1,,mysqltest_1; +connection user1; +show create table mysqltest_2.t1; +ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't1' +create table t1 like mysqltest_2.t1; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' +connection default; +grant select on mysqltest_2.t1 to mysqltest_u1@localhost; +connection user1; +show create table mysqltest_2.t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +create table t1 like mysqltest_2.t1; +connection default; +disconnect user1; +use test; +drop database mysqltest_1; +drop database mysqltest_2; +drop user mysqltest_u1@localhost; +grant all on `mysqltest\_%`.* to mysqltest_1@localhost with grant option; +grant usage on *.* to mysqltest_2@localhost; +connect con18600_1,localhost,mysqltest_1,,; +create database mysqltest_1; +use mysqltest_1; +create table t1 (f1 int); +grant create on `mysqltest\_1`.* to mysqltest_2@localhost; +grant select on mysqltest_1.t1 to mysqltest_2@localhost; +connect con3,localhost,mysqltest_2,,; +connection con3; +create database mysqltest_3; +ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest_3' +use mysqltest_1; +create table t2(f1 int); +select * from t1; +f1 +connection default; +drop database mysqltest_1; +connection default; +disconnect con3; +disconnect con18600_1; +revoke all privileges, grant option from mysqltest_1@localhost; +revoke all privileges, grant option from mysqltest_2@localhost; +drop user mysqltest_1@localhost; +drop user mysqltest_2@localhost; +CREATE DATABASE db1; +USE db1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE TABLE t2 (b INT, c INT); +INSERT INTO t2 VALUES (1,100),(2,200); +GRANT SELECT ON t1 TO mysqltest1@localhost; +GRANT SELECT (b) ON t2 TO mysqltest1@localhost; +connect conn1,localhost,mysqltest1,,; +connection conn1; +USE db1; +SELECT c FROM t2; +ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2' +SELECT * FROM t2; +ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for table 't2' +SELECT * FROM t1 JOIN t2 USING (b); +ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2' +connection default; +disconnect conn1; +USE test; +DROP TABLE db1.t1, db1.t2; +DROP USER mysqltest1@localhost; +DROP DATABASE db1; +End of 5.0 tests +USE mysql; +SELECT LEFT(CURRENT_USER(),INSTR(CURRENT_USER(),'@')-1) INTO @u; +SELECT MID(CURRENT_USER(),INSTR(CURRENT_USER(),'@')+1) INTO @h; +SELECT password FROM user WHERE user=@u AND host=@h INTO @pwd; +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; +user host password insert_priv +root localhost Y +UPDATE user SET insert_priv='N' WHERE user=@u AND host=@h; +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; +user host password insert_priv +root localhost N +GRANT INSERT ON *.* TO CURRENT_USER(); +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; +user host password insert_priv +root localhost Y +UPDATE user SET insert_priv='N' WHERE user=@u AND host=@h; +GRANT INSERT ON *.* TO CURRENT_USER() IDENTIFIED BY 'keksdose'; +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; +user host password insert_priv +root localhost *0BB7188CF0DE9B403BA66E9DD810D82652D002EB Y +UPDATE user SET password=@pwd WHERE user=@u AND host=@h; +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; +user host password insert_priv +root localhost Y +FLUSH PRIVILEGES; +USE test; +End of 5.1 tests + +# -- +# -- Bug#11746602: 27480 - Extend CREATE TEMPORARY TABLES privilege to +# -- allow temp table operations +# -- +# -- Bug#12771903: User with create temporary tables priv only has full +# -- access to a regular table +# -- + +############################################################################ +# Setup environment. +########################################################################### +DROP DATABASE IF EXISTS mysqltest_db1; +DROP DATABASE IF EXISTS mysqltest_db2; +CREATE DATABASE mysqltest_db1; +CREATE DATABASE mysqltest_db2; +# mysqltest_u1@localhost has CREATE_TMP_ACL, FILE_ACL and EXECUTE_ACL only +# (EXECUTE_ACL is needed to call p0, and FILE_ACL is needed for SELECT +# OUTFILE/LOAD DATA INFILE). +GRANT FILE ON *.* TO mysqltest_u1@localhost; +GRANT CREATE TEMPORARY TABLES, EXECUTE ON mysqltest_db1.* TO mysqltest_u1@localhost; +# mysqltest_u2@localhost has all privileges but CREATE_TMP_ACL. +GRANT ALL PRIVILEGES ON mysqltest_db1.* TO mysqltest_u2@localhost; +REVOKE CREATE TEMPORARY TABLES ON mysqltest_db1.* FROM mysqltest_u2@localhost; +# mysqltest_u3@localhost has CREATE_TMP_ACL & EXECUTE_ACL. +# This user is required to check SUID-stored-routines. +GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u3@localhost; +GRANT EXECUTE ON mysqltest_db1.* TO mysqltest_u3@localhost; +# mysqltest_u4@localhost has only EXECUTE_ACL. +# We need this user to check that once created temporary tables +# are accessible by anyone. +GRANT EXECUTE ON mysqltest_db1.* TO mysqltest_u4@localhost; +# mysqltest_u5@localhost has CREATE_TMP_ACL and SELECT_ACL, UPDATE_ACL, +# DELETE_ACL on mysqltest_db1; and only CREATE_TMP_ACL on mysqltest_db2. +# By means of this user we check privileges required for merge tables. +GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u5@localhost; +GRANT CREATE TEMPORARY TABLES ON mysqltest_db2.* TO mysqltest_u5@localhost; +GRANT SELECT, UPDATE, DELETE ON mysqltest_db1.* TO mysqltest_u5@localhost; +# Create stored routine to test how privilege checking is done for its +# arguments. +CREATE PROCEDURE mysqltest_db1.p0(i INT) SELECT i; +# Create SUID-stored-routines. +CREATE DEFINER = mysqltest_u3@localhost PROCEDURE mysqltest_db1.p1() +CREATE TEMPORARY TABLE t4(x INT); +CREATE DEFINER = mysqltest_u3@localhost PROCEDURE mysqltest_db1.p2() +INSERT INTO t4 VALUES (1), (2), (3); +CREATE DEFINER = mysqltest_u3@localhost PROCEDURE mysqltest_db1.p3() +SELECT * FROM t4 ORDER BY x; +# We need separate key cache to test CACHE INDEX and LOAD INDEX. +SET GLOBAL keycache1.key_buffer_size = 128 * 1024; +CREATE TABLE mysqltest_db2.t2_1(a INT); +########################################################################### +# Check that CREATE_TMP_ACL is enough to issue almost any supported +# SQL-statement against temporary tables (loosely follow order in +# sql_command enum). +########################################################################### +connect con1,localhost,mysqltest_u1,,mysqltest_db1; +# +# Variants of CREATE TABLE. +# +CREATE TEMPORARY TABLE t1(a INT); +CREATE TEMPORARY TABLE t2 LIKE t1; +CREATE TEMPORARY TABLE t3(a INT, b INT, PRIMARY KEY (a)); +CREATE TEMPORARY TABLE t4 SELECT * FROM t1; +# Check that we do *not* allow creation of MERGE table with underlying +# temporary table without additional privileges. +CREATE TEMPORARY TABLE t5(a INT) ENGINE = MyISAM; +CREATE TEMPORARY TABLE t6(a INT) ENGINE = MERGE UNION = (t5); +ERROR 42000: SELECT, UPDATE, DELETE command denied to user 'mysqltest_u1'@'localhost' for table 't5' +# Check that we allow creation of MERGE table with no underlying table +# without additional privileges. +CREATE TEMPORARY TABLE t6(a INT) ENGINE = MERGE UNION = (); +# +# SELECT. +# +INSERT INTO t1 VALUES (1), (2), (3); +SELECT * FROM t1 ORDER BY a; +a +1 +2 +3 +# +# CREATE/DROP INDEX. +# +CREATE INDEX idx1 ON t3(b); +DROP INDEX idx1 ON t3; +# +# ALTER TABLE. +# +ALTER TABLE t4 ADD COLUMN b INT; +# Check that we allow altering of MERGE table with no underlying +# without additional privileges. +ALTER TABLE t6 UNION = (); +# Check that we do *not* allow altering of MERGE table with underlying +# temporary table without additional privileges. +ALTER TABLE t6 UNION = (t5); +ERROR 42000: SELECT, UPDATE, DELETE command denied to user 'mysqltest_u1'@'localhost' for table 't5' +# +# Simple INSERT and INSERT ... SELECT. +# +INSERT INTO t1 VALUES (4); +INSERT INTO t2 SELECT a FROM t1; +SELECT * FROM t1 ORDER BY a; +a +1 +2 +3 +4 +SELECT * FROM t2 ORDER BY a; +a +1 +2 +3 +4 +# +# UPDATE and multi-UPDATE. +# +UPDATE t1 SET a = a * 10; +UPDATE t1 SET a = 100 WHERE a = 10; +UPDATE t1, t2 SET t1.a = 200 WHERE t1.a = t2.a * 10 AND t1.a = 20; +SELECT * FROM t1 ORDER BY a; +a +30 +40 +100 +200 +# +# DELETE and multi-DELETE. +# +DELETE FROM t1 WHERE a = 100; +DELETE t1 FROM t1, t2 WHERE t1.a = t2.a * 100 AND t1.a = 200; +SELECT * FROM t1 ORDER BY a; +a +30 +40 +# +# TRUNCATE TABLE. +# +TRUNCATE TABLE t1; +SELECT * FROM t1 ORDER BY a; +a +# +# SHOW COLUMNS/DESCRIBE and SHOW KEYS. +# +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +SHOW KEYS FROM t3; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t3 0 PRIMARY 1 a A 0 NULL NULL BTREE +# +# SHOW CREATE TABLE. +# +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TEMPORARY TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +# +# LOAD DATA INFILE (also SELECT INTO OUTFILE). +# +INSERT INTO t1 VALUES (1), (2), (3); +SELECT a INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug27480.txt' FROM t1 ; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug27480.txt' INTO TABLE t1; +SELECT * FROM t1 ORDER BY a; +a +1 +1 +2 +2 +3 +3 +# +# SET. +# +SET @a := (SELECT COUNT(*) FROM t1); +SELECT @a; +@a +6 +# +# LOCK TABLES. +# +LOCK TABLES t1 READ; +UNLOCK TABLES; +# +# CHECK/REPAIR/ANALYZE/OPTIMIZE and CHECKSUM. +# +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +mysqltest_db1.t1 analyze status OK +CHECK TABLE t1; +Table Op Msg_type Msg_text +mysqltest_db1.t1 check status OK +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +mysqltest_db1.t1 optimize status Table is already up to date +REPAIR TABLE t1; +Table Op Msg_type Msg_text +mysqltest_db1.t1 repair status OK +# +# REPLACE and REPLACE ... SELECT. +# +INSERT INTO t3 VALUES (1, 111), (2, 222), (3, 333); +REPLACE INTO t3 VALUES (1, 1111), (4, 444), (0, 001); +REPLACE INTO t2 SELECT b FROM t3; +SELECT * FROM t2 ORDER BY a; +a +1 +1 +2 +3 +4 +222 +333 +444 +1111 +SELECT * FROM t3 ORDER BY a; +a b +0 1 +1 1111 +2 222 +3 333 +4 444 +# +# CACHE and LOAD INDEX. +# +CACHE INDEX t3 IN keycache1; +Table Op Msg_type Msg_text +mysqltest_db1.t3 assign_to_keycache status OK +LOAD INDEX INTO CACHE t3; +Table Op Msg_type Msg_text +mysqltest_db1.t3 preload_keys status OK +# +# RENAME (doesn't work for temporary tables, thus should fail). +# +RENAME TABLE t3 TO t3_1; +ERROR 42000: INSERT, CREATE command denied to user 'mysqltest_u1'@'localhost' for table 't3_1' +# +# HANDLER OPEN/READ/CLOSE. +# +HANDLER t1 OPEN; +HANDLER t1 READ NEXT; +a +1 +HANDLER t1 CLOSE; +# +# DO. +# +DO (SELECT COUNT(*) FROM t1); +# +# CHECKSUM TABLE. +# +DELETE FROM t1; +CHECKSUM TABLE t1; +Table Checksum +mysqltest_db1.t1 0 +# +# CALL. +# +CALL p0((SELECT COUNT(*) FROM t1)); +i +0 +# +# PREPARE, EXECUTE and DEALLOCATE. +# +PREPARE stmt1 FROM 'SELECT * FROM t1 ORDER BY a'; +PREPARE stmt2 FROM 'SELECT * FROM t2 ORDER BY a'; +EXECUTE stmt1; +a +EXECUTE stmt2; +a +1 +1 +2 +3 +4 +222 +333 +444 +1111 +DEALLOCATE PREPARE stmt1; +DEALLOCATE PREPARE stmt2; +# +# DROP TABLE and DROP TEMPORARY TABLE. +# +DROP TABLE t1; +CREATE TEMPORARY TABLE t1(a INT); +DROP TEMPORARY TABLE t1; +########################################################################### +# - Check that even having all privileges but CREATE_TMP_ACL is not enough +# to create temporary tables. +# - Check that creation/working with temporary tables is possible via +# SUID-stored-routines. +# - Check that even outside of SUID context we can access temporary +# table once it is created. +########################################################################### +connect con2,localhost,mysqltest_u2,,mysqltest_db1; +CREATE TEMPORARY TABLE t2(a INT); +ERROR 42000: Access denied for user 'mysqltest_u2'@'localhost' to database 'mysqltest_db1' +CALL p1(); +CALL p2(); +CALL p3(); +x +1 +2 +3 +# Check that once table is created it can be accessed even +# outside of such a SUID context. +INSERT INTO t4 VALUES (4); +UPDATE t4 SET x = 10 WHERE x = 1; +DELETE FROM t4 WHERE x < 3; +SELECT * FROM t4 ORDER BY x; +x +3 +4 +10 +DROP TEMPORARY TABLE t4; +########################################################################### +# - Check that once table is created it can be accessed from within any +# context, even by user without any privileges on tables. +########################################################################### +connect con3,localhost,mysqltest_u4,,mysqltest_db1; +CALL p1(); +INSERT INTO t4 VALUES (4); +UPDATE t4 SET x = 10 WHERE x = 1; +DELETE FROM t4 WHERE x < 3; +SELECT * FROM t4 ORDER BY x; +x +4 +DROP TEMPORARY TABLE t4; +########################################################################### +# Check special case for MERGE-tables: +# - CREATE_TMP_ACL is required to create a temporary merge table; +# - SELECT_ACL, UPDATE_ACL and DELETE_ACL are required to include +# a temporary table into the underlying-table-list. +########################################################################### +connect con4,localhost,mysqltest_u5,,mysqltest_db1; +CREATE TEMPORARY TABLE t7(a INT); +CREATE TEMPORARY TABLE t8(a INT); +CREATE TEMPORARY TABLE t9(a INT); +CREATE TEMPORARY TABLE t10(a INT) ENGINE = MERGE UNION = (t7, t8); +ALTER TABLE t10 UNION = (t9); +ALTER TABLE t10 UNION = (mysqltest_db2.t2_1); +ERROR 42000: SELECT, UPDATE, DELETE command denied to user 'mysqltest_u5'@'localhost' for table 't2_1' +CREATE TEMPORARY TABLE mysqltest_db2.t2_2(a INT) ENGINE = MERGE UNION = (t7, t8); +ALTER TABLE mysqltest_db2.t2_2 UNION = (t9); +ALTER TABLE mysqltest_db2.t2_2 UNION = (); +DROP TEMPORARY TABLE mysqltest_db2.t2_2; +DROP TEMPORARY TABLE t10; +DROP TEMPORARY TABLE t7; +DROP TEMPORARY TABLE t8; +DROP TEMPORARY TABLE t9; +########################################################################### +# That's all. Cleanup. +########################################################################### +connection default; +# All remaining temporary tables are automatically dropped. +disconnect con1; +disconnect con2; +disconnect con3; +disconnect con4; +SET GLOBAL keycache1.key_buffer_size = 0; +DROP DATABASE mysqltest_db1; +DROP DATABASE mysqltest_db2; +DROP USER mysqltest_u1@localhost; +DROP USER mysqltest_u2@localhost; +DROP USER mysqltest_u3@localhost; +DROP USER mysqltest_u4@localhost; +DROP USER mysqltest_u5@localhost; +set GLOBAL sql_mode=default; |