Posts Tagged ‘audit table

11
Oct
10

How to Recognize DML Origins within Table

Pernahkah anda mengalami kondisi dimana nilai pada suatu kolom di Table X berubah tanpa sepengetahuan kita, atau, dengan kata lain, kita tidak tahu SQL Object (Packages, Function, etc) yang melakukan DML terhadap Table X?

Berikut ini contoh untuk memonitornya menggunakan bantuan trigger.. (kasus DML Update)

CREATE OR REPLACE TRIGGER <schema>.<trigger_name>
 BEFORE UPDATE OF <nama_kolom_yg_mau_dimonitor>
 ON <schema>.<nama_table>
 REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
DECLARE
 vchstr      VARCHAR2 (32767);
BEGIN
 FOR arec IN (SELECT USER, program, machine, obj.object_name,sq.sql_text
 FROM v$session s,
 v$sql sq
 ,dba_objects obj
 WHERE s.audsid = USERENV ('sessionid')
 AND sq.sql_id = s.sql_id
 and obj.object_id = sq.program_id)
 LOOP
 vchstr :=
 'USER:'
 || TRIM (arec.USER)
 || '_PROG:'
 || TRIM (arec.program)
 || '_@:'
 || TRIM (arec.object_name)
 || '@SQL:'
 || TRIM (arec.sql_text);
 EXIT;
 END LOOP;

 <panggil program untuk menulis VCHSTR ke dalam log table>

EXCEPTION
 WHEN OTHERS
 THEN
 -- Consider logging the error and then re-raise
 RAISE;
END;

That’s it. Hope it helps..

Advertisements



Blog Stats

  • 36,654 hits

RSS World News

  • Why would Trump invite Putin back for more? July 20, 2018
    There will be verdicts, votes -- and now Vlad? And it'll all end with a victory parade of sorts. This fall is going to be *lit* in Washington.
  • Trump is inviting Putin to Washington July 20, 2018
    President Donald Trump hopes to welcome Russian President Vladimir Putin in Washington this fall, White House press secretary Sarah Sanders said in a statement Thursday.
  • See intel chief stunned by Putin invite July 20, 2018
    Director of National Intelligence Dan Coats reacted to breaking news that Russian President Vladimir Putin is scheduled for a visit to Washington in the fall, according to the White House.
  • Jake Tapper: What the hell is going on? July 19, 2018
    CNN's Jake Tapper reacts to White House press secretary Sarah Sanders' statement that says President Trump disagrees with Putin's "sincere" proposal to interrogate Americans.
  • Reversals deepen questions about Trump and Putin's meeting July 19, 2018
    When President Donald Trump insisted he meet his Russian counterpart one-on-one, he cited the potential for leaks as rationale to bar aides from attending the session.
Advertisements