summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/grant.result2
-rw-r--r--mysql-test/r/sp-security.result29
-rw-r--r--mysql-test/suite/funcs_1/r/innodb_storedproc_06.result4
-rw-r--r--mysql-test/suite/funcs_1/r/memory_storedproc_06.result4
-rw-r--r--mysql-test/suite/funcs_1/r/myisam_storedproc_06.result4
-rw-r--r--mysql-test/suite/funcs_1/storedproc/storedproc_06.inc10
-rw-r--r--mysql-test/t/grant.test5
-rw-r--r--mysql-test/t/sp-security.test37
-rw-r--r--sql/sp.cc32
-rw-r--r--sql/sp.h3
-rw-r--r--sql/sql_parse.cc64
11 files changed, 100 insertions, 94 deletions
diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result
index 84cac386b57..37074e9a32f 100644
--- a/mysql-test/r/grant.result
+++ b/mysql-test/r/grant.result
@@ -1448,8 +1448,6 @@ CREATE USER 'userbug33464'@'localhost';
GRANT CREATE ROUTINE ON dbbug33464.* TO 'userbug33464'@'localhost';
userbug33464@localhost dbbug33464
-DROP PROCEDURE IF EXISTS sp3;
-DROP FUNCTION IF EXISTS fn1;
CREATE PROCEDURE sp3(v1 char(20))
BEGIN
SELECT * from dbbug33464.t6 where t6.f2= 'xyz';
diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result
index 4ea26d1021a..c09579b13eb 100644
--- a/mysql-test/r/sp-security.result
+++ b/mysql-test/r/sp-security.result
@@ -44,7 +44,7 @@ ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1'
create procedure db1_secret.dummy() begin end;
ERROR 42000: Access denied for user 'user1'@'localhost' to database 'db1_secret'
drop procedure db1_secret.dummy;
-ERROR 42000: PROCEDURE db1_secret.dummy does not exist
+ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.dummy'
drop procedure db1_secret.stamp;
ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.stamp'
drop function db1_secret.db;
@@ -58,7 +58,7 @@ ERROR 42000: SELECT command denied to user ''@'localhost' for table 't1'
create procedure db1_secret.dummy() begin end;
ERROR 42000: Access denied for user ''@'%' to database 'db1_secret'
drop procedure db1_secret.dummy;
-ERROR 42000: PROCEDURE db1_secret.dummy does not exist
+ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.dummy'
drop procedure db1_secret.stamp;
ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.stamp'
drop function db1_secret.db;
@@ -567,3 +567,28 @@ DROP USER 'tester';
DROP USER 'Tester';
DROP DATABASE B48872;
End of 5.0 tests.
+#
+# Test for bug#57061 "User without privilege on routine can discover
+# its existence."
+#
+drop database if exists mysqltest_db;
+create database mysqltest_db;
+# Create user with no privileges on mysqltest_db database.
+create user bug57061_user@localhost;
+create function mysqltest_db.f1() returns int return 0;
+create procedure mysqltest_db.p1() begin end;
+# Connect as user 'bug57061_user@localhost'
+# Attempt to drop routine on which user doesn't have privileges
+# should result in the same 'access denied' type of error whether
+# routine exists or not.
+drop function if exists mysqltest_db.f_does_not_exist;
+ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.f_does_not_exist'
+drop procedure if exists mysqltest_db.p_does_not_exist;
+ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.p_does_not_exist'
+drop function if exists mysqltest_db.f1;
+ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.f1'
+drop procedure if exists mysqltest_db.p1;
+ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.p1'
+# Connection 'default'.
+drop user bug57061_user@localhost;
+drop database mysqltest_db;
diff --git a/mysql-test/suite/funcs_1/r/innodb_storedproc_06.result b/mysql-test/suite/funcs_1/r/innodb_storedproc_06.result
index ee1548fe012..f19030834c8 100644
--- a/mysql-test/suite/funcs_1/r/innodb_storedproc_06.result
+++ b/mysql-test/suite/funcs_1/r/innodb_storedproc_06.result
@@ -110,10 +110,10 @@ Ensure that root always has the GRANT CREATE ROUTINE privilege.
--------------------------------------------------------------------------------
grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
flush privileges;
+DROP PROCEDURE IF EXISTS db_storedproc_1.sp3;
+DROP FUNCTION IF EXISTS db_storedproc_1.fn1;
user_1@localhost db_storedproc_1
-DROP PROCEDURE IF EXISTS sp3;
-DROP FUNCTION IF EXISTS fn1;
CREATE PROCEDURE sp3(v1 char(20))
BEGIN
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
diff --git a/mysql-test/suite/funcs_1/r/memory_storedproc_06.result b/mysql-test/suite/funcs_1/r/memory_storedproc_06.result
index 096cbd1261e..0a117c830ee 100644
--- a/mysql-test/suite/funcs_1/r/memory_storedproc_06.result
+++ b/mysql-test/suite/funcs_1/r/memory_storedproc_06.result
@@ -111,10 +111,10 @@ Ensure that root always has the GRANT CREATE ROUTINE privilege.
--------------------------------------------------------------------------------
grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
flush privileges;
+DROP PROCEDURE IF EXISTS db_storedproc_1.sp3;
+DROP FUNCTION IF EXISTS db_storedproc_1.fn1;
user_1@localhost db_storedproc_1
-DROP PROCEDURE IF EXISTS sp3;
-DROP FUNCTION IF EXISTS fn1;
CREATE PROCEDURE sp3(v1 char(20))
BEGIN
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
diff --git a/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result b/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result
index 096cbd1261e..0a117c830ee 100644
--- a/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result
+++ b/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result
@@ -111,10 +111,10 @@ Ensure that root always has the GRANT CREATE ROUTINE privilege.
--------------------------------------------------------------------------------
grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
flush privileges;
+DROP PROCEDURE IF EXISTS db_storedproc_1.sp3;
+DROP FUNCTION IF EXISTS db_storedproc_1.fn1;
user_1@localhost db_storedproc_1
-DROP PROCEDURE IF EXISTS sp3;
-DROP FUNCTION IF EXISTS fn1;
CREATE PROCEDURE sp3(v1 char(20))
BEGIN
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
diff --git a/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc b/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc
index 4ecca63351d..0695a0724d8 100644
--- a/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc
+++ b/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc
@@ -117,15 +117,15 @@ create user 'user_1'@'localhost';
grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
flush privileges;
+--disable_warnings
+DROP PROCEDURE IF EXISTS db_storedproc_1.sp3;
+DROP FUNCTION IF EXISTS db_storedproc_1.fn1;
+--enable_warnings
+
# disconnect default;
connect (user2, localhost, user_1, , db_storedproc_1);
--source suite/funcs_1/include/show_connection.inc
---disable_warnings
-DROP PROCEDURE IF EXISTS sp3;
-DROP FUNCTION IF EXISTS fn1;
---enable_warnings
-
delimiter //;
CREATE PROCEDURE sp3(v1 char(20))
BEGIN
diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test
index e73f45a6c53..aad0c42a5b3 100644
--- a/mysql-test/t/grant.test
+++ b/mysql-test/t/grant.test
@@ -1419,11 +1419,6 @@ GRANT CREATE ROUTINE ON dbbug33464.* TO 'userbug33464'@'localhost';
connect (connbug33464, localhost, userbug33464, , dbbug33464);
--source suite/funcs_1/include/show_connection.inc
---disable_warnings
-DROP PROCEDURE IF EXISTS sp3;
-DROP FUNCTION IF EXISTS fn1;
---enable_warnings
-
delimiter //;
CREATE PROCEDURE sp3(v1 char(20))
BEGIN
diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test
index 96f82c92248..d7ea829bf50 100644
--- a/mysql-test/t/sp-security.test
+++ b/mysql-test/t/sp-security.test
@@ -82,7 +82,7 @@ select * from db1_secret.t1;
# ...and not this
--error ER_DBACCESS_DENIED_ERROR
create procedure db1_secret.dummy() begin end;
---error ER_SP_DOES_NOT_EXIST
+--error ER_PROCACCESS_DENIED_ERROR
drop procedure db1_secret.dummy;
--error ER_PROCACCESS_DENIED_ERROR
drop procedure db1_secret.stamp;
@@ -106,7 +106,7 @@ select * from db1_secret.t1;
# ...and not this
--error ER_DBACCESS_DENIED_ERROR
create procedure db1_secret.dummy() begin end;
---error ER_SP_DOES_NOT_EXIST
+--error ER_PROCACCESS_DENIED_ERROR
drop procedure db1_secret.dummy;
--error ER_PROCACCESS_DENIED_ERROR
drop procedure db1_secret.stamp;
@@ -926,6 +926,39 @@ DROP DATABASE B48872;
--echo End of 5.0 tests.
+
+--echo #
+--echo # Test for bug#57061 "User without privilege on routine can discover
+--echo # its existence."
+--echo #
+--disable_warnings
+drop database if exists mysqltest_db;
+--enable_warnings
+create database mysqltest_db;
+--echo # Create user with no privileges on mysqltest_db database.
+create user bug57061_user@localhost;
+create function mysqltest_db.f1() returns int return 0;
+create procedure mysqltest_db.p1() begin end;
+--echo # Connect as user 'bug57061_user@localhost'
+connect (conn1, localhost, bug57061_user,,);
+--echo # Attempt to drop routine on which user doesn't have privileges
+--echo # should result in the same 'access denied' type of error whether
+--echo # routine exists or not.
+--error ER_PROCACCESS_DENIED_ERROR
+drop function if exists mysqltest_db.f_does_not_exist;
+--error ER_PROCACCESS_DENIED_ERROR
+drop procedure if exists mysqltest_db.p_does_not_exist;
+--error ER_PROCACCESS_DENIED_ERROR
+drop function if exists mysqltest_db.f1;
+--error ER_PROCACCESS_DENIED_ERROR
+drop procedure if exists mysqltest_db.p1;
+--echo # Connection 'default'.
+connection default;
+disconnect conn1;
+drop user bug57061_user@localhost;
+drop database mysqltest_db;
+
+
# Wait till all disconnects are completed
--source include/wait_until_count_sessions.inc
diff --git a/sql/sp.cc b/sql/sp.cc
index 8821dc9365d..87eb40c29ac 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -1636,38 +1636,6 @@ sp_exist_routines(THD *thd, TABLE_LIST *routines, bool any)
}
-/**
- Check if a routine exists in the mysql.proc table, without actually
- parsing the definition. (Used for dropping).
-
- @param thd thread context
- @param name name of procedure
-
- @retval
- 0 Success
- @retval
- non-0 Error; SP_OPEN_TABLE_FAILED or SP_KEY_NOT_FOUND
-*/
-
-int
-sp_routine_exists_in_table(THD *thd, int type, sp_name *name)
-{
- TABLE *table;
- int ret;
- Open_tables_backup open_tables_state_backup;
-
- if (!(table= open_proc_table_for_read(thd, &open_tables_state_backup)))
- ret= SP_OPEN_TABLE_FAILED;
- else
- {
- if ((ret= db_find_routine_aux(thd, type, name, table)) != SP_OK)
- ret= SP_KEY_NOT_FOUND;
- close_system_tables(thd, &open_tables_state_backup);
- }
- return ret;
-}
-
-
extern "C" uchar* sp_sroutine_key(const uchar *ptr, size_t *plen,
my_bool first)
{
diff --git a/sql/sp.h b/sql/sp.h
index 10e70261b86..5d0490586a1 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -100,9 +100,6 @@ sp_cache_routine(THD *thd, int type, sp_name *name,
bool
sp_exist_routines(THD *thd, TABLE_LIST *procs, bool any);
-int
-sp_routine_exists_in_table(THD *thd, int type, sp_name *name);
-
bool
sp_show_create_routine(THD *thd, int type, sp_name *name);
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 2f8a72ee25c..0f3338cb6a6 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4023,49 +4023,39 @@ create_sp_error:
int sp_result;
int type= (lex->sql_command == SQLCOM_DROP_PROCEDURE ?
TYPE_ENUM_PROCEDURE : TYPE_ENUM_FUNCTION);
+ char *db= lex->spname->m_db.str;
+ char *name= lex->spname->m_name.str;
- /*
- @todo: here we break the metadata locking protocol by
- looking up the information about the routine without
- a metadata lock. Rewrite this piece to make sp_drop_routine
- return whether the routine existed or not.
- */
- sp_result= sp_routine_exists_in_table(thd, type, lex->spname);
- thd->warning_info->opt_clear_warning_info(thd->query_id);
- if (sp_result == SP_OK)
- {
- char *db= lex->spname->m_db.str;
- char *name= lex->spname->m_name.str;
-
- if (check_routine_access(thd, ALTER_PROC_ACL, db, name,
- lex->sql_command == SQLCOM_DROP_PROCEDURE, 0))
- goto error;
+ if (check_routine_access(thd, ALTER_PROC_ACL, db, name,
+ lex->sql_command == SQLCOM_DROP_PROCEDURE, 0))
+ goto error;
- /* Conditionally writes to binlog */
- sp_result= sp_drop_routine(thd, type, lex->spname);
+ /* Conditionally writes to binlog */
+ sp_result= sp_drop_routine(thd, type, lex->spname);
#ifndef NO_EMBEDDED_ACCESS_CHECKS
- /*
- We're going to issue an implicit REVOKE statement.
- It takes metadata locks and updates system tables.
- Make sure that sp_create_routine() did not leave any
- locks in the MDL context, so there is no risk to
- deadlock.
- */
- close_mysql_tables(thd);
+ /*
+ We're going to issue an implicit REVOKE statement.
+ It takes metadata locks and updates system tables.
+ Make sure that sp_create_routine() did not leave any
+ locks in the MDL context, so there is no risk to
+ deadlock.
+ */
+ close_mysql_tables(thd);
- if (sp_automatic_privileges && !opt_noacl &&
- sp_revoke_privileges(thd, db, name,
- lex->sql_command == SQLCOM_DROP_PROCEDURE))
- {
- push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
- ER_PROC_AUTO_REVOKE_FAIL,
- ER(ER_PROC_AUTO_REVOKE_FAIL));
- /* If this happens, an error should have been reported. */
- goto error;
- }
-#endif
+ if (sp_result != SP_KEY_NOT_FOUND &&
+ sp_automatic_privileges && !opt_noacl &&
+ sp_revoke_privileges(thd, db, name,
+ lex->sql_command == SQLCOM_DROP_PROCEDURE))
+ {
+ push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ ER_PROC_AUTO_REVOKE_FAIL,
+ ER(ER_PROC_AUTO_REVOKE_FAIL));
+ /* If this happens, an error should have been reported. */
+ goto error;
}
+#endif
+
res= sp_result;
switch (sp_result) {
case SP_OK:
View Lorry Controller Status