Definer Security Model

Stored procedures, table valued functions, and views use the definer security model. This means that when a user executes an extensibility object, the object is executed using the security permissions of the user that created that object. So, even if a user does not have the permissions to access or modify the data in a table, the user could be able to use extensibility objects to access the data in that table in a controlled way.

Once an extensibility object has been created, a user only needs the EXECUTE security permission in order to execute that object. It doesn’t matter how complex the object’s body is, and it doesn’t matter if the body calls other objects or executes SQL statements that reference tables.

However, even if a user has permissions to execute an extensibility object, the SELECT, CALL, or ECHO statement that references that object can still fail for security reasons. This would occur if the object’s definer does not have the correct permissions to execute everything in the object’s body. The definer’s permissions are not checked when the object is created, only when the object is executed. If the object’s definer does not have the correct permissions to execute the object’s body, then no user will be able to successfully 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.

Last modified: November 5, 2021

Was this article helpful?