Start of change

ALTER TRIGGER

The ALTER TRIGGER statement changes the description of a trigger at the current server.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.

Authorization

The privilege set that is defined below must include at least one of the following:

  • Ownership of the trigger
  • The ALTERIN privilege on the schema
  • SYSADM authority
  • SYSCTRL authority
  • Start of changeSystem DBADMEnd of change

The authorization ID that matches the schema name implicitly has the ALTERIN privilege on the schema.

Privilege set: If the statement is embedded in an application program, the privilege set is the set of privileges that are held by the owner of the plan or package. If the statement is dynamically prepared, the privilege set is the set of privileges that are held by the SQL authorization ID of the process. The specified trigger name can include a schema name (a qualifier). However, if the schema name is not the same as one of the authorization ID of the process, one of the following conditions must be met:

  • Start of changeThe privilege set includes SYSADM authority, SYSCTRL authority, or system DBADM authority.End of change
  • The authorization ID of the process has the ALTERIN privilege on the schema.
If the SECURED option is specified, at least one of the following privileges is required:
  • SECADM authority
  • CREATE_SECURE_OBJECT privilege
Note: If the SEPARATE SECURITY subsystem parameter is set to NO, SYSADM authority has implicit SECADM authority.

Syntax

>>-ALTER TRIGGER--trigger-name--+-SECURED-----+----------------><
                                '-NOT SECURED-'   

Description

trigger-name
Identifies the trigger that is to be changed. The trigger must exist at the current server.
SECURED or NOT SECURED
Specifies that the trigger is to be changed to be secure or not secure. Changing a trigger between SECURED and NOT SECURED causes an implicit rebind of the trigger package. If an error is encountered during the implicit rebind of the trigger package, the ALTER TRIGGER statement returns the error.
SECURED
Specifies the trigger is considered secure.

SECURED must be specified for a trigger if its subject table is using row access control or column access control. SECURED must also be specified for a trigger that is created for a view and one or more of the underlying tables in the view definition is using row access control or column access control.

NOT SECURED
Specifies the trigger is considered not secure.

NOT SECURED must not be specified for a trigger whose subject table is using row access control or column access control. NOT SECURED must also not be specified for a trigger that is created for a view and one or more of the underlying tables in the view definition is using row access control or column access control.

Notes

Altering a trigger from NOT SECURED to SECURED:
Typically, the security administrator will examine the data that is accessed by a trigger, ensure that it is secure, and grant the CREATE_SECURE_OBJECT privilege to the owner of the trigger. After the trigger is changed to SECURED, the security administrator will revoke the CREATE_SECURE_OBJECT privilege from the owner of the trigger.

The trigger is considered secure after the ALTER TRIGGER statement is executed. DB2® treats the SECURED attribute as an assertion that declares that the user has established an audit procedure for all activities in the trigger body. If a secure trigger references user-defined functions, DB2 assumes those functions are secure without validation. If those functions can access sensitive data, the user with SECADM authority needs to ensure that those functions are allowed to access that data and that an audit procedure is in place for all versions of those functions, and that all subsequent ALTER FUNCTION statements or changes to external packages are being reviewed by this audit process.

A trigger must be secure if its subject table is using row access control or column access control. SECURED must also be specified for a trigger that is created for a view and one or more of the underlying tables in the view definition is using row access control or column access control.

Altering a trigger from SECURED to NOT SECURED:
The ALTER TRIGGER statement returns an error if the subject table of the trigger is using row access control or column access control, or if the trigger is for a view and one or more of the underlying tables in the view definition is using row access control or column access control.
Row access control and column access control that is not enforced for transition variables and transition tables:
If row access control or column access control is enforced for the subject table of the trigger, row permissions and column masks are not applied to the initial values of transition variables and transition tables. Row access control and column access control is enforced for the triggering table, but is ignored for transition variables and transition tables that are referenced in the body of the trigger body or are passed as arguments to user-defined functions that are invoked in the body of the trigger. To ensure that there are no security concerns for SQL statements accessing sensitive data in transition variables and transition tables in the trigger action, the trigger must be changed to use the SECURED option. If a trigger is not secure, row access control and column access control cannot be enforced for the triggering table.

Examples

Example 1: Change the definition of trigger TRIGGER1 to secured:

ALTER TRIGGER TRIGGER1
 SECURED;
End of change