SingleStore Managed Service

Security Models Used by Procedural Extensions
The Definer Security Model

An object that uses the definer security model is executed using the security permissions of the user that created the object. Stored procedures use the definer security model, by default. Table valued functions, and views always use the definer security model.

If such an object's definer does not have the needed permissions to execute all of the commands in the object's body, the object will fail to execute. The definer’s permissions are not checked when the object is created; the permissions are checked only when the object is executed.

Once the object has been created, a user only needs the EXECUTE security permission to execute that object.

If the object’s definer is deleted, then the object will no longer be able to be executed. If the object’s definer ever has a permission REVOKED that is required to execute the object’s body, then the object can no longer be executed.

If a procedure p2 uses the current user security model (see the next section), but was called by a procedure p1 that uses the definer security model, p2 will run as the user who defined p1.

Although stored procedures use the definer security model by default, you can indicate in a stored procedure definition that definer security be used. This is shown in the following stored procedure definition, where AUTHORIZE AS DEFINER is specified.

DELIMITER //

CREATE PROCEDURE p() AUTHORIZE AS DEFINER AS
BEGIN
    ...
END //

DELIMITER ;

Note that this stored procedure definition could have included a DECLARE block (as is the case for any stored procedure).

The Current User Security Model

Stored procedures can be configured to use the current user security model. When the current user executes a stored procedure that uses the current security model, the stored procedure is executed using the security permissions of that user.

If such a stored procedure does not have the needed permissions to execute all of the commands in the object's body, the object will fail to execute. The current user's permissions are not checked when the object is created; the permissions are checked only when the object is executed.

If a procedure p2 uses the current user security model, but was called by a procedure p1 that uses the definer security model (see the previous section), p2 will run as the user who defined p1.

To create a stored procedure that uses current user security, specify the AUTHORIZE AS CURRENT_USER clause in a CREATE PROCEDURE statement, as shown in the following definition.

DELIMITER //

CREATE PROCEDURE p() AUTHORIZE AS CURRENT_USER AS
BEGIN
    ...
END //

DELIMITER ;

Note that this stored procedure definition could have included a DECLARE block (as is the case for any stored procedure).