Database Triggers and event attributes

Introduction

Creation of Database Triggers

A database trigger is created and dropped with the following commands:

    CREATE OR REPLACE TRIGGER trigger_name (BEFORE|AFTER)
                 database_trigger_event ON (DATABASE|schema.SCHEMA)…;

    DROP TRIGGER trigger_name;

 

When a database trigger is created, the trigger is checked for syntax, the dependency tree and privileges are checked,  and then the trigger is compiled into pcode and stored in the database. Therefore, triggers are similar to stored packages and procedures in the sense of creation, storage, and execution of pcode. The main differences are that database triggers source code is stored in a different data dictionary table and database triggers execute implicitly based on actions, whereas, packages and procedures are explicitly called. If errors occur during the creation or compilation of a database trigger, then the trigger is still created and enabled. If a database event executes that causes the database trigger to execute, the database event will fail. Therefore, if an error occurs during creation or compilation, the trigger needs to be either dropped, fixed and re-created, or disabled to ensure that processing does not stop. To view errors, the SHOW ERRORS command can be executed or the errors can be retrieved from the USER_ERRORS data dictionary view.

Security of Database Triggers

In order to create a database trigger, the schema must have one of 3 Oracle system privileges:

• CREATE TRIGGER: this privilege allows for a schema to create a database trigger on a table they own.
• CREATE ANY TRIGGER: this privilege allows a schema to create a database trigger on a table owned by another schema.
• ADMINISTER DATABASE TRIGGER: this privilege allows a schema to create a database wide database trigger.

 

Once a trigger is created, it is executed implicitly. Internally, Oracle fires the trigger in the existing user transaction.

Triggers are the same as stored packages and procedures and therefore, have dependencies that can cause a trigger to become invalidated. Any time a referenced stored package or procedure is modified, the trigger becomes invalidated. If a trigger ever becomes invalidated, then Oracle will attempt to internally re-compile the trigger the next time it is referenced. As a standard, a trigger that becomes invalidated, should be recompiled manually to ensure that the trigger will compile successfully. To compile a trigger manually, the ALTER TRIGGER command is used. This is shown below:

    ALTER TRIGGER logon_trigger COMPILE;

 

To recompile a trigger, you must either own the trigger or have the ALTER ANY TRIGGER system privilege. 

Enabling and Disabling Database Triggers

Disabled database triggers are companions to invalid objects. In some respects, a disabled trigger is far more dangerous than an invalid object because it doesn’t fail; it just doesn’t execute! This can have severe consequences for applications (and, consequently, for business processes) that depend on business logic stored within procedural code in database triggers. For this reason, you MUST run the following script regularly to ensure there are not any disabled triggers that you are not aware of:

 

SELECT trigger_name, trigger_type, base_object_type,

       triggering_event

FROM   user_triggers

WHERE  status <> 'ENABLED'

AND    db_object_type IN ('DATABASE        ', 'SCHEMA')

ORDER BY trigger_name;

 

TRIGGER_NAME        TRIGGER_TYPE  BASE_OBJECT_TYPE TRIGGERING_EVEN

------------------- ------------- ---------------- ---------------

DB_STARTUP_TRIGGER  AFTER EVENT   DATABASE         STARTUP        

 

Once the triggers are identified, they can be enabled manually or a dynamic SQL or PL/SQL script can be created to build the SQL statements to ENABLE the triggers. To enable database triggers, the following three commands could be executed.

 ALTER TRIGGER db_startup_trigger ENABLE;      -- enabling a database trigger

 ALTER TRIGGER before_insert_customer ENABLE;  -- enabling a table trigger

 ALTER TABLE s_customer ENABLE ALL TRIGGERS;   -- enabling all triggers on a table

 

The preceding commands allow you to enable one trigger at a time or all the triggers on a table. To enable all triggers under a schema, the following script can be used to build an ENABLE script dynamically:

SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT   'ALTER TRIGGER ' || trigger_name || ' ENABLE;'
FROM     user_triggers
ORDER BY table_name;

 

ALTER TRIGGER DB_STARTUP_TRIGGER ENABLE;
ALTER TRIGGER BEFORE_INSERT_CUSTOMER ENABLE;
ALTER TRIGGER BEFORE_UPDATE_CUSTOMER ENABLE;

  

New Database Triggers

The 20 new triggers are broken into two main categories by Oracle, namely, database system events and DDL/client events. For each event, there are event attributes set internally by Oracle when the event takes place. These event attributes can be referenced in the database trigger logic. For example, the CREATE database trigger can reference the schema name, the type of object created, the name of the object, etc. for the object just created.

Database System Events

There are six database system event triggers. The six database system event triggers are outlined below, along with a description and the event attributes that are set for each event.

Database Trigger	BEFORE/AFTER Execution	Description	Attribute Event
LOGOFF	BEFORE	Executed when a user logs off, at the start of the logoff process	ora_sysevent ora_login_user ora_instance_num ora_database_name
LOGON	AFTER	Executed when a user logs into the database, after a successful login of the user	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_client_ip_address
STARTUP	AFTER	Executed when the database is opened; starts a separate transaction and commits after this trigger is complete	ora_sysevent ora_login_user ora_instance_num ora_database_name
SHUTDOWN	BEFORE	Executed when the instance is shutdown; prior to the shutdown of the instance process; not always executed on abnormal shutdown; starts a separate transaction and commits after this trigger is complete	ora_sysevent ora_login_user ora_instance_num ora_database_name
SERVERERROR	AFTER	Executes when an Oracle error occurs (can check for a specific error number to only execute for (errno=eno)); does not execute for certain errors (1034, 1403, 1422, 1423, 4030); starts a separate transaction and commits after this trigger is complete	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_server_error ora_is_servererror space_error_info
SUSPEND	AFTER	Executed whenever a server error causes a transaction to be suspended (example: out-of-space error)	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_server_error ora_is_servererror space_error_info

 

