">

Postgresql DML Audit Mekanizması

Postgresql DML Audit Mekanizması

merhaba,

Postgresql için DML audit mekanizmasını aşağıdaki gibi yapılabilir;

Logların tutulacağı bir tablo oluşturulur.

CREATE TABLE public.dml_event 

(

SchemaName CHARACTER VARYING

,TableName CHARACTER VARYING

,UserName CHARACTER VARYING    

,DMLAction CHARACTER VARYING

,OriginalData TEXT

,ExecutedNewData TEXT

,ExecutedSQL TEXT

,RecordDateTime TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()

,client_id text

);

 

Triggerin tetkleyeceği fonksiyon;

CREATE OR REPLACE FUNCTION public.trg_AuditDML() 

RETURNS TRIGGER LANGUAGE plpgsql

AS $BODY$

DECLARE

    OldData TEXT;

    NewData TEXT;

BEGIN 

 

    IF (TG_OP = 'UPDATE') THEN

        OldData := ROW(OLD.*);

        NewData := ROW(NEW.*);

       INSERT INTO public.dml_event 

(

SchemaName

,TableName

,UserName

,DMLAction

,OriginalData

,ExecutedNewData

,ExecutedSQL

,client_id

        VALUES 

(

TG_TABLE_SCHEMA::TEXT

,TG_TABLE_NAME::TEXT

,session_user::TEXT

,substring(TG_OP,1,1)

,OldData

,NewData

,current_query(),inet_client_addr());

        RETURN NEW;

    ELSIF (TG_OP = 'DELETE') THEN

        OldData := ROW(OLD.*);

        INSERT INTO public.dml_event 

(

SchemaName

,TableName

,UserName

,DMLAction

,OriginalData

,ExecutedSQL

,client_id

)

        VALUES 

(

TG_TABLE_SCHEMA::TEXT

,TG_TABLE_NAME::TEXT

,session_user::TEXT

,substring(TG_OP,1,1)

,OldData

,current_query(),inet_client_addr()

);

        RETURN OLD;

    ELSIF (TG_OP = 'INSERT') THEN

        NewData := ROW(NEW.*);

        INSERT INTO public.dml_event 

(

SchemaName

,TableName

,UserName

,DMLAction

,ExecutedNewData

,ExecutedSQL

,client_id

)

        VALUES 

(

TG_TABLE_SCHEMA::TEXT

,TG_TABLE_NAME::TEXT

,session_user::TEXT

,substring(TG_OP,1,1)

,NewData

,current_query(),inet_client_addr()

);

        RETURN NEW;

    ELSE

        RAISE WARNING '[public.trg_AuditDML] - Other action occurred: %, at %',TG_OP,now();

        RETURN NULL;

    END IF;

END

$BODY$

 

Son olarakta trigger'ımız;

 

CREATE TRIGGER trg_tbl_Employees_INSERT_UPDATE_DELETE

AFTER INSERT OR UPDATE OR DELETE ON public.izlenecek_tablo_ismi

FOR EACH ROW EXECUTE PROCEDURE public.trg_AuditDML();

 

Umarım faydali olmuştur..

Yorumlar

Henüz yorum yapılmadı.

Yorum Yapın