Privileges to Execute Trigger Actions

If you do not own the trigger, but the access privileges held by the trigger owner include WITH GRANT OPTION, then for each triggered SQL statement you inherit the privileges of the trigger owner (with grant option), in addition to any privileges that have been granted to you individually, or through an active or default role that you hold, or that you hold as a member of the PUBLIC group. If the triggered action calls a UDR, you need Execute privilege on the UDR, or the trigger owner must have Execute privilege with grant option.

Important: As a security precaution, discretionary access privileges that the user holds only from a role (but that were not granted to the user individually or as member of the PUBLIC group) cannot provide access to tables outside the current database through a triggered action or through a trigger routine.

As a security precaution, however, discretionary access privileges that the user holds only from a role (but that were not granted to the user individually or as member of the PUBLIC group) cannot provide access to tables outside the current database through a triggered action or through a trigger routine.

While executing the UDR, however, you do not inherit the privileges of the trigger owner; instead, you receive the privileges granted with the UDR, depending on whether the routine is a DBA-privileged or an owner-privileged UDR:
  1. Privileges for a DBA-privileged UDR

    When a UDR is registered with the DBA keyword, and you are granted the Execute privilege on the UDR, the database server automatically grants you temporary DBA privileges that are available only when you are executing the UDR.

  2. Privileges for an owner-privileged UDR

    If the UDR was created without the DBA keyword, but the owner of the UDR was granted the necessary privileges on the underlying database objects with the WITH GRANT OPTION keywords, then you inherit these privileges when you are granted the Execute privilege on the UDR.

For a UDR that is not DBA privileged, all non-qualified database objects that the UDR references are implicitly qualified by the name of the UDR owner.

If the UDR owner has no WITH GRANT OPTION privilege, you have your original privileges on the underlying database objects when the UDR executes. For more information on privileges on SPL routines, refer to the HCL OneDB™ Guide to SQL: Tutorial.

A view that has no INSTEAD OF trigger has only Select (with grant option) privilege. If an INSTEAD OF trigger is created on it, however, then the view has Insert (with grant option) privilege during creation of the trigger. The view owner can now grant only Select and Insert privileges to others. This is independent of the trigger action. It is not necessary to obtain Execute (with grant option) privilege on the procedure or function. By default, Execute privilege (without grant option) is granted on each UDR in the action list.

You can use roles with triggers. Role-related statements (CREATE ROLE, DROP ROLE, GRANT, REVOKE, and SET ROLE) and SET SESSION AUTHORIZATION statements are valid in a UDR that the triggered action invokes. Privileges that a user acquired by enabling a role or by a SET SESSION AUTHORIZATION statement are not relinquished when a trigger is executed.

On a complex view (one with columns from more than one table), only the owner or DBA can create an INSTEAD OF trigger. The owner receives Select privileges when the trigger is created. Only after obtaining the required Execute privileges can the owner of the view grant privileges to other users. When the trigger on the complex view is dropped, all of these privileges are revoked.