Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1784
  • Last Modified:

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!
0
ralph_rea
Asked:
ralph_rea
  • 6
  • 5
2 Solutions
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now