# 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-function-tvf.md) use the definer security model, by default. [Table valued functions](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-function-tvf.md), and [views](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/alter-view.md) 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/current-user.md) 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'); ``` ```output +-----------+------------+ | last_name | first_name | +-----------+------------+ | Smith | John | | Smith | Sally | +-----------+------------+ ``` Call `get_student_info` with access level `2`: ```sql 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`: ```sql 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 | +-----------+------------+------------------+------+ ``` *** Modified at: November 29, 2022 Source: [/db/v9.1/developer-resources/procedural-extensions/procedural-extensions-security/](https://docs.singlestore.com/db/v9.1/developer-resources/procedural-extensions/procedural-extensions-security/) (An index of the documentation is available at /llms.txt)