Definer Security Model
Stored procedures, table valued functions, and views use the definer security model. This means that when a user executes an extensibility object, the object is executed using the security permissions of the user that created that object. So, even if a user does not have the permissions to access or modify the data in a table, the user could be able to use extensibility objects to access the data in that table in a controlled way.
Once an extensibility object has been created, a user only needs the EXECUTE security permission in order to execute that object. It doesn’t matter how complex the object’s body is, and it doesn’t matter if the body calls other objects or executes SQL statements that reference tables.
However, even if a user has permissions to execute an extensibility object, the SELECT, CALL, or ECHO statement that references that object can still fail for security reasons. This would occur if the object’s definer does not have the correct permissions to execute everything in the object’s body. The definer’s permissions are not checked when the object is created, only when the object is executed. If the object’s definer does not have the correct permissions to execute the object’s body, then no user will be able to successfully execute that object.
If the object’s definer is deleted, then the object will no longer be able to be executed. If the object’s definer ever has a permission REVOKED that is required to execute the object’s body, then the object can no longer be executed.
Application-Based User Security in Stored Procedures
Within stored procedures, you can handle scenarios where your application’s users are given access to data based on an access level.
For example, consider a reporting application for an educational institution. When this application’s users are provisioned, they are assigned an access level of 1,2 or 3. When users access a feature to view a student’s data, the application calls the get_student_info procedure, which is shown below. This procedure returns only the data that the users’ access level allows.
SET sql_mode = 'PIPES_AS_CONCAT';
DROP DATABASE IF EXISTS memsql_docs_example;
CREATE DATABASE memsql_docs_example;
use memsql_docs_example;
CREATE TABLE student(first_name TEXT, last_name TEXT, address TEXT, gpa FLOAT);
INSERT INTO student VALUES ('John', 'Smith', '10 Anywhere St', 3.5),
('Sally', 'Smith', '20 Anywhere Lane', 4.0), ('Ann', 'Brown', '30 Anywhere Dr', 3.8);
DELIMITER //
CREATE PROCEDURE get_student_info(user_access_level INT, last_name TEXT) AS
DECLARE
cols_to_return TEXT;
BEGIN
IF user_access_level = 1 THEN
cols_to_return = 'last_name, first_name';
ELSIF user_access_level = 2 THEN
cols_to_return = 'last_name, first_name, address';
ELSIF user_access_level = 3 THEN
cols_to_return = 'last_name, first_name, address, gpa';
END IF;
EXECUTE IMMEDIATE 'ECHO SELECT ' || cols_to_return ||
' FROM student WHERE last_name = ' || QUOTE(last_name) ||
' ORDER BY last_name';
END
//
DELIMITER ;
Call get_student_info with access level 1:
CALL get_student_info(1, 'Smith');
Output:
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| Smith | John |
| Smith | Sally |
+-----------+------------+
Call get_student_info with access level 2:
CALL get_student_info(2, 'Smith');
Output:
+-----------+------------+------------------+
| last_name | first_name | address |
+-----------+------------+------------------+
| Smith | John | 10 Anywhere St |
| Smith | Sally | 20 Anywhere Lane |
+-----------+------------+------------------+
Call get_student_info with access level 3:
CALL get_student_info(3, 'Smith');
Output:
+-----------+------------+------------------+------+
| last_name | first_name | address | gpa |
+-----------+------------+------------------+------+
| Smith | John | 10 Anywhere St | 3.5 |
| Smith | Sally | 20 Anywhere Lane | 4 |
+-----------+------------+------------------+------+
