grant2.result 6.25 KB
Newer Older
1
SET NAMES binary;
2
drop database if exists mysqltest;
serg@serg.mylan's avatar
serg@serg.mylan committed
3
drop database if exists mysqltest_1;
4 5
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
6 7
delete from mysql.tables_priv where user like 'mysqltest\_%';
delete from mysql.columns_priv where user like 'mysqltest\_%';
8 9 10 11 12
flush privileges;
grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option;
select current_user();
current_user()
mysqltest_1@localhost
13 14
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;
15
ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'my_%'
16 17 18 19 20 21 22 23
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE 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
24
show grants for mysqltest_3@localhost;
25
ERROR 42000: There is no such grant defined for user 'mysqltest_3' on host 'localhost'
26 27 28
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
flush privileges;
serg@serg.mylan's avatar
serg@serg.mylan committed
29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
create database mysqltest_1;
grant all privileges on `mysqltest\_1`.* to mysqltest_1@localhost with grant option;
select current_user();
current_user()
mysqltest_1@localhost
show databases;
Database
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'
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;
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
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));
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: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest'
insert into t1 values (1, 'XXX') on duplicate key update data= 'I can change it!';
ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest'
select * from t1;
id	data
1	I can't change it!
drop table t1;
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
flush privileges;
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
69 70 71 72 73 74 75 76 77 78 79 80 81 82
create table t1 (a int, b int);
grant select (a) on t1 to mysqltest_1@localhost with grant option;
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'
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;
83 84 85 86 87 88 89 90 91 92 93 94 95 96
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';
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
REVOKE ALL ON mysqltest_1.t1 FROM mysqltest_1@'127.0.0.0/255.0.0.0';
97 98
delete from mysql.user where user like 'mysqltest\_1';
flush privileges;
99
drop table mysqltest_1.t1;
100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121
grant all on mysqltest_1.* to mysqltest_1@'127.0.0.1';
select current_user();
current_user()
mysqltest_1@127.0.0.1
set password = password('changed');
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';
select current_user();
current_user()
mysqltest_1@127.0.0.0/255.0.0.0
set password = password('changed');
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;
122
drop database mysqltest_1;
123 124
set password = password("changed");
ERROR 42000: Access denied for user ''@'localhost' to database 'mysql'
125
lock table mysql.user write;
126 127
flush privileges;
grant all on *.* to 'mysqltest_1'@'localhost';
128 129
unlock tables;
lock table mysql.user write;
130 131
set password for 'mysqltest_1'@'localhost' = password('');
revoke all on *.* from 'mysqltest_1'@'localhost';
132 133
unlock tables;
drop user 'mysqltest_1'@'localhost';
134 135 136 137 138 139 140 141 142 143 144 145
insert into mysql.user (user, host) values
('mysqltest_1', 'host1'),
('mysqltest_2', 'host2'),
('mysqltest_3', 'host3'),
('mysqltest_4', 'host4'),
('mysqltest_5', 'host5'),
('mysqltest_6', 'host6'),
('mysqltest_7', 'host7');
flush privileges;
drop user mysqltest_3@host3;
drop user mysqltest_1@host1, mysqltest_2@host2, mysqltest_4@host4,
mysqltest_5@host5, mysqltest_6@host6, mysqltest_7@host7;
146 147 148 149 150 151 152 153 154 155
create database mysqltest_1;
grant select, insert, update on `mysqltest\_1`.* to mysqltest_1@localhost;
set sql_log_off = 1;
ERROR HY000: Access denied; you need the SUPER privilege for this operation
set sql_log_bin = 0;
ERROR HY000: Access denied; you need the SUPER privilege for this operation
delete from mysql.user where user like 'mysqltest\_1';
delete from mysql.db where user like 'mysqltest\_1';
drop database mysqltest_1;
flush privileges;