Can I use Fine Grained Auditing (FGA)?

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!
ralph_reaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndytwCommented:
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
ralph_reaAuthor Commented:
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
ralph_reaAuthor Commented:
or also:
AUDIT_LOG table:
USER_NAME.......EVENT..........TABLE...................DATA_EVENT
SAM..................INSERT..........MY_TABLE.................07/15/2009
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

AndytwCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ralph_reaAuthor Commented:
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
AndytwCommented:
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
ralph_reaAuthor Commented:
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
AndytwCommented:
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
ralph_reaAuthor Commented:
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
AndytwCommented:
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
AndytwCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.