grant.res

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

grant insert (b), update (b) on grant_test.test to grant_user@localhost
select count(a) from test
3

select count(skr.a) from test as skr
3

select count(a) from test where a > 5
2

insert into test (b) values (5)
insert into test (b) values (a)
update test set b=3 where a > 0
select * from test
Error in execute: select command denied to user: 'grant_user'@'localhost' for column 'b' in table 'test'
select b from test
Error in execute: SELECT command denied to user: 'grant_user'@'localhost' for column 'b' in table 'test'
select a from test where b > 0
Error in execute: SELECT command denied to user: 'grant_user'@'localhost' for column 'b' in table 'test'
insert into test (a) values (10)
Error in execute: INSERT command denied to user: 'grant_user'@'localhost' for column 'a' in table 'test'
insert into test (b) values (b)
Error in execute: SELECT command denied to user: 'grant_user'@'localhost' for column 'b' in table 'test'
insert into test (a,b) values (1,5)
Error in execute: INSERT command denied to user: 'grant_user'@'localhost' for column 'a' in table 'test'
insert into test (b) values (1),(b)
Error in execute: SELECT command denied to user: 'grant_user'@'localhost' for column 'b' in table 'test'
update test set b=3 where b > 0
Error in execute: SELECT command denied to user: 'grant_user'@'localhost' for column 'b' in table 'test'
select Host, Db, User, Table_name, Grantor, Table_priv, Column_priv from mysql.tables_priv
localhost	grant_test	grant_user	test	root@localhost		Select,Insert,Update

select Host, Db, User, Table_name, Column_name, Column_priv from mysql.columns_priv
localhost	grant_test	grant_user	test	b	Insert,Update
localhost	grant_test	grant_user	test	a	Select

revoke select(a), update (b) on grant_test.test from grant_user@localhost
select Host, Db, User, Table_name, Grantor, Table_priv, Column_priv from mysql.tables_priv
localhost	grant_test	grant_user	test	root@localhost		Insert

select Host, Db, User, Table_name, Column_name, Column_priv from mysql.columns_priv
localhost	grant_test	grant_user	test	b	Insert

select count(a) from test
Error in execute: select command denied to user: 'grant_user'@'localhost' for table 'test'
update test set b=4
Error in execute: update command denied to user: 'grant_user'@'localhost' for table 'test'
grant select(a,b), update (a,b) on grant_test.test to grant_user@localhost
select count(a),count(b) from test where a+b > 0
3	3

insert into test (b) values (9)
update test set b=6 where b > 0
flush privileges
select Host, Db, User, Table_name, Grantor, Table_priv, Column_priv from mysql.tables_priv
localhost	grant_test	grant_user	test	root@localhost		Select,Insert,Update

select Host, Db, User, Table_name, Column_name, Column_priv from mysql.columns_priv
localhost	grant_test	grant_user	test	b	Select,Insert,Update
localhost	grant_test	grant_user	test	a	Select,Update

insert into test (a,b) values (12,12)
Error in execute: INSERT command denied to user: 'grant_user'@'localhost' for column 'a' in table 'test'
grant insert on grant_test.* to grant_user@localhost
Connecting grant_user
insert into test (a,b) values (13,13)
revoke select(b) on grant_test.test from grant_user@localhost
select count(a) from test where a+b > 0
Error in execute: SELECT command denied to user: 'grant_user'@'localhost' for column 'b' in table 'test'
update test set b=5 where a=2
grant select on grant_test.test to grant_user@localhost
Connecting grant_user
select count(a) from test where a+b > 0
4

revoke select(b) on grant_test.test from grant_user@localhost
select count(a) from test where a+b > 0
4

revoke select on grant_test.test from grant_user@localhost
Connecting grant_user
select count(a) from test where a+b > 0
Error in execute: select command denied to user: 'grant_user'@'localhost' for table 'test'
grant select(a) on grant_test.test to grant_user@localhost
select count(a) from test where a+b > 0
Error in execute: SELECT command denied to user: 'grant_user'@'localhost' for column 'b' in table 'test'
grant select on *.* to grant_user@localhost
Connecting grant_user
select count(a) from test where a+b > 0
4

revoke select on *.* from grant_user@localhost
grant select(b) on grant_test.test to grant_user@localhost
Connecting grant_user
select count(a) from test where a+b > 0
4

select * from mysql.db where user = 'grant_user'
localhost	grant_test	grant_user	N	Y	N	N	N	N	N	N	N	N	N	N

select Host, Db, User, Table_name, Grantor, Table_priv, Column_priv from mysql.tables_priv where user = 'grant_user'
localhost	grant_test	grant_user	test	root@localhost		Select,Insert,Update

select Host, Db, User, Table_name, Column_name, Column_priv from mysql.columns_priv where user = 'grant_user'
localhost	grant_test	grant_user	test	b	Select,Insert,Update
localhost	grant_test	grant_user	test	a	Select,Update

revoke ALL PRIVILEGES on grant_test.test from grant_user@localhost
select count(a) from test
Error in execute: select command denied to user: 'grant_user'@'localhost' for table 'test'
select * from mysql.user order by hostname
Error in execute: select command denied to user: 'grant_user'@'localhost' for table 'user'
select * from mysql.db where user = 'grant_user'
localhost	grant_test	grant_user	N	Y	N	N	N	N	N	N	N	N	N	N

