See ECHO SELECT for more details and examples. This is useful for debugging or returning multiple results from a single stored procedure call. In ECHO SELECT, the SELECT statement will execute and the resulting rows will be returned to the client as part of a multi-result set.

SELECT statements may be used if assigned to a QUERY type variable or if used as part of an ECHO SELECT statement. Additionally, only the following Data Definition Language statement types are supported in the body of a stored procedure:

A query-type value is a query, which may contain bound parameters, that can evaluated to produce a row set, or stored in a query-type variable.Ī vast majority of Data Manipulation Language statements including EXPLAIN (but not EXPLAIN BALANCE), PROFILE, and all SHOW commands are supported in the body of a stored procedure. Scalar and non-scalar-valued types are described in Data Types. It is assumed the definer has EXECUTE permission:Īny scalar-valued, non-scalar-valued, or query data type. Here is a stored procedure example where the executor permissions will be the same as the definer's permissions. See the Procedural Extensions Security page for more details on how the DEFINER and CURRENT_ USER security model works. All other users must have EXECUTE permission to execute the stored procedure.Ĭreating a stored procedure with different user permissions than the stored procedure creator can be useful for securing your database and ensuring that the appropriate actions are performed only by authorized users. If AUTHORIZE AS DEFINER ] | AUTHORIZE AS CURRENT_ USER [DEFINER = options are not used when creating a stored procedure, the definer is the user who created it by default. Users must have EXECUTE permission to execute stored procedures created with the AUTHORIZE AS CURRENT_ USER option. A stored procedure created using the AUTHORIZE AS CURRENT_ USER option, the stored procedure is executed using the current user's security permissions. The definer must have EXECUTE permission. A stored procedure that uses the AUTHORIZE AS DEFINER [DEFINER option is executed using the security permissions of the definer assigned to that stored procedure. It determines the security permissions of potential executors of a stored procedure. In addition, you can specify the collation for tables and columns introduced in procedural extensions.įor more information, see Character Encoding.ĪUTHORIZE AS DEFINER ] | AUTHORIZE AS CURRENT_ USER [DEFINER = is an optional parameter. In a similar fashion, you can use variables with any supported character sets. Master Aggregator High Availability After Availability Zone Failure.Operations that Take Either a Database or a Workspace Level Lock.Getting Started with SingleStoreDB Cloud.

Sqlpro save queries