The startup and shutdown triggers can only be created at the database level. The other four database system events can be created at the database or schema levels. The STARTUP trigger returns a success, even if the trigger fails.

The SERVERERROR trigger does not execute when the following Oracle errors are returned:

·               ORA-01403: data not found

·               ORA-01422: exact fetch returns more than requested number of rows

·               ORA-01423: error encountered while checking for extra rows in exact fetch

·               ORA-01034: ORACLE not available

·               ORA-04030: out of process memory

For these triggers, Oracle opens an autonomous transaction scope, fires the trigger, and commits any separate transaction.

DDL/Client Events

There are 14 DDL/client event triggers and these can be created at the database level and will execute for all schemas, or these can be created at the schema level and will execute only for the schema it is created for. When a trigger is created at the schema level, the trigger is created in the schema specified and executes only for that schema.

This provides a great deal of flexibility depending on your environment and what you want to monitor or respond to. The 14 DDL/client event triggers are outlined below, along with a description and the event attributes that are set for each event.

Database Trigger	BEFORE/AFTER Execution	Description	Attribute Events
ALTER	BEFORE/AFTER	Executed when object altered	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner ora_des_encrypted_password (for ALTER USER events) ora_is_alter_column, ora_is_drop_column (for ALTER TABLE events)
DROP	BEFORE/AFTER	Executed when object is dropped	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner
ANALYZE	BEFORE/AFTER	Executed when the analyze command is executed	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner
ASSOCIATE STATISTICS	BEFORE/AFTER	Executed when the associate statistics command is executed	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_dict_obj_name_list ora_dict_obj_owner_list
AUDIT/NOAUDIT	BEFORE/AFTER	Executed when the audit or noaudit command is executed	ora_sysevent ora_login_user ora_instance_num ora_database_name
COMMENT	BEFORE/AFTER	Executed when the comment command is executed	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner
CREATE	BEFORE/AFTER	Executed when an object is created	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner ora_is_creating_nested_table (for CREATE TABLE events)
DDL	BEFORE/AFTER	Executed when SQL DDL commands are executed (not executed when and ALTER/CREATE DATABASE, CREATE CONTROLFILE, or DDL issued through the PL/SQL procedure interface)	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner
DISASSOCIATE STATISTICS	BEFORE/AFTER	Executed when the disassociate statistics command is executed	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_dict_obj_name_list ora_dict_obj_owner_list
GRANT	BEFORE/AFTER	Executed when the grant command is executed	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_grantee ora_with_grant_option ora_privileges
RENAME	BEFORE/AFTER	Executed when the rename command is executed	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_owner ora_dict_obj_type
REVOKE	BEFORE/AFTER	Executed when the revoke command is executed	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_revokee ora_privileges
TRUNCATE	BEFORE/AFTER	Execute when a table is truncated	ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner

 

The new triggers are ideal for DBAs to build mechanisms based on certain events. When the database is started, the objects that need to be pinned can now be moved from the startup SQL script to the STARTUP trigger. When the database is shut down, statistics scripts can be executed to log information into monitoring tables with the SHUTDOWN trigger. Error trapping can be enhanced with the SERVERERROR trigger. Capturing user connect time can be handled through the LOGON and LOGOFF triggers. An object audit trail can be created through the CREATE, ALTER, and DROP triggers.

Event Attributes

With the introduction of the 20 new database triggers, came the creation of attribute events or variables that are set when a certain database trigger event is executed. The previous section highlighted each of the database triggers, along with the event attributes that are set and can be referenced for each trigger. Below is a list of each of the attribute events, the data type and a short description.

