In OceanBase AI Database, the PL privilege is used to manage user operations on stored procedures and stored functions. A stored procedure is a routine in the database that can be created using the CREATE PROCEDURE or CREATE FUNCTION statement, and its database ownership can be specified at creation. Stored procedures are executed using the CALL statement, while stored functions are referenced directly in expressions and return a value when the expression is evaluated.
Create a stored procedure and grant privileges
The relationship between OceanBase AI Database's privilege system and stored procedures is as follows:
Permission |
Description |
|---|---|
| CREATE ROUTINE | Can create PROCEDURE and FUNCTION privilege. |
| EXECUTE | executable PROCEDURE and FUNCTION privilege. |
| ALTER ROUTINE | Modifiable and Deletable PROCEDURE and FUNCTION privilege. |
You must have the
CREATE ROUTINEprivilege to create a stored procedure. After being granted theCREATE ROUTINEprivilege, users can createPROCEDURES.After you create a
PROCEDURE, the system automatically grants the correspondingEXECUTEandALTER ROUTINEprivileges on the createdPROCEDURE.
An example is as follows:
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 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.
An example is as follows:
mysql> SELECT @@automatic_sp_privileges;
+---------------------------+
| @@automatic_sp_privileges |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.03 sec)
The query result shows that the value of the automatic_sp_privileges system parameter is 1. You can now manually drop this automatically created privilege.
mysql> REVOKE EXECUTE ON PROCEDURE my.p1 FROM mingye;
mysql> CALL p1();/
automatic_sp_privileges is a global variable that requires the SUPER privilege to modify. 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 the DEFINER clause when creating a PROCEDURE. If the DEFINER clause is omitted during definition, it defaults 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 |
+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
User who specifies DEFINER:
- A user with the
SUPERprivilege can assign any value toDEFINER. - Otherwise,
DEFINERcan only be set to the current user by usingCURRENT_USER,CURRENT_USER(), or writing the text directly.
If DEFINER specifies a non-existent user, an orphan object will be created.
When invoking storage objects, the executor must have the following privileges:
- The caller must have the
EXECUTEprivilege. - The
DEFINERprivilege is required forEXECUTE.
During the execution of a stored object, the privileges of the user specified by DEFINER are used.
Make a stored object an orphan object
A stored object becomes an orphan when you attempt to delete a user who is defined as the DEFINER of that object. The operation fails due to dependencies. To avoid this, the system may require you to delete or modify these stored objects first, so that the user can be deleted successfully.
- When executing DROP USER, if the user is the
DEFINERof any stored object, the operation will fail and an error will be returned. - When executing RENAME USER, if the user is the
DEFINERof any stored object, the operation will fail and an error will be returned.
If the database system allows deleting a user without deleting or modifying the corresponding stored objects, then after the user is deleted, the related stored objects become orphan objects without a DEFINER.
Adopt an orphan object
If orphan storage objects exist—objects that currently have no valid DEFINER—creating a new user with the same DEFINER name as the orphan objects will not automatically adopt them. The orphan objects will remain in their original state until explicitly changed.
When executing CREATE USER, if the user is the DEFINER of any stored object, the operation will fail and an error will be returned.
SECURITY TYPE
You can specify SQL SECURITY when creating a stored object. If not explicitly specified, the default value is DEFINER. It can only be set to two values: 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 of INVOKER is specified, the statement will be executed with the privileges of the caller during execution, and DEFINER will be invalid.
*Triggers and events do not have SQL SECURITY and are always executed in the DEFINER context.
Examples
The following example demonstrates how to create a stored procedure. In this example, the stored procedure is named GetHighSalaryEmployees, accepts an input parameter minSalary, and returns information about employees whose salaries are 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 ;
Call the stored procedure now:
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 of employees with salaries higher than 55,000.
In the preceding example, the CREATE ROUTINE privilege is used to create a 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';