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