Manage stored routines and privileges

In OceanBase Database, PL privileges are used to manage users' operations on stored routines. Stored routines are a type of routine in the database. You can create a stored routine by using the CREATE PROCEDURE or CREATE FUNCTION statement. When you create a stored routine, you can specify the database to which it belongs. You can execute a stored routine by using the CALL statement. You can directly reference a stored function in an expression and it returns a value when the expression is evaluated.

Create stored routines and manage privileges

The following table describes the privileges related to stored routines in OceanBase Database:

• To create a stored routine, you must have the CREATE ROUTINE privilege. After you are granted the CREATE ROUTINE privilege, you 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:

mysql> GRANT CREATE ROUTINE ON my.* TO mingye;
mysql> 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

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

Here is an example:

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

The automatic_sp_privileges system variable is set to 1. You can manually revoke the automatically granted privileges.

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

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

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

When you create a PROCEDURE, you can specify the DEFINER. If you omit the DEFINER clause, the system automatically sets the DEFINER to the user who created the PROCEDURE.

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

mysql> SHOW PROCEDURE STATUS LIKE '%p2%';

The return result is as follows:

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

Specify the DEFINER:

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

If the DEFINER is specified as a non-existent user, an orphan object will be created.

When you execute a stored routine, you must have the following privileges:

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

During the execution of the stored routine, the privileges of the user specified as the DEFINER are used.

Make a stored routine an orphan object

A stored routine becomes an orphan object when you try to drop a user who is the DEFINER of any stored routine. The operation will fail due to dependencies. To avoid this, the system may require you to delete or modify these stored routines before dropping the user.

• When you execute the DROP USER statement and the user is the DEFINER of any stored routine, the operation will fail and an error will be returned.
• When you execute the RENAME USER statement and the user is the DEFINER of any stored routine, the operation will fail and an error will be returned.

If the database system allows you to drop a user without deleting or modifying the stored routines, the stored routines will become orphan objects without a DEFINER after the user is dropped.

Adopt an orphan object

If a stored routine is an orphan object, it does 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 object will remain in its original state until you explicitly change it.

When you execute the CREATE USER statement and the user is the DEFINER of any stored routine, the operation will fail and an error will be returned.

SECURITY TYPE

When you create a stored routine, you can specify the SQL SECURITY clause. If you do not specify the SQL SECURITY clause, the default value is DEFINER. The SQL SECURITY clause can only be set to DEFINER or INVOKER.

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

The return result is as follows:

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

If the SECURITY TYPE is specified as INVOKER, the privileges of the caller are used during execution. In this case, the DEFINER will be ineffective.

*Triggers and events do not have a SQL SECURITY clause. They are always executed in the context of the DEFINER.

Examples

The following example demonstrates how to create a stored procedure. The stored procedure is named GetHighSalaryEmployees, accepts an input parameter minSalary, and returns information about 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:

obclient > CALL GetHighSalaryEmployees(55000);

The return result is as follows:

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

The above call returns information about employees whose salary is higher than 55000.

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

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