auditing_problem

[Arsalan]

Salam Sir

Kindly review the below script which i made to track records of  user logon or logoff
which are working fine but it also keep records of DBSNMP BACKGROUND PROCESS
so i don;t want to keep records of DBSNMP .
Kindly bring some change of   SYS.AUD_LOGON_TRIGGER AFTER which must not keep records of DBSNMP.

CREATE TABLE DB_AUDIT.AUD_LOGON
(
  USERNAME       VARCHAR2(50BYTE),
  SID NUMBER,
  SESSIONID NUMBER,
  IPADDR         VARCHAR2(50BYTE),
  TERMINAL       VARCHAR2(50BYTE),
  OS_USER         VARCHAR2(50BYTE),
  HOSTNAME VARCHAR2(100),
  PROGRAM         VARCHAR2(50BYTE),
--MODULE VARCHAR2(100),
  LOGON_DATE       DATE,
  LOGOFF_DATE     DATE,
  DURATION_IN_MIN   NUMBER,
  FATTEMPT_DATE    DATE)
TABLESPACE DB_AUDIT;
----------------------------------------------------------


CREATE OR REPLACE TRIGGER SYS.AUD_LOGON_TRIGGER AFTER
LOGON ON DATABASE
DECLARE
  V_PROGRAM VARCHAR2(64);
  V_IP   VARCHAR2(20);
  V_MESSAGE VARCHAR2(200);
  V_SESSION NUMBER;
  V_SID NUMBER;
  V_IPADDRESS VARCHAR2(50);
  V_TERMINAL VARCHAR2(100);
  V_OS_USER   VARCHAR2(100);
  V_LOGON_USER VARCHAR2(20);
  V_HOST VARCHAR2(100);
--V_MODULE VARCHAR2(100);
  V_TRUSTED_APP NUMBER:=1;
BEGIN
 SELECT PROGRAM,USERNAME,SID INTO V_PROGRAM,V_LOGON_USER,V_SID FROM V$SESSION WHERE AUDSID=SYS_CONTEXT('USERENV','SESSIONID') AND ROWNUM=1;
  V_IPADDRESS:=SYS_CONTEXT('USERENV','IP_ADDRESS');
  V_TERMINAL:=SYS_CONTEXT('USERENV','TERMINAL');
  V_OS_USER:=SYS_CONTEXT('USERENV','OS_USER');
  V_SESSION:=SYS_CONTEXT('USERENV','SESSIONID');
  V_HOST:=SYS_CONTEXT('USERENV', 'HOST');
--V_MODULE:=SYS_CONTEXT('USERENV', 'MODULE');
 
IF V_IPADDRESS IS NULL THEN
   V_IPADDRESS:='0.0.0.0';
END IF;
IF  V_IPADDRESS='0.0.0.0' THEN
NULL;
ELSE
INSERT INTO DB_AUDIT.AUD_LOGON
           (
     USERNAME,
     SID,
     SESSIONID,
     IPADDR,
     TERMINAL,
     OS_USER,
     HOSTNAME,
     PROGRAM,
          --MODULE,
     LOGON_DATE,
     FATTEMPT_DATE
           )
            VALUES
           (
            V_LOGON_USER,
            V_SID,
            V_SESSION,
            V_IPADDRESS,
            V_TERMINAL,
            V_OS_USER,
            V_HOST,
            V_PROGRAM,
          --V_MODULE,
            SYSDATE,
            NULL
           );
END IF;
 EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      NULL;
END;
/

SET SERVEROUTPUT ON

CREATE  OR REPLACE TRIGGER SYS.AUD_LOGOFF_TRIGGER
BEFORE LOGOFF ON DATABASE
BEGIN
UPDATE
DB_AUDIT.AUD_LOGON
SET
(
LOGOFF_DATE,
  DURATION_IN_MIN
)
=
(
SELECT
SYSDATE,
ROUND((SYSDATE-LOGON_TIME)*(24*60),1)
FROM
V$SESSION
WHERE    
AUDSID = SYS_CONTEXT('USERENV','SESSIONID')
)
WHERE
SESSIONID = SYS_CONTEXT('USERENV','SESSIONID')
AND LOGOFF_DATE IS NULL;
 EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      NULL;
END;
/

[ErmanArslansOracleBlog]

Modify the line:

SELECT PROGRAM,USERNAME,SID INTO V_PROGRAM,V_LOGON_USER,V_SID FROM V$SESSION WHERE AUDSID=SYS_CONTEXT('USERENV','SESSIONID') AND ROWNUM=1;

