Solved

Can I use Fine Grained Auditing (FGA)?

Posted on 2009-07-15
13
1,494 Views
Last Modified: 2013-12-19
Hi,
I'd like to use Fine Grained Auditing (FGA) on my Oracle database.

My Oracle version is:
select *
from V$VERSION;

BANNER

--------------------------------------------------------------------------------
Oracle Database 10g Release 10.2.0.1.0 -
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1

I know that FGA is available only in Oracle Enterprise Edition.

Can I use FGA also in Oracle Standard Edition iserting new packages.

Thanks in advance!
0
Comment
Question by:ralph_rea
  • 6
  • 5
13 Comments
 
LVL 11

Expert Comment

by:Andytw
ID: 24857951
Unfortunately the "Fine-grained auditing" feature/option is *only* available in Oracle Enterprise Ediiton; you can't use this if you have Oracle Standard Edition.

What do want to do - perhaps normal auditing could meet your requirements?
0
 

Author Comment

by:ralph_rea
ID: 24858767
This is my problem:
I've my application that connect to Oracle via ODBC. This application use an oracle schema with owner TOM. In this schema there is a table TAB_USERS with username and password:
CREATE TABLE TAB_USERS
(
USER_PWD VARCHAR2(64 BYTE),
USER_NAME VARCHAR2(64 BYTE)
)

When I connect to oracle from my application the user is TOM.

Users of the TAB_USERS table  are not Oracle users, but I'd like to audit DML statement on other tables.

For example:
USER_NAME..........USER_PWD
SAM........................LONDON

When I connect in my application with user SAM and insert a new record in table MY_TABLE, I'd like to get a new record in AUDIT_LOG table.

AUDIT_LOG table:
USER_NAME.......EVENT........DATA_EVENT
SAM..................INSERT............07/15/2009

Have you any idea?
0
 

Author Comment

by:ralph_rea
ID: 24858786
or also:
AUDIT_LOG table:
USER_NAME.......EVENT..........TABLE...................DATA_EVENT
SAM..................INSERT..........MY_TABLE.................07/15/2009
0
 
LVL 11

Accepted Solution

by:
Andytw earned 500 total points
ID: 24867471
Since you want to audit your application users, then you can't use standard Oracle auditing.  Instead you need to roll your own, using triggers.  

Using this approach, you need to create a statement-level trigger on each of the tables that you would like to audit.  This is ok, providing you don't have 100s of tables to audit, and as long as you don't want to store any more information in your audit log (e.g. the statement that fired the trigger)
Using the attached setup, here's a quick example ...
SQL> insert into test values(1,2);
 

1 row created.
 

SQL> update test set j=4;
 

1 row updated.
 

SQL> delete from test;
 

1 row deleted.
 

SQL>

SQL> select * from audit_log;
 

USER_NAME                      EVENT      TABLE_NAME                     EVENT_DAT

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

ANDY                           INSERT     TEST                           16-JUL-09

ANDY                           UPDATE     TEST                           16-JUL-09

ANDY                           DELETE     TEST                           16-JUL-09
 

SQL>

Open in new window

audit-trigger-setup.txt
0
 

Author Comment

by:ralph_rea
ID: 24871466
Andytw,
When I connect to oracle from my application via ODBC the Oracle user is always TOM
before of create your trigger I must retrieve application user from TAB_USERS table.
The users into TAB_USERS table aren't Oracle users.
How can I retrieve application user from TAB_USERS table??
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.

 
LVL 11

Expert Comment

by:Andytw
ID: 24871685
Like I said in the comment in the file  "audit-trigger-setup.txt", you will need to replace "USER", which I used in my example with your application user.

I understand that TAB_USERS contains a list of application users.  But that's all it is, a list of users.  You need to find out which of those users is currently logged on (in the Oracle session that you are running).  If your application is doing it's own authentication, user management etc. then the absolute *only* way you can get this answer is to ask your application.    

0
 

Author Comment

by:ralph_rea
ID: 24873934
I thought that with FGA I could monitor the users of TAB_USERS table:

