grant.result

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

drop table if exists t1;
drop database if exists mysqltest;
SET NAMES binary;
delete from mysql.user where user='mysqltest_1';
delete from mysql.db where user='mysqltest_1';
flush privileges;
grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA";
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
grant delete on mysqltest.* to mysqltest_1@localhost;
select * from mysql.user where user="mysqltest_1";
Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections
localhost	mysqltest_1		N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	SPECIFIED	EDH-RSA-DES-CBC3-SHA			0	0	0	0
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'
GRANT SELECT, DELETE ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
revoke delete on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
grant select on mysqltest.* to mysqltest_1@localhost require NONE;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "MySQL AB";
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'MySQL AB' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA'
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'MySQL AB' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA'
delete from mysql.user where user='mysqltest_1';
flush privileges;
delete from mysql.user where user='mysqltest_1';
flush privileges;
grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10;
select * from mysql.user where user="mysqltest_1";
Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections
localhost	mysqltest_1		N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N					10	0	0	0
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10
grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30;
select * from mysql.user where user="mysqltest_1";
Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections
localhost	mysqltest_1		N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N					10	20	30	0
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30
flush privileges;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30
delete from mysql.user where user='mysqltest_1';
flush privileges;
grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
flush privileges;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION;
flush privileges;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
delete from mysql.user where user='mysqltest_1';
flush privileges;
grant usage on test.* to mysqltest_1@localhost with grant option;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT USAGE ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
GRANT USAGE ON `test`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
delete from mysql.user where user='mysqltest_1';
delete from mysql.db where user='mysqltest_1';
delete from mysql.tables_priv where user='mysqltest_1';
delete from mysql.columns_priv where user='mysqltest_1';
flush privileges;
show grants for mysqltest_1@localhost;
ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host 'localhost'
create table t1 (a int);
GRANT select,update,insert on t1 to mysqltest_1@localhost;
GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT SELECT, SELECT (a), INSERT, INSERT (a), UPDATE, UPDATE (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
table_priv	column_priv
Select,Insert,Update	Select,Insert,Update,References
REVOKE select (a), update on t1 from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT SELECT, INSERT, INSERT (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
GRANT select,references on t1 to mysqltest_1@localhost;
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
table_priv	column_priv
Select,References	References
grant all on test.* to mysqltest_3@localhost with grant option;
revoke all on test.* from mysqltest_3@localhost;
show grants for mysqltest_3@localhost;
Grants for mysqltest_3@localhost
GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost'
GRANT USAGE ON `test`.* TO 'mysqltest_3'@'localhost' WITH GRANT OPTION
revoke grant option on test.* from mysqltest_3@localhost;
show grants for mysqltest_3@localhost;
Grants for mysqltest_3@localhost
GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost'
grant all on test.t1 to mysqltest_2@localhost with grant option;
revoke all on test.t1 from mysqltest_2@localhost;
show grants for mysqltest_2@localhost;
Grants for mysqltest_2@localhost
GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost'
GRANT USAGE ON `test`.`t1` TO 'mysqltest_2'@'localhost' WITH GRANT OPTION
revoke grant option on test.t1 from mysqltest_2@localhost;
show grants for mysqltest_2@localhost;
Grants for mysqltest_2@localhost
GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost'
delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
delete from mysql.columns_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
flush privileges;
drop table t1;
GRANT FILE on mysqltest.*  to mysqltest_1@localhost;
ERROR HY000: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
select 1;
1
1
insert into mysql.user (host, user) values ('localhost', 'test11');
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
insert into mysql.db (host, db, user, select_priv) values 
('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y');
alter table mysql.db order by db asc;
flush privileges;
show grants for test11@localhost;
Grants for test11@localhost
GRANT USAGE ON *.* TO 'test11'@'localhost'
GRANT SELECT ON `ab%`.* TO 'test11'@'localhost'
GRANT SELECT ON `a%`.* TO 'test11'@'localhost'
alter table mysql.db order by db desc;
flush privileges;
show grants for test11@localhost;
Grants for test11@localhost
GRANT USAGE ON *.* TO 'test11'@'localhost'
GRANT SELECT ON `ab%`.* TO 'test11'@'localhost'
GRANT SELECT ON `a%`.* TO 'test11'@'localhost'
delete from mysql.user where user='test11';
delete from mysql.db where user='test11';
create database mysqltest1;
grant usage on mysqltest1.* to test6123 identified by 'magic123';
select host,db,user,select_priv,insert_priv from mysql.db where db="mysqltest1";
host	db	user	select_priv	insert_priv
delete from mysql.user where user='test6123';
drop database mysqltest1;
create table t1 (a int);
grant ALL PRIVILEGES on *.* to drop_user2@localhost with GRANT OPTION;
show grants for drop_user2@localhost;
Grants for drop_user2@localhost
GRANT ALL PRIVILEGES ON *.* TO 'drop_user2'@'localhost' WITH GRANT OPTION
revoke all privileges, grant option from drop_user2@localhost;
drop user drop_user2@localhost;
grant ALL PRIVILEGES on *.* to drop_user@localhost with GRANT OPTION;
grant ALL PRIVILEGES on test.* to drop_user@localhost with GRANT OPTION;
grant select(a) on test.t1 to drop_user@localhost;
show grants for drop_user@localhost;
Grants for drop_user@localhost
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON `test`.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT SELECT (a) ON `test`.`t1` TO 'drop_user'@'localhost'
set sql_mode=ansi_quotes;
show grants for drop_user@localhost;
Grants for drop_user@localhost
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON "test".* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT SELECT (a) ON "test"."t1" TO 'drop_user'@'localhost'
set sql_mode=default;
set sql_quote_show_create=0;
show grants for drop_user@localhost;
Grants for drop_user@localhost
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON test.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT SELECT (a) ON test.t1 TO 'drop_user'@'localhost'
set sql_mode="ansi_quotes";
show grants for drop_user@localhost;
Grants for drop_user@localhost
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON test.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT SELECT (a) ON test.t1 TO 'drop_user'@'localhost'
set sql_quote_show_create=1;
show grants for drop_user@localhost;
Grants for drop_user@localhost
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON "test".* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT SELECT (a) ON "test"."t1" TO 'drop_user'@'localhost'
set sql_mode="";
show grants for drop_user@localhost;
Grants for drop_user@localhost
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON `test`.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT SELECT (a) ON `test`.`t1` TO 'drop_user'@'localhost'
revoke all privileges, grant option from drop_user@localhost;
show grants for drop_user@localhost;
Grants for drop_user@localhost
GRANT USAGE ON *.* TO 'drop_user'@'localhost'
drop user drop_user@localhost;
revoke all privileges, grant option from drop_user@localhost;
ERROR HY000: Can't revoke all privileges for one or more of the requested users
grant select(a) on test.t1 to drop_user1@localhost;
grant select on test.t1 to drop_user2@localhost;
grant select on test.* to drop_user3@localhost;
grant select on *.* to drop_user4@localhost;
drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,
drop_user4@localhost;
revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost,
drop_user3@localhost, drop_user4@localhost;
ERROR HY000: Can't revoke all privileges for one or more of the requested users
drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,
drop_user4@localhost;
ERROR HY000: Operation DROP USER failed for 'drop_user1'@'localhost','drop_user2'@'localhost','drop_user3'@'localhost','drop_user4'@'localhost'
drop table t1;
grant usage on *.* to mysqltest_1@localhost identified by "password";
grant select, update, insert on test.* to mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'mysqltest_1'@'localhost'
drop user mysqltest_1@localhost;
SET NAMES koi8r;
CREATE DATABASE 履;
USE 履;
CREATE TABLE 粤