MySQL Bugs: #27337: Privileges are not properly restored

Bug #27337	Privileges are not properly restored
Submitted:	21 Mar 2007 16:41	Modified:	4 Apr 2007 5:43
Reporter:	Alexander Nozdrin
Status:	Closed
Category:	Server: SP	Severity:	S1 (Critical)
Version:	5.0.40	OS:	Any (any)
Assigned to:	Alexander Nozdrin	Target Version:

Description:
If a stored routine is declared as SQL SECURITY INVOKER,
we have a security flaw, that allows the user to raise
privileges.

Inspired by BUG#25082.

The problem is that in mysql_change_db() we have no_access attribute
(this attribute is TRUE, when mysql_change_db() is called from
sp_head::execute()). If this attribute is TRUE, we do not check
privileges on the new db, neither we change database privileges.
So, actually, we change privileges when we switch to the routine's
database, but do not change them when we switch back.

The problem happens only when the routine is defined with SQL SECURITY
INVOKER. With SQL SECURITY DEFINER, security context is switched properly
to the definer and then back to the invoker.

This bug affects every object, that uses stored routines, that is triggers,
stored procedures and functions.

How to repeat:
--
-- Login as root@mysql
--

CREATE DATABASE db1;
CREATE DATABASE db2;

GRANT ALL PRIVILEGES ON db1.* TO u1@localhost;

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

--
-- Login as u1@db2
--

CREATE TABLE t1(c INT);
--> Error: CREATE command denied to user 'u1'@'localhost' for table 't1'

CALL db1.p1();

CREATE TABLE t1(c INT);
--> Success!!! (it is a security breach)

--
-- Login as u1@db2
--

CREATE TABLE t1(c INT);
--> Error: CREATE command denied to user 'u1'@'localhost' for table 't1'

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/23293

ChangeSet@1.2423, 2007-03-29 16:21:50+04:00, anozdrin@booka.opbmk +5 -0
  Fix for BUG#27337: Privileges are not properly restored.
  
  The problem was that THD::db_access variable was not restored after
  database switch in stored-routine-execution code.
  
  The fix is to restore THD::db_access in this case.
  
  Unfortunately, this fix requires additional changes,
  because in prepare_schema_table(), called on the parsing stage, we checked
  privileges. That was wrong according to our design, but this flaw haven't
  struck so far, because it was masked. All privilege checkings must be
  done on the execution stage in order to be compatible with prepared statements
  and stored routines. So, this patch also contains patch for
  prepare_schema_table(), which moves the checkings to the execution phase.

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/23650

ChangeSet@1.2427, 2007-04-03 15:11:34+04:00, anozdrin@ibm.opbmk +6 -0
  Fix for BUG#27337: Privileges are not properly restored.
  
  The problem was that THD::db_access variable was not restored after
  database switch in stored-routine-execution code.
  
  The fix is to restore THD::db_access in this case.
  
  Unfortunately, this fix requires additional changes,
  because in prepare_schema_table(), called on the parsing stage, we checked
  privileges. That was wrong according to our design, but this flaw haven't
  struck so far, because it was masked. All privilege checkings must be
  done on the execution stage in order to be compatible with prepared statements
  and stored routines. So, this patch also contains patch for
  prepare_schema_table(), which moves the checkings to the execution phase.

Pushed into 5.0.40 and 5.1.18

Noted in 5.0.40, 5.1.18 changelogs.

Security fix: If a stored routine was declared using SQL SECURITY
INVOKER, a user who invoked the routine could gain privileges.

Pushed into 5.1.18-beta

Pushed into 5.0.42

Moved the 5.0.40 changelog entry to 5.0.42.