From 3f619478f796eddbba6e39502fe941b285dd97b1 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 20:00:34 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- mysql-test/main/grant.result | 2878 ++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 2878 insertions(+) create mode 100644 mysql-test/main/grant.result (limited to 'mysql-test/main/grant.result') diff --git a/mysql-test/main/grant.result b/mysql-test/main/grant.result new file mode 100644 index 00000000..d89bf9f0 --- /dev/null +++ b/mysql-test/main/grant.result @@ -0,0 +1,2878 @@ +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; +select priv into @root_priv from mysql.global_priv where user='root' and host='localhost'; +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 mysql_native_password +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 mysql_native_password +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 mysql_native_password +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 HISTORY 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.global_priv (host, user) values ('localhost', 'test11'); +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,,"*NO-ONE*",; +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 `mysqltest_1`.`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 `mysqltest_2`.`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 `mysqltest_1`.`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 `mysqltest_1`.`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 `mysqltest_1`.`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 `mysqltest_1`.`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 history 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 +Binlog admin Server To purge binary logs +Binlog monitor Server To use SHOW BINLOG STATUS and SHOW BINARY LOG +Binlog replay Server To use BINLOG (generated by mariadb-binlog) +Replication master admin Server To monitor connected slaves +Replication slave admin Server To start/stop slave and apply binlog events +Slave monitor Server To use SHOW SLAVE STATUS and SHOW RELAYLOG EVENTS +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 +Set user Server To create views and stored routines with a different definer +Federated admin Server To execute the CREATE SERVER, ALTER SERVER, DROP SERVER statements +Connection admin Server To bypass connection limits and kill other users' connections +Read_only admin Server To perform write operations even if @@read_only=ON +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 `mysqltest`.`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 ALL PRIVILEGES ON `mysqltest`.`dummytable` TO `dummy`@`localhost` +GRANT ALL PRIVILEGES ON `mysqltest`.`dummyview` TO `dummy`@`localhost` +GRANT USAGE ON *.* 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 HISTORY, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE +mysqltest dummyview ALTER, CREATE, CREATE VIEW, DELETE, DELETE HISTORY, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE +FLUSH PRIVILEGES; +SHOW GRANTS FOR dummy@localhost; +Grants for dummy@localhost +GRANT ALL PRIVILEGES ON `mysqltest`.`dummytable` TO `dummy`@`localhost` +GRANT ALL PRIVILEGES ON `mysqltest`.`dummyview` TO `dummy`@`localhost` +GRANT USAGE ON *.* 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 HISTORY, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE +mysqltest dummyview ALTER, CREATE, CREATE VIEW, DELETE, DELETE HISTORY, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE +SHOW FIELDS FROM mysql.tables_priv; +Field Type Null Key Default Extra +Host char(255) NO PRI +Db char(64) NO PRI +User char(128) NO PRI +Table_name char(64) NO PRI +Grantor varchar(384) 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 CREATE VIEW ON `mysqltest`.`dummytable` TO `dummy`@`localhost` +GRANT CREATE VIEW ON `mysqltest`.`dummyview` TO `dummy`@`localhost` +GRANT USAGE ON *.* 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 CREATE VIEW ON `mysqltest`.`dummytable` TO `dummy`@`localhost` +GRANT CREATE VIEW ON `mysqltest`.`dummyview` TO `dummy`@`localhost` +GRANT USAGE ON *.* 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`.`dummytable` TO `dummy`@`localhost` +GRANT SHOW VIEW ON `mysqltest`.`dummyview` 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`.`dummytable` TO `dummy`@`localhost` +GRANT SHOW VIEW ON `mysqltest`.`dummyview` 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 `db27515`.`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 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@''; +grant select on test.* 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 `test`.* 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 `test`.* 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 `test`.* 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 +'mysqltest_8'@'%' def test 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`@`%` +GRANT SELECT ON `test`.* TO `mysqltest_8`@`%` +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT SELECT ON `test`.* 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 test SELECT NO +flush privileges; +show grants for mysqltest_8@''; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT SELECT ON `test`.* TO `mysqltest_8`@`%` +show grants for mysqltest_8@; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT SELECT ON `test`.* 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`@`%` +GRANT SELECT ON `test`.* 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 SELECT ON `test`.* 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 SELECT ON `test`.* 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 SELECT ON `test`.* 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 SELECT ON `test`.* 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`@`%` +GRANT SELECT ON `test`.* TO `mysqltest_8`@`%` +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT SELECT ON `test`.* 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`@`%` +GRANT SELECT ON `test`.* TO `mysqltest_8`@`%` +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT SELECT ON `test`.* 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 SELECT ON `test`.* 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 SELECT ON `test`.* 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 SELECT ON `test`.* 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 SELECT ON `test`.* 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 +'mariadb.sys'@'localhost' def mysql global_priv SELECT NO +'mariadb.sys'@'localhost' def mysql global_priv DELETE 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`@`%` +GRANT SELECT ON `test`.* TO `mysqltest_8`@`%` +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT SELECT ON `test`.* TO `mysqltest_8`@`%` +select * from information_schema.table_privileges; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE +'mariadb.sys'@'localhost' def mysql global_priv SELECT NO +'mariadb.sys'@'localhost' def mysql global_priv DELETE NO +flush privileges; +show grants for mysqltest_8@''; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT SELECT ON `test`.* TO `mysqltest_8`@`%` +show grants for mysqltest_8; +Grants for mysqltest_8@% +GRANT USAGE ON *.* TO `mysqltest_8`@`%` +GRANT SELECT ON `test`.* 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 SELECT ON `test`.* 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 SELECT ON `test`.* 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 SELECT ON `test`.* 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 SELECT ON `test`.* 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' +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,"*NO-ONE*"; +SHOW CREATE VIEW mysqltest2.v_nn; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table `mysqltest2`.`v_nn` +SHOW CREATE TABLE mysqltest2.v_nn; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table `mysqltest2`.`v_nn` +SHOW CREATE VIEW mysqltest2.v_yn; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table `mysqltest2`.`v_yn` +SHOW CREATE TABLE mysqltest2.v_yn; +ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table `mysqltest2`.`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 `mysqltest3`.`t_nn` +SHOW CREATE VIEW mysqltest3.t_nn; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table `mysqltest3`.`t_nn` +SHOW CREATE VIEW mysqltest3.v_nn; +ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table `mysqltest3`.`v_nn` +SHOW CREATE TABLE mysqltest3.v_nn; +ERROR 42000: SHOW command denied to user 'mysqltest_1'@'localhost' for table `mysqltest3`.`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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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_4567890123456789012345678901234567890123456789012345...' is too long for user name (should be no longer than 128) +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 `mysqltest1`.`t1` +ALTER TABLE t1 RENAME TO t2; +ERROR 42000: DROP, ALTER command denied to user 'mysqltest_1'@'localhost' for table `mysqltest1`.`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 `mysqltest1`.`t1` +ALTER TABLE t1 RENAME TO t2; +ERROR 42000: ALTER command denied to user 'mysqltest_1'@'localhost' for table `mysqltest1`.`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 `mysqltest1`.`t2` +ALTER TABLE t1 RENAME TO t2; +ERROR 42000: INSERT, CREATE command denied to user 'mysqltest_1'@'localhost' for table `mysqltest1`.`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 `mysqltest1`.`t1` +ALTER TABLE t1 RENAME TO t2; +ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table `mysqltest1`.`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_4567890123456789012345678901234567890123456789012345...' is too long for user name (should be no longer than 128) +GRANT CREATE ON mysqltest.* TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; +ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYa...' is too long for host name (should be no longer than 255) +REVOKE CREATE ON mysqltest.* FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; +ERROR HY000: String 'longer_than_80_4567890123456789012345678901234567890123456789012345...' is too long for user name (should be no longer than 128) +REVOKE CREATE ON mysqltest.* FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYabcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; +ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTYa...' is too long for host name (should be no longer than 255) +GRANT CREATE ON t1 TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; +ERROR HY000: String 'longer_than_80_4567890123456789012345678901234567890123456789012345...' is too long for user name (should be no longer than 128) +GRANT CREATE ON t1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; +ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1...' is too long for host name (should be no longer than 255) +REVOKE CREATE ON t1 FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; +ERROR HY000: String 'longer_than_80_4567890123456789012345678901234567890123456789012345...' is too long for user name (should be no longer than 128) +REVOKE CREATE ON t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; +ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1...' is too long for host name (should be no longer than 255) +GRANT EXECUTE ON PROCEDURE p1 TO longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; +ERROR HY000: String 'longer_than_80_4567890123456789012345678901234567890123456789012345...' is too long for user name (should be no longer than 128) +GRANT EXECUTE ON PROCEDURE p1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; +ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1...' is too long for host name (should be no longer than 255) +REVOKE EXECUTE ON PROCEDURE p1 FROM longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost; +ERROR HY000: String 'longer_than_80_4567890123456789012345678901234567890123456789012345...' is too long for user name (should be no longer than 128) +REVOKE EXECUTE ON PROCEDURE t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY; +ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1...' is too long for host name (should be no longer than 255) +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 `bug23556`.`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 `mysqltest2`.`t1` +CALL mysqltest1.p1(); +1 +1 +CREATE TABLE t1(c INT); +ERROR 42000: CREATE command denied to user 'mysqltest_1'@'localhost' for table `mysqltest2`.`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 `mysqltest2`.`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 `mysqltest2`.`t2` +EXECUTE stmt2; +ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table `mysqltest2`.`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| +Warnings: +Warning 1287 ' INTO FROM...' instead +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 `mysqltest1`.`t2` +EXECUTE s2; +ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `mysqltest1`.`t2` +CALL p2; +ERROR 42000: SELECT command denied to user 'testuser'@'localhost' for table `mysqltest1`.`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,,"*NO-ONE*"; +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,,"*NO-ONE*"; +# 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 `mysqltest1`.`t11` +select * from mysqltest1.t22; +ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table `mysqltest1`.`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,,"*NO-ONE*"; +# 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 `mysqltest1`.`t11` +select * from mysqltest1.t22; +ERROR 42000: SELECT command denied to user 'mysqluser1'@'localhost' for table `mysqltest1`.`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,,"*NO-ONE*"; +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 очень_длинный_юзер890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890@localhost; +ERROR HY000: String 'очень_длинный_юзер890123456789012345678901234567890...' is too long for user name (should be no longer than 128) +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 `mysqltest`.`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 `mysqltest`.`t1` +create table if not exists t3 select * from t2; +ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table `mysqltest`.`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 `mysqltest`.`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 `mysqltest`.`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; +call mtr.add_suppression("Can't open and lock privilege tables"); +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,,"*NO-ONE*"; +** 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,,"*NO-ONE*"; +** 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 ;|| +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`@`%` +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `db1`.`proc2` 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 `../db2`.`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; +GRANT SLAVE MONITOR 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, BINLOG MONITOR, CREATE USER, SLAVE MONITOR 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 `mysqltest_db1`.`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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 +create user foo@localhost; +create database foodb; +grant create routine on foodb.* to foo@localhost; +connect con1,localhost,foo,,foodb; +create procedure fooproc() select 'i am fooproc'; +show grants; +Grants for foo@localhost +GRANT USAGE ON *.* TO `foo`@`localhost` +GRANT CREATE ROUTINE ON `foodb`.* TO `foo`@`localhost` +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `foodb`.`fooproc` TO `foo`@`localhost` +disconnect con1; +connection default; +rename table mysql.procs_priv to mysql.procs_priv1; +flush privileges; +show grants for foo@localhost; +Grants for foo@localhost +GRANT USAGE ON *.* TO `foo`@`localhost` +GRANT CREATE ROUTINE ON `foodb`.* TO `foo`@`localhost` +rename table mysql.procs_priv1 to mysql.procs_priv; +show grants for foo@localhost; +Grants for foo@localhost +GRANT USAGE ON *.* TO `foo`@`localhost` +GRANT CREATE ROUTINE ON `foodb`.* TO `foo`@`localhost` +flush privileges; +show grants for foo@localhost; +Grants for foo@localhost +GRANT USAGE ON *.* TO `foo`@`localhost` +GRANT CREATE ROUTINE ON `foodb`.* TO `foo`@`localhost` +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `foodb`.`fooproc` TO `foo`@`localhost` +drop user foo@localhost; +drop procedure foodb.fooproc; +drop database foodb; +# +# 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,,"*NO-ONE*"; +SHOW GRANTS; +Grants for untrusted@localhost +GRANT USAGE ON *.* TO `untrusted`@`localhost` +SHOW DATABASES; +Database +information_schema +# 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; +# +# MDEV-22755 CREATE USER leads to indirect SIGABRT in __stack_chk_fail () from fill_schema_user_privileges + *** stack smashing detected *** (on optimized builds) +# +SET NAMES utf8; +SET SQL_MODE=''; +CREATE USER 觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻@localhost; +SELECT * FROM INFORMATION_SCHEMA.user_privileges WHERE GRANTEE LIKE "'abcdefghijklmnopqrstuvwxyz'%"; +GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE +SELECT GRANTEE FROM INFORMATION_SCHEMA.user_privileges WHERE GRANTEE LIKE '%觻%'; +GRANTEE +'觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻'@'localhost' +DROP USER 觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻_觻觻觻觻觻觻觻觻觻觻@localhost; +SET 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 COLLATE=latin1_swedish_ci +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; +# +# MDEV-23082: ER_TABLEACCESS_DENIED_ERROR error message is truncated, and +# inaccurately +# +CREATE USER foo; +CREATE DATABASE db; +CREATE TABLE db.t (a INT); +connect con1,localhost,foo,,"*NO-ONE*"; +GRANT ALL ON db.t TO foo; +ERROR 42000: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW ... command denied to user 'foo'@'localhost' for table `db`.`t` +GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, REFERENCES, +INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON db.t TO foo; +ERROR 42000: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW ... command denied to user 'foo'@'localhost' for table `db`.`t` +connection default; +disconnect con1; +DROP USER foo; +DROP TABLE db.t; +DROP DATABASE db; +# +# Bug#33578113: DROP privilege on performance_schema.* can't be revoked +# +connection default; +CREATE USER bug33578113; +GRANT DROP ON performance_schema.* TO bug33578113; +REVOKE DROP ON performance_schema.* FROM bug33578113; +DROP USER bug33578113; +# +# End of 10.2 tests +# +# +# Start of 10.3 tests +# +# +# MDEV-19948 'show grants' return privileges individually +# +# switching from mysql.global_priv to mysql.user +CREATE USER ten2; +GRANT ALL ON *.* TO ten2; +SHOW GRANTS FOR ten2; +Grants for ten2@% +GRANT ALL PRIVILEGES ON *.* TO `ten2`@`%` +FLUSH PRIVILEGES; +SHOW GRANTS FOR ten2; +Grants for ten2@% +GRANT ALL PRIVILEGES ON *.* TO `ten2`@`%` +DROP USER ten2; +# switching back from mysql.user to mysql.global_priv +# +# End of 10.3 tests +# +# +# MDEV-17932 : Assertion upon double RENAME USER +# +CREATE USER foo@localhost; +CREATE USER bar2@localhost; +RENAME USER foo@localhost TO bar1@localhost, bar1@localhost TO bar3@localhost; +DROP USER bar2@localhost; +DROP USER bar3@localhost; +# +# MDEV-17946 : Unsorted acl_dbs after RENAME USER +# +CREATE USER a; +CREATE USER foo; +GRANT SELECT ON test.* TO foo; +RENAME USER 'a' TO 'name'; +GRANT UPDATE ON test.* TO foo; +RENAME USER 'name' to 'a'; +DROP USER foo; +DROP USER a; +# +# End of 10.4 tests +# +update mysql.global_priv set priv=@root_priv where user='root' and host='localhost'; -- cgit v1.2.3