Attribute Event	Data Type	Description
ora_client_ip_address	VARCHAR2	Provides the IP address of the client machine when using TCP/IP
ora_database_name	VARCHAR2(50)	Provides the database name
ora_des_encrypted_password	VARCHAR2	Provides the DES encrypted password of the user being created or altered
ora_dict_obj_name	VARCHAR(30)	Provides the object name of the object being manipulated
ora_dict_obj_name_list (name_list OUT ora_name_list_t)	BINARY_INTEGER	Provides a list of object names being manipulated
ora_dict_obj_owner	VARCHAR(30)	Provides the owner of the object being manipulated
ora_dict_obj_owner_list(owner_list OUT ora_name_list_t)	BINARY_INTEGER	Provides the owners of the objects being manipulated
ora_dict_obj_type	VARCHAR(20)	Provides the type of object being manipulated
ora_grantee(   user_list   OUT ora_name_list_t)	BINARY_INTEGER	Provides the number of grantees
ora_instance_num	NUMBER	Provides the instance number.
ora_is_alter_column( column_name IN VARCHAR2)	BOOLEAN	Provides a return value of TRUE if the specified column is altered
ora_is_creating_nested_table	BOOLEAN	Provides a return value of TRUE if the current event is creating a nested table
ora_is_drop_column( column_name IN VARCHAR2)	BOOLEAN	Provides a return value of TRUE if the specified column is dropped
ora_is_servererror	BOOLEAN	Provides a return value of TRUE is the error specified is on the error stack
ora_login_user	VARCHAR2(30)	Provides the login schema
ora_partition_pos	BINARY_INTEGER	Provides the position in a CREATE TABLE command where the partition clause can be inserted when using the INSTEAD OF trigger
ora_privilege_list( privilege_list OUT ora_name_list_t)	BINARY_INTEGER	Provides the list of privileges being granted or revoked
ora_revokee (user_list OUT ora_name_list_t)	BINARY_INTEGER	Provides a list of the revokees of the revoke command
ora_server_error	NUMBER	Provides the error on the error stack for the position specified in the stack (1 meaning the top of the stack)
ora_server_error_depth	BINARY_INTEGER	Provides the total number of errors on the error stack
ora_server_error_msg (position in binary_integer)	VARCHAR2	Provides the error on the error stack for the position specified in the stack (1 meaning the top of the stack)
ora_server_error_num_params (position in binary_integer)	BINARY_INTEGER	Provides the number of strings that have been substituted into the error message on the error stack for the position specified in the stack (1 meaning the top of the stack)
ora_server_error_param (position in binary_integer, param in binary_integer)	VARCHAR2	Provides the matching substitution value in the error message for the parameter number specified in conjunction with the position specified on the stack ( 1 meaning the top of the stack)
ora_sql_txt (sql_text out ora_name_list_t)	BINARY_INTEGER	Provides the SQL statement of the statement that caused the trigger to execute (if the statement is lengthy, it will separate it into multiple PL/SQL table elements); the value returned specifies the number of elements
ora_sysevent	VARCHAR2(20)	Provides the system or client event that caused the trigger to execute
ora_with_grant_option	BOOLEAN	Provides a return value of TRUE if the privileges are granted with the grant option
space_error_info( error_number OUT NUMBER, error_type OUT VARCHAR2, object_owner OUT VARCHAR2, table_space_name OUT VARCHAR2, object_name OUT VARCHAR2, sub_object_name OUT VARCHAR2)	BOOLEAN	Provides a return value of true if the error is related to an out-of-space error and provides the object information of the object with the error

These attribute events allow extreme flexibility and functionality in each of the database triggers and should be used as necessary.

Database Trigger Examples (DBA and Developer)

The power and flexibility is endless with the 20 new database triggers. This section provides a variety of examples to illustrate this power. A list of the examples is provided below.

·         Logging Connection Time (LOGON/LOGOFF)

·         Pinning Objects Upon Startup (STARTUP)

·         Audit Trail of Objects (CREATE/ALTER/DROP)

·         Disable the Ability to Drop Objects (DROP)

·         Disable Logins Dynamically (LOGON)

·         Source Version History (CREATE)

·         Log SQL Information Upon Shutdown (SHUTDOWN)

 

Logging Connection Time (LOGON/LOGOFF)

The following example creates a logon statistics table and a LOGON and LOGOFF database trigger to capture the time when a user connects/disconnects to/from the database.

CREATE TABLE session_logon_statistics

(sid         NUMBER,

 user_logged VARCHAR2(30),

 start_time  DATE,

 end_time    DATE);

CREATE OR REPLACE TRIGGER logon_log_trigger

AFTER LOGON ON DATABASE

BEGIN

   INSERT INTO session_logon_statistics

   (sid, user_logged, start_time)

   SELECT DISTINCT sid, ora_login_user, SYSDATE

   FROM   v$mystat;

END;

/

 

CREATE OR REPLACE TRIGGER logoff_log_trigger

BEFORE LOGOFF ON DATABASE

BEGIN

   UPDATE session_logon_statistics

   SET    end_time    = SYSDATE

   WHERE  sid = (select distinct sid from v$mystat)

   AND    end_time IS NULL;

END;

/

create or replace trigger
   logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- *************************************************
-- Update the last action accessed
-- *************************************************
update stats_user_log
set last_action = (select action from v$session
                   where sys_context('USERENV','SESSIONID') = audsid)
where sys_context('USERENV','SESSIONID') = session_id;
--*************************************************
-- Update the last program accessed
-- *************************************************
update stats_user_log
set last_program = (select program from v$session where     
                     sys_context('USERENV','SESSIONID') = audsid)
where sys_context('USERENV','SESSIONID') = session_id;
-- *************************************************
-- Update the last module accessed
-- *************************************************
update stats_user_log
set last_module = (select module from v$session where     
                   sys_context('USERENV','SESSIONID') = audsid)
where sys_context('USERENV','SESSIONID') = session_id;
-- *************************************************
-- Update the logoff day
-- *************************************************
update stats_user_log
set logoff_day = sysdate
where sys_context('USERENV','SESSIONID') = session_id;
-- *************************************************
-- Update the logoff time
-- *************************************************
update stats_user_log
set logoff_time = to_char(sysdate, 'hh24:mi:ss')
where sys_context('USERENV','SESSIONID') = session_id;
-- *************************************************
-- Compute the elapsed minutes
-- *************************************************
update stats_user_log
set elapsed_minutes = round((logoff_day - logon_day)*1440)
where sys_context('USERENV','SESSIONID') = session_id;
COMMIT;
END;
/
 

The SID is selected from the V$MYSTAT view. SELECT privilege must be granted on the V_$MYSTAT view to the creator of these triggers. The following script retrieves the information from the SESSION_LOGON_STATISTICS table.

COLUMN user_logged FORMAT a15

COLUMN start_time  FORMAT a20

COLUMN end_time    FORMAT a20

SELECT sid, user_logged,

       TO_CHAR(start_time, 'MM/DD/YYYY HH24:MI:SS') start_time,

       TO_CHAR(end_time, 'MM/DD/YYYY HH24:MI:SS') end_time

FROM   session_logon_statistics

order by sid, user_logged, start_time;

 

SID USER_LOGGED   START_TIME          END_TIME

