Triggers for DBA’s

1. LOGON trigger to restrict user connections filtering USERNAME and HOST:

CREATE OR REPLACE TRIGGER restrict
AFTER LOGON ON database
begin
If sys_context(‘USERENV’,’CURRENT_USER’) like ‘PV%’ or
sys_context(‘USERENV’,’CURRENT_USER’) like ‘PA%’ or
sys_context(‘USERENV’,’CURRENT_USER’) like ‘PB%’ or
sys_context(‘USERENV’,’CURRENT_USER’) like ‘PD%’ or
sys_context(‘USERENV’,’CURRENT_USER’) like ‘PR%’
AND (upper(sys_context(‘USERENV’,’HOST’)) not in (‘ACILH502′,’ACILH504′,’ACILH506′,’ACILH506’)
or (upper(sys_context(‘USERENV’,’OS_USER’)) <> ‘FCP41’))
THEN
RAISE_APPLICATION_ERROR(-20001, ”YOU ARE NOT ALLOWED TO LOGIN…Incorrect HOST
[‘ || (upper(sys_context(‘USERENV’,’HOST’)) || ‘] or incorrect OS_USER
[‘ || (upper(sys_context(‘USERENV’,’OS_USER’)) || ‘]’ );
end if;
end;
/

2. LOGON trigger to enable session tracing:

create or replace trigger trace_trigger
AFTER LOGON ON DATABASE
WHEN (USER=’ABCD’)
declare
stmt varchar2(100);
hname varchar2(20);
uname varchar2(20);
begin
select sys_context(‘USERENV’,’HOST’),
sys_context(‘USERENV’,’SESSION_USER’)
into hname,uname from dual;
stmt := ‘alter session set tracefile_identifier=’||hname||’_’||uname;
EXECUTE IMMEDIATE stmt;
EXECUTE IMMEDIATE ‘alter session set sql_trace=true’;
end;

3. Trigger to capture DDL statements:

CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE CREATE OR ALTER OR DROP
ON SCHEMA
DECLARE
oper ddl_log.operation%TYPE;
sql_text ora_name_list_t;
i PLS_INTEGER;
BEGIN
SELECT ora_sysevent
INTO oper
FROM DUAL;
i := sql_txt(sql_text);
IF oper IN (‘CREATE’, ‘DROP’) THEN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, sql_text(1), USER, SYSDATE
FROM DUAL;
ELSIF oper = ‘ALTER’ THEN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, sql_text(1), USER, SYSDATE
FROM sys.gv_$sqltext
WHERE UPPER(sql_text) LIKE ‘ALTER%’
AND UPPER(sql_text) LIKE ‘%NEW_TABLE%’;
END IF;
END ddl_trigger;
/

4. Trigger to capture on SERVERERROR:

CREATE OR REPLACE TRIGGER logon_failures
AFTER SERVERERROR
ON DATABASE
BEGIN
IF (IS_SERVERERROR(1017)) THEN
INSERT INTO sys.logon_table
(login_date, user_name,status)
VALUES
(SYSDATE, user,’ORA-01017′);
END IF;
commit;
END logon_failures;

5. Trigger on server shutdown:

SQL> CREATE TABLE Trigger_table (
database_name VARCHAR2(30),
event_name VARCHAR2(20),
event_time DATE,
triggered_by_user VARCHAR2(30)
);
SQL> CREATE OR REPLACE TRIGGER log_shutdown
BEFORE SHUTDOWN ON DATABASE
BEGIN
INSERT INTO sys.trigger_table
(database_name,
event_name,
event_time,
triggered_by_user)
VALUES (‘ORADB’,
‘SHUTDOWN initiated’,
sysdate,
user);
COMMIT;
END;
/

Hope these examples will help you to build triggers based on your project requirements.

About The Author

Add Comment