Etiket arşivi: Oracle Logon Trigger

Oracle Logon Trigger

Oracle Logon Trigger

Hangi kullanıcı, hangi ipden, hangi makineden ne zaman bağlanmış vb. bilgileri loglamaktadır.

Öncelikle SYS ile login olup, logları tutacak bir tablo create edelim.

CREATE  TABLE LOGON_HISTORY_TAB (
  user_id             VARCHAR2(30),
  session_id          NUMBER(8),
  host                VARCHAR2(30),
  program             VARCHAR2(48),
  logon_time          DATE,
  terminal            VARCHAR2(512),
  sessionid           VARCHAR2(512),
  isdba               VARCHAR2(512),
  session_user        VARCHAR2(512),
  os_user             VARCHAR2(512),
  ip_address          VARCHAR2(512),
  network_protocol    VARCHAR2(512),
  authentication_type VARCHAR2(512) );

Şimdi de loginleri tutacak triggerımızı oluşturalım.(SYS ile)

CREATE OR REPLACE TRIGGER LOGON_HISTORY_TRG AFTER
LOGON ON DATABASE
DECLARE
  SESS NUMBER(10);
  PROG VARCHAR2(100);
BEGIN
    SESS := SYS_CONTEXT('USERENV', 'SESSIONID');
    SELECT PROGRAM
      INTO PROG
      FROM V$SESSION
     WHERE AUDSID = SESS
       AND ROWNUM <= 1;
    INSERT INTO LOGON_HISTORY_TAB
      ( user_id,             
        session_id,           
        host,                 
        program,              
        logon_time,           
        terminal,             
        sessionid,            
        isdba,                
        session_user,         
        os_user,              
        ip_address,           
        network_protocol,     
        authentication_type )
    VALUES
      (USER,
       SYS_CONTEXT('USERENV', 'SESSIONID'),
       SYS_CONTEXT('USERENV', 'HOST'),
       PROG,
       SYSDATE,
       SYS_CONTEXT('USERENV', 'TERMINAL'),
       SYS_CONTEXT('USERENV', 'SESSIONID'),
       SYS_CONTEXT('USERENV', 'ISDBA'),
       SYS_CONTEXT('USERENV', 'SESSION_USER'),
       SYS_CONTEXT('USERENV', 'OS_USER'), 
       SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
       SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL'),
       SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE'));
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
END LOGON_HISTORY_TRG;

Triggerı create ettikten sonraki tüm loginleri loglayıp ilgili tablomuza yazacaktır.
Loglara bakmak için;

SELECT * FROM LOGON_HISTORY_TAB T;

Oracle Trigger Nedir, Trigger Örnekleri

Trigger Nedir, Nasıl Kullanılır? Trigger Örnekleri.

Trigger (tetikleyici) , RDBMS   sistemlerde bir tabloda belirli olaylar meydana geldiği zaman yani ekleme, güncelleme, silme işlemlerinden biri gerçekleşmeden önce veya sonra çalışan ve belirli işlemleri kodlandığı şekilde yerine getiren oracle nesnesidir.Sadece tablo değil login logof işlemlerinde de kullanılabilir.

Trigger ile yapabileceklerimize örnekler verirsek;
-Tabloya veri girişi, güncelleme ve silme işlemlerini loglayabiliriz.
-Oracle Login-Logof işlemlerini loglayabilir ve kontroller koyabiliriz.
-Tablo üzerinde yapılan DML işlemlerine kontroller koyabiliriz
-Otomatik artan primary key alanını (sequence) trigger ile default gönderebiliriz.

Trigger Oluşturmak:

Genel syntax aşağıdaki gibidir.

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }

Disable Trigger Oluşturmak:

Triggerlar oluşturulduğunda hemen devreye girerler, prod ortamlarında sorunlar çıkabilir.
Bu yüzden disable oluşturmak istersek;

CREATE OR REPLACE TRIGGER SALES_TRG
BEFORE INSERT ON SALES_TAB
FOR EACH ROW
DISABLED
BEGIN
NULL;
END;

Trigger Drop Etmek:

DROP TRIGGER SALES_TRG;
Trigger Dropped.

:OLD eski kayıt setini :NEW yeni girilen kayıt setini temsil eder.BEFORE ve AFTER anahtar kelimeleri işlemden önce veya sonra çalışması gerektiğini belirtir.Loglama işlemlerinde AFTER kullanılırken, kontrol ve hata verdirmek için BEFORE kullanılır.Aşağıda bazı örnekler vermeye çalıştım, ama sınırlar tamamen sizin hayal gücünüze kalmış..

Örneklerimiz için kullanacağımız bir tablo yaratalım;

CREATE TABLE sales_tab(
order_no NUMBER,
order_date DATE,
customer_id VARCHAR2(10),
sales_user VARCHAR2(30));

Örnek 1: Trigger ile Default değer atama

