Auditing DML statements issues from SQLPLUS prompt

 

There are probably several approaches to the issue.

The first approach can be demonstrated as follows

 

Assume that you are auditing a table called 'XX'

Write the following Trigger

 

CREATE OR REPLACE TRIGGER TESTING

BEFORE INSERT ON XX

FOR EACH ROW

DECLARE

    V_PROG VARCHAR2(100);

BEGIN  SELECT PROGRAM INTO V_PROG FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');

   IF upper(V_PROG) = 'SQLPLUSW.EXE' THEN   --  may need to modify to in ('SQLPLUSW.EXE',SQLPLUS..etc) '

              INSERT INTO TEST VALUES (SYSDATE);

   END IF;

END;

 

if you get the following error

Warning: Trigger created with compilation errors.

SQL> sho error
Errors for TRIGGER TESTING:

LINE/COL ERROR
-------- ----------------------------------------------------
3/8 PL/SQL: SQL Statement ignored
3/40 PL/SQL: ORA-00942: table or view does not exist

 

It is because  you need to grant yourself a privilege to read Dictionary View (V$session). Withing PL/SQL packages, you need to be given read access to V$sesion directly, or better do the following

 

GRANT SELECT ANY DICTIONARY to User;

 

 

The Second Approach depends on the usage of DBMS_APPLICATION_INFO

 

The DBMS_APPLICATION_INFO package provides a mechanism for registering the name of the application module that is currently running with the RDBMS. Registering the name of the module allows DBAs to monitor how the system is being used.  It also allows them to do performance analysis and resource
accounting by module.  The purpose of the package is auditing or performance tracking.  DBMS_APPLICATION_INFO is available from Oracle RDBMS Version 7.2 and later.
 

When DBMS_APPLICATION_INFO.set_module (Module_name, Action_name) is run, the values of Module_name paramtere and the Action_name parameter are registered under the MODULE and ACTION columns of V$SESSION and V$SQLAREA.  Example of V$session structure is shown below and highlighting the two columns.  (Please note that you give the parameters mentioned above values of you choice that you can later distinguish for example DBMS_APPLICATION_INFO.set_module('Order_process','Clearing')  )

SQL> desc v$session

 Name                                      Null?    Type

 ----------------------------------------- -------- ---------------------

 SADDR                                              RAW(4)

 SID                                                NUMBER

 SERIAL#                                            NUMBER

 AUDSID                                             NUMBER

 PADDR                                              RAW(4)

 USER#                                              NUMBER

 USERNAME                                           VARCHAR2(30)

 COMMAND                                            NUMBER

 OWNERID                                            NUMBER

 TADDR                                              VARCHAR2(8)

 LOCKWAIT                                           VARCHAR2(8)

 STATUS                                             VARCHAR2(8)

 SERVER                                             VARCHAR2(9)

 SCHEMA#                                            NUMBER

 SCHEMANAME                                         VARCHAR2(30)

 OSUSER                                             VARCHAR2(30)

 PROCESS                                            VARCHAR2(12)

 MACHINE                                            VARCHAR2(64)

 TERMINAL                                           VARCHAR2(16)

 PROGRAM                                            VARCHAR2(64)

 TYPE                                               VARCHAR2(10)

 SQL_ADDRESS                                        RAW(4)

 SQL_HASH_VALUE                                     NUMBER

 PREV_SQL_ADDR                                      RAW(4)

 PREV_HASH_VALUE                                    NUMBER

 MODULE                                             VARCHAR2(48)

 MODULE_HASH                                        NUMBER

 ACTION                                             VARCHAR2(32)

 ACTION_HASH                                        NUMBER

Now, you can use this package inside your application at the moment when you insert data to the database.

 

Then write a database trigger that simply reads the Module and if it does not contain the information that you provided that you know that the source of this INSERT is not your application.

 

You can read the Module information either by reading the Module column in V$session or use  DBMS_APPLICATION_INFO.READ_MODULE.

 

There are other interesting members of the package like SET_CLIENT_INFO and READ_CLIENT_INFO

 

Example skeleton

 

CREATE Or REPLACE PROCEDURE action1(  ,   ,   ) 
AS
BEGIN
        DBMS_APPLICATION_INFO.SET_MODULE( ‘DataEntry_Form’,'Action1');
 
        INSERT INTO anytable 
        VALUES (  );
 
        DBMS_APPLICATION_INFO.SET_MODULE ('','');
END;

 

 

Inside you database trigger

     

      If DBMS_APPLICATION_INFO.GET_MODULE != ‘DataEntry_Form’ then

      -- log the entry