Creating stored procedure with the desired definer

{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}