To be as follows;

 SELECT PROGRAM,USERNAME,SID INTO V_PROGRAM,V_LOGON_USER,V_SID FROM V$SESSION WHERE AUDSID=SYS_CONTEXT('USERENV','SESSIONID') AND USERNAME!='DBSNMP' AND ROWNUM=1;

[Arsalan]

Hi Erman

Thanks you that you replay me , my problem 50% have been solved . now DBNMSP Background process are not recording.
but problem is that after logoff of sys user the  logoff time are not recording
kindly review the below trigger and guide me , for more details of aduting script check the auditing script which i sent you last day.

Thanks
Arsala


CREATE  OR REPLACE TRIGGER SYS.AUD_LOGOFF_TRIGGER
BEFORE LOGOFF ON DATABASE
BEGIN
UPDATE
DB_AUDIT.AUD_LOGON
SET
(
LOGOFF_DATE,
  DURATION_IN_MIN
)
=
(
SELECT
SYSDATE,
ROUND((SYSDATE-LOGON_TIME)*(24*60),1)
FROM
V$SESSION
WHERE    
AUDSID = SYS_CONTEXT('USERENV','SESSIONID')
)
WHERE
SESSIONID = SYS_CONTEXT('USERENV','SESSIONID')
AND LOGOFF_DATE IS NULL;
 EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      NULL;
END;
/

[ErmanArslansOracleBlog]

Hi Arsala,

This is beyond the scope.
It was a one time only thing. I don't do work from here.
What I do is, helpying you for solving your problems.

So, I can suggest you debugging for that.

You have already a general exception defined in your trigger.
Login using sqlplus with SYS user.
set serveroutput on --> SQL>set serveroutput on
then logoff and read the displayed message. (this will be coming from your trigger's exception)

If you will not see any messages , then I suggest you to modify your logoff trigger and make it write additional information in its output.

[Arsalan]

OK  SIR.

Thanks...

[Arsalan]

Sir

One small issue i have auditing ddl  when sys user do DDL Operation it will not recorded
so i change the query but i could'n solve kindly bring change on script and replay me

Thanks.

CREATE OR REPLACE TRIGGER SYS.AUD_DDL_TRIGGER AFTER
DDL ON DATABASE
DECLARE
V_SID NUMBER;
V_PROGRAM VARCHAR2(100);
V_NUM NUMBER;
SQL_TXT  ORA_NAME_LIST_T;
SQL_STMT CLOB;
ERR_CODE VARCHAR2(15);
ERR_MSG  VARCHAR2(512);
V_UNITE  VARCHAR2(30):='AUD_DDL_TRIGGER';
BEGIN
V_NUM:=ORA_SQL_TXT(SQL_TXT);
FOR I IN 1 .. NVL(V_NUM,0) LOOP
SQL_STMT:=SQL_STMT||SQL_TXT(I);
END LOOP;
---problem in this query

SELECT SID,PROGRAM INTO V_SID,V_PROGRAM FROM V$SESSION WHERE
AUDSID=SYS_CONTEXT('USERENV','SESSIONID')
AND SYS_CONTEXT('USERENV','SESSION_USER') NOT IN 'SYS';


INSERT INTO DB_AUDIT.AUD_DDL(
                   USER_NAME,
             SID,
                   SESSIONID,
                   DDL_DATE,
                   DDL_TYPE,
                   OBJECT_TYPE,
                   OWNER,
                   OBJECT_NAME,
                   IP_ADDR,
                   TERMINAL,
                   HOSTNAME,
                   OS_USER,
                   PROGRAM,
                   SQL_STMT
                   )
VALUES(
                   ORA_LOGIN_USER,
                   V_SID,
                   SYS_CONTEXT('USERENV','SESSIONID'),
SYSDATE,
                   ORA_SYSEVENT,
                   ORA_DICT_OBJ_TYPE,
                   ORA_DICT_OBJ_OWNER,
                   ORA_DICT_OBJ_NAME,
                   SYS_CONTEXT('USERENV','IP_ADDRESS'),
                   SYS_CONTEXT('USERENV','TERMINAL'),
                   SYS_CONTEXT('USERENV','HOST'),
                   SYS_CONTEXT('USERENV','OS_USER'),
                   V_PROGRAM,
                   SQL_STMT);
EXCEPTION
WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
NULL;
END;
/

[ErmanArslansOracleBlog]

It says -> NOT IN 'SYS'

Dont you see it?

Also, why do you use custom triggers for auditing?
I think you are not so familiar with sql, plsql  and db objects, so use Oracle auditing. No need to make things complex.

Oracle has its builtin auditing mechanism. Use that instead.

Read, Oracle Database Online Documentation , it clearly explains auditing.