--- ------------- ------------------- -------------------

12  TRIGGER_TEST  01/22/2003 19:11:53 01/22/2003 19:17:22

12  TRIGGER_TEST  01/22/2003 19:17:24 01/22/2003 19:17:46

13  PLSQL_USER    01/22/2003 19:12:19 01/22/2003 19:18:13

13  SYS           01/22/2003 19:18:38 01/22/2003 19:19:34

13  SYS           01/22/2003 19:19:35 01/22/2003 19:19:53

13  SYS           01/22/2003 19:19:59

14  TRIGGER_TEST  01/22/2003 19:12:29 01/22/2003 19:18:03

 

Pinning Objects Upon Startup (STARTUP)

The following example creates a startup mechanism that pins objects in the shared pool. It provides a dynamic and flexible method to control the pinning by merely inserting and deleting from a database table.

Because Oracle uses an LRU algorithm for the caching of objects in the Shared Pool, objects can be flushed out of the Shared Pool. If the objects are large, this will cause degradation in performance and possible errors because objects are loaded into the Shared Pool in contiguous segments. Heavily executed or large and important stored PL/SQL program units should be cached or pinned in the Shared Pool. Stored PL/SQL program units get pinned in the object cache and cursors get pinned in the SQL Area.

Oracle provides a procedure in the DBMS_SHARED_POOL package to pin these objects. This is the only method of pinning these objects in the shared pool and this package is not created by default. The dbmspool.sql script must be executed under the SYS schema to create this package. By default, even in Oracle 9.2, no objects, not even the STANDARD package is pinned by default.

The following is a pinning mechanism that allows stored PL/SQL program units to be pinned or unpinned by inserting or deleting the name of the program unit from a database table.

The following command creates the table to store the names of the stored PL/SQL program units to pin. This should be created under a user with DBA privilege.

CREATE TABLE objects_to_pin

   (owner    VARCHAR2(30) NOT NULL,

    object   VARCHAR2(128) NOT NULL,

    type     VARCHAR2(1) NOT NULL);

 

The following is a basic method of inserting objects to pin into the table.

INSERT INTO objects_to_pin (owner, object, type)

VALUES (UPPER('&owner'), UPPER('&object'), UPPER('&type'));

 

The type needs to follow the same conventions as defined in the SHARED_POOL package. This is outlined in the creation script of the DBMS_SHARED_POOL script (dbmspool.sql) and is included for reference below:

Value  Kind of Object to keep

-----  ----------------------

P      package/procedure/function

Q      sequence

R      trigger

T      type

JS     java source

JC     java class

JR     java resource

JD     java shared data

C      cursor

 

The following procedure takes one parameter to signify if the objects in the table should be pinned or unpinned. The default is to pin the objects. A U as input will unpin the objects in the table.

CREATE OR REPLACE PROCEDURE pin_objects

   (p_pin_flag_txt IN VARCHAR2 := 'P') IS

   -- The p_pin_flag_txt is either 'P' for pin

   -- or 'U' for unpin.

   CURSOR cur_pin_objects IS

      SELECT owner || '.' owner, object, type

      FROM   objects_to_pin

      ORDER BY owner, object;

BEGIN

    FOR cur_pin_objects_rec IN cur_pin_objects LOOP

      IF p_pin_flag_txt = 'U' THEN

         DBMS_SHARED_POOL.UNKEEP(cur_pin_objects_rec.owner ||

            cur_pin_objects_rec.object, cur_pin_objects_rec.type);

      ELSE

         DBMS_SHARED_POOL.KEEP(cur_pin_objects_rec.owner ||

            cur_pin_objects_rec.object, cur_pin_objects_rec.type);

      END IF;

   END LOOP;

END pin_objects;

/

 

The pin_objects procedure should be called from the database startup script to make certain the PL/SQL objects are pinned immediately, which will ensure the Shared Pool space is contiguously allocated in memory. The STANDARD package and SOURCE_HISTORY were insert into the OBJECTS_TO_PIN table using the INSERT script above. The following output displays the contents of this table.

COLUMN object FORMAT a30

SELECT * FROM   objects_to_pin;

 

OWNER           OBJECT                         TYPE                                                

--------------- ------------------------------ ------------                                        

SYS             STANDARD                       P                                                    

TRIGGER_TEST    SOURCE_HISTORY                 R

 

The following query displays the stored PL/SQL program units information regarding pinning. The script below has been modified to focus on the two objects that we are attempting to pin. The WHERE clause would usually only contain the WHERE  kept = 'YES' condition.

The modified script is shown and executed below.

COLUMN owner FORMAT a15

COLUMN name  FORMAT a25

COLUMN type  FORMAT a12

SET PAGESIZE 58

SELECT owner, name, type, kept

FROM   v$db_object_cache

WHERE  name in ('STANDARD', 'SOURCE_HISTORY');

 

OWNER           NAME                      TYPE         KEP                                              

--------------- ------------------------- ------------ ---                                              

SYS             STANDARD                  PACKAGE      NO                                               

SYS             STANDARD                  PACKAGE BODY NO                                                

TRIGGER_TEST    SOURCE_HISTORY            TABLE        NO                                               

TRIGGER_TEST    SOURCE_HISTORY            TRIGGER      NO

 

The previous query can be executed to list the sharable memory (sharable_mem column) required for the object and the number of times the object was loaded and executed (loads and executions columns) to determine which objects should be pinned.

In order to take this example full circle, a database startup script is created that calls the PIN_OBJECTS procedure. This will ensure that objects will be pinned upon startup no matter where the database is being started from (whether manually or through a script).

 

