Solved

Can I use Fine Grained Auditing (FGA)?

Posted on 2009-07-15
13
1,518 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 65
Oracle - How to analyze data using DATE COLUMN? 7 74
PL/SQL Search for multiple strings 5 40
Bash Script to Analyze Oracle Schemas 11 84
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

896 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

18 Experts available now in Live!

Get 1:1 Help Now