Thursday, December 1, 2011

Get email alerts, when someone accesses specific tables

I wanted to create a procedure, which will be run by a job every few seconds, and get email alerts every time someone accesses specific tables.
I want to know usernames, terminal names, some additional session info and the SQL statement executed.
I used UTL_MAIL package, which is not installed by default, so I had it installed.
Let's say I want to monitor a table, called AR_PAYMENT_SCHEDULES_ALL.
The procedure is:
CREATE OR REPLACE PROCEDURE SYSTEM.CHECKSQL
IS
   TYPE T_CURSOR IS REF CURSOR;

   V_CURSOR      T_CURSOR;
   V_INST        NUMBER;
   V_SID         NUMBER;
   V_SERIAL      NUMBER;
   V_USERNAME    VARCHAR2 (30);
   V_OSUSER      VARCHAR2 (30);
   V_MACHINE     VARCHAR2 (64);
   V_TERMINAL    VARCHAR2 (30);
   V_PROGRAM     VARCHAR2 (48);
   V_MODULE      VARCHAR2 (48);
   V_LOGONTIME   DATE;
   V_SQLTEXT     VARCHAR2 (1000);
   V_OUTPUT      VARCHAR2 (32767);
   V_ALTER       VARCHAR2 (500);
BEGIN
   V_OUTPUT := NULL;

   OPEN V_CURSOR FOR
      SELECT B.INST_ID,
             B.SID,
             B.SERIAL#,
             B.USERNAME,
             B.OSUSER,
             B.MACHINE,
             B.TERMINAL,
             B.PROGRAM,
             B.MODULE,
             B.LOGON_TIME,
             A.SQL_TEXT
        FROM GV$SQL A, GV$SESSION B
       WHERE     A.SQL_ID = B.SQL_ID
             AND A.INST_ID = B.INST_ID
             AND UPPER (A.SQL_TEXT) LIKE '%AR_PAYMENT_SCHEDULES_ALL%'
             AND UPPER (A.SQL_TEXT) NOT LIKE '%V$SESSION%';

   LOOP
      FETCH V_CURSOR
      INTO V_INST,
           V_SID,
           V_SERIAL,
           V_USERNAME,
           V_OSUSER,
           V_MACHINE,
           V_TERMINAL,
           V_PROGRAM,
           V_MODULE,
           V_LOGONTIME,
           V_SQLTEXT;

      EXIT WHEN V_CURSOR%NOTFOUND;
      V_OUTPUT :=
            V_OUTPUT
         || CHR (13)
         || CHR (10)
         || 'INSTANCE: '
         || V_INST
         || CHR (13)
         || CHR (10)
         || 'SID: '
         || V_SID
         || CHR (13)
         || CHR (10)
         || 'SERIAL#: '
         || V_SERIAL
         || CHR (13)
         || CHR (10)
         || 'USERNAME: '
         || V_USERNAME
         || CHR (13)
         || CHR (10)
         || 'OSUSER: '
         || V_OSUSER
         || CHR (13)
         || CHR (10)
         || 'MACHINE: '
         || V_MACHINE
         || CHR (13)
         || CHR (10)
         || 'TERMINAL: '
         || V_TERMINAL
         || CHR (13)
         || CHR (10)
         || 'PROGRAM: '
         || V_PROGRAM
         || CHR (13)
         || CHR (10)
         || 'MODULE: '
         || V_MODULE
         || CHR (13)
         || CHR (10)
         || 'LOGON TIME: '
         || TO_CHAR (V_LOGONTIME, 'DD/MM/RR HH:MI:SS')
         || CHR (13)
         || CHR (10)
         || 'SQL: '
         || V_SQLTEXT
         || CHR (13)
         || CHR (10)
         || '**************************************************';
   END LOOP;

   CLOSE V_CURSOR;

   IF V_OUTPUT IS NOT NULL
   THEN
      V_ALTER := 'ALTER SESSION SET SMTP_OUT_SERVER = ''[YourSMTPserver.YourDomain.com]''';

      EXECUTE IMMEDIATE V_ALTER;

      UTL_MAIL.SEND_ATTACH_VARCHAR2 (
         SENDER         => '[The sender's email address, can be fake]',
         RECIPIENTS     => '[The email addresses of the recipients, separated by commas]',
         SUBJECT        => 'SQL CAUGHT',
         ATT_FILENAME   => 'sessions.txt',
         ATTACHMENT     => V_OUTPUT);
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END CHECKSQL;
/

Whenever a user accesses this table, I get an email with subject 'SQL CAUGHT' and an attached text file, named 'sessions.txt'.
A sample of this file is:
INSTANCE: 2
SID: 8125
SERIAL#: 7646
USERNAME: APPS
OSUSER: oracle
MACHINE: ecapps2
TERMINAL:
PROGRAM:
MODULE: XXIEX_GRP_DATA_PARALLEL
LOGON TIME: 01/12/11 12:15:18
SQL: SELECT /*+ use_nl(ps x) index_ffs(x XXIEX_SCORING_INVOICES_U1) */ :B1 ,X.PARTY_ID ,PS.CUSTOMER_TRX_ID ,PS.TRX_DATE ,PS.TRX_NUMBER ,ROUND(PS.AMOUNT_DUE_ORIGINAL * NVL(PS.EXCHANGE_RATE, 1), 2) AMOUNT_DUE_ORIGINAL ,PS.ACCTD_AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING ,PS.DUE_DATE ,PS.ORG_ID ,XXIEX_IEX_CUSTOMER_SCORING_PKG.FUN_GET_APPL_AMOUNT(PS.CUSTOMER_TRX_ID, PS.DUE_DATE, :B2 ) FROM AR_PAYMENT_SCHEDULES_ALL PS ,XXIEX_SCORING_INVOICES X WHERE X.REQUEST_ID = :B1 AND PS.CUSTOMER_TRX_ID = X.CUSTOMER_TRX_ID AND PS.CLASS IN ('INV', 'DM', 'CM')
**************************************************
INSTANCE: 1
SID: 8111
SERIAL#: 53187
USERNAME: AFRAGOU
OSUSER: afragou
MACHINE: CENTRAL-DOMAIN\OTE-8B2-015
TERMINAL: OTE-8B2-015
PROGRAM: oracle@ecdb1 (P005)
MODULE: TOAD 9.7.2.5
LOGON TIME: 01/12/11 12:33:30
SQL: CREATE TABLE  AR_PS_WRONG_DIAKAN_PART012 AS (SELECT /*+  PARALLEL(A,6)    */        *   FROM apps.ar_payment_schedules_all partition(AR_PAYMENT_SCHEDULES_PART012)a WHERE        a.CLASS = 'DM'    AND a.status = 'OP'    AND EXISTS (           SELECT   b.customer_id, COUNT(*),b.trx_date,                    b.amount_due_original               FROM apps.ar_payment_schedules_all b              WHERE b.CLASS = 'DM'                AND b.customer_id = a.customer_id                AND b.trx_date = a.trx_date                AND b.amount_due_original = a.amount_due_original           GROUP BY b.customer_id, b.trx_date, b.amount_due_original             HAVING COUNT (b.customer_id) > 1                AND COUNT (TRUNC (b.trx_date)) > 1                AND COUNT (b.amount_due_original) > 1)                      )
**************************************************