Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Can I use Fine Grained Auditing (FGA)?

Posted on 2009-07-15
13
Medium Priority
?
1,734 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 11

Accepted Solution

by:
Andytw earned 2000 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
 
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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

972 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