CREATE OR REPLACE TRIGGER SALES_TRG_1
BEFORE INSERT ON sales_tab
FOR EACH ROW
DECLARE
BEGIN
:NEW.SALES_USER:=USER;
:NEW.ORDER_DATE:=SYSDATE;
END;

Triggerımızı create edelim, ve aşağıdaki insert işlemini yapalım.

INSERT INTO sales_tab(order_no,customer_id) VALUES (1,'M0001');

Tablomuzu Sorgulayalım.

SELECT  * FROM sales_tab;

ORDER_NO  ORDER_DATE            CUSTOMER_ID   SALES_USER
1	  1/13/2013 5:32:37 PM	M0001	      HR

Sales_user ve order date tarihini insertte belirtmedik ama triggerımız otomatik olarak
set ettiğimiz değerleri attı tabloya..Değer belirtmiş de olsaydık yine trigger değerleri ezip kendi değerlerini gönderecekti.

 Örnek 2:Trigger ile silme kontrolü

Triggerımızı oluşturalım:

CREATE OR REPLACE TRIGGER SALES_TRG_2
  BEFORE DELETE ON sales_tab
  FOR EACH ROW
DECLARE
BEGIN
raise_application_error(-20000,'SILME_YOK:Kayit silemezsiniz...');
END;

Şimdi tablomuzdan kayıt silmeye çalışalım.Trigger’da belirttiğimiz hatayı alacağız.

DELETE FROM sales_tab n WHERE n.order_no=1; 

ORA-20000: SILME_YOK:Kayit silemezsiniz...
ORA-06512: at "HR.SALES_TRG_2", line 3 
ORA-04088: error during execution of trigger 'HR.SALES_TRG_2'

Örnek 3: Trigger ile zaman kontrolü

Haftasonu işlem yapılmasını engelleyelim.Triggerımızı oluşturalım.

CREATE OR REPLACE TRIGGER SALES_TRG_3
  BEFORE INSERT OR UPDATE OR DELETE ON sales_tab
  FOR EACH ROW
DECLARE
BEGIN
IF TRIM(to_char(SYSDATE,'DAY')) IN ('SATURDAY','SUNDAY') THEN
raise_application_error(-20000,'HSONU:Haftasonu islem yapamazsiniz..');
END IF;
END;

Tablomuzda işlem yapmaya çalışalım..

UPDATE sales_tab f
SET f.order_date=f.order_date+1
WHERE f.order_no=1;

ORA-20000: HSONU:Haftasonu islem yapamazsiniz..
ORA-06512: at "HR.SALES_TRG_3", line 5
ORA-04088: error during execution of trigger 'HR.SALES_TRG_3'

Örnek 4: Trigger ile logon kontrolü

Belirli bir kullanıcının sisteme login olmasını engelleyelim.

CREATE OR REPLACE TRIGGER SALES_TRG_4
   AFTER LOGON ON DATABASE
BEGIN
   IF SYS_CONTEXT('USERENV','SESSION_USER') IN ('OMER') THEN
      raise_application_error(-20003,'Veritabanına bağlantınız engellendi,lütfen sistem yöneticinizle görüşün..');
   END IF;
END;

Omer Kullanıcısı login olmaya çalıştığında hata alacaktır.

Veritabanına bağlantınız engellendi,lütfen sistem yöneticinizle görüşün..

Örnek 5: Trigger ile loglama islemleri

Siilinen kayıtları kim ne zaman sildi loglayalım.Loglarımızı tutması için bir log tablosu oluşturalım.

CREATE TABLE SALES_TAB_LOG
( order_no NUMBER,
order_date DATE,
customer_id VARCHAR2(10),
sales_user VARCHAR2(30),
delete_user VARCHAR2(30),
delete_time DATE);

Silme işlemini loglayacak triggerımızı oluşturalım.

CREATE OR REPLACE TRIGGER SALES_TRG_5
AFTER DELETE ON sales_tab
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO sales_tab_log VALUES(
:OLD.Order_No,
:OLD.Order_Date,
:OLD.CUSTOMER_ID,
:OLD.SALES_USER,
USER,
SYSDATE,
:OLD.Order_No||' Nolu kayıt '||USER||' Tarafından '||SYSDATE ||' Tarihinde Silindi.');
END;

Şimdi kayıt silelim.Ve log tablomuza bakalım.

DELETE FROM sales_tab n WHERE n.order_no=1;
SELECT t.delete_user,t.delete_time,t.notes FROM SALES_TAB_LOG t;

1	HR	1/14/2013 10:15:10 AM	1 Nolu kayıt HR Tarafindan 14-JAN-13 Tarihinde Silindi.

Yine konuyla ilgili olarak bu makaleme bakabilirsiniz.

http://omeracar.com.tr/oracle-logon-trigger-audit/

Umarım faydalı olur.