{question}
How to create a stored procedure with the desired definer?
{question}
{answer}
In SingleStore Database, DEFINER is always implicitly set by the engine to the user running the CREATE PROCEDURE statement. Therefore, it's not possible to set definer explicitly.
To CREATE PROCEDURE with the desired definer, simply connect to the cluster via the desired user and CREATE the procedure.
Note: Make sure the desired database user has sufficient privilege to create a stored procedure. Click here to learn about the Grants required for creating a Stored Procedure.
Example:
Here we are going to create a stored procedure called as thisworks
with user as admin
Connect to the cluster as admin
Connect via SQL Client like below or using SingleStore Studio.
singlestore -u admin -p
Now Create a Stored procedure called as thisworks
singlestore> use memsql_demo;
singlestore> DELIMITER //
singlestore> CREATE OR REPLACE PROCEDURE `thisworks`() RETURNS void AS BEGIN ECHO SELECT 1; END;//
Query OK, 1 row affected (0.01 sec)
Now Check the stored procedure definer as below:
singlestore> use memsql_demo;
Database changed
singlestore> show procedures;
+---------------------------+------------------+----------------+
| Procedures_in_memsql_demo | Routine Lifetime | Definer |
+---------------------------+------------------+----------------+
| thisworks | Permanent | admin@% |
+---------------------------+------------------+----------------+
1 row in set (0.00 sec)
Click here to learn about changing the definer of a stored procedure.
{answer}