drop table if exists t1; 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 ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_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 SPECIFIED EDH-RSA-DES-CBC3-SHA 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; 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 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; 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: Wrong usage of DB GRANT and GLOBAL PRIVILEGES select 1; 1 1 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, grant 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; ERROR HY000: Can't drop one or more of the requested users revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost, drop_user3@localhost, drop_user4@localhost; drop user 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@localhost; show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' drop user mysqltest_1@localhost;