Security: Procedures and Triggers

Procedures and triggers provide security by allowing users limited access to data in tables that they cannot directly examine or modify.

Triggers, for example, execute under the table privileges of the owner of the associated table, but any user with privileges to insert, update or delete rows in the table can fire them. Similarly, procedures (including user-defined functions) execute with privileges of the procedure owner, but any user granted privileges can call them. Procedures and triggers can (and usually do) have different privileges than the user ID that invoked them.

Procedures

In high-security systems, consider disallowing access to all base tables, and instead permit access to data and tasks through procedures. To restrict access using procedures:

Create a role for each set of authorized tasks to be performed and grant the role the applicable system privileges.
Grant each of these roles to a single common role.
Grant EXECUTE privileges on the procedure for performing the authorized tasks to the applicable role.
When a new user is created, grant only the roles required for the tasks the user will perform.

Security: Procedures and Triggers

Procedures

See also