CREATE OR REPLACE TRIGGER db_startup_trigger

AFTER STARTUP ON DATABASE

BEGIN

   pin_objects;

END;

/

 

The database is then shutdown and started up and the query against the V$DB_OBJECT_CACHE view is executed again as shown below.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  135338868 bytes

Fixed Size                   453492 bytes

Variable Size             109051904 bytes

Database Buffers           25165824 bytes

Redo Buffers                 667648 bytes

Database mounted.

Database opened.

 

OWNER           NAME              TYPE         KEP

--------------- ------------------------------ ---

SYS             STANDARD          PACKAGE      YES

SYS             STANDARD          PACKAGE BODY YES

TRIGGER_TEST    SOURCE_HISTORY    TABLE        NO

TRIGGER_TEST    SOURCE_HISTORY    TRIGGER      YES

 

Audit modification of Objects (CREATE/ALTER/DROP)

The next example provides the ability to build your own flexible object audit trail. The example below creates database triggers to capture and log every CREATE, ALTER and DROP operation on the database. First a table is created to store the audit information.

 

CREATE TABLE audit_object_mods

(mod_date      DATE,

 type_of_mod   VARCHAR2(20),

 mod_user      VARCHAR2(30),

 instance_num  NUMBER,

 database_name VARCHAR2(50),

 object_owner  VARCHAR2(30),

 object_type   VARCHAR2(20),

 object_name   VARCHAR2(30));

 

Next, the CREATE, ALTER, and DROP database triggers are created. These are created for all schemas. If the audit was only focused on one particular schema, then the “ON DATABASE” line would be changed to “ON TRIGGER_TEST.SCHEMA” where TRIGGER _TEST is the name of the schema desired to audit.

 

CREATE OR REPLACE TRIGGER create_object_trigger

AFTER CREATE ON DATABASE

BEGIN

   INSERT INTO audit_object_mods

      (mod_date, type_of_mod, mod_user,

       instance_num, database_name,

       object_owner, object_type, object_name)

   VALUES

      (sysdate, ora_sysevent, ora_login_user,

       ora_instance_num, ora_database_name,

       ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name);

END;

/

 

CREATE OR REPLACE TRIGGER alter_object_trigger

AFTER ALTER ON DATABASE

BEGIN

   INSERT INTO audit_object_mods

      (mod_date, type_of_mod, mod_user,

       instance_num, database_name,

       object_owner, object_type, object_name)

   VALUES

      (sysdate, ora_sysevent, ora_login_user,

       ora_instance_num, ora_database_name,

       ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name);

END;

/

 

CREATE OR REPLACE TRIGGER drop_object_trigger

AFTER DROP ON DATABASE

BEGIN

   INSERT INTO audit_object_mods

      (mod_date, type_of_mod, mod_user,

       instance_num, database_name,

       object_owner, object_type, object_name)

   VALUES

      (sysdate, ora_sysevent, ora_login_user,

       ora_instance_num, ora_database_name,

       ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name);

END;

/

 

At this point, the audit is set up and as these operations take place in the database, they will be logged to the AUDIT_OBJECT_MODS table. To view the audit, the following script can be executed.

 

COLUMN type_of_mod   FORMAT a10

COLUMN mod_user      FORMAT a12

COLUMN database_name FORMAT a10

COLUMN object_owner  FORMAT a12

COLUMN object_type   FORMAT a10

COLUMN object_name   FORMAT a25

SET LINESIZE 130

SELECT mod_date, type_of_mod, mod_user, instance_num,

       database_name, object_owner, object_type, object_name

FROM   audit_object_mods

ORDER BY mod_date, type_of_mod, object_owner, object_type, object_name;

 

MOD_DATE  TYPE_OF_MO MOD_USER     I_NUM DATABASE_N OBJECT_OWNER OBJECT_TYP OBJECT_NAME                                    

--------- ---------- ------------ ----- ---------- ------------ ---------- ------------------------                      

27-JAN-03 CREATE     TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST TRIGGER    ALTER_OBJECT_TRIGGER

27-JAN-03 CREATE     TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST TRIGGER    DROP_OBJECT_TRIGGER

27-JAN-03 CREATE     TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST PROCEDURE  PIN_OBJECTS      

27-JAN-03 CREATE     TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST TRIGGER    DB_STARTUP_TRIGGER

27-JAN-03 DROP       TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST TRIGGER    DATABASE_STARTUP_TRIGGER

27-JAN-03 CREATE     TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST TABLE      TEST14

27-JAN-03 ALTER      TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST TRIGGER    DB_STARTUP_TRIGGER

27-JAN-03 ALTER      TRIGGER_TEST     1 TUSC.WORLD TRIGGER_TEST TRIGGER    DB_STARTUP_TRIGGER

 

The individual CREATE, ALTER, and DROP database triggers created previously can be replaced with one trigger creation with the use of the OR condition as shown below.

 

CREATE OR REPLACE TRIGGER c_a_d_object_trigger

AFTER CREATE OR ALTER OR DROP ON DATABASE

BEGIN

   INSERT INTO audit_object_mods

      (mod_date, type_of_mod, mod_user,

       instance_num, database_name,

       object_owner, object_type, object_name)

   VALUES

      (sysdate, ora_sysevent, ora_login_user,

       ora_instance_num, ora_database_name,

       ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name);

END;

/

 

Disable the Ability to Drop Objects (DROP)

The following example illustrates how a trigger can be created for specific schema to disallow the ability to DROP objects. The trigger is for the XXX user.

 

