Solved

Problem with AUDITING

Posted on 2010-11-19
12
633 Views
Last Modified: 2012-05-10
Hi,
I've an Oracle schema with 2 users: AFM and AFM_SECURE
I've an application that connect Oracle via ODBC.

I've these tables:

CREATE TABLE AFM_SECURE.AFM_USERS
(
  USER_PWD          VARCHAR2(64 BYTE),
  USER_NAME         VARCHAR2(64 BYTE)
);

CREATE TABLE AFM.AFM_MODS
(
  COMMENTS        VARCHAR2(256 BYTE),
  AFM_MODULE      VARCHAR2(32 BYTE)
);

CREATE TABLE AFM.BU
(
   NAME          VARCHAR2(64 BYTE),
  BU_ID         VARCHAR2(64 BYTE)
);

Open in new window


In Table AFM_USERS (OWNER=AFM_SECURE) are registered users of my application (they are not Oracle users).

Now I'd like to register each update that the user (of my application) performs on the AFM.BU table

I add policy:
--SYS user
BEGIN
    DBMS_FGA.ADD_POLICY(
    OBJECT_SCHEMA => 'AFM',
    OBJECT_NAME   => 'AFM_MODS',
    POLICY_NAME   => 'SELECT_AFM_MODS');
END;

BEGIN
    DBMS_FGA.ADD_POLICY(
    OBJECT_SCHEMA => 'AFM_SECURE',
    OBJECT_NAME   => 'AFM_USERS',
    POLICY_NAME   => 'SELECT_AFM_USERS');
END;

grant select on DBA_FGA_AUDIT_TRAIL to afm;

Open in new window


When I connect to the application with a new user of the AFM_USERS table I get 2 new record into  SYS.DBA_FGA_AUDIT_TRAIL view
with 2 new SESSION_ID, before with DB_USER=AFM_SECURE OBJECT_NAME = AFM_USERS and after with DB_USER=AFM OBJECT_NAME = AFM_MODS.
At SESSION_ID of DB_USER=AFM_SECURE  DBA_FGA_AUDIT_TRAIL.SQL_BIND = #1(3):TOM  (TOM is user of my application).

I tried this trigger (LOG_W_BU is a LOG TABLE):

--AFM user
CREATE OR REPLACE TRIGGER TRG_LOG_BU BEFORE UPDATE
ON BU
FOR EACH ROW

DECLARE

      V_TERMINAL        	VARCHAR2(60);
      V_OSUSER          	VARCHAR2(32);
      V_USER          	        VARCHAR2(32);
      V_IPADDRESS       	VARCHAR2(32);
      V_SESSION_ID   		NUMBER;

BEGIN

    SELECT SYS_CONTEXT('USERENV','TERMINAL')
    INTO V_TERMINAL
    FROM DUAL;

	 SELECT  SYS_CONTEXT('USERENV','SESSIONID')
	INTO V_SESSION_ID
	FROM DUAL;

 	SELECT SUBSTR(SQL_BIND,8)
	INTO V_USER
	FROM DBA_FGA_AUDIT_TRAIL
	WHERE SESSION_ID = V_SESSION_ID;

    SELECT SYS_CONTEXT('USERENV','OS_USER')
    INTO V_OSUSER
    FROM DUAL;

    SELECT SYS_CONTEXT('USERENV','IP_ADDRESS')
    INTO V_IPADDRESS
    FROM DUAL;

       INSERT INTO LOG_W_BU (NAME, BU_ID, USER_APPLICATION, OS_USER, IP_ADDRESS, TERMINAL, DATA_ACTION, HOUR_ACTION, RECORD_TYPE, ACTION)
        VALUES (:OLD.NAME, :OLD.BU_ID, V_USER, V_OSUSER, V_IPADDRESS, V_TERMINAL, SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'), 'OLD','UPDATE');

       INSERT INTO LOG_W_BU (NAME, BU_ID, USER_APPLICATION, OS_USER, IP_ADDRESS, TERMINAL, DATA_ACTION, HOUR_ACTION, RECORD_TYPE, ACTION)
        VALUES (:NEW.NAME, :NEW.BU_ID, V_USER, V_OSUSER, V_IPADDRESS, V_TERMINAL, SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'), 'NEW','UPDATE');

END;

Open in new window


but with this query:
SELECT  SYS_CONTEXT('USERENV','SESSIONID')
INTO V_SESSION_ID
FROM DUAL;

Open in new window


I get SESSION_ID of DB_USER=AFM and DBA_FGA_AUDIT_TRAIL.SQL_BIND is NULL

How can I insert into LOG_W_BU.USER_APPLICATION the user of my application?

Thanks in advance!!
0
Comment
Question by:ralph_rea
  • 5
  • 5
12 Comments
 

Author Comment

by:ralph_rea
ID: 34172861
Here an example of SYS.DBA_FGA_AUDIT_TRAIL view for 3 new session:

SESSION_ID DB_USER    OBJECT_NAM SQL_BIND             SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
---------- ---------- ---------- -------------------- ---------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
760077     AFM_SECURE AFM_USERS   #1(7):SAM           SELECT user_name, user_pwd FROM afm_users WHERE user_name=:1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
760078     AFM        AFM_MODS                        SELECT afm_module,comments FROM afm_mods 

760080     AFM_SECURE AFM_USERS   #1(7):TOM           SELECT user_name, user_pwd FROM afm_users WHERE user_name=:1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
760082     AFM        AFM_MODS                        SELECT afm_module,comments FROM afm_mods

760084     AFM_SECURE AFM_USERS   #1(7):JOHN          SELECT user_name, user_pwd FROM afm_users WHERE user_name=:1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
760085     AFM        AFM_MODS                        SELECT afm_module,comments FROM afm_mods    

Open in new window

   

Have someone any idea??
0
 
LVL 5

Expert Comment

by:Zopilote
ID: 34177598
interesting question!!

it will not work, because a database trigger fires with the privileges of owner not that of use.

you can try a logon/logoff trigger, check here:

http://www.dba-oracle.com/art_builder_sec_audit.htm
0
 

Author Comment

by:ralph_rea
ID: 34177728
Zopilote,
I tried this trigger after logon:

CREATE OR REPLACE TRIGGER SYS.TRG_LOGON_AFM_SECURE
AFTER LOGON ON DATABASE
WHEN (
USER='AFM_SECURE'
      )
DECLARE

   
BEGIN

        INSERT INTO LOG_ON_AFM_SECURE
        (USERNAME,
        OSUSER,
        SESSION_ID,
        HOSTNAME,
        IP_ADDRESS,
        TERMINAL,
        INSTANCE,
        ENTRYID,
        LOGON_DAY,
        LOGIN_TIME,
        LAST_ACTION,
        LAST_PROGRAM,
        LAST_MODULE,
        LOGOFF_DAY,
        LOGOFF_TIME,
        ELAPSED_MINUTES)
        VALUES 
        (USER,
        SYS_CONTEXT('USERENV', 'OS_USER'),
        SYS_CONTEXT('USERENV','SESSIONID'),
        SYS_CONTEXT('USERENV','HOST'),
        SYS_CONTEXT('USERENV','IP_ADDRESS'),
        SYS_CONTEXT('USERENV','TERMINAL'),
        SYS_CONTEXT('USERENV','INSTANCE'),
        SYS_CONTEXT('USERENV','ENTRYID'),
        SYSDATE,
        TO_CHAR(SYSDATE, 'HH24:MI:SS'),
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL);     

        COMMIT;

END TRG_LOGON_AFM_SECURE;
/

Open in new window


BUT How can I use this trigger (SESSION_ID??) for my case??

Thanks in advance!
0
 
LVL 5

Expert Comment

by:Zopilote
ID: 34178128
tricky...  

1) you can try to set up the module name from your application

  DBMS_APPLICATION_INFO.set_module(module_name => 'add_order',
                                   action_name => 'insert into orders');

2) and then get it from v$session

