MySQL Bugs: #9504: Stored procedures: execute privilege doesn't make 'use database' okay

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

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.

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.

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?

I'm going to work on it because fix for BUG#25082 depends on this bug.

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.

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.

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.

BUG#26185 has been marked as a duplicate of this bug.

Pushed into 5.0.40 and 5.1.18

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.

Pushed into 5.1.18-beta

Pushed into 5.0.42

Moved the 5.0.40 changelog entry to 5.0.42.