CREATE OR REPLACE TRIGGER stop_drop_trigger

   BEFORE DROP ON XXX.SCHEMA

   BEGIN

      RAISE_APPLICATION_ERROR (

         num => -20000,

         msg => 'Cannot DROP Objects');

   END;

/

 

When the XXX USER attempts to DROP any object an error will result as shown below.

SHOW USER

USER is "XXX"

 

DROP TABLE temp;

drop table temp

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20000: Cannot DROP Objects

ORA-06512: at line 2

An example to avoid alter or drop column:
create table scott.test ( c number);

create trigger trtest
  before drop or alter on database
begin
  if ora_sysevent = 'ALTER' and ora_dict_obj_type = 'TABLE' and ora_dict_obj_name = 'TEST' then
     if ora_is_alter_column('C') then
        raise_application_error(-20001,'Cannot Alter column');
     elsif ora_is_drop_column('C') then
        raise_application_error(-20001,'Cannot Drop column');
     end if;
   end if;
end;
/

 

Protecting from Dropping
TRIGGER SCHEMA_DDL_PROTECT
   BEFORE DDL ON SCHEMA
DECLARE
   v_exec_user VARCHAR2(1000) := sys_context('USERENV', 'OS_USER');
   NO_PRIVS EXCEPTION;
   PRAGMA EXCEPTION_INIT(NO_PRIVS, -20202);
BEGIN
   IF ora_sysevent IN('TRUNCATE', 'ALTER') AND LOWER(V_EXEC_USER) NOT IN( 'lee.s', 'lee', 'oracle') THEN
      INSERT INTO REPORTS.AUD_DDL(OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE, DDL, OS_USER, LOGIN_USER, TSP)
         VALUES(ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, ora_sysevent, V_EXEC_USER, ora_login_user, SYSDATE);
   ELSE
      IF LOWER(V_EXEC_USER) NOT IN( 'lee.s', 'lee', 'oracle') AND V_EXEC_USER IS NOT NULL THEN
         RAISE_APPLICATION_ERROR(-20202, 'Un-authorize DDL, next time you try this Oracle will blow-up your computer');
      END IF;
   END IF;
END SCHEMA_DDL_PROTECT;
/

Disable Logins Dynamically (LOGON)

Oracle has added features and commands to allow a DBA to perform more and more of their responsibilities while the database is up and running, thus reducing the time of off-hours administration. With database triggers, the logon trigger allows a DBA to disable new logons by setting a flag in a table. The following table can be used for this toggle flag.

 

CREATE TABLE logons_allowed

(logons_allowed VARCHAR2(3) NOT NULL);

 

A record is inserted into this table with the value of YES. The following logon trigger is created to reference this table upon logon and look for the value of the record to determine if logins are allowed.

 

CREATE OR REPLACE TRIGGER logon_allow_trigger

AFTER LOGON  ON DATABASE

DECLARE

   CURSOR logon_allowed IS

      SELECT logons_allowed

         FROM   logons_allowed;

   lv_allowed   logons_allowed.logons_allowed%TYPE;

BEGIN

   OPEN logon_allowed;   

   FETCH logon_allowed INTO lv_allowed;

   IF lv_allowed != 'YES' THEN

      CLOSE logon_allowed;

      RAISE_APPLICATION_ERROR (

         num => -20000,

         msg => 'Logins Not Allowed.');

   ELSE

      CLOSE logon_allowed;

   END IF;

END;

/

 

The trigger looks for a value of YES and if the value is NOT YES, then the login will not succeed and the login will fail with an error. To illustrate, the table is updated and the logons_allowed value is set to NO. A user attempts to login and they get the following error:

 

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jan 22 2003

Copyright (c) 1982, 2002, Oracle Corporation.

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-20000: Logins Not Allowed.

ORA-06512: at line 12

 

Any time an error is returned either at runtime as forced in the above trigger or if the above trigger was INVALID due to an error upon creation, the SYS and SYSTEM users are exempt from errors in the logon triggers and will be allowed to logon. Likewise, any schema with ADMINISTER DATABASE TRIGGER system privilege follows the same logic. Therefore, when the creator of the trigger above (TRIGGER_TEST who has the above privilege), as well as the SYS and SYSTEM users attempt to logon, they succeed. If the trigger was created with an error and is INVALID, the 3 users and any schema with the privilege above would still succeed upon logon. However, if the trigger was created with an error and is INVALID and anyone else attempts to logon, they will receive the following message.

 

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jan 28 2003

Copyright (c) 1982, 2002, Oracle Corporation.

ERROR:

ORA-04098: trigger 'TRIGGER_TEST.LOGON_ALLOW_TRIGGER' is invalid and failed

re-validation

Enter user-name:

 

Source Version History (CREATE)

The ability to view the source code of stored PL/SQL objects in the database provides a lot of advantages, however, the biggest disadvantage is that developers often times bypass version control mechanisms in place to make a quick fix and forget to update the main source code version of the PL/SQL object that resides outside of the database. The problem comes in when the next change is made the correct way by modifying the code outside the database and then recreating it in the database. The prior change made in the database is now lost. I have heard about this many times.

The new triggers provide a mechanism to help this problem and create an audit trail/version of all PL/SQL source code created in the database. This is accomplished by inserting the new source code for an object into a history table each time an object is created or recreated. It is accomplished behind the scenes with the CREATE trigger. Each of the creations is also timestamped with the creation date.

A history table is first created that mirrors the DBA_SOURCE table as shown below.

 

CREATE TABLE source_history

(change_date DATE           NOT NULL,

 owner       VARCHAR2(30)   NOT NULL,

 name        VARCHAR2(30)   NOT NULL,

 type        VARCHAR2(20),

 line        NUMBER         NOT NULL,

 text        VARCHAR2(4000));

 

