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:
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.