Stored objects and privilege management

In OceanBase Database, PL privileges are used to manage user operations on stored objects, including stored procedures and stored functions. Stored objects are a type of routines in a database. You can use the CREATE PROCEDURE or CREATE FUNCTION statement to create a stored procedure or stored function and specify the database to which the stored procedure or stored function belongs when you create it. A stored procedure is executed by using the CALL statement. A stored function is directly referenced in an expression and returns a value when it calculates the expression.

Creation and privileges

The following table describes the privileges on stored objects in OceanBase Database.

• To create a stored object, you must have the CREATE ROUTINE privilege. A user granted the CREATE ROUTINE privilege can create stored procedures and stored functions.

• After a stored procedure is created, the system automatically grants the EXECUTE and ALTER ROUTINE privileges on the stored procedure to the user who created it.

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 system variable

If the default value of the automatic_sp_privileges system variable is set to 1, the system automatically grants the EXECUTE and ALTER ROUTINE privileges. If you do not need either privilege, you can manually delete it.

Here is an example:

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

The value of automatic_sp_privileges is 1. In this case, you can manually delete the privileges that are automatically granted to you.

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

Note that automatic_sp_privileges 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

You can specify DEFINER when you create a stored procedure. If DEFINER is not specified during the creation of a stored procedure, it is automatically set to the user who created the stored 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 a value for DEFINER:

• A user with the SUPER privilege can specify any value for DEFINER.
• Otherwise, the value of DEFINER can only be the current user. You can use CURRENT_USER or CURRENT_USER() to specify a value for DEFINER, or directly enter a value.

If you specify a nonexistent user for DEFINER, an orphan object is created.

The privileges required for calling a stored object are as follows:

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

A stored object is executed based on the privileges of its definer, which is specified by DEFINER.

Convert a stored object into an orphan object

When you attempt to drop a user that has been defined as the definer of any stored object, the operation will fail due to the dependency. In this case, you must first drop or modify the stored object, and then drop the user.

• When you execute the DROP USER statement to drop a user that is the definer of a stored object, the execution fails and an error is returned.
• When you execute the RENAME USER statement to rename a user that is the definer of a stored object, the execution fails and an error is returned.

If the database system allows you to drop a user defined as the definer of a stored object without dropping or modifying the stored object first, the stored object becomes an orphan object after the user is dropped.

Enable an orphan object to be adopted by a user

If an orphan object, namely an object without a valid definer, exists, the orphan object remains in the original state after you create a user with the same name as the original definer of the orphan object. You need to explicitly make the user adopt the orphan object.

When you execute the CREATE USER statement to create a user that is the definer of a stored object, the execution fails and an error is returned.

SECURITY TYPE

You can specify SQL SECURITY when you create a stored object. If this parameter is not explicitly specified, the default value is DEFINER. The valid values are DEFINER and 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 INVOKER is specified for SECURITY TYPE, the stored object is executed based on the privileges of the caller. In this case, DEFINER is invalid.

The SQL SECURITY parameter is invalid for triggers and events, which are always executed in the definer context.

Examples

The following example shows how to create a stored procedure. Create a stored procedure named GetHighSalaryEmployees that accepts an input parameter minSalary and returns information about employees whose salary exceeds 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 ;

Call the stored procedure.

obclient > CALL GetHighSalaryEmployees(55000);

The return result is as follows:

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

The preceding call will return the information about employees whose salary exceeds 55,000.

In the preceding example, the stored procedure is created by using the CREATE ROUTINE privilege. To grant other users the privilege to execute this stored procedure, execute the following statement:

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