Procedural Extensions Security

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

Procedural Extensions Security

On this page

Application-Based User Security in Stored Procedures

In this section

Was this article helpful?

On this page

Was this article helpful?