begin
dbms_fga.add_policy (
object_schema=>'TOM',
object_name=>'TAB_USERS',
policy_name=>'TAB_USERS'
);
end;

CREATE OR REPLACE VIEW DBA_FGA_AUDIT_TRAIL
(SESSIONID, TIMESTAMP#, DBUID, OSUID, OSHST,
CLIENTID, EXTID, OBJ$SCHEMA, OBJ$NAME, POLICYNAME,
SCN, SQLTEXT, LSQLTEXT, SQLBIND, COMMENT$TEXT,
PLHOL, STMT_TYPE, NTIMESTAMP#, PROXY$SID, USER$GUID,
INSTANCE#, PROCESS#, XID, AUDITID, STATEMENT,
ENTRYID, DBID, LSQLBIND)
AS
select "SESSIONID","TIMESTAMP#","DBUID","OSUID","OSHST","CLIENTID","EXTID","OBJ$SCHEMA","OBJ$NAME","POLICYNAME","SCN",
"SQLTEXT","LSQLTEXT","SQLBIND","COMMENT$TEXT","PLHOL","STMT_TYPE","NTIMESTAMP#","PROXY$SID",
"USER$GUID","INSTANCE#","PROCESS#","XID","AUDITID","STATEMENT","ENTRYID","DBID","LSQLBIND"
from sys.fga_log$;

0
 
LVL 11

Expert Comment

by:Andytw
ID: 24874721
No, you cannot use DBMS_FGA - that is FGA.  And, unless you are going to pay the license upgrade for Oracle Enterprise addition it is off-limits.  

However, even if you were able to use FGA, why do you want to use it?  What are you hoping FGA will do for you here?  

>>with FGA I could monitor the users of TAB_USERS table
What do you mean by this - what's "users of TAB_USERS"?  The policy that you define in your post above would just audit any SELECT statements issued against TAB_USERS - it does nothing about the application users that happen to be .  I don't see how this helps you with your original question.  

The only thing I can think of is that you are  expecting this to help you determine which application user is logged on (since your application will perform a SELECT against TAB_USERS?? ) If so, I think you are taking a wrong approach trying to use FGA for this purpose.
0
 

Author Comment

by:ralph_rea
ID: 24877592
YES I want know which application user is logged on
YES my application will perform a SELECT against TAB_USERS.

Which is, in your opinion, the best approach for this purpose?
0
 
LVL 11

Assisted Solution

by:Andytw
Andytw earned 500 total points
ID: 24910288
As I understand it, your application does it's own authentication. How it does is unimportant, but what does matter is that these application users are unknown to the database.  

You would like to audit DML that the application users issue against certain tables.  While this can be done by creating triggers for the tables in question, the issue is that the application users are unknown to the database - so how do you audit?.  

Well I would recommend using "Client identifiers".  Basically, CLIENT_IDENTIFIER is a predefined attribute of an application context, USERENV.  It can be set by the application, and be used to keep track of the application user in the session.  Use the DBMS_SESSION package to set and clear the CLIENT_IDENTIFIER:
# SET_IDENTIFIER
# CLEAR_IDENTIFIER

Then, the CLIENT_IDENTIFIER can be retrieved by the following function SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER').
 
Check out the Oracle Database Security Guide docs, and you should see that this is exactly what you need.
"Support for Application User Models by Using Client Identifiers"
http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/apdvprxy.htm#DBSEG15003

You need to change your app code that authenticates application users, so that it sets the CLIENT_IDENTIFIER to the user.  e.g. if user is 'SAM', then:
  DBMS_SESSION.SET_IDENTIFIER('SAM');

In your trigger, you can then retrieve 'SAM' and insert that value into your audit table ...
INSERT INTO AUDIT_LOG (user_name, ...) VALUES ( SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'), ... );

Open in new window

0
 
LVL 11

Expert Comment

by:Andytw
ID: 25109834
I think that http:#24857951 answered the original question.

However, the author's real problem came to light after further posts.  I think that posts http:#24867471 and http:#24910288 solve the author's problem and accepted as solutions.
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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

759 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

22 Experts available now in Live!

Get 1:1 Help Now