Skip to Main Content
  • Questions
  • Database Trigger Versus Fine Grained Access

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mohamed.

Asked: February 16, 2005 - 9:48 am UTC

Last updated: February 16, 2005 - 2:04 pm UTC

Version: 9i.2

Viewed 1000+ times

You Asked

First :
In order to establish a complete auditing module in our application there are many ways .. Really i am confused .

The auditing policy that use dba_fga_audit_trail has one main disadvantage ... That i cannot monitor the affected rows in the base table ...

suppose there is an audit policy on table emp :

connect scott/tiger
connected

update emp
set salary = salary +(salary * 0.05 )
where empid = 10 ;

In this case the search criteria(empid = 10 ) is useful for auditing

Here i can know that the user updated the salary for employee number 10 to the new value.

but suppose another where condition :

where deptid = 10 ; (not the primary key)
In this moment there are 5 employees belongs to department 10 but after two days the employees that belongs to department 10 are changed may be less/more or deleted(as an example) .

How can you track the affected rows in this case .

Now i use the two methods :
Fine Grained Access for monitoring user actions only without rows in many parts of the application.
Database Trigger for monitoring affected rows in some parts of the application .
Using two ways in auditing make me so confused ...
I am asking how can i use fine grained access to achieve the two objects (Monitoring user actions + affected rows) ?????

Secondly
I am trying to install Oracle 10g application server for my first trial .
There was a message that require at least 1024 MB Ram in order to begin setup .

Is there any way to complete setup without this condition where i have only 768 MB RAM for testing only or i must change RAM ?? Thanks tom


and Tom said...

instead of being confused by the options -- why not list your specific requirments?


If you have the requirement to know "user X updated the EMP table" the right answer is "audit command"

If you have a requirment to know the "sql that user X used to update the EMP table", a trigger is correct (using ora_sql_txt to capture it) or in 10g, dbms_fga can capture modifications as well as select queries.


If you have a reqirement to row by row capture the before/after values to know that user X changed the row from "this to that", perhaps workspace management is right for you:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4529781014729 http://docs.oracle.com/cd/B10501_01/appdev.920/a96628/toc.htm <code>

or you do it yourself via triggers and "a shadow table that looks like the original table but you save the :old values" into it.




You cannot use "audit" (event auditing) to do what "dbms_fga" (statement auditing) does and you cannot use "dbms_fga" to do what "dbms_wm" (versioning of data including date/time/who did the operation) does...




Not sure if the install will fly or not, I have never attempted it without a machine "at spec"


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.