select Host, Db, User, Table_name, Grantor, Table_priv, Column_priv from mysql.tables_priv where user = 'grant_user'
select Host, Db, User, Table_name, Column_name, Column_priv from mysql.columns_priv where user = 'grant_user'
delete from user where user='grant_user'
delete from db where user='grant_user'
flush privileges
show grants for grant_user@localhost
Error in execute: There is no such grant defined for user 'grant_user' on host 'localhost'
grant ALL PRIVILEGES on grant_test.test to grant_user@localhost identified by 'dummy',  grant_user@127.0.0.1 identified by 'dummy2'
Connecting grant_user
grant SELECT on grant_test.* to grant_user@localhost identified by ''
Connecting grant_user
revoke ALL PRIVILEGES on grant_test.test from grant_user@localhost identified by '', grant_user@127.0.0.1 identified by 'dummy2'
revoke ALL PRIVILEGES on grant_test.* from grant_user@localhost identified by ''
show grants for grant_user@localhost
GRANT USAGE ON *.* TO 'grant_user'@'localhost'

create table grant_test.test3 (a int, b int)
grant SELECT on grant_test.test3 to grant_user@localhost
grant FILE on *.* to grant_user@localhost
insert into grant_test.test3 values (1,1)
Connecting grant_user
select * into outfile '/tmp/mysql-grant.test' from grant_test.test3
revoke SELECT on grant_test.test3 from grant_user@localhost
grant SELECT(a) on grant_test.test3 to grant_user@localhost
select a from grant_test.test3
1

select * from grant_test.test3
Error in execute: select command denied to user: 'grant_user'@'localhost' for column 'b' in table 'test3'
select a,b from grant_test.test3
Error in execute: SELECT command denied to user: 'grant_user'@'localhost' for column 'b' in table 'test3'
select b from grant_test.test3
Error in execute: SELECT command denied to user: 'grant_user'@'localhost' for column 'b' in table 'test3'
revoke SELECT(a) on grant_test.test3 from grant_user@localhost
revoke FILE on *.* from grant_user@localhost
drop table grant_test.test3
create table grant_test.test3 (a int)
Connecting grant_user
Error on connect: Access denied for user: 'grant_user'@'localhost' to database 'grant_test'
grant INSERT on grant_test.test3 to grant_user@localhost
Connecting grant_user
select * into outfile '/tmp/mysql-grant.test' from grant_test.test3
Error in execute: Access denied for user: 'grant_user'@'localhost' (Using password: NO)
grant SELECT on grant_test.test3 to grant_user@localhost
Connecting grant_user
LOCK TABLES grant_test.test3 READ
Error in execute: Access denied for user: 'grant_user'@'localhost' to database 'grant_test'
grant LOCK TABLES on *.* to grant_user@localhost
show grants for grant_user@localhost
GRANT LOCK TABLES ON *.* TO 'grant_user'@'localhost'
GRANT SELECT, INSERT ON `grant_test`.`test3` TO 'grant_user'@'localhost'

select * from mysql.user where user='grant_user'
127.0.0.1	grant_user	*042a99b3d247ae587783f647f2d69496d390aa71eab3	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N					0	0	0
localhost	grant_user		N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	Y	N	N	N					0	0	0

Connecting grant_user
LOCK TABLES grant_test.test3 READ
UNLOCK TABLES
revoke SELECT,INSERT,UPDATE,DELETE on grant_test.test3 from grant_user@localhost
Connecting grant_user
revoke LOCK TABLES on *.* from grant_user@localhost
Connecting grant_user
Error on connect: Access denied for user: 'grant_user'@'localhost' to database 'grant_test'
drop table grant_test.test3
show grants for grant_user@localhost
GRANT USAGE ON *.* TO 'grant_user'@'localhost'

grant all on *.* to grant_user@localhost WITH MAX_QUERIES_PER_HOUR 1 MAX_UPDATES_PER_HOUR 2 MAX_CONNECTIONS_PER_HOUR 3
show grants for grant_user@localhost
GRANT ALL PRIVILEGES ON *.* TO 'grant_user'@'localhost' WITH MAX_QUERIES_PER_HOUR 1 MAX_UPDATES_PER_HOUR 2 MAX_CONNECTIONS_PER_HOUR 3

revoke LOCK TABLES on *.* from grant_user@localhost
flush privileges
show grants for grant_user@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'grant_user'@'localhost' WITH MAX_QUERIES_PER_HOUR 1 MAX_UPDATES_PER_HOUR 2 MAX_CONNECTIONS_PER_HOUR 3

revoke ALL PRIVILEGES on *.* from grant_user@localhost
show grants for grant_user@localhost
GRANT USAGE ON *.* TO 'grant_user'@'localhost' WITH MAX_QUERIES_PER_HOUR 1 MAX_UPDATES_PER_HOUR 2 MAX_CONNECTIONS_PER_HOUR 3

drop database grant_test
delete from user where user='grant_user'
delete from db where user='grant_user'
delete from tables_priv
delete from columns_priv
flush privileges
end of test