A CREATE trigger is then created that will insert into the SOURCE_HISTORY table after each new object creation as shown below.

 

CREATE OR REPLACE trigger source_history

AFTER CREATE ON DATABASE

BEGIN

   INSERT INTO source_history

   SELECT SYSDATE, owner, name, type, line, text

   FROM  dba_source

   WHERE owner = ORA_DICT_OBJ_OWNER

   AND   name  = ORA_DICT_OBJ_NAME

   AND   type  = ORA_DICT_OBJ_TYPE;

END source_history;

/

 

The same scripts created for the DBA_SOURCE view can now be used on this view by only changing the view name to SOURCE_HISTORY. A sample script is shown below with the query limited to one object.

 

COLUMN owner FORMAT a12

COLUMN name  FORMAT a11

COLUMN line  FORMAT 9999

COLUMN text  FORMAT a60 WORD_WRAPPED

SELECT change_date, owner, name, type, line, text

FROM   source_history

WHERE  name = 'PIN_OBJECTS'

order by change_date, owner, name, type, line;

 

CHANGE_DA OWNER        NAME        TYPE        LINE TEXT                                                                         

--------- ------------ ----------- ---------- ----- ------------------------------------------------------------                 

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      1 PROCEDURE pin_objects                                                        

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      2 (p_pin_flag_txt IN VARCHAR2 := 'P') IS                                       

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      3 -- The p_pin_flag_txt is either 'P' for pin                                  

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      4 -- or 'U' for unpin.                                                         

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      5 CURSOR cur_pin_objects IS                                                    

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      6 SELECT owner || '.' owner,                                                   

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      7 object                                                                       

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      8 FROM   objects_to_pin                                                        

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE      9 ORDER BY owner, object;                                                      

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     10 BEGIN                                                                        

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     11 FOR cur_pin_objects_rec IN cur_pin_objects LOOP                              

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     12 IF p_pin_flag_txt = 'U' THEN                                                 

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     13 DBMS_SHARED_POOL.UNKEEP(cur_pin_objects_rec.owner ||                         

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     14 cur_pin_objects_rec.object, 'P');                                            

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     15 ELSE                                                                          

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     16 DBMS_SHARED_POOL.KEEP(cur_pin_objects_rec.owner ||                           

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     17 cur_pin_objects_rec.object, 'P');                                             

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     18 END IF;                                                                      

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     19 END LOOP;                                                                     

27-JAN-03 TRIGGER_TEST PIN_OBJECTS PROCEDURE     20 END pin_objects;

 

This query can also be changed to include the time .

Log SQL Information Upon Shutdown (SHUTDOWN)

The following illustrates a method of logging information when the database is shutdown. This can be extended to log more information or perform certain operations. The example logs information from the V$SQLAREA view. First, a log table is created with a timestamp column as shown below.

 

CREATE TABLE sqlarea_history

(log_date         DATE,

 disk_reads       NUMBER,

 executions       NUMBER,

 execution_ratio      NUMBER,

 sql_text         VARCHAR2(1000));

 

A procedure is created to log the desired information from the view into the log table. There is a low threshold in the example below for the number of READS. This can be modified for your system to only log the SQL for statements over a large number of disk reads.

 

CREATE OR REPLACE PROCEDURE SQLAREA_LOG AS

   CURSOR c1 IS

      SELECT disk_reads reads, executions exe,

          ROUND(disk_reads/DECODE(executions, 0, 1, executions)) ratio,

          sql_text text

        FROM   v$sqlarea

        WHERE  disk_reads > 100

        ORDER BY disk_reads/DECODE(executions, 0, 1, executions) DESC;

   lv_current_date DATE := SYSDATE;

BEGIN

   FOR c1_rec in c1 LOOP

      INSERT INTO sqlarea_history

         (log_date, disk_reads, executions, execution_ratio, sql_text)

      VALUES

         (lv_current_date, c1_rec.reads, c1_rec.exe, c1_rec.ratio,c1_rec.text);

   END LOOP;

END;

/

 

Lastly, the database shutdown trigger is created to call the SQLAREA_LOG procedure.

 

CREATE OR REPLACE TRIGGER db_shutdown_trigger

BEFORE SHUTDOWN ON DATABASE

BEGIN

   sqlarea_log;

END;

/

 

The SQL can then be queried at anytime in the future with the following statement.

 

COLUMN sql_text FORMAT a25

COLUMN log_date FORMAT a19

SET PAGESIZE 58

SELECT TO_CHAR(log_date, 'MM/DD/YYYY:HH24:MI:SS') log_date,

       disk_reads, executions exe, execution_ratio ratio, sql_text

FROM   sqlarea_history;

 

LOG_DATE            DISK_READS   EXE   RATIO SQL_TEXT                  

------------------- ---------- ----- ------- ------------------------- 

