Stored procedures and privileges

In seekdb, PL privileges are used to manage user operations on stored procedures and stored functions. Stored procedures are routines in the database that can be created using the CREATE PROCEDURE and CREATE FUNCTION statements. You can specify the database to which a stored procedure belongs when you create it. To execute a stored procedure, you use the CALL statement. Stored functions are directly referenced in expressions and return a value when the expression is evaluated.

Create and manage stored procedures

The following table describes the privileges related to stored procedures:

Privilege	Description
CREATE ROUTINE	The privilege to create PROCEDURE and FUNCTION.
EXECUTE	The privilege to execute PROCEDURE and FUNCTION.
ALTER ROUTINE	The privilege to modify and delete PROCEDURE and FUNCTION.

• To create a stored procedure, you must have the CREATE ROUTINE privilege. After you grant the CREATE ROUTINE privilege to a user, the user can create a PROCEDURE.

• After you create a PROCEDURE, the system automatically grants the EXECUTE and ALTER ROUTINE privileges to the PROCEDURE.

Here is an example:

GRANT CREATE ROUTINE ON my.* TO mingye;
CREATE PROCEDURE p1()
    -> BEGIN
    -> SELECT 1 FROM dual;
    -> END;
    -> /

The return result is as follows:

+---------------------------------------------------------------------+
| Grants for mingye@%                                                 |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mingye`@`%`                                  |
| GRANT CREATE ROUTINE ON `my`.* TO `mingye`@`%`                      |
| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `my`.`p1` TO `mingye`@`%` |
+---------------------------------------------------------------------+

automatic_sp_privileges system variable

If the automatic_sp_privileges system variable is set to 1 by default, the system automatically grants the EXECUTE and ALTER ROUTINE privileges. If you do not need these privileges, you can manually revoke them.

Here is an example:

SELECT @@automatic_sp_privileges;
+---------------------------+
| @@automatic_sp_privileges |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.03 sec)

The query result shows that the automatic_sp_privileges system variable is set to 1. You can now manually revoke the automatically granted privileges.

REVOKE EXECUTE ON PROCEDURE my.p1 FROM mingye;
CALL p1();/

The automatic_sp_privileges variable is a global variable. You must have the SUPER privilege to modify it. Here is an example:

SET GLOBAL automatic_sp_privileges = 0;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

DEFINER and SECURITY TYPE

You can specify DEFINER when you create a PROCEDURE. If you omit DEFINER, it defaults to the user who created the PROCEDURE.

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
    BEGIN
    UPDATE t1 SET counter = counter + 1;
    END;

SHOW PROCEDURE STATUS LIKE '%p2%';

The result set is as follows:

+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name | Type      | Definer         |x| Security_type |
+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | p2   | PROCEDURE | admin@localhost |x| DEFINER       |
+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

User who specifies DEFINER:

• A user with the SUPER privilege can specify any value for DEFINER.
• Otherwise, DEFINER can only be specified as the current user, using CURRENT_USER, CURRENT_USER(), or directly writing the text.

If DEFINER specifies a non-existent user, an orphan object will be created.

When calling a stored object, the caller must have the following privileges:

• The caller must have the EXECUTE privilege.
• The DEFINER must have the EXECUTE privilege.

During the execution of a stored object, it will be executed based on the privileges of the user specified by DEFINER.

Make a stored object an orphan object

A stored object becomes an orphan object when you attempt to delete a user who is defined as the DEFINER of any stored object. The operation will fail due to dependencies. To avoid this, the system may require you to delete or modify these stored objects before successfully deleting the user.

• If a user is the DEFINER of any stored object, the DROP USER operation will fail and an error will be returned.
• If a user is the DEFINER of any stored object, the RENAME USER operation will fail and an error will be returned.

If the database system allows you to delete a user without deleting or modifying the corresponding stored objects, the stored objects will become orphan objects without a DEFINER after the user is deleted.

Adopt an orphan object

If there are orphan stored objects, which are objects that currently do not have a valid DEFINER, creating a new user with the same name as the original DEFINER of the orphan object will not automatically adopt the object. The orphan object will remain in its original state until explicitly modified.

If a user is the DEFINER of any stored object, the CREATE USER operation will fail and an error will be returned.

SECURITY TYPE

You can specify SQL SECURITY when you create a stored object. If you do not explicitly specify it, the default value is DEFINER. It can only be set to either DEFINER or INVOKER.

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p2()
     SQL SECURITY INVOKER
     BEGIN
       UPDATE t1 SET counter = counter + 1;
     END;
     /
SHOW PROCEDURE STATUS LIKE '%p2%';

The result set is as follows:

+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name | Type      | Definer         |x| Security_type |
+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | p2   | PROCEDURE | admin@localhost |x| INVOKER       |
+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

If you specify INVOKER for the SECURITY TYPE, the execution will use the caller's privileges during execution, making DEFINER invalid.

Triggers and events do not have SQL SECURITY and are always executed in the DEFINER context.

Example

The following example shows how to create a stored procedure. The stored procedure is named GetHighSalaryEmployees, accepts an input parameter minSalary, and returns the information of employees whose salary is higher than the specified value.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary INT);

INSERT INTO employees (id, name, salary) VALUES
(1, 'John Doe', 50000),
(2, 'Jane Smith', 60000),
(3, 'Bob Johnson', 45000);

DELIMITER //

CREATE PROCEDURE GetHighSalaryEmployees(IN minSalary INT)
BEGIN
    SELECT name, salary
    FROM employees
    WHERE salary > minSalary;
END //

DELIMITER ;

Now, call the stored procedure:

CALL GetHighSalaryEmployees(55000);

The result is as follows:

+------------+--------+
| name       | salary |
+------------+--------+
| Jane Smith |  60000 |
+------------+--------+
1 row in set

The preceding call returns the information of employees whose salary is higher than 55000.

In the preceding example, the CREATE ROUTINE privilege is used to create the stored procedure. To grant other users the privilege to execute this stored procedure, use the GRANT statement:

GRANT EXECUTE ON PROCEDURE GetHighSalaryEmployees TO 'your_user'@'your_host';