diff options
Diffstat (limited to 'mysql-test/r/grant.result')
| -rw-r--r-- | mysql-test/r/grant.result | 2734 |
1 files changed, 0 insertions, 2734 deletions
diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result deleted file mode 100644 index 22add627144..00000000000 --- a/mysql-test/r/grant.result +++ /dev/null @@ -1,2734 +0,0 @@ -set GLOBAL sql_mode=""; -set LOCAL sql_mode=""; -SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; -SET GLOBAL log_bin_trust_function_creators = 1; -drop table if exists t1; -drop database if exists mysqltest; -connect master,localhost,root,,; -connection master; -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 localhost -User mysqltest_1 -Password -Select_priv N -Insert_priv N -Update_priv N -Delete_priv N -Create_priv N -Drop_priv N -Reload_priv N -Shutdown_priv N -Process_priv N -File_priv N -Grant_priv N -References_priv N -Index_priv N -Alter_priv N -Show_db_priv N -Super_priv N -Create_tmp_table_priv N -Lock_tables_priv N -Execute_priv N -Repl_slave_priv N -Repl_client_priv N -Create_view_priv N -Show_view_priv N -Create_routine_priv N -Alter_routine_priv N -Create_user_priv N -Event_priv N -Trigger_priv N -Create_tablespace_priv N -Delete_history_priv N -ssl_type SPECIFIED -ssl_cipher EDH-RSA-DES-CBC3-SHA -x509_issuer -x509_subject -max_questions 0 -max_updates 0 -max_connections 0 -max_user_connections 0 -plugin -authentication_string -password_expired N -is_role N -default_role -max_statement_time 0.000000 -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 "Monty Program Ab"; -show grants for mysqltest_1@localhost; -Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'Monty Program 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 'Monty Program 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 max_statement_time 60; -select * from mysql.user where user="mysqltest_1"; -Host localhost -User mysqltest_1 -Password -Select_priv N -Insert_priv N -Update_priv N -Delete_priv N -Create_priv N -Drop_priv N -Reload_priv N -Shutdown_priv N -Process_priv N -File_priv N -Grant_priv N -References_priv N -Index_priv N -Alter_priv N -Show_db_priv N -Super_priv N -Create_tmp_table_priv N -Lock_tables_priv N -Execute_priv N -Repl_slave_priv N -Repl_client_priv N -Create_view_priv N -Show_view_priv N -Create_routine_priv N -Alter_routine_priv N -Create_user_priv N -Event_priv N -Trigger_priv N -Create_tablespace_priv N -Delete_history_priv N -ssl_type -ssl_cipher -x509_issuer -x509_subject -max_questions 10 -max_updates 0 -max_connections 0 -max_user_connections 0 -plugin -authentication_string -password_expired N -is_role N -default_role -max_statement_time 60.000000 -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_STATEMENT_TIME 60.000000 -grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30 max_statement_time 0; -select * from mysql.user where user="mysqltest_1"; -Host localhost -User mysqltest_1 -Password -Select_priv N -Insert_priv N -Update_priv N -Delete_priv N -Create_priv N -Drop_priv N -Reload_priv N -Shutdown_priv N -Process_priv N -File_priv N -Grant_priv N -References_priv N -Index_priv N -Alter_priv N -Show_db_priv N -Super_priv N -Create_tmp_table_priv N -Lock_tables_priv N -Execute_priv N -Repl_slave_priv N -Repl_client_priv N -Create_view_priv N -Show_view_priv N -Create_routine_priv N -Alter_routine_priv N -Create_user_priv N -Event_priv N -Trigger_priv N -Create_tablespace_priv N -Delete_history_priv N -ssl_type -ssl_cipher -x509_issuer -x509_subject -max_questions 10 -max_updates 20 -max_connections 30 -max_user_connections 0 -plugin -authentication_string -password_expired N -is_role N -default_role -max_statement_time 0.000000 -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, DELETE VERSIONING ROWS 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 -Warning 1364 Field 'authentication_string' 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 ÔÁ (ËÏÌ INT); -GRANT SELECT ON ÂÄ.* TO ÀÚÅÒ@localhost; -SHOW GRANTS FOR ÀÚÅÒ@localhost; -Grants for ÀÚÅÒ@localhost -GRANT USAGE ON *.* TO 'ÀÚÅÒ'@'localhost' -GRANT SELECT ON `ÂÄ`.* TO 'ÀÚÅÒ'@'localhost' -REVOKE SELECT ON ÂÄ.* FROM ÀÚÅÒ@localhost; -GRANT SELECT ON ÂÄ.ÔÁ TO ÀÚÅÒ@localhost; -SHOW GRANTS FOR ÀÚÅÒ@localhost; -Grants for ÀÚÅÒ@localhost -GRANT USAGE ON *.* TO 'ÀÚÅÒ'@'localhost' -GRANT SELECT ON `ÂÄ`.`ÔÁÂ` TO 'ÀÚÅÒ'@'localhost' -REVOKE SELECT ON ÂÄ.ÔÁ FROM ÀÚÅÒ@localhost; -GRANT SELECT (ËÏÌ) ON ÂÄ.ÔÁ TO ÀÚÅÒ@localhost; -SHOW GRANTS FOR ÀÚÅÒ@localhost; -Grants for ÀÚÅÒ@localhost -GRANT USAGE ON *.* TO 'ÀÚÅÒ'@'localhost' -GRANT SELECT (ËÏÌ) ON `ÂÄ`.`ÔÁÂ` TO 'ÀÚÅÒ'@'localhost' -REVOKE SELECT (ËÏÌ) ON ÂÄ.ÔÁ FROM ÀÚÅÒ@localhost; -DROP USER ÀÚÅÒ@localhost; -DROP DATABASE ÂÄ; -SET NAMES latin1; -USE test; -CREATE TABLE t1 (a int ); -CREATE TABLE t2 LIKE t1; -CREATE TABLE t3 LIKE t1; -CREATE TABLE t4 LIKE t1; -CREATE TABLE t5 LIKE t1; -CREATE TABLE t6 LIKE t1; -CREATE TABLE t7 LIKE t1; -CREATE TABLE t8 LIKE t1; -CREATE TABLE t9 LIKE t1; -CREATE TABLE t10 LIKE t1; -CREATE DATABASE testdb1; -CREATE DATABASE testdb2; -CREATE DATABASE testdb3; -CREATE DATABASE testdb4; -CREATE DATABASE testdb5; -CREATE DATABASE testdb6; -CREATE DATABASE testdb7; -CREATE DATABASE testdb8; -CREATE DATABASE testdb9; -CREATE DATABASE testdb10; -GRANT ALL ON testdb1.* TO testuser@localhost; -GRANT ALL ON testdb2.* TO testuser@localhost; -GRANT ALL ON testdb3.* TO testuser@localhost; -GRANT ALL ON testdb4.* TO testuser@localhost; -GRANT ALL ON testdb5.* TO testuser@localhost; -GRANT ALL ON testdb6.* TO testuser@localhost; -GRANT ALL ON testdb7.* TO testuser@localhost; -GRANT ALL ON testdb8.* TO testuser@localhost; -GRANT ALL ON testdb9.* TO testuser@localhost; -GRANT ALL ON testdb10.* TO testuser@localhost; -GRANT SELECT ON test.t1 TO testuser@localhost; -GRANT SELECT ON test.t2 TO testuser@localhost; -GRANT SELECT ON test.t3 TO testuser@localhost; -GRANT SELECT ON test.t4 TO testuser@localhost; -GRANT SELECT ON test.t5 TO testuser@localhost; -GRANT SELECT ON test.t6 TO testuser@localhost; -GRANT SELECT ON test.t7 TO testuser@localhost; -GRANT SELECT ON test.t8 TO testuser@localhost; -GRANT SELECT ON test.t9 TO testuser@localhost; -GRANT SELECT ON test.t10 TO testuser@localhost; -GRANT SELECT (a) ON test.t1 TO testuser@localhost; -GRANT SELECT (a) ON test.t2 TO testuser@localhost; -GRANT SELECT (a) ON test.t3 TO testuser@localhost; -GRANT SELECT (a) ON test.t4 TO testuser@localhost; -GRANT SELECT (a) ON test.t5 TO testuser@localhost; -GRANT SELECT (a) ON test.t6 TO testuser@localhost; -GRANT SELECT (a) ON test.t7 TO testuser@localhost; -GRANT SELECT (a) ON test.t8 TO testuser@localhost; -GRANT SELECT (a) ON test.t9 TO testuser@localhost; -GRANT SELECT (a) ON test.t10 TO testuser@localhost; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser@localhost; -SHOW GRANTS FOR testuser@localhost; -Grants for testuser@localhost -GRANT USAGE ON *.* TO 'testuser'@'localhost' -DROP USER testuser@localhost; -DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; -DROP DATABASE testdb1; -DROP DATABASE testdb2; -DROP DATABASE testdb3; -DROP DATABASE testdb4; -DROP DATABASE testdb5; -DROP DATABASE testdb6; -DROP DATABASE testdb7; -DROP DATABASE testdb8; -DROP DATABASE testdb9; -DROP DATABASE testdb10; -create table t1(a int, b int, c int, d int); -grant insert(b), insert(c), insert(d), insert(a) on t1 to grant_user@localhost; -show grants for grant_user@localhost; -Grants for grant_user@localhost -GRANT USAGE ON *.* TO 'grant_user'@'localhost' -GRANT INSERT (a, d, c, b) ON `test`.`t1` TO 'grant_user'@'localhost' -select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv order by Column_name; -Host Db User Table_name Column_name Column_priv -localhost test grant_user t1 a Insert -localhost test grant_user t1 b Insert -localhost test grant_user t1 c Insert -localhost test grant_user t1 d Insert -revoke ALL PRIVILEGES on t1 from grant_user@localhost; -show grants for grant_user@localhost; -Grants for grant_user@localhost -GRANT USAGE ON *.* TO 'grant_user'@'localhost' -select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv; -Host Db User Table_name Column_name Column_priv -drop user grant_user@localhost; -drop table t1; -create database mysqltest_1; -create database mysqltest_2; -create table mysqltest_1.t1 select 1 a, 2 q; -create table mysqltest_1.t2 select 1 b, 2 r; -create table mysqltest_2.t1 select 1 c, 2 s; -create table mysqltest_2.t2 select 1 d, 2 t; -grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost; -grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost; -grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost; -grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost; -connect conn1,localhost,mysqltest_3,,; -connection conn1; -SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES -WHERE GRANTEE = '''mysqltest_3''@''localhost''' - ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE; -GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE -'mysqltest_3'@'localhost' def mysqltest_1 t1 a UPDATE NO -'mysqltest_3'@'localhost' def mysqltest_2 t1 c SELECT NO -'mysqltest_3'@'localhost' def mysqltest_1 t2 b SELECT NO -'mysqltest_3'@'localhost' def mysqltest_2 t2 d UPDATE NO -SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES -WHERE GRANTEE = '''mysqltest_3''@''localhost''' - ORDER BY TABLE_NAME,PRIVILEGE_TYPE; -GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE -SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES -WHERE GRANTEE = '''mysqltest_3''@''localhost''' - ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE; -GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE -SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES -WHERE GRANTEE = '''mysqltest_3''@''localhost''' - ORDER BY TABLE_CATALOG,PRIVILEGE_TYPE; -GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE -'mysqltest_3'@'localhost' def USAGE NO -update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1; -ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for column 'q' in table 't1' -update mysqltest_1.t2, mysqltest_2.t2 set d=20 where d=1; -ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'd' in table 't2' -update mysqltest_1.t1, mysqltest_2.t2 set d=20 where d=1; -ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't1' -update mysqltest_2.t1, mysqltest_1.t2 set c=20 where b=1; -ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1' -update mysqltest_2.t1, mysqltest_2.t2 set d=10 where s=2; -ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 's' in table 't1' -update mysqltest_1.t1, mysqltest_2.t2 set a=10,d=10; -update mysqltest_1.t1, mysqltest_2.t1 set a=20 where c=20; -connection master; -select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2; -a q b r -10 2 1 2 -select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2; -c s d t -1 2 10 2 -revoke all on mysqltest_1.t1 from mysqltest_3@localhost; -revoke all on mysqltest_1.t2 from mysqltest_3@localhost; -revoke all on mysqltest_2.t1 from mysqltest_3@localhost; -revoke all on mysqltest_2.t2 from mysqltest_3@localhost; -grant all on mysqltest_2.* to mysqltest_3@localhost; -grant select on *.* to mysqltest_3@localhost; -grant select on mysqltest_2.t1 to mysqltest_3@localhost; -flush privileges; -disconnect conn1; -connect conn2,localhost,mysqltest_3,,; -connection conn2; -use mysqltest_1; -update mysqltest_2.t1, mysqltest_2.t2 set c=500,d=600; -update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200; -ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1' -use mysqltest_2; -update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200; -ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1' -update mysqltest_2.t1, mysqltest_1.t2 set c=100,b=200; -ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't2' -update mysqltest_1.t1, mysqltest_2.t2 set a=100,d=200; -ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1' -connection master; -select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2; -a q b r -10 2 1 2 -select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2; -c s d t -500 2 600 2 -delete from mysql.user where user='mysqltest_3'; -delete from mysql.db where user="mysqltest_3"; -delete from mysql.tables_priv where user="mysqltest_3"; -delete from mysql.columns_priv where user="mysqltest_3"; -flush privileges; -drop database mysqltest_1; -drop database mysqltest_2; -disconnect conn2; -SHOW PRIVILEGES; -Privilege Context Comment -Alter Tables To alter the table -Alter routine Functions,Procedures To alter or drop stored functions/procedures -Create Databases,Tables,Indexes To create new databases and tables -Create routine Databases To use CREATE FUNCTION/PROCEDURE -Create temporary tables Databases To use CREATE TEMPORARY TABLE -Create view Tables To create new views -Create user Server Admin To create new users -Delete Tables To delete existing rows -Delete versioning rows Tables To delete versioning table historical rows -Drop Databases,Tables To drop databases, tables, and views -Event Server Admin To create, alter, drop and execute events -Execute Functions,Procedures To execute stored routines -File File access on server To read and write files on the server -Grant option Databases,Tables,Functions,Procedures To give to other users those privileges you possess -Index Tables To create or drop indexes -Insert Tables To insert data into tables -Lock tables Databases To use LOCK TABLES (together with SELECT privilege) -Process Server Admin To view the plain text of currently executing queries -Proxy Server Admin To make proxy user possible -References Databases,Tables To have references on tables -Reload Server Admin To reload or refresh tables, logs and privileges -Replication client Server Admin To ask where the slave or master servers are -Replication slave Server Admin To read binary log events from the master -Select Tables To retrieve rows from table -Show databases Server Admin To see all databases with SHOW DATABASES -Show view Tables To see views with SHOW CREATE VIEW -Shutdown Server Admin To shut down the server -Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. -Trigger Tables To use triggers -Create tablespace Server Admin To create/alter/drop tablespaces -Update Tables To update existing rows -Usage Server Admin No privileges - allow connect only -connect root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -connection root; -create database mysqltest; -create table mysqltest.t1 (a int,b int,c int); -grant all on mysqltest.t1 to mysqltest_1@localhost; -connect user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK; -connection user1; -alter table t1 rename t2; -ERROR 42000: INSERT, CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2' -disconnect user1; -connection root; -revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; -delete from mysql.user where user=_binary'mysqltest_1'; -drop database mysqltest; -connection default; -disconnect root; -CREATE USER dummy@localhost; -CREATE DATABASE mysqltest; -CREATE TABLE mysqltest.dummytable (dummyfield INT); -CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable; -GRANT ALL PRIVILEGES ON mysqltest.dummytable TO dummy@localhost; -GRANT ALL PRIVILEGES ON mysqltest.dummyview TO dummy@localhost; -SHOW GRANTS FOR dummy@localhost; -Grants for dummy@localhost -GRANT USAGE ON *.* TO 'dummy'@'localhost' -GRANT ALL PRIVILEGES ON `mysqltest`.`dummyview` TO 'dummy'@'localhost' -GRANT ALL PRIVILEGES ON `mysqltest`.`dummytable` TO 'dummy'@'localhost' -use INFORMATION_SCHEMA; -SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY -PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE -= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; -TABLE_SCHEMA TABLE_NAME PRIVILEGES -mysqltest dummytable ALTER, CREATE, CREATE VIEW, DELETE, DELETE VERSIONING ROWS, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE -mysqltest dummyview ALTER, CREATE, CREATE VIEW, DELETE, DELETE VERSIONING ROWS, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE -FLUSH PRIVILEGES; -SHOW GRANTS FOR dummy@localhost; -Grants for dummy@localhost -GRANT USAGE ON *.* TO 'dummy'@'localhost' -GRANT ALL PRIVILEGES ON `mysqltest`.`dummyview` TO 'dummy'@'localhost' -GRANT ALL PRIVILEGES ON `mysqltest`.`dummytable` TO 'dummy'@'localhost' -SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY -PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE -= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; -TABLE_SCHEMA TABLE_NAME PRIVILEGES -mysqltest dummytable ALTER, CREATE, CREATE VIEW, DELETE, DELETE VERSIONING ROWS, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE -mysqltest dummyview ALTER, CREATE, CREATE VIEW, DELETE, DELETE VERSIONING ROWS, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE -SHOW FIELDS FROM mysql.tables_priv; -Field Type Null Key Default Extra -Host char(60) NO PRI -Db char(64) NO PRI -User char(80) NO PRI -Table_name char(64) NO PRI -Grantor char(141) NO MUL -Timestamp timestamp NO current_timestamp() on update current_timestamp() -Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger','Delete versioning rows') NO -Column_priv set('Select','Insert','Update','References') NO -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost; -DROP USER dummy@localhost; -DROP DATABASE mysqltest; -CREATE USER dummy@localhost; -CREATE DATABASE mysqltest; -CREATE TABLE mysqltest.dummytable (dummyfield INT); -CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable; -GRANT CREATE VIEW ON mysqltest.dummytable TO dummy@localhost; -GRANT CREATE VIEW ON mysqltest.dummyview TO dummy@localhost; -SHOW GRANTS FOR dummy@localhost; -Grants for dummy@localhost -GRANT USAGE ON *.* TO 'dummy'@'localhost' -GRANT CREATE VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost' -GRANT CREATE VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost' -use INFORMATION_SCHEMA; -SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY -PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE -= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; -TABLE_SCHEMA TABLE_NAME PRIVILEGES -mysqltest dummytable CREATE VIEW -mysqltest dummyview CREATE VIEW -FLUSH PRIVILEGES; -SHOW GRANTS FOR dummy@localhost; -Grants for dummy@localhost -GRANT USAGE ON *.* TO 'dummy'@'localhost' -GRANT CREATE VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost' -GRANT CREATE VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost' -SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY -PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE -= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; -TABLE_SCHEMA TABLE_NAME PRIVILEGES -mysqltest dummytable CREATE VIEW -mysqltest dummyview CREATE VIEW -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost; -DROP USER dummy@localhost; -DROP DATABASE mysqltest; -CREATE USER dummy@localhost; -CREATE DATABASE mysqltest; -CREATE TABLE mysqltest.dummytable (dummyfield INT); -CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable; -GRANT SHOW VIEW ON mysqltest.dummytable TO dummy@localhost; -GRANT SHOW VIEW ON mysqltest.dummyview TO dummy@localhost; -SHOW GRANTS FOR dummy@localhost; -Grants for dummy@localhost -GRANT USAGE ON *.* TO 'dummy'@'localhost' -GRANT SHOW VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost' -GRANT SHOW VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost' -use INFORMATION_SCHEMA; -SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY -PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE -= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; -TABLE_SCHEMA TABLE_NAME PRIVILEGES -mysqltest dummytable SHOW VIEW -mysqltest dummyview SHOW VIEW -FLUSH PRIVILEGES; -SHOW GRANTS FOR dummy@localhost; -Grants for dummy@localhost -GRANT USAGE ON *.* TO 'dummy'@'localhost' -GRANT SHOW VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost' -GRANT SHOW VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost' -SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY -PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE -= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; -TABLE_SCHEMA TABLE_NAME PRIVILEGES -mysqltest dummytable SHOW VIEW -mysqltest dummyview SHOW VIEW -use test; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost; -DROP USER dummy@localhost; -DROP DATABASE mysqltest; -connection default; -use mysql; -insert into tables_priv values ('','test_db','mysqltest_1','test_table','test_grantor',CURRENT_TIMESTAMP,'Select','Select'); -flush privileges; -delete from tables_priv where host = '' and user = 'mysqltest_1'; -flush privileges; -use test; -set @user123="non-existent"; -select * from mysql.db where user=@user123; -Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv Delete_history_priv -set names koi8r; -create database ÂÄ; -grant select on ÂÄ.* to root@localhost; -select hex(Db) from mysql.db where Db='ÂÄ'; -hex(Db) -D0B1D0B4 -show grants for root@localhost; -Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION -GRANT SELECT ON `ÂÄ`.* TO 'root'@'localhost' -GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION -flush privileges; -show grants for root@localhost; -Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION -GRANT SELECT ON `ÂÄ`.* TO 'root'@'localhost' -GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION -drop database ÂÄ; -revoke all privileges on ÂÄ.* from root@localhost; -show grants for root@localhost; -Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION -GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION -set names latin1; -create user mysqltest_7@; -set password for mysqltest_7@ = password('systpass'); -show grants for mysqltest_7@; -Grants for mysqltest_7@% -GRANT USAGE ON *.* TO 'mysqltest_7'@'%' IDENTIFIED BY PASSWORD '*2FB071A056F9BB745219D9C876814231DAF46517' -drop user mysqltest_7@; -show grants for mysqltest_7@; -ERROR 42000: There is no such grant defined for user 'mysqltest_7' on host '%' -create database mysqltest; -use mysqltest; -create table t1(f1 int); -GRANT DELETE ON mysqltest.t1 TO mysqltest1@'%'; -GRANT SELECT ON mysqltest.t1 TO mysqltest1@'192.%'; -show grants for mysqltest1@'192.%'; -Grants for mysqltest1@192.% -GRANT USAGE ON *.* TO 'mysqltest1'@'192.%' -GRANT SELECT ON `mysqltest`.`t1` TO 'mysqltest1'@'192.%' -show grants for mysqltest1@'%'; -Grants for mysqltest1@% -GRANT USAGE ON *.* TO 'mysqltest1'@'%' -GRANT DELETE ON `mysqltest`.`t1` TO 'mysqltest1'@'%' -delete from mysql.user where user='mysqltest1'; -delete from mysql.db where user='mysqltest1'; -delete from mysql.tables_priv where user='mysqltest1'; -flush privileges; -drop database mysqltest; -connection master; -create database db27515; -use db27515; -create table t1 (a int); -grant alter on db27515.t1 to user27515@localhost; -grant insert, create on db27515.t2 to user27515@localhost; -connect conn27515, localhost, user27515, , db27515; -connection conn27515; -rename table t1 to t2; -ERROR 42000: DROP command denied to user 'user27515'@'localhost' for table 't1' -disconnect conn27515; -connection master; -revoke all privileges, grant option from user27515@localhost; -drop user user27515@localhost; -drop database db27515; -End of 4.1 tests -use test; -create table t1 (a int); -create table t2 as select * from mysql.user where user=''; -delete from mysql.user where user=''; -flush privileges; -create user mysqltest_8@''; -create user mysqltest_8@host8; -create user mysqltest_8@''; -ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@'%' -create user mysqltest_8; -ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@'%' -create user mysqltest_8@host8; -ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@'host8' -select user, QUOTE(host) from mysql.user where user="mysqltest_8"; -user QUOTE(host) -mysqltest_8 '%' -mysqltest_8 'host8' -Schema privileges -grant select on mysqltest.* to mysqltest_8@''; -show grants for mysqltest_8@''; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'%' -grant select on mysqltest.* to mysqltest_8@; -show grants for mysqltest_8@; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'%' -grant select on mysqltest.* to mysqltest_8; -show grants for mysqltest_8; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'%' -select * from information_schema.schema_privileges -where grantee like "'mysqltest_8'%"; -GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE -'mysqltest_8'@'%' def mysqltest SELECT NO -connect conn3,localhost,mysqltest_8,,; -select * from t1; -a -disconnect conn3; -connection master; -revoke select on mysqltest.* from mysqltest_8@''; -show grants for mysqltest_8@''; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -show grants for mysqltest_8; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -select * from information_schema.schema_privileges -where grantee like "'mysqltest_8'%"; -GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE -flush privileges; -show grants for mysqltest_8@''; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -show grants for mysqltest_8@; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -grant select on mysqltest.* to mysqltest_8@''; -flush privileges; -show grants for mysqltest_8@; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'%' -revoke select on mysqltest.* from mysqltest_8@''; -flush privileges; -Column privileges -grant update (a) on t1 to mysqltest_8@''; -grant update (a) on t1 to mysqltest_8; -show grants for mysqltest_8@''; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' -show grants for mysqltest_8; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' -flush privileges; -show grants for mysqltest_8@''; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' -show grants for mysqltest_8; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' -select * from information_schema.column_privileges; -GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE -'mysqltest_8'@'%' def test t1 a UPDATE NO -connect conn4,localhost,mysqltest_8,,; -select * from t1; -a -disconnect conn4; -connection master; -revoke update (a) on t1 from mysqltest_8@''; -show grants for mysqltest_8@''; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -show grants for mysqltest_8; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -select * from information_schema.column_privileges; -GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE -flush privileges; -show grants for mysqltest_8@''; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -show grants for mysqltest_8; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -Table privileges -grant update on t1 to mysqltest_8@''; -grant update on t1 to mysqltest_8; -show grants for mysqltest_8@''; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%' -show grants for mysqltest_8; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%' -flush privileges; -show grants for mysqltest_8@''; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%' -show grants for mysqltest_8; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%' -select * from information_schema.table_privileges; -GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE -'mysqltest_8'@'%' def test t1 UPDATE NO -connect conn5,localhost,mysqltest_8,,; -select * from t1; -a -disconnect conn5; -connection master; -revoke update on t1 from mysqltest_8@''; -show grants for mysqltest_8@''; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -show grants for mysqltest_8; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -select * from information_schema.table_privileges; -GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE -flush privileges; -show grants for mysqltest_8@''; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -show grants for mysqltest_8; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -"DROP USER" should clear privileges -grant all privileges on mysqltest.* to mysqltest_8@''; -grant select on mysqltest.* to mysqltest_8@''; -grant update on t1 to mysqltest_8@''; -grant update (a) on t1 to mysqltest_8@''; -grant all privileges on mysqltest.* to mysqltest_8; -show grants for mysqltest_8@''; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%' -GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' -show grants for mysqltest_8; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%' -GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' -select * from information_schema.user_privileges -where grantee like "'mysqltest_8'%"; -GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE -'mysqltest_8'@'host8' def USAGE NO -'mysqltest_8'@'%' def USAGE NO -connect conn5,localhost,mysqltest_8,,; -select * from t1; -a -disconnect conn5; -connection master; -flush privileges; -show grants for mysqltest_8@''; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%' -GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' -show grants for mysqltest_8; -Grants for mysqltest_8@% -GRANT USAGE ON *.* TO 'mysqltest_8'@'%' -GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%' -GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%' -drop user mysqltest_8@''; -show grants for mysqltest_8@''; -ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host '%' -connect(localhost,mysqltest_8,,test,MASTER_PORT,MASTER_SOCKET); -connect conn6,localhost,mysqltest_8,,; -ERROR 28000: Access denied for user 'mysqltest_8'@'localhost' (using password: NO) -connection master; -show grants for mysqltest_8; -ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host '%' -drop user mysqltest_8@host8; -show grants for mysqltest_8@host8; -ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host 'host8' -insert into mysql.user select * from t2; -flush privileges; -drop table t2; -drop table t1; -connection master; -CREATE DATABASE mysqltest3; -USE mysqltest3; -CREATE TABLE t_nn (c1 INT); -CREATE VIEW v_nn AS SELECT * FROM t_nn; -CREATE DATABASE mysqltest2; -USE mysqltest2; -CREATE TABLE t_nn (c1 INT); -CREATE VIEW v_nn AS SELECT * FROM t_nn; -CREATE VIEW v_yn AS SELECT * FROM t_nn; -CREATE VIEW v_gy AS SELECT * FROM t_nn; -CREATE VIEW v_ny AS SELECT * FROM t_nn; -CREATE VIEW v_yy AS SELECT * FROM t_nn WHERE c1=55; -GRANT SHOW VIEW ON mysqltest2.v_ny TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; -GRANT SELECT ON mysqltest2.v_yn TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; -GRANT SELECT ON mysqltest2.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; -GRANT SHOW VIEW,SELECT ON mysqltest2.v_yy TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1'; -connect mysqltest_1, localhost, mysqltest_1, mysqltest_1,; -SHOW CREATE VIEW mysqltest2.v_nn; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_nn' -SHOW CREATE TABLE mysqltest2.v_nn; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_nn' -SHOW CREATE VIEW mysqltest2.v_yn; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_yn' -SHOW CREATE TABLE mysqltest2.v_yn; -ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_yn' -SHOW CREATE TABLE mysqltest2.v_ny; -View Create View character_set_client collation_connection -v_ny CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_ny` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` latin1 latin1_swedish_ci -SHOW CREATE VIEW mysqltest2.v_ny; -View Create View character_set_client collation_connection -v_ny CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_ny` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` latin1 latin1_swedish_ci -SHOW CREATE TABLE mysqltest3.t_nn; -ERROR 42000: SHOW command denied to user 'mysqltest_1'@'localhost' for table 't_nn' -SHOW CREATE VIEW mysqltest3.t_nn; -ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 't_nn' -SHOW CREATE VIEW mysqltest3.v_nn; -ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v_nn' -SHOW CREATE TABLE mysqltest3.v_nn; -ERROR 42000: SHOW command denied to user 'mysqltest_1'@'localhost' for table 'v_nn' -SHOW CREATE TABLE mysqltest2.t_nn; -Table Create Table -t_nn CREATE TABLE `t_nn` ( - `c1` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -SHOW CREATE VIEW mysqltest2.t_nn; -ERROR HY000: 'mysqltest2.t_nn' is not of type 'VIEW' -SHOW CREATE VIEW mysqltest2.v_yy; -View Create View character_set_client collation_connection -v_yy CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_yy` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` where `mysqltest2`.`t_nn`.`c1` = 55 latin1 latin1_swedish_ci -SHOW CREATE TABLE mysqltest2.v_yy; -View Create View character_set_client collation_connection -v_yy CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_yy` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` where `mysqltest2`.`t_nn`.`c1` = 55 latin1 latin1_swedish_ci -connection master; -SHOW CREATE TABLE mysqltest2.v_nn; -View Create View character_set_client collation_connection -v_nn CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_nn` AS select `t_nn`.`c1` AS `c1` from `t_nn` latin1 latin1_swedish_ci -SHOW CREATE VIEW mysqltest2.v_nn; -View Create View character_set_client collation_connection -v_nn CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_nn` AS select `t_nn`.`c1` AS `c1` from `t_nn` latin1 latin1_swedish_ci -SHOW CREATE TABLE mysqltest2.t_nn; -Table Create Table -t_nn CREATE TABLE `t_nn` ( - `c1` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -SHOW CREATE VIEW mysqltest2.t_nn; -ERROR HY000: 'mysqltest2.t_nn' is not of type 'VIEW' -DROP VIEW mysqltest2.v_nn; -DROP VIEW mysqltest2.v_yn; -DROP VIEW mysqltest2.v_ny; -DROP VIEW mysqltest2.v_yy; -DROP TABLE mysqltest2.t_nn; -DROP DATABASE mysqltest2; -DROP VIEW mysqltest3.v_nn; -DROP TABLE mysqltest3.t_nn; -DROP DATABASE mysqltest3; -disconnect mysqltest_1; -REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost'; -DROP USER 'mysqltest_1'@'localhost'; -USE test; -connection default; -disconnect master; -create user longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789; -ERROR HY000: String 'longer_than_80_4567890123456789012345678901234567890123456789012345678' is too long for user name (should be no longer than 80) -CREATE DATABASE mysqltest1; -CREATE TABLE mysqltest1.t1 ( -int_field INTEGER UNSIGNED NOT NULL, -char_field CHAR(10), -INDEX(`int_field`) -); -CREATE TABLE mysqltest1.t2 (int_field INT); -"Now check that we require equivalent grants for " -"RENAME TABLE and ALTER TABLE" -CREATE USER mysqltest_1@localhost; -GRANT SELECT ON mysqltest1.t1 TO mysqltest_1@localhost; -connect conn42,localhost,mysqltest_1,,mysqltest1; -SELECT USER(); -USER() -mysqltest_1@localhost -SHOW GRANTS; -Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost' -RENAME TABLE t1 TO t2; -ERROR 42000: DROP, ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1' -ALTER TABLE t1 RENAME TO t2; -ERROR 42000: DROP, ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1' -disconnect conn42; -connection default; -GRANT DROP ON mysqltest1.t1 TO mysqltest_1@localhost; -connect conn42,localhost,mysqltest_1,,mysqltest1; -RENAME TABLE t1 TO t2; -ERROR 42000: ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1' -ALTER TABLE t1 RENAME TO t2; -ERROR 42000: ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1' -disconnect conn42; -connection default; -GRANT ALTER ON mysqltest1.t1 TO mysqltest_1@localhost; -connect conn42,localhost,mysqltest_1,,mysqltest1; -SHOW GRANTS; -Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, DROP, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost' -RENAME TABLE t1 TO t2; -ERROR 42000: INSERT, CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2' -ALTER TABLE t1 RENAME TO t2; -ERROR 42000: INSERT, CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2' -disconnect conn42; -connection default; -GRANT INSERT, CREATE ON mysqltest1.t1 TO mysqltest_1@localhost; -connect conn42,localhost,mysqltest_1,,mysqltest1; -SHOW GRANTS; -Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost' -disconnect conn42; -connection default; -GRANT INSERT, SELECT, CREATE, ALTER, DROP ON mysqltest1.t2 TO mysqltest_1@localhost; -DROP TABLE mysqltest1.t2; -connect conn42,localhost,mysqltest_1,,mysqltest1; -SHOW GRANTS; -Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost' -GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t2` TO 'mysqltest_1'@'localhost' -RENAME TABLE t1 TO t2; -RENAME TABLE t2 TO t1; -ALTER TABLE t1 RENAME TO t2; -ALTER TABLE t2 RENAME TO t1; -disconnect conn42; -connection default; -REVOKE DROP, INSERT ON mysqltest1.t1 FROM mysqltest_1@localhost; -REVOKE DROP, INSERT ON mysqltest1.t2 FROM mysqltest_1@localhost; -connect conn42,localhost,mysqltest_1,,mysqltest1; -SHOW GRANTS; -Grants for mysqltest_1@localhost -GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, CREATE, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost' -GRANT SELECT, CREATE, ALTER ON `mysqltest1`.`t2` TO 'mysqltest_1'@'localhost' -RENAME TABLE t1 TO t2; -ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 't1' -ALTER TABLE t1 RENAME TO t2; -ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 't1' -disconnect conn42; -connection default; -DROP USER mysqltest_1@localhost; -DROP DATABASE mysqltest1; -USE test; -GRANT CREATE ON mysqltest.* TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; -ERROR HY000: String 'longer_than_80_4567890123456789012345678901234567890123456789012345678' is too long for user name (should be no longer than 80) -GRANT CREATE ON mysqltest.* TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; -ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60) -REVOKE CREATE ON mysqltest.* FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; -ERROR HY000: String 'longer_than_80_4567890123456789012345678901234567890123456789012345678' is too long for user name (should be no longer than 80) -REVOKE CREATE ON mysqltest.* FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; -ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60) -GRANT CREATE ON t1 TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; -ERROR HY000: String 'longer_than_80_4567890123456789012345678901234567890123456789012345678' is too long for user name (should be no longer than 80) -GRANT CREATE ON t1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; -ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60) -REVOKE CREATE ON t1 FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; -ERROR HY000: String 'longer_than_80_4567890123456789012345678901234567890123456789012345678' is too long for user name (should be no longer than 80) -REVOKE CREATE ON t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; -ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60) -GRANT EXECUTE ON PROCEDURE p1 TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; -ERROR HY000: String 'longer_than_80_4567890123456789012345678901234567890123456789012345678' is too long for user name (should be no longer than 80) -GRANT EXECUTE ON PROCEDURE p1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; -ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60) -REVOKE EXECUTE ON PROCEDURE p1 FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; -ERROR HY000: String 'longer_than_80_4567890123456789012345678901234567890123456789012345678' is too long for user name (should be no longer than 80) -REVOKE EXECUTE ON PROCEDURE t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; -ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60) -CREATE USER bug23556@localhost; -CREATE DATABASE bug23556; -GRANT SELECT ON bug23556.* TO bug23556@localhost; -connect bug23556,localhost,bug23556,,bug23556; -connection default; -USE bug23556; -CREATE TABLE t1 (a INT PRIMARY KEY); -INSERT INTO t1 VALUES (1),(2),(3),(4),(5); -GRANT DELETE ON t1 TO bug23556@localhost; -connection bug23556; -USE bug23556; -TRUNCATE t1; -ERROR 42000: DROP command denied to user 'bug23556'@'localhost' for table 't1' -connection default; -USE bug23556; -REVOKE DELETE ON t1 FROM bug23556@localhost; -GRANT DROP ON t1 TO bug23556@localhost; -connection bug23556; -USE bug23556; -TRUNCATE t1; -connection default; -USE bug23556; -DROP TABLE t1; -USE test; -DROP DATABASE bug23556; -DROP USER bug23556@localhost; -connection default; -disconnect bug23556; -connect con1, localhost, root,,*NO-ONE*; -connection con1; -GRANT PROCESS ON * TO user@localhost; -ERROR 3D000: No database selected -disconnect con1; -connection default; -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; -DROP DATABASE IF EXISTS mysqltest3; -DROP DATABASE IF EXISTS mysqltest4; -CREATE DATABASE mysqltest1; -CREATE DATABASE mysqltest2; -CREATE DATABASE mysqltest3; -CREATE DATABASE mysqltest4; -CREATE PROCEDURE mysqltest1.p_def() SQL SECURITY DEFINER -SELECT 1; -CREATE PROCEDURE mysqltest2.p_inv() SQL SECURITY INVOKER -SELECT 1; -CREATE FUNCTION mysqltest3.f_def() RETURNS INT SQL SECURITY DEFINER -RETURN 1; -CREATE FUNCTION mysqltest4.f_inv() RETURNS INT SQL SECURITY INVOKER -RETURN 1; -GRANT EXECUTE ON PROCEDURE mysqltest1.p_def TO mysqltest_1@localhost; -GRANT EXECUTE ON PROCEDURE mysqltest2.p_inv TO mysqltest_1@localhost; -GRANT EXECUTE ON FUNCTION mysqltest3.f_def TO mysqltest_1@localhost; -GRANT EXECUTE ON FUNCTION mysqltest4.f_inv TO mysqltest_1@localhost; -GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost; -connect bug9504_con1,localhost,mysqltest_1,,; -use mysqltest1; -use mysqltest2; -use mysqltest3; -use mysqltest4; -use test; -CALL mysqltest1.p_def(); -1 -1 -CALL mysqltest2.p_inv(); -1 -1 -SELECT mysqltest3.f_def(); -mysqltest3.f_def() -1 -SELECT mysqltest4.f_inv(); -mysqltest4.f_inv() -1 -connection default; -disconnect bug9504_con1; -DROP DATABASE mysqltest1; -DROP DATABASE mysqltest2; -DROP DATABASE mysqltest3; -DROP DATABASE mysqltest4; -DROP USER mysqltest_1@localhost; -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; -CREATE DATABASE mysqltest1; -CREATE DATABASE mysqltest2; -GRANT ALL PRIVILEGES ON mysqltest1.* TO mysqltest_1@localhost; -GRANT SELECT ON mysqltest2.* TO mysqltest_1@localhost; -CREATE PROCEDURE mysqltest1.p1() SQL SECURITY INVOKER -SELECT 1; -connect bug27337_con1,localhost,mysqltest_1,,mysqltest2; -CREATE TABLE t1(c INT); -ERROR 42000: CREATE command denied to user 'mysqltest_1'@'localhost' for table 't1' -CALL mysqltest1.p1(); -1 -1 -CREATE TABLE t1(c INT); -ERROR 42000: CREATE command denied to user 'mysqltest_1'@'localhost' for table 't1' -disconnect bug27337_con1; -connect bug27337_con2,localhost,mysqltest_1,,mysqltest2; -CREATE TABLE t1(c INT); -ERROR 42000: CREATE command denied to user 'mysqltest_1'@'localhost' for table 't1' -SHOW TABLES; -Tables_in_mysqltest2 -connection default; -disconnect bug27337_con2; -DROP DATABASE mysqltest1; -DROP DATABASE mysqltest2; -DROP USER mysqltest_1@localhost; -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; -CREATE DATABASE mysqltest1; -CREATE DATABASE mysqltest2; -CREATE TABLE mysqltest1.t1(c INT); -CREATE TABLE mysqltest2.t2(c INT); -GRANT SELECT ON mysqltest1.t1 TO mysqltest_1@localhost; -GRANT SELECT ON mysqltest2.t2 TO mysqltest_2@localhost; -connect bug27337_con1,localhost,mysqltest_1,,mysqltest1; -SHOW TABLES FROM mysqltest1; -Tables_in_mysqltest1 -t1 -PREPARE stmt1 FROM 'SHOW TABLES FROM mysqltest1'; -EXECUTE stmt1; -Tables_in_mysqltest1 -t1 -connect bug27337_con2,localhost,mysqltest_2,,mysqltest2; -SHOW COLUMNS FROM mysqltest2.t2; -Field Type Null Key Default Extra -c int(11) YES NULL -PREPARE stmt2 FROM 'SHOW COLUMNS FROM mysqltest2.t2'; -EXECUTE stmt2; -Field Type Null Key Default Extra -c int(11) YES NULL -connection default; -REVOKE SELECT ON mysqltest1.t1 FROM mysqltest_1@localhost; -REVOKE SELECT ON mysqltest2.t2 FROM mysqltest_2@localhost; -connection bug27337_con1; -SHOW TABLES FROM mysqltest1; -ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest1' -EXECUTE stmt1; -ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest1' -connection bug27337_con2; -SHOW COLUMNS FROM mysqltest2.t2; -ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 't2' -EXECUTE stmt2; -ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 't2' -connection default; -disconnect bug27337_con1; -disconnect bug27337_con2; -DROP DATABASE mysqltest1; -DROP DATABASE mysqltest2; -DROP USER mysqltest_1@localhost; -DROP USER mysqltest_2@localhost; -USE test; -CREATE TABLE t1 (f1 int, f2 int); -INSERT INTO t1 VALUES(1,1), (2,2); -CREATE DATABASE db27878; -GRANT UPDATE(f1) ON t1 TO 'mysqltest_1'@'localhost'; -GRANT SELECT ON `test`.* TO 'mysqltest_1'@'localhost'; -GRANT ALL ON db27878.* TO 'mysqltest_1'@'localhost'; -USE db27878; -CREATE SQL SECURITY INVOKER VIEW db27878.v1 AS SELECT * FROM test.t1; -connect user1,localhost,mysqltest_1,,test; -connection user1; -USE db27878; -UPDATE v1 SET f2 = 4; -ERROR HY000: View 'db27878.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -SELECT * FROM test.t1; -f1 f2 -1 1 -2 2 -disconnect user1; -connection default; -REVOKE UPDATE (f1) ON `test`.`t1` FROM 'mysqltest_1'@'localhost'; -REVOKE SELECT ON `test`.* FROM 'mysqltest_1'@'localhost'; -REVOKE ALL ON db27878.* FROM 'mysqltest_1'@'localhost'; -DROP USER mysqltest_1@localhost; -DROP DATABASE db27878; -USE test; -DROP TABLE t1; -# -# Bug#33275 Server crash when creating temporary table mysql.user -# -CREATE TEMPORARY TABLE mysql.user (id INT); -FLUSH PRIVILEGES; -DROP TABLE mysql.user; -drop table if exists test; -Warnings: -Note 1051 Unknown table 'test.test' -drop function if exists test_function; -Warnings: -Note 1305 FUNCTION test.test_function does not exist -drop view if exists v1; -Warnings: -Note 4092 Unknown VIEW: 'test.v1' -create table test (col1 varchar(30)); -create function test_function() returns varchar(30) -begin -declare tmp varchar(30); -select col1 from test limit 1 into tmp; -return '1'; -end| -create view v1 as select test.* from test where test.col1=test_function(); -grant update (col1) on v1 to 'greg'@'localhost'; -drop user 'greg'@'localhost'; -drop view v1; -drop table test; -drop function test_function; -SELECT CURRENT_USER(); -CURRENT_USER() -root@localhost -SET PASSWORD FOR CURRENT_USER() = PASSWORD("admin"); -SET PASSWORD FOR CURRENT_USER() = PASSWORD(""); - -# Bug#57952 - -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; -CREATE DATABASE mysqltest1; -CREATE DATABASE mysqltest2; -use mysqltest1; -CREATE TABLE t1(a INT, b INT); -INSERT INTO t1 VALUES (1, 1); -CREATE TABLE t2(a INT); -INSERT INTO t2 VALUES (2); -CREATE TABLE mysqltest2.t3(a INT); -INSERT INTO mysqltest2.t3 VALUES (4); -CREATE USER testuser@localhost; -GRANT CREATE ROUTINE, EXECUTE ON mysqltest1.* TO testuser@localhost; -GRANT SELECT(b) ON t1 TO testuser@localhost; -GRANT SELECT ON t2 TO testuser@localhost; -GRANT SELECT ON mysqltest2.* TO testuser@localhost; -connect bug57952_con1,localhost,testuser,,mysqltest1; -PREPARE s1 FROM 'SELECT b FROM t1'; -PREPARE s2 FROM 'SELECT a FROM t2'; -PREPARE s3 FROM 'SHOW TABLES FROM mysqltest2'; -CREATE PROCEDURE p1() SELECT b FROM t1; -CREATE PROCEDURE p2() SELECT a FROM t2; -CREATE PROCEDURE p3() SHOW TABLES FROM mysqltest2; -CALL p1; -b -1 -CALL p2; -a -2 -CALL p3; -Tables_in_mysqltest2 -t3 -connection default; -REVOKE SELECT ON t1 FROM testuser@localhost; -GRANT SELECT(a) ON t1 TO testuser@localhost; -REVOKE SELECT ON t2 FROM testuser@localhost; -REVOKE SELECT ON mysqltest2.* FROM testuser@localhost; -connection bug57952_con1; -# - Check column-level privileges... -EXECUTE s1; -ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for column 'b' in table 't1' -SELECT b FROM t1; -ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for column 'b' in table 't1' -EXECUTE s1; -ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for column 'b' in table 't1' -CALL p1; -ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for column 'b' in table 't1' -# - Check table-level privileges... -SELECT a FROM t2; -ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table 't2' -EXECUTE s2; -ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table 't2' -CALL p2; -ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table 't2' -# - Check database-level privileges... -SHOW TABLES FROM mysqltest2; -ERROR 42000: Access denied for user 'testuser'@'localhost' to database 'mysqltest2' -EXECUTE s3; -ERROR 42000: Access denied for user 'testuser'@'localhost' to database 'mysqltest2' -CALL p3; -ERROR 42000: Access denied for user 'testuser'@'localhost' to database 'mysqltest2' -connection default; -disconnect bug57952_con1; -DROP DATABASE mysqltest1; -DROP DATABASE mysqltest2; -DROP USER testuser@localhost; -use test; - -# -# Test for bug #36544 "DROP USER does not remove stored function -# privileges". -# -create database mysqltest1; -create function mysqltest1.f1() returns int return 0; -create procedure mysqltest1.p1() begin end; -# -# 1) Check that DROP USER properly removes privileges on both -# stored procedures and functions. -# -create user mysqluser1@localhost; -grant execute on function mysqltest1.f1 to mysqluser1@localhost; -grant execute on procedure mysqltest1.p1 to mysqluser1@localhost; -# Quick test that granted privileges are properly reflected -# in privilege tables and in in-memory structures. -show grants for mysqluser1@localhost; -Grants for mysqluser1@localhost -GRANT USAGE ON *.* TO 'mysqluser1'@'localhost' -GRANT EXECUTE ON PROCEDURE `mysqltest1`.`p1` TO 'mysqluser1'@'localhost' -GRANT EXECUTE ON FUNCTION `mysqltest1`.`f1` TO 'mysqluser1'@'localhost' -select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; -db routine_name routine_type proc_priv -mysqltest1 f1 FUNCTION Execute -mysqltest1 p1 PROCEDURE Execute -connect bug36544_con1,localhost,mysqluser1,,; -call mysqltest1.p1(); -select mysqltest1.f1(); -mysqltest1.f1() -0 -connection default; -drop user mysqluser1@localhost; -# -# Test that dropping of user is properly reflected in -# both privilege tables and in in-memory structures. -# -connection bug36544_con1; -# The connection cold be alive but should not be able to -# access to any of the stored routines. -call mysqltest1.p1(); -ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.p1' -select mysqltest1.f1(); -ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.f1' -disconnect bug36544_con1; -connection default; -# -# Now create user with the same name and check that he -# has not inherited privileges. -create user mysqluser1@localhost; -show grants for mysqluser1@localhost; -Grants for mysqluser1@localhost -GRANT USAGE ON *.* TO 'mysqluser1'@'localhost' -select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; -db routine_name routine_type proc_priv -connect bug36544_con2,localhost,mysqluser1,,; -# Newly created user should not be able to access any of the routines. -call mysqltest1.p1(); -ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.p1' -select mysqltest1.f1(); -ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.f1' -connection default; -# -# 2) Check that RENAME USER properly updates privileges on both -# stored procedures and functions. -# -grant execute on function mysqltest1.f1 to mysqluser1@localhost; -grant execute on procedure mysqltest1.p1 to mysqluser1@localhost; -# -# Create one more user to make in-memory hashes non-trivial. -# User names 'mysqluser11' and 'mysqluser10' were selected -# to trigger bug discovered during code inspection. -create user mysqluser11@localhost; -grant execute on function mysqltest1.f1 to mysqluser11@localhost; -grant execute on procedure mysqltest1.p1 to mysqluser11@localhost; -# Also create a couple of tables to test for another bug -# discovered during code inspection (again table names were -# chosen especially to trigger the bug). -create table mysqltest1.t11 (i int); -create table mysqltest1.t22 (i int); -grant select on mysqltest1.t22 to mysqluser1@localhost; -grant select on mysqltest1.t11 to mysqluser1@localhost; -# Quick test that granted privileges are properly reflected -# in privilege tables and in in-memory structures. -show grants for mysqluser1@localhost; -Grants for mysqluser1@localhost -GRANT USAGE ON *.* TO 'mysqluser1'@'localhost' -GRANT SELECT ON `mysqltest1`.`t11` TO 'mysqluser1'@'localhost' -GRANT SELECT ON `mysqltest1`.`t22` TO 'mysqluser1'@'localhost' -GRANT EXECUTE ON PROCEDURE `mysqltest1`.`p1` TO 'mysqluser1'@'localhost' -GRANT EXECUTE ON FUNCTION `mysqltest1`.`f1` TO 'mysqluser1'@'localhost' -select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; -db routine_name routine_type proc_priv -mysqltest1 f1 FUNCTION Execute -mysqltest1 p1 PROCEDURE Execute -select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost'; -db table_name table_priv -mysqltest1 t11 Select -mysqltest1 t22 Select -connection bug36544_con2; -call mysqltest1.p1(); -select mysqltest1.f1(); -mysqltest1.f1() -0 -select * from mysqltest1.t11; -i -select * from mysqltest1.t22; -i -connection default; -rename user mysqluser1@localhost to mysqluser10@localhost; -# -# Test that there are no privileges left for mysqluser1. -# -connection bug36544_con2; -# The connection cold be alive but should not be able to -# access to any of the stored routines or tables. -call mysqltest1.p1(); -ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.p1' -select mysqltest1.f1(); -ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.f1' -select * from mysqltest1.t11; -ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 't11' -select * from mysqltest1.t22; -ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 't22' -disconnect bug36544_con2; -connection default; -# -# Now create user with the old name and check that he -# has not inherited privileges. -create user mysqluser1@localhost; -show grants for mysqluser1@localhost; -Grants for mysqluser1@localhost -GRANT USAGE ON *.* TO 'mysqluser1'@'localhost' -select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser1' and host='localhost'; -db routine_name routine_type proc_priv -select db, table_name, table_priv from mysql.tables_priv where user='mysqluser1' and host='localhost'; -db table_name table_priv -connect bug36544_con3,localhost,mysqluser1,,; -# Newly created user should not be able to access to any of the -# stored routines or tables. -call mysqltest1.p1(); -ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.p1' -select mysqltest1.f1(); -ERROR 42000: execute command denied to user 'mysqluser1'@'localhost' for routine 'mysqltest1.f1' -select * from mysqltest1.t11; -ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 't11' -select * from mysqltest1.t22; -ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table 't22' -disconnect bug36544_con3; -connection default; -# -# Now check that privileges became associated with a new user -# name - mysqluser10. -# -show grants for mysqluser10@localhost; -Grants for mysqluser10@localhost -GRANT USAGE ON *.* TO 'mysqluser10'@'localhost' -GRANT SELECT ON `mysqltest1`.`t11` TO 'mysqluser10'@'localhost' -GRANT SELECT ON `mysqltest1`.`t22` TO 'mysqluser10'@'localhost' -GRANT EXECUTE ON PROCEDURE `mysqltest1`.`p1` TO 'mysqluser10'@'localhost' -GRANT EXECUTE ON FUNCTION `mysqltest1`.`f1` TO 'mysqluser10'@'localhost' -select db, routine_name, routine_type, proc_priv from mysql.procs_priv where user='mysqluser10' and host='localhost'; -db routine_name routine_type proc_priv -mysqltest1 f1 FUNCTION Execute -mysqltest1 p1 PROCEDURE Execute -select db, table_name, table_priv from mysql.tables_priv where user='mysqluser10' and host='localhost'; -db table_name table_priv -mysqltest1 t11 Select -mysqltest1 t22 Select -connect bug36544_con4,localhost,mysqluser10,,; -call mysqltest1.p1(); -select mysqltest1.f1(); -mysqltest1.f1() -0 -select * from mysqltest1.t11; -i -select * from mysqltest1.t22; -i -disconnect bug36544_con4; -connection default; -# -# Clean-up. -drop user mysqluser1@localhost; -drop user mysqluser10@localhost; -drop user mysqluser11@localhost; -drop database mysqltest1; -End of 5.0 tests -set names utf8; -grant select on test.* to юзер_юзер@localhost; -user() -юзер_юзер@localhost -revoke all on test.* from юзер_юзер@localhost; -drop user юзер_юзер@localhost; -grant select on test.* to очень_длинный_юзер890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890@localhost; -ERROR HY000: String 'очень_длинный_юзер890123456789012345678901234567890123' is too long for user name (should be no longer than 80) -set names default; -create database mysqltest; -use mysqltest; -grant create on mysqltest.* to mysqltest@localhost; -create table t1 (i INT); -connect user1,localhost,mysqltest,,mysqltest; -connection user1; -insert into t1 values (1); -ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table 't1' -create table t2 (i INT); -create table t4 (i INT); -connection default; -grant select, insert on mysqltest.t2 to mysqltest@localhost; -grant insert on mysqltest.t4 to mysqltest@localhost; -grant create, insert on mysqltest.t5 to mysqltest@localhost; -grant create, insert on mysqltest.t6 to mysqltest@localhost; -flush privileges; -connection user1; -insert into t2 values (1); -create table if not exists t1 select * from t2; -ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table 't1' -create table if not exists t3 select * from t2; -ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table 't3' -create table if not exists t4 select * from t2; -Warnings: -Note 1050 Table 't4' already exists -create table if not exists t5 select * from t2; -create table t6 select * from t2; -create table t7 select * from t2; -ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table 't7' -create table t4 select * from t2; -ERROR 42S01: Table 't4' already exists -create table t1 select * from t2; -ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table 't1' -connection default; -drop table t1,t2,t4,t5,t6; -revoke create on mysqltest.* from mysqltest@localhost; -revoke select, insert on mysqltest.t2 from mysqltest@localhost; -revoke insert on mysqltest.t4 from mysqltest@localhost; -revoke create, insert on mysqltest.t5 from mysqltest@localhost; -revoke create, insert on mysqltest.t6 from mysqltest@localhost; -drop user mysqltest@localhost; -disconnect user1; -drop database mysqltest; -use test; -FLUSH PRIVILEGES without procs_priv table. -RENAME TABLE mysql.procs_priv TO mysql.procs_gone; -FLUSH PRIVILEGES; -Assigning privileges without procs_priv table. -CREATE DATABASE mysqltest1; -CREATE PROCEDURE mysqltest1.test() SQL SECURITY DEFINER -SELECT 1; -CREATE FUNCTION mysqltest1.test() RETURNS INT RETURN 1; -GRANT EXECUTE ON FUNCTION mysqltest1.test TO mysqltest_1@localhost; -ERROR 42S02: Table 'mysql.procs_priv' doesn't exist -GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost; -CALL mysqltest1.test(); -1 -1 -DROP DATABASE mysqltest1; -RENAME TABLE mysql.procs_gone TO mysql.procs_priv; -DROP USER mysqltest_1@localhost; -FLUSH PRIVILEGES; -CREATE DATABASE dbbug33464; -CREATE USER 'userbug33464'@'localhost'; -GRANT CREATE ROUTINE ON dbbug33464.* TO 'userbug33464'@'localhost'; -connect connbug33464, localhost, userbug33464, , dbbug33464; - -userbug33464@localhost dbbug33464 -CREATE PROCEDURE sp3(v1 char(20)) -BEGIN -SELECT * from dbbug33464.t6 where t6.f2= 'xyz'; -END// -CREATE FUNCTION fn1() returns char(50) SQL SECURITY INVOKER -BEGIN -return 1; -END// -CREATE FUNCTION fn2() returns char(50) SQL SECURITY DEFINER -BEGIN -return 2; -END// -disconnect connbug33464; -connection default; -USE dbbug33464; - -root@localhost dbbug33464 -SELECT fn1(); -fn1() -1 -SELECT fn2(); -fn2() -2 -DROP USER 'userbug33464'@'localhost'; -DROP FUNCTION fn1; -DROP FUNCTION fn2; -DROP PROCEDURE sp3; -DROP USER 'userbug33464'@'localhost'; -USE test; -DROP DATABASE dbbug33464; -SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; -CREATE USER user1; -CREATE USER user2; -GRANT CREATE ON db1.* TO 'user1'@'localhost'; -GRANT CREATE ROUTINE ON db1.* TO 'user1'@'localhost'; -GRANT CREATE ON db1.* TO 'user2'@'%'; -GRANT CREATE ROUTINE ON db1.* TO 'user2'@'%'; -FLUSH PRIVILEGES; -SHOW GRANTS FOR 'user1'@'localhost'; -Grants for user1@localhost -GRANT USAGE ON *.* TO 'user1'@'localhost' -GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user1'@'localhost' -connect con1,localhost,user1,,; -** Connect as user1 and create a procedure. -** The creation will imply implicitly assigned -** EXECUTE and ALTER ROUTINE privileges to -** the current user user1@localhost. -SELECT @@GLOBAL.sql_mode; -@@GLOBAL.sql_mode - -SELECT @@SESSION.sql_mode; -@@SESSION.sql_mode - -CREATE DATABASE db1; -CREATE PROCEDURE db1.proc1(p1 INT) -BEGIN -SET @x = 0; -REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; -END ;|| -connect con2,localhost,user2,,; -** Connect as user2 and create a procedure. -** Implicitly assignment of privileges will -** fail because the user2@localhost is an -** unknown user. -CREATE PROCEDURE db1.proc2(p1 INT) -BEGIN -SET @x = 0; -REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; -END ;|| -Warnings: -Warning 1404 Failed to grant EXECUTE and ALTER ROUTINE privileges -connection default; -SHOW GRANTS FOR 'user1'@'localhost'; -Grants for user1@localhost -GRANT USAGE ON *.* TO 'user1'@'localhost' -GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user1'@'localhost' -GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `db1`.`proc1` TO 'user1'@'localhost' -SHOW GRANTS FOR 'user2'; -Grants for user2@% -GRANT USAGE ON *.* TO 'user2'@'%' -GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user2'@'%' -disconnect con1; -disconnect con2; -DROP PROCEDURE db1.proc1; -DROP PROCEDURE db1.proc2; -REVOKE ALL ON db1.* FROM 'user1'@'localhost'; -REVOKE ALL ON db1.* FROM 'user2'@'%'; -DROP USER 'user1'; -DROP USER 'user1'@'localhost'; -DROP USER 'user2'; -DROP DATABASE db1; -# -# Bug #25863 No database selected error, but documentation -# says * for global allowed -# -connect conn1,localhost,root,,*NO-ONE*; -GRANT ALL ON * TO mysqltest_1; -ERROR 3D000: No database selected -GRANT ALL ON *.* TO mysqltest_1; -SHOW GRANTS FOR mysqltest_1; -Grants for mysqltest_1@% -GRANT ALL PRIVILEGES ON *.* TO 'mysqltest_1'@'%' -DROP USER mysqltest_1; -USE test; -GRANT ALL ON * TO mysqltest_1; -SHOW GRANTS FOR mysqltest_1; -Grants for mysqltest_1@% -GRANT USAGE ON *.* TO 'mysqltest_1'@'%' -GRANT ALL PRIVILEGES ON `test`.* TO 'mysqltest_1'@'%' -DROP USER mysqltest_1; -GRANT ALL ON *.* TO mysqltest_1; -SHOW GRANTS FOR mysqltest_1; -Grants for mysqltest_1@% -GRANT ALL PRIVILEGES ON *.* TO 'mysqltest_1'@'%' -DROP USER mysqltest_1; -connection default; -disconnect conn1; -CREATE DATABASE db1; -CREATE DATABASE db2; -GRANT SELECT ON db1.* to 'testbug'@localhost; -USE db2; -CREATE TABLE t1 (a INT); -USE test; -connect con1,localhost,testbug,,db1; -SELECT * FROM `../db2/tb2`; -ERROR 42S02: Table 'db1.../db2/tb2' doesn't exist -SELECT * FROM `../db2`.tb2; -ERROR 42000: SELECT command denied to user 'testbug'@'localhost' for table 'tb2' -SELECT * FROM `#mysql50#/../db2/tb2`; -ERROR 42000: Incorrect table name '#mysql50#/../db2/tb2' -connection default; -disconnect con1; -DROP USER 'testbug'@localhost; -DROP TABLE db2.t1; -DROP DATABASE db1; -DROP DATABASE db2; -# -# Bug #36742 -# -grant usage on Foo.* to myuser@Localhost identified by 'foo'; -grant select on Foo.* to myuser@localhost; -select host,user from mysql.user where User='myuser'; -host user -localhost myuser -revoke select on Foo.* from myuser@localhost; -delete from mysql.user where User='myuser'; -flush privileges; -######################################################################### -# -# Bug#38347: ALTER ROUTINE privilege allows SHOW CREATE TABLE. -# -######################################################################### - -# -- -# -- Prepare the environment. -# -- -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 IF EXISTS mysqltest_db1; -CREATE DATABASE mysqltest_db1; -CREATE TABLE mysqltest_db1.t1(a INT); - -# -- -# -- Check that global privileges don't allow SHOW CREATE TABLE. -# -- -GRANT EVENT ON mysqltest_db1.* TO mysqltest_u1@localhost; -GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost; -GRANT LOCK TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost; -GRANT ALTER ROUTINE ON mysqltest_db1.* TO mysqltest_u1@localhost; -GRANT CREATE ROUTINE ON mysqltest_db1.* TO mysqltest_u1@localhost; -GRANT EXECUTE ON mysqltest_db1.* TO mysqltest_u1@localhost; -GRANT FILE ON *.* TO mysqltest_u1@localhost; -GRANT CREATE USER ON *.* TO mysqltest_u1@localhost; -GRANT PROCESS ON *.* TO mysqltest_u1@localhost; -GRANT RELOAD ON *.* TO mysqltest_u1@localhost; -GRANT REPLICATION CLIENT ON *.* TO mysqltest_u1@localhost; -GRANT REPLICATION SLAVE ON *.* TO mysqltest_u1@localhost; -GRANT SHOW DATABASES ON *.* TO mysqltest_u1@localhost; -GRANT SHUTDOWN ON *.* TO mysqltest_u1@localhost; -GRANT USAGE ON *.* TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO 'mysqltest_u1'@'localhost' -GRANT CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, EVENT ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't1' -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that global SELECT allows SHOW CREATE TABLE. -# -- - -GRANT SELECT ON mysqltest_db1.* TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT SELECT ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that global INSERT allows SHOW CREATE TABLE. -# -- - -GRANT INSERT ON mysqltest_db1.* TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT INSERT ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that global UPDATE allows SHOW CREATE TABLE. -# -- - -GRANT UPDATE ON mysqltest_db1.* TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT UPDATE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that global DELETE allows SHOW CREATE TABLE. -# -- - -GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT DELETE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that global CREATE allows SHOW CREATE TABLE. -# -- - -GRANT CREATE ON mysqltest_db1.* TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT CREATE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that global DROP allows SHOW CREATE TABLE. -# -- - -GRANT DROP ON mysqltest_db1.* TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT DROP ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that global ALTER allows SHOW CREATE TABLE. -# -- - -GRANT ALTER ON mysqltest_db1.* TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT ALTER ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that global INDEX allows SHOW CREATE TABLE. -# -- - -GRANT INDEX ON mysqltest_db1.* TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT INDEX ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that global REFERENCES allows SHOW CREATE TABLE. -# -- - -GRANT REFERENCES ON mysqltest_db1.* TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT REFERENCES ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that global GRANT OPTION allows SHOW CREATE TABLE. -# -- - -GRANT GRANT OPTION ON mysqltest_db1.* TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT USAGE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' WITH GRANT OPTION -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that global CREATE VIEW allows SHOW CREATE TABLE. -# -- - -GRANT CREATE VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT CREATE VIEW ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that global SHOW VIEW allows SHOW CREATE TABLE. -# -- - -GRANT SHOW VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT SHOW VIEW ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that table-level SELECT allows SHOW CREATE TABLE. -# -- - -GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT SELECT ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that table-level INSERT allows SHOW CREATE TABLE. -# -- - -GRANT INSERT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT INSERT ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that table-level UPDATE allows SHOW CREATE TABLE. -# -- - -GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT UPDATE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that table-level DELETE allows SHOW CREATE TABLE. -# -- - -GRANT DELETE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT DELETE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that table-level CREATE allows SHOW CREATE TABLE. -# -- - -GRANT CREATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT CREATE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that table-level DROP allows SHOW CREATE TABLE. -# -- - -GRANT DROP ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT DROP ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that table-level ALTER allows SHOW CREATE TABLE. -# -- - -GRANT ALTER ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT ALTER ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that table-level INDEX allows SHOW CREATE TABLE. -# -- - -GRANT INDEX ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT INDEX ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that table-level REFERENCES allows SHOW CREATE TABLE. -# -- - -GRANT REFERENCES ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT REFERENCES ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that table-level GRANT OPTION allows SHOW CREATE TABLE. -# -- - -GRANT GRANT OPTION ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT USAGE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' WITH GRANT OPTION -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that table-level CREATE VIEW allows SHOW CREATE TABLE. -# -- - -GRANT CREATE VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT CREATE VIEW ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Check that table-level SHOW VIEW allows SHOW CREATE TABLE. -# -- - -GRANT SHOW VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost; - -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' -GRANT SHOW VIEW ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' -connect con1,localhost,mysqltest_u1,,mysqltest_db1; -connection con1; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -connection default; -disconnect con1; - -REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; -SHOW GRANTS FOR mysqltest_u1@localhost; -Grants for mysqltest_u1@localhost -GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost' - -# -- -# -- Cleanup. -# -- - -DROP DATABASE mysqltest_db1; -DROP USER mysqltest_u1@localhost; - -# End of Bug#38347. - -# -# BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES -# DIFFERENTLY'. -# -drop database if exists mysqltest_db1; -create database mysqltest_db1; -create user mysqltest_u1; -# Both GRANT statements below should fail with the same error. -grant execute on function mysqltest_db1.f1 to mysqltest_u1; -ERROR 42000: FUNCTION or PROCEDURE f1 does not exist -grant execute on procedure mysqltest_db1.p1 to mysqltest_u1; -ERROR 42000: FUNCTION or PROCEDURE p1 does not exist -# Let us show that GRANT behaviour for routines is consistent -# with GRANT behaviour for tables. Attempt to grant privilege -# on non-existent table also results in an error. -grant select on mysqltest_db1.t1 to mysqltest_u1; -ERROR 42S02: Table 'mysqltest_db1.t1' doesn't exist -show grants for mysqltest_u1; -Grants for mysqltest_u1@% -GRANT USAGE ON *.* TO 'mysqltest_u1'@'%' -drop database mysqltest_db1; -drop user mysqltest_u1; -# -# Bug#12766319 - 61865: RENAME USER DOES NOT WORK CORRECTLY - -# REQUIRES FLUSH PRIVILEGES -# -CREATE USER foo@'127.0.0.1'; -GRANT ALL ON *.* TO foo@'127.0.0.1'; -# First attempt, should connect successfully -connect conn1, '127.0.0.1', foo,,test; -SELECT user(), current_user(); -user() current_user() -foo@localhost foo@127.0.0.1 -# Rename the user -RENAME USER foo@'127.0.0.1' to foo@'127.0.0.0/255.0.0.0'; -# Second attempt, should connect successfully as its valid mask -# This was failing without fix -connect conn2, '127.0.0.1', foo,,test; -SELECT user(), current_user(); -user() current_user() -foo@localhost foo@127.0.0.0/255.0.0.0 -# Rename the user back to original -RENAME USER foo@'127.0.0.0/255.0.0.0' to foo@'127.0.0.1'; -# Third attempt, should connect successfully -connect conn3, '127.0.0.1', foo,,test; -SELECT user(), current_user(); -user() current_user() -foo@localhost foo@127.0.0.1 -# Clean-up -connection default; -disconnect conn1; -disconnect conn2; -disconnect conn3; -DROP USER foo@'127.0.0.1'; -# End of Bug#12766319 -# -# Bug#11756966 - 48958: STORED PROCEDURES CAN BE LEVERAGED TO BYPASS -# DATABASE SECURITY -# -DROP DATABASE IF EXISTS secret; -DROP DATABASE IF EXISTS no_such_db; -CREATE DATABASE secret; -GRANT USAGE ON *.* TO untrusted@localhost; -connect con1, localhost, untrusted; -SHOW GRANTS; -Grants for untrusted@localhost -GRANT USAGE ON *.* TO 'untrusted'@'localhost' -SHOW DATABASES; -Database -information_schema -test -# Both statements below should fail with the same error. -# They used to give different errors, thereby -# hinting that the secret database exists. -CREATE PROCEDURE no_such_db.foo() BEGIN END; -ERROR 42000: Access denied for user 'untrusted'@'localhost' to database 'no_such_db' -CREATE PROCEDURE secret.peek_at_secret() BEGIN END; -ERROR 42000: Access denied for user 'untrusted'@'localhost' to database 'secret' -connection default; -disconnect con1; -DROP USER untrusted@localhost; -DROP DATABASE secret; -set GLOBAL sql_mode=default; -# -# Start of 10.2 tests -# -# -# MDEV-10134 Add full support for DEFAULT -# -CREATE TABLE t1 (a VARCHAR(30) DEFAULT USER()); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` varchar(30) DEFAULT user() -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -INSERT INTO t1 VALUES (); -GRANT ALL PRIVILEGES ON test.* TO dummy@localhost IDENTIFIED BY 'pwd'; -connect conn1,localhost,dummy,pwd,test; -connection conn1; -INSERT INTO t1 VALUES (); -connection default; -disconnect conn1; -INSERT INTO t1 VALUES (); -SELECT * FROM t1; -a -root@localhost -dummy@localhost -root@localhost -DROP TABLE t1; -DROP USER dummy@localhost; -# -# End of 10.2 tests -# |