SELECT sid,
       serial#,
       username,
       osuser,
       module,
       action,
       client_info
FROM   v$session;

3) problem is... v$session is not reliable enough, it doesn't keep all sessions.

Note: WARNING!! if the on logon trigger has errors then all logins to the database are blocked
to solve this problem:
connect sys as sysdba
DROP TRIGGER SYS.TRG_LOGON_AFM_SECURE

question: what are you trying to do?


0
 
LVL 5

Expert Comment

by:Zopilote
ID: 34178251
I read your case again.

imho you cannot use the v_session because they are always going to be different.

the first select to get the user/pass from afm_users will have a different session id from the next one, so there is no way.

the only possibility is to find something linked to the user that you can use to know the relevant session_ids, i.e. ip address (but here also you will have the problem that the ip address will be of the server and not of the user)

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ralph_rea
ID: 34181058
what I am trying to do is to register each update that the user (of my application) performs on the AFM.BU table.

One way would be this:
When I connect to the application with a new user of the AFM_USERS table, I get 2 new SESSION_ID.
SESSION_ID (AFM) > SESSION_ID (AFM_SECURE)
with this query:
SELECT  SYS_CONTEXT('USERENV','SESSIONID')
INTO V_SESSION_ID
FROM DUAL;

Open in new window


I get the SESSION_ID (AFM)
I'd like to write a query that takes the number immediately below that of the SESSION_ID (AFM), make a difference.
for example: V_DIFFERENCE = SESSION_ID (AFM) - NUMBER IMMEDIATELY BELOW

SELECT  SYS_CONTEXT('USERENV','SESSIONID') - V_DIFFERENCE
INTO V_SESSION_ID
FROM DUAL;

Open in new window

In this case I'll get alway SESSION_ID (AFM_SECURE)

What do you think?
Have someone help me??



0
 
LVL 5

Expert Comment

by:Zopilote
ID: 34181133
sessionid is really the audses$ sequence.  It is set to cycle.  Its max_value may
vary by implementation but eventually it can cycle. select * from user_sequences where sequence_name = 'AUDSES$'; after 2,000,000,000 (2 billion) logins -- the numbers will be reused.

-how will you know when the sequence is reused?
-with multiple users in a database, so how are you going to be sure that the immediately below is going to be the one for that user? if two users access at the same time you can get very wrong results.

what is the value of SYS.DBA_FGA_AUDIT_TRAIL.OS_USER?       (Operating system username who executed the query)

a solution could be to modify the application to save the last_modified_date and last_modified_user (application users Tom, etc) in the tables itself, this is a common practice when you have only one oracle user for many application's user.

0
 

Author Comment

by:ralph_rea
ID: 34186593
The link below:
http://www.go-faster.co.uk/PSFT.fga.pdf

provides an example of the package DBMS_APPLICATION_INFO.

Do you think it will be useful for my case?
0
 
LVL 5

Accepted Solution

by:
Zopilote earned 500 total points
ID: 34188586
the real problem is that you want to link two session id data when there is nothing that link them (I mean in a reliable way).
alternative would be to use the IP address (but this is tricky as normally will be the database server IP address), or the OS user (but this will not warrant that TOM is using Jacques' machine).

Let's us see other's people input, I have a lot of interest too.
0
 

Author Comment

by:ralph_rea
ID: 34415903
NO answer
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now