mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 04:46:15 +01:00
256 lines
6.8 KiB
Text
256 lines
6.8 KiB
Text
set @save_query_cache_size=@@global.query_cache_size;
|
|
set @save_sql_mode=@@global.sql_mode;
|
|
set @save_query_cache_type=@@global.query_cache_type;
|
|
set GLOBAL sql_mode="";
|
|
set LOCAL sql_mode="";
|
|
drop table if exists test.t1,mysqltest.t1,mysqltest.t2;
|
|
drop database if exists mysqltest;
|
|
set GLOBAL query_cache_type=ON;
|
|
set LOCAL query_cache_type=ON;
|
|
set GLOBAL query_cache_size=1355776;
|
|
reset query cache;
|
|
flush status;
|
|
connect root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
|
|
connection root;
|
|
show grants for current_user;
|
|
Grants for root@localhost
|
|
GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION
|
|
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
|
|
show grants;
|
|
Grants for root@localhost
|
|
GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION
|
|
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
|
|
create database if not exists mysqltest;
|
|
create table mysqltest.t1 (a int,b int,c int);
|
|
create table mysqltest.t2 (a int,b int,c int);
|
|
insert into mysqltest.t1 values (1,1,1),(2,2,2);
|
|
insert into mysqltest.t2 values (3,3,3);
|
|
create table test.t1 (a char (10));
|
|
insert into test.t1 values ("test.t1");
|
|
select * from t1;
|
|
a
|
|
test.t1
|
|
connect root2,localhost,root,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK;
|
|
connection root2;
|
|
select * from t1;
|
|
a b c
|
|
1 1 1
|
|
2 2 2
|
|
select a from t1;
|
|
a
|
|
1
|
|
2
|
|
select c from t1;
|
|
c
|
|
1
|
|
2
|
|
select * from t2;
|
|
a b c
|
|
3 3 3
|
|
select * from mysqltest.t1,test.t1;
|
|
a b c a
|
|
1 1 1 test.t1
|
|
2 2 2 test.t1
|
|
show status like "Qcache_queries_in_cache";
|
|
Variable_name Value
|
|
Qcache_queries_in_cache 6
|
|
show status like "Qcache_hits%";
|
|
Variable_name Value
|
|
Qcache_hits 0
|
|
grant SELECT on mysqltest.* to mysqltest_1@localhost;
|
|
grant SELECT on test.t1 to mysqltest_1@localhost;
|
|
grant SELECT on mysqltest.t1 to mysqltest_2@localhost;
|
|
grant SELECT on test.t1 to mysqltest_2@localhost;
|
|
grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost;
|
|
grant SELECT(a) on test.t1 to mysqltest_3@localhost;
|
|
connect user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK;
|
|
connection user1;
|
|
show grants for current_user();
|
|
Grants for mysqltest_1@localhost
|
|
GRANT USAGE ON *.* TO `mysqltest_1`@`localhost`
|
|
GRANT SELECT ON `mysqltest`.* TO `mysqltest_1`@`localhost`
|
|
GRANT SELECT ON `test`.`t1` TO `mysqltest_1`@`localhost`
|
|
show status like "Qcache_queries_in_cache";
|
|
Variable_name Value
|
|
Qcache_queries_in_cache 6
|
|
show status like "Qcache_hits";
|
|
Variable_name Value
|
|
Qcache_hits 0
|
|
show status like "Qcache_not_cached";
|
|
Variable_name Value
|
|
Qcache_not_cached 0
|
|
select "user1";
|
|
user1
|
|
user1
|
|
show status like "Qcache_queries_in_cache";
|
|
Variable_name Value
|
|
Qcache_queries_in_cache 6
|
|
show status like "Qcache_hits";
|
|
Variable_name Value
|
|
Qcache_hits 0
|
|
show status like "Qcache_not_cached";
|
|
Variable_name Value
|
|
Qcache_not_cached 1
|
|
select * from t1;
|
|
a b c
|
|
1 1 1
|
|
2 2 2
|
|
show status like "Qcache_queries_in_cache";
|
|
Variable_name Value
|
|
Qcache_queries_in_cache 6
|
|
show status like "Qcache_hits";
|
|
Variable_name Value
|
|
Qcache_hits 1
|
|
show status like "Qcache_not_cached";
|
|
Variable_name Value
|
|
Qcache_not_cached 1
|
|
select a from t1 ;
|
|
a
|
|
1
|
|
2
|
|
show status like "Qcache_queries_in_cache";
|
|
Variable_name Value
|
|
Qcache_queries_in_cache 6
|
|
show status like "Qcache_hits";
|
|
Variable_name Value
|
|
Qcache_hits 2
|
|
show status like "Qcache_not_cached";
|
|
Variable_name Value
|
|
Qcache_not_cached 1
|
|
select c from t1;
|
|
c
|
|
1
|
|
2
|
|
show status like "Qcache_queries_in_cache";
|
|
Variable_name Value
|
|
Qcache_queries_in_cache 6
|
|
show status like "Qcache_hits";
|
|
Variable_name Value
|
|
Qcache_hits 3
|
|
show status like "Qcache_not_cached";
|
|
Variable_name Value
|
|
Qcache_not_cached 1
|
|
connect unkuser,localhost,unkuser,,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK;
|
|
connection unkuser;
|
|
show grants for current_user();
|
|
Grants for @localhost
|
|
GRANT USAGE ON *.* TO ``@`localhost`
|
|
connect user2,localhost,mysqltest_2,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK;
|
|
connection user2;
|
|
select "user2";
|
|
user2
|
|
user2
|
|
select * from t1;
|
|
a b c
|
|
1 1 1
|
|
2 2 2
|
|
select a from t1;
|
|
a
|
|
1
|
|
2
|
|
select c from t1;
|
|
c
|
|
1
|
|
2
|
|
select * from mysqltest.t1,test.t1;
|
|
a b c a
|
|
1 1 1 test.t1
|
|
2 2 2 test.t1
|
|
select * from t2;
|
|
ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table `mysqltest`.`t2`
|
|
show status like "Qcache_queries_in_cache";
|
|
Variable_name Value
|
|
Qcache_queries_in_cache 6
|
|
show status like "Qcache_hits";
|
|
Variable_name Value
|
|
Qcache_hits 7
|
|
show status like "Qcache_not_cached";
|
|
Variable_name Value
|
|
Qcache_not_cached 2
|
|
connect user3,localhost,mysqltest_3,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK;
|
|
connection user3;
|
|
select "user3";
|
|
user3
|
|
user3
|
|
select * from t1;
|
|
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table `mysqltest`.`t1`
|
|
select a from t1;
|
|
a
|
|
1
|
|
2
|
|
select c from t1;
|
|
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'c' in table 't1'
|
|
select * from t2;
|
|
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table `mysqltest`.`t2`
|
|
select mysqltest.t1.c from test.t1,mysqltest.t1;
|
|
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'c' in table 't1'
|
|
show status like "Qcache_queries_in_cache";
|
|
Variable_name Value
|
|
Qcache_queries_in_cache 6
|
|
show status like "Qcache_hits";
|
|
Variable_name Value
|
|
Qcache_hits 7
|
|
show status like "Qcache_not_cached";
|
|
Variable_name Value
|
|
Qcache_not_cached 3
|
|
connect user4,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK;
|
|
connection user4;
|
|
select "user4";
|
|
user4
|
|
user4
|
|
show grants;
|
|
Grants for mysqltest_1@localhost
|
|
GRANT USAGE ON *.* TO `mysqltest_1`@`localhost`
|
|
GRANT SELECT ON `mysqltest`.* TO `mysqltest_1`@`localhost`
|
|
GRANT SELECT ON `test`.`t1` TO `mysqltest_1`@`localhost`
|
|
select a from t1;
|
|
ERROR 3D000: No database selected
|
|
select * from mysqltest.t1,test.t1;
|
|
a b c a
|
|
1 1 1 test.t1
|
|
2 2 2 test.t1
|
|
select a from mysqltest.t1;
|
|
a
|
|
1
|
|
2
|
|
select a from mysqltest.t1;
|
|
a
|
|
1
|
|
2
|
|
show status like "Qcache_queries_in_cache";
|
|
Variable_name Value
|
|
Qcache_queries_in_cache 8
|
|
show status like "Qcache_hits";
|
|
Variable_name Value
|
|
Qcache_hits 8
|
|
show status like "Qcache_not_cached";
|
|
Variable_name Value
|
|
Qcache_not_cached 4
|
|
connection root;
|
|
disconnect root;
|
|
connection root2;
|
|
disconnect root2;
|
|
connection user1;
|
|
disconnect user1;
|
|
connection user2;
|
|
disconnect user2;
|
|
connection user3;
|
|
disconnect user3;
|
|
connection user4;
|
|
disconnect user4;
|
|
connection unkuser;
|
|
disconnect unkuser;
|
|
connection default;
|
|
set names binary;
|
|
delete from mysql.user where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
|
|
delete from mysql.db where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
|
|
delete from mysql.tables_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
|
|
delete from mysql.columns_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
|
|
flush privileges;
|
|
drop table test.t1,mysqltest.t1,mysqltest.t2;
|
|
drop database mysqltest;
|
|
set GLOBAL query_cache_type=ON;
|
|
set LOCAL query_cache_type=ON;
|
|
set GLOBAL query_cache_size=@save_query_cache_size;
|
|
set GLOBAL sql_mode=@save_sql_mode;
|
|
set GLOBAL query_cache_type=@save_query_cache_type;
|