Procedural Extensions Security

Security Models Used by Procedural Extensions

The Definer Security Model

An object that uses the definer security model is executed using the security permissions of the user that created the object. Stored procedures use the definer security model, by default. Table valued functions, and views always use the definer security model.

If such an object's definer does not have the needed permissions to execute all of the commands in the object's body, the object will fail to execute. The definer’s permissions are not checked when the object is created; the permissions are checked only when the object is executed.

Once the object has been created, a user only needs the EXECUTE security permission to 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.

If a procedure p2 uses the current user security model (see the next section), but was called by a procedure p1 that uses the definer security model, p2 will run as the user who defined p1.

Although stored procedures use the definer security model by default, you can indicate in a stored procedure definition that definer security be used. This is shown in the following stored procedure definition, where AUTHORIZE AS DEFINER is specified.

DELIMITER //

CREATE PROCEDURE p() AUTHORIZE AS DEFINER AS
BEGIN
    ...
END //

DELIMITER ;

Note that this stored procedure definition could have included a DECLARE block (as is the case for any stored procedure).

The Current User Security Model

Stored procedures can be configured to use the current user security model. When the current user executes a stored procedure that uses the current security model, the stored procedure is executed using the security permissions of that user.

If such a stored procedure does not have the needed permissions to execute all of the commands in the object's body, the object will fail to execute. The current user's permissions are not checked when the object is created; the permissions are checked only when the object is executed.

If a procedure p2 uses the current user security model, but was called by a procedure p1 that uses the definer security model (see the previous section), p2 will run as the user who defined p1.

To create a stored procedure that uses current user security, specify the AUTHORIZE AS CURRENT_USER clause in a CREATE PROCEDURE statement, as shown in the following definition.

DELIMITER //

CREATE PROCEDURE p() AUTHORIZE AS CURRENT_USER AS
BEGIN
    ...
END //

DELIMITER ;

Note that this stored procedure definition could have included a DECLARE block (as is the case for any stored procedure).

See the CURRENT_USER page for more information.

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.

SQL

SET sql_mode = 'PIPES_AS_CONCAT';

DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
use singlestore_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:

SQL

CALL get_student_info(1, 'Smith');

+-----------+------------+
| last_name | first_name |
+-----------+------------+
| Smith     | John       |
| Smith     | Sally      |
+-----------+------------+

Call get_student_info with access level 2:

SQL

CALL get_student_info(2, 'Smith');

+-----------+------------+------------------+
| last_name | first_name | address          |
+-----------+------------+------------------+
| Smith     | John       | 10 Anywhere St   |
| Smith     | Sally      | 20 Anywhere Lane |
+-----------+------------+------------------+

Call get_student_info with access level 3:

SQL

CALL get_student_info(3, 'Smith');

+-----------+------------+------------------+------+
| last_name | first_name | address          | gpa  |
+-----------+------------+------------------+------+
| Smith     | John       | 10 Anywhere St   |  3.5 |
| Smith     | Sally      | 20 Anywhere Lane |    4 |
+-----------+------------+------------------+------+