01/22/2003:22:55:09        379     1     379 select o.owner#,o.obj#,de 

                                             code(o.linkname,null, dec 

                                             ode(u.name,null,'SYS',u.n 

                                             ame),o.remoteowner), o.na 

                                             me,o.linkname,o.namespace 

                                             ,o.subname from user$ u,  

                                             obj$ o where u.user#(+)=o 

                                             .owner# and o.type#=:1 an 

                                             d not exists (select p_ob 

                                             j# from dependency$ where 

                                             p_obj# = o.obj#) for upd 

                                             ate                       

                                       

01/22/2003:22:55:09        213     1     213 select distinct i.obj# fr 

                                             om sys.idl_ub1$ i where i 

                                             .obj#>=:1 and i.obj# not  

                                             in (select d.p_obj# from  

                                             sys.dependency$ d)        

                                        

01/22/2003:22:55:09        115     1     115 select distinct d.p_obj#, 

                                             d.p_timestamp from sys.de 

                                             pendency$ d, obj$ o where 

                                             d.p_obj#>=:1 and d.d_obj 

                                             #=o.obj# and o.status!=5  

                                                                               

Data Dictionary Views for Database Triggers

There are several data dictionary views that reveal information about triggers. The main two views center on the source code view that contains the source code for package, procedures and functions, but also stores the compilation status of triggers. This is important to determine if a trigger has been compiled successfully. The source code for triggers and the important information regarding being ENABLED or DISABLED is also stored in the trigger views. There are 3 views for each that center on the scope of the information. These are listed below.

 

      user_triggers     user_source

      all_triggers      all_source

      dba_triggers      dba_source

 

Some examples follow to exemplify the usefulness of these views.

The first example script provides information on the compilation status of triggers. A status of VALID indicates the trigger is compiled and is ready for execution. A status of INVALID means that the trigger needs to be compiled prior to execution. This will be automatically attempted by Oracle the next time the trigger is fired or manually with the ALTER…COMPILE command prior to the next firing of the trigger. I always recommend recompiling manually to ensure the compilation will be valid and if not, then changes can be made until it does become VALID. You always want to know prior to production if there will be compilation issues that you need to address.

 

COLUMN object_name FORMAT a24

SELECT object_name, object_type, status

  FROM   user_objects

  WHERE  object_type = 'TRIGGER';

 

OBJECT_NAME              OBJECT_TYP STATUS 

------------------------ ---------- -------

ALTER_OBJECT_TRIGGER     TRIGGER    VALID   

CREATE_OBJECT_TRIGGER    TRIGGER    VALID   

DB_SHUTDOWN_TRIGGER      TRIGGER    VALID   

DB_STARTUP_TRIGGER       TRIGGER    VALID   

DROP_OBJECT_TRIGGER      TRIGGER    VALID  

LOGOFF_LOG_TRIGGER       TRIGGER    VALID   

LOGON_ALLOW_TRIGGER      TRIGGER    VALID  
LOGON_LOG_TRIGGER        TRIGGER    VALID

SOURCE_HISTORY           TRIGGER    VALID

STOP_DROP_TRIGGER        TRIGGER    VALID

TEST                     TRIGGER    VALID

TEST_LOGON               TRIGGER    VALID 

 

As evidenced in the output, all triggers are VALID at the current time. If an object that is referenced by one of these triggers is dropped or altered in any manner, this will cause the trigger to become INVALID.

The second example provides information about the triggers in the system. This is useful to understand the database and schema triggers that are defined in a database.

 

COLUMN trigger_name FORMAT a24

COLUMN triggering_event FORMAT a15

SELECT trigger_name, trigger_type, base_object_type, triggering_event, status

  FROM   user_triggers

  WHERE  db_object_type IN ('DATABASE        ', 'SCHEMA');

 

TRIGGER_NAME          TRIGGER_TYPE  BASE_OBJECT_TYPE TRIGGERING_EVEN STATUS

--------------------- ------------------------------ --------------- -------

ALTER_OBJECT_TRIGGER  AFTER EVENT   DATABASE         ALTER           ENABLED

CREATE_OBJECT_TRIGGER AFTER EVENT   DATABASE         CREATE          ENABLED

DB_SHUTDOWN_TRIGGER   BEFORE EVENT  DATABASE         SHUTDOWN        ENABLED

DB_STARTUP_TRIGGER    AFTER EVENT   DATABASE         STARTUP         ENABLED

DROP_OBJECT_TRIGGER   AFTER EVENT   DATABASE         DROP            ENABLED

LOGOFF_LOG_TRIGGER    BEFORE EVENT  DATABASE         LOGOFF          ENABLED

LOGON_ALLOW_TRIGGER   AFTER EVENT   DATABASE         LOGON           ENABLED

LOGON_LOG_TRIGGER     AFTER EVENT   DATABASE         LOGON           ENABLED

SOURCE_HISTORY        AFTER EVENT   DATABASE         CREATE          ENABLED

TEST_LOGON            AFTER EVENT   DATABASE         LOGON           ENABLED

STOP_DROP_TRIGGER     BEFORE EVENT  SCHEMA           DROP            ENABLED

TEST                  AFTER EVENT   SCHEMA           LOGON           ENABLED

 

The output identifies the triggers with the type of triggers, events that cause each to execute, the timing of the execution (when) and the execution status. Refer to the previous section on ENABLING and DISABLING triggers for more information on this view.

The last example provides the source code of each of the triggers (limited to the DB_STARTUP_TRIGGER trigger).

 

COLUMN trigger_name FORMAT a24

COLUMN triggering_event FORMAT a15

COLUMN trigger_body FORMAT a25 word_wrapped

SELECT trigger_name, trigger_type, base_object_type, triggering_event, trigger_body

  FROM   user_triggers

  WHERE  trigger_name = 'DB_STARTUP_TRIGGER';

 

TRIGGER_NAME       TRIGGER_TYPE BASE_OBJECT_TYPE TRIGGERING_EVEN TRIGGER_BODY

------------------ ------------ ---------------- --------------- ------------

DB_STARTUP_TRIGGER AFTER EVENT  DATABASE         STARTUP         BEGIN       

                                                                 pin_objects;

                                                                 END;

 

The output provides attributes on the triggers, as well as, the entire contents of the trigger that was created.