{question}
How to change the definer of a Stored Procedure?
{question}
{answer}
The definer of a Stored Procedure cannot be directly altered. To change it, you will need to recreate the procedure with the desired definer.
Below are the recommended steps to change the definer of a Stored Procedure.
1. Get the Stored Procedure CREATE statement using the command: SHOW CREATE PROCEDURE <procedure_name>;
2. While connected to the database as the desired user (new definer) that is expected to own these procedures, execute the existing Stored Procedure's CREATE statement CREATE OR REPLACE PROCEDURE <procedure_definition>; this step will recreate and replace the existing Stored Procedure with new definer.
3. Verify the definer change using the SHOW PROCEDURES or SHOW PROCEDURE STATUS command.
Note: Make sure the desired database user has sufficient privilege to be the new definer. Click here to learn about the Grants required for creating a Stored Procedure.
Example:
Here we have a procedure thisworks with definer adminand we have a user named db_user . Now we are going to change the definer to db_user.
mysql> SHOW PROCEDURE STATUS;
+---------+-----------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation | Routine Lifetime |
+---------+-----------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+------------------+
| testing | thisworks | PROCEDURE | admin@% | 2021-07-05 08:02:30 | 2021-07-05 08:01:22 | DEFINER | | | | | PERSISTENT |
+---------+-----------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+------------------+
1 row in set (0.33 sec)
The next step is to log in as db_user, into the cluster via SQL client or SingleStore Studio
mysql> use testing;
Database changed
mysql> SHOW CREATE PROCEDURE thisworks;
+-----------+-------------------+-------------------------------------------------------------------------------------+----------------------+----------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection |
+-----------+-------------------+-------------------------------------------------------------------------------------+----------------------+----------------------+
| thisworks | STRICT_ALL_TABLES | CREATE OR REPLACE PROCEDURE `thisworks`() RETURNS void AS BEGIN ECHO SELECT 1; END; | utf8 | utf8_general_ci |
+-----------+-------------------+-------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.21 sec)
Now execute the Stored Procedure CREATE statement,
Note: Now, we are executing the same statement as db_user
mysql> DELIMITER //
mysql> CREATE OR REPLACE PROCEDURE `thisworks`() RETURNS void AS BEGIN ECHO SELECT 1; END //
Query OK, 1 row affected (0.36 sec)
Validate the change: Now the definer changed to db_user user from the admin user.
mysql> SHOW PROCEDURE STATUS;
+---------+-----------+-----------+-----------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation | Routine Lifetime |
+---------+-----------+-----------+-----------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+------------------+
| testing | thisworks | PROCEDURE | db_user@% | 2021-07-05 08:16:51 | 2021-07-05 08:01:22 | DEFINER | | | | | PERSISTENT |
+---------+-----------+-----------+-----------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+------------------+
1 row in set (0.32 sec)
Above MySQL Compatible command doesn't work with the older version of SingleStore 6.8.X versions and before, so to validate in older versions, use the below command:
mysql> use testing;
Database changed
mysql> SHOW PROCEDURES;
+-----------------------+------------------+-----------+
| Procedures_in_testing | Routine Lifetime | Definer |
+-----------------------+------------------+-----------+
| thisworks | Permanent | db_user@% |
+-----------------------+------------------+-----------+
1 row in set (0.26 sec)
Note: Test the above procedure before implementing the same in production.
Click here to learn about creating Stored Procedures in SingleStore.
{answer}