Bug #9504 Stored procedures: execute privilege doesn't make 'use database' okay
Submitted: 30 Mar 2005 23:37 Modified: 4 Apr 2007 3:38
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.4-beta-debug OS:Linux (SUSE 9.2)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[30 Mar 2005 23:37] Peter Gulutzan
Description:
If a user has SELECT privilege on a table in database D, then the user can say 'USE D'.
If a user has EXECUTE privilege on a routine in database D, then the user can't say 'USE D'.
Surely it would be more natural to allow the user to set D as the default database if the user has EXECUTE privilege.

How to repeat:
As user root, say:
create database db34;
use db34;
create function f () returns int return 5;
grant execute on f to user18@localhost;

As user user18@localhost, say:
use db34;
The result will be an error message: "Access denied ..."

As user root, say:
create table t (s1 int);
grant select on t to user18@localhost;

As user user18@localhost, say:
use db34;
The result will be success, no error message.
[30 Nov 2006 20:22] Konstantin Osipov
The bug is still there.
The correct grant syntax now is 
grant execute on function f to user18@localhost;

The problem is in check_grant_db, it doesn't search for grants in routine_privs_hash.
[22 Feb 2007 2:13] Simon Scudder
This is still happening in 5.0.27 (Windows)

I think the severity of the bug should be upgraded, as this creates a potential security risk.

Why have separate privileges for procedures if you have to grant select to at least one table to use a database?
[22 Mar 2007 12:39] Alexander Nozdrin
I'm going to work on it because fix for BUG#25082 depends on this bug.
[22 Mar 2007 17:20] Alexander Nozdrin
Another problem here is that the following case failed:

CREATE PROCEDURE db1.p1() SQL SECURITY INVOKER
  SELECT 1;

CREATE FUNCTION db2.f1() RETURNS INT SQL SECURITY INVOKER
  RETURN 1;

GRANT EXECUTE ON PROCEDURE db1.p1 TO mysqltest_1@localhost;
GRANT EXECUTE ON FUNCTION db1.f1 TO mysqltest_1@localhost;

CALL db1.p1();
--> Access denied.

SELECT db2.f2();
--> Access denied.

The problem is that since routines are defined with
SQL SECURITY INVOKER, security context is not switched
and when sp_head::execute() tries to change database,
it fails.
[23 Mar 2007 22:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22841

ChangeSet@1.2488, 2007-03-22 20:23:35+03:00, anozdrin@booka.opbmk +3 -0
  Fix for BUG#9504: Stored procedures: execute privilege doesn't
  make 'use database' okay.
  
  The problem was that we didn't check stored-routine privileges
  in check_grant_db().
  
  The patch adds this check.
[23 Mar 2007 22:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22843

ChangeSet@1.2488, 2007-03-23 14:12:11+03:00, anozdrin@booka.opbmk +5 -0
  Fix for BUG#9504: Stored procedures: execute privilege doesn't
  make 'use database' okay.
  
  The problem was that we didn't check stored-routine privileges
  in check_grant_db().
  
  The patch adds this check.
[28 Mar 2007 16:47] Alexander Nozdrin
BUG#26185 has been marked as a duplicate of this bug.
[3 Apr 2007 23:26] Konstantin Osipov
Pushed into 5.0.40 and 5.1.18
[4 Apr 2007 3:38] Paul Dubois
Noted in 5.0.40, 5.1.18 changelogs.

Having the EXECUTE privilege for a routine in a database should make
it possible to USE that database, but the server returned an error
instead. This has been corrected. As a result of the change, SHOW
TABLES for a database in which you have only the EXECUTE privilege
returns an empty set rather than an error.
[18 Apr 2007 15:46] Bugs System
Pushed into 5.1.18-beta
[18 Apr 2007 15:48] Bugs System
Pushed into 5.0.42
[26 Apr 2007 19:30] Paul Dubois
Moved the 5.0.40 changelog entry to 5.0.42.