Still obtaining false positive
wneu, February 12, 2003 - 1:44 pm UTC
This thread was very enlightening, however, although I have the statistics on my table (updated recently) and believe I am using the COST based optimizer, I still have unexpected statements being reported to the DBA_FGA_AUDIT_TRAIL table. Perhaps you could assist me in finding my mistake.
These are the options employed on my database:
optimizer_features_enable string 9.2.0
optimizer_mode string CHOOSE
This is the table:
Name Null? Type
------------------------------- -------- -------------
DX_ACCOUNT_ID NOT NULL NUMBER(10)
LAB_ACCOUNT_NAME NOT NULL VARCHAR2(100)
PROVIDER_ID NOT NULL NUMBER(10)
CREATED_BY VARCHAR2(30)
CREATED_DATE DATE
This is the policy
BEGIN
dbms_fga.add_policy
( object_schema => 'DX_OWNER',
object_name => 'DX_ACCOUNT_LAB',
policy_name => 'GET_DX_ACCOUNT_LAB',
audit_condition => 'dx_account_id > 0',
audit_column => 'LAB_ACCOUNT_NAME' );
END;
/
Here are the select statements I did not expect to appear in the DBA_FGA_AUDIT_TRAIL table but they did:
select count(*) from dx_account_lab;
select dx_account_id, provider_id from dx_account_lab;
and finally, here are the statistics on my table:
OWNER: DX_OWNER
TABLE_NAME: DX_ACCOUNT_LAB
TABLESPACE_NAME: MDDTA_DATA
NUM_ROWS: 12
BLOCKS: 1
EMPTY_BLOCKS: 62
AVG_SPACE: 7670
CHAIN_CNT: 0
AVG_ROW_LEN: 32
AVG_SPACE_FREELIST_BLOCKS: 7670
DEGREE: 1
INSTANCES: 1
CACHE: N
SAMPLE_SIZE: 12
LAST_ANALYZED: 12-FEB-03
ROW_MOVEMENT: DISABLED
GLOBAL_STATS: NO
USER_STATS: NO
SKIP_CORRUPT: DISABLED
DEPENDENCIES: DISABLED
Thank you,
-wn
February 12, 2003 - 3:55 pm UTC
I would need method to reproduce. Have you verified the timestamps to make sure they did not come from some earlier iteration of your testing? I cannot reproduce this at all -- will need some help there.
should not generate any false positives as the column needed isn't even there. You aren't using FGAC too are you with a predicate on LAB_ACCOUNT_NAME??
ops$tkyte@ORA920> create table dx_account_lab
2 ( DX_ACCOUNT_ID NUMBER(10),
3 LAB_ACCOUNT_NAME VARCHAR2(100),
4 PROVIDER_ID NUMBER(10),
5 CREATED_BY VARCHAR2(30),
6 CREATED_DATE DATE
7 )
8 /
Table created.
ops$tkyte@ORA920> insert into dx_account_lab values ( 10, 'x', 10, 'x', sysdate );
1 row created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> analyze table dx_account_lab compute statistics;
Table analyzed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> BEGIN
2 dbms_fga.add_policy
3 ( object_schema => user,
4 object_name => 'DX_ACCOUNT_LAB',
5 policy_name => 'GET_DX_ACCOUNT_LAB',
6 audit_condition => 'dx_account_id > 0',
7 audit_column => 'LAB_ACCOUNT_NAME' );
8 END;
9 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> set autotrace on explain
ops$tkyte@ORA920> select count(*) from dx_account_lab cbo;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DX_ACCOUNT_LAB' (Cost=2 Card=1)
ops$tkyte@ORA920> select dx_account_id, provider_id from dx_account_lab cbo;
DX_ACCOUNT_ID PROVIDER_ID
------------- -----------
10 10
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)
1 0 TABLE ACCESS (FULL) OF 'DX_ACCOUNT_LAB' (Cost=2 Card=1 Bytes=4)
ops$tkyte@ORA920> alter session set optimizer_goal = rule;
Session altered.
ops$tkyte@ORA920> select count(*) from dx_account_lab rbo;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DX_ACCOUNT_LAB'
ops$tkyte@ORA920> select dx_account_id, provider_id from dx_account_lab rbo;
DX_ACCOUNT_ID PROVIDER_ID
------------- -----------
10 10
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (FULL) OF 'DX_ACCOUNT_LAB'
ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from dba_fga_audit_trail;
no rows selected
Previous entry review should have been 5
wneu, February 12, 2003 - 1:48 pm UTC
Hmmm... interesting
wneu, February 13, 2003 - 4:03 pm UTC
I am using FGAC, but, the predicate is on DX_ACCOUNT_ID, not the LAB_ACCOUNT_NAME column.
Based on the Role I am using, the predicate checks to see what the Context has for DX_ACCOUNT_ID and uses it to limit the rows returned where dx_account_id = syscontext('DxHubApp', 'DxAccount'). The Role for this user is 1. No other condition is added.
Interestingly, if I disable the FGAC policies, then the right statements are audited and there are no false positives. When I re-enable the policies, I am able to get 'false positive' results again.
Here is the security policy I have on this table (it only has a select predicate, no others):
FUNCTION on_dx_account_lab (p_schema IN VARCHAR2,
p_object IN VARCHAR2 )
RETURN VARCHAR2
IS
BEGIN
IF (g_account_lab_pred IS NULL) THEN
-- for normal/admin users, they can see their dx_account
IF (sys_context(g_app_ctx, 'RoleNumber') IN ('1', '2'))
THEN
g_account_lab_pred := ' dx_account_id =
TO_NUMBER (sys_context('''||
g_app_ctx||
''', ''DXACCOUNT''))';
-- for super users, they can see all rows
ELSIF (sys_context (g_app_ctx, 'RoleNumber') = '3')
THEN
g_account_lab_pred := ' 1=1';
-- if no role is set, they can see nothing
ELSE
g_account_lab_pred := ' 1=0';
END IF;
END IF;
RETURN g_account_lab_pred;
END on_dx_account_lab;
I also checked the timestamp on the rows to make certain that I was looking at the right thing.
Thanks again for your time.
February 13, 2003 - 5:32 pm UTC
I believe the way they rewrite
select x from t
if there is a predicate on T would be
select x from ( select * from t where .... );
so, you are getting this for that reason. You are in fact "seeing" the column you are not looking at due to the implementation whenever they have role 1, 2, or 3..
Yup, just confirmed it -- the fgac is triggering it....
A reader, May 03, 2003 - 2:12 pm UTC
I've found no other way to access dba_fga_audit_trail under DEMO's account then creating that "print_table" procedure as SYS and grant execute to DEMO. SELECT ANY TABLE didn't suffice. did I miss something?
May 03, 2003 - 7:39 pm UTC
you would have to grant directly on it. Most SYS objects are not visible without direct grants (security thing in 9i and up)
DO NOT, repeat DO NOT create things as sys, just don't even go there.
A reader, May 04, 2003 - 9:24 am UTC
just to get it right: system privileges such as SELECT ANY don't suffice (anymore) in 9i and up? explicit object privileges are required?
How do I capture the values in query as well?
Charles Leung, June 25, 2003 - 11:56 pm UTC
Hi Tom,
I found that Fine-Grained Audit captures only the SCN. I'm required to log the queried values also. How would I archive this?
Besides, how can I log other info (such as module name) also? I can think of dbms_application_info, but I have no idea how to put these things together.
June 26, 2003 - 9:05 am UTC
You are kidding ? You have to audit the actual values? Just capturing the query and the inputs to the query -- and knowing that you can reasonably use flashback in a period of time you configure (a REASONABLE period of time) is not enough?
I have some disk drives to sell to you!
Sorry, but I cannot fathom the system where every result set would have to be written out to persistent storage, seems a bit over the wall? Do you have a real world business case for this -- do the people driving your requirements understand what they have said? the implications?
To get the 'module', the application must set the module. then you can use v$session to see the action/module and client info.
What is the View to show the policy
A reader, August 05, 2003 - 12:36 pm UTC
I apologize for picking up a 10-month long thread; but I coudn't find the information anywhere else. What is the data dictionary view that shows the FGA policies set on the tables? DBA_POLICIES shows only VPD policies, not FGA ones.
August 05, 2003 - 1:26 pm UTC
select * from DBA_AUDIT_POLICIES;
He may not be kidding
David Gibbs, November 12, 2003 - 2:16 pm UTC
Tom
A couple of posts earlier you replied:
"You are kidding ? You have to audit the actual values? Just capturing the
query and the inputs to the query -- and knowing that you can reasonably use
flashback in a period of time you configure (a REASONABLE period of time) is not
enough?
I have some disk drives to sell to you!
Sorry, but I cannot fathom the system where every result set would have to be
written out to persistent storage, seems a bit over the wall? Do you have a
real world business case for this -- do the people driving your requirements
understand what they have said? the implications? "
A real world example is in the healthcare field. HIPAA and PIPEDA (in Canada) require logging of all activity against Personal Information (PI) in the database. This means we require an audit trail of who looked at or updated PI. How would you recommend doing this (since you are in the field)? We are using 8.1.7 so would like you suggestions on that version too (I'm anticipating that the best solution will require us to migrate to a newer version).
November 12, 2003 - 4:50 pm UTC
You need an audit trail that shows what people looked at personal information. Using the
a) question
b) inputs to the question
you have that. (i know about HIPAA, i work in OGEH -- Oracle Government Education and Healthcare).
Or, you expose all but the persons identifier (anonymize the data) for general SELECTS and you wrap all "personalized accesses (small result sets, a person at a time)" in procedures that audit whatever they want (eg: you are NOT using SQL in the application, you call a procedure, procedure audits whatever it needs).
this is not auditing EVERY bit of data coming out, this is excessively selective auditing when a specific process to return data about a specific person is invoked. very different -- NO ad-hoc, all very controled. the only ad-hoc is against the anonymized data.
problem with dba_fga_audit_trail
Ashwin, May 12, 2004 - 10:26 pm UTC
I am facing a problem with dba_fga_audit_trail
I have a package which selects from this table.
When I try to compile the package it gives an error
saying "Insufficient privilges"
If I run the same Select statement in SQLPLUS, it gives the output. I have got select, insert, update, delete permissions on this table.
Any clues as to what is happening
Please help !!
Thanks
Ashwin N.
How do I recreate the data accessed?
mandy, July 09, 2004 - 2:43 pm UTC
Greetings Tom,
Great site. Very helpful.
FGA gives me:
SQL_TEXT : select empno, ename from emp where sal < :sal
SQL_BIND : #1(4):1700
I want to use the FGA to create a command file to re-run a selected set of queries to analyze fragment of emp accessed by a user or a set of users within a certain period.
For example in the above case I want to store the actual query as follows:
select empno, ename from emp where sal < 1700;
Any idea how to do this? Sorry if you have already answered this. I didn't find it in my search.
Thank you in advance.
July 09, 2004 - 4:13 pm UTC
you'd have to do a bit of parsing and merging of text on your own, we don't store it that way -- with the binds replaced with literals.
sorry...
Auditing more than one column
Agung, July 17, 2004 - 9:26 pm UTC
Hi Tom,
Just a quick second..
Suppose I want to audit "sal" and "dept_no" columns so that everytime users retrieve one of those columns FGA will log them.
Can a policy audit more than one column (but not all columns in the table)? Or
Do I have to create a policy for each column I want to audit?
Thanks so much
July 18, 2004 - 12:16 pm UTC
in 9i, two policies, in 10g, one policy is needed.
10g adds:
Supports for DML statements :
A. INSERT
B. UPDATE
C. DELETE
merge is covered by having an insert/update policy
Support for more than one relevant column in an FGA policy :
if any one of the audit columns is present in the DML statement, it is
audited by default.
An option is provided to audit based on whether ANY or ALL of the
relevant columns are used in the statement:
audit_column_opts => DBMS_FGA.ALL_COLUMNS / DBMS_FGA.ANY_COLUMNS
Audit trail writes the SQL text and SQL bind information to LOBs.
FGA and mviews
Cid, September 21, 2004 - 3:36 pm UTC
Tom,
Is it possible to audit queries against a master table of a mview without stored procedures and predicates ?
I have the following:
SQL> CREATE TABLE DBA02.DBA02_FGA
2 (COL1 NUMBER
3 ,COL2 DATE
4 ,COL3 VARCHAR2(50));
Table created.
SQL> INSERT INTO DBA02.DBA02_FGA
2 SELECT OBJECT_ID,CREATED,SUBSTR(OBJECT_NAME,1,50)
3 FROM DBA_OBJECTS
4 WHERE OBJECT_ID IS NOT NULL;
6781 rows created.
SQL> BEGIN
2 DBMS_FGA.ADD_POLICY(
3 OBJECT_SCHEMA=> 'DBA02',
4 OBJECT_NAME=> 'DBA02_FGA',
5 POLICY_NAME=> 'FGA_POLICY',
6 AUDIT_CONDITION=> '1=1',
7 ENABLE=>TRUE);
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> CREATE MATERIALIZED VIEW DBA02.DBA02_FGA_MV
2 BUILD IMMEDIATE
3 REFRESH START WITH SYSDATE
4 NEXT SYSDATE+5/1440
5 ENABLE QUERY REWRITE
6 AS SELECT *
7 FROM DBA02.DBA02_FGA
8 WHERE COL1 > 10000;
FROM DBA02.DBA02_FGA
*
ERROR at line 7:
ORA-00604: error occurred at recursive SQL level 1
ORA-28133: full table access is restricted by fine-grained security
How can I skip audit during refresh ?
Tks a lot!
September 21, 2004 - 4:05 pm UTC
contact support to discuss:
Hdr: 3031005
Abstract: ORA-28133 CREATING MATERIALIZED VIEW ON TABLE WITH FINE GRAINED AUDIT POLICY
fixed in 10.1 (but patches may exist for your version on your platform)
Multiple table
Skip, October 19, 2004 - 12:47 pm UTC
Given what I have read here and in oracle tech docs, is it true that FGA is only for one table at a time? What if I have over 3000 tables on one database alone that has columns I need checked. In other words I need to know if anyone is accessing specific columns (like your example salary) in any table. Is there a way to specify auditing whenever the column salary is accessed without creating a policy for each of the 3000 tables? What if I have a new table added with one or more of these columns added? Would I have to create a new policy each time?
October 19, 2004 - 1:08 pm UTC
you have the same column in 3000 tables?
FGA is done at the object (table/view level). If you have salary in 3000 tables, you have bigger problems (or even in multiple tables, more than one).
Multiple tables
Skip, October 19, 2004 - 2:30 pm UTC
I agree that it is a large number of tables, however salary was just an example. I do have things like account number and other infomation that is private and should only be accessed by our applications and not the individual users on the database. I have them locked down with permissions but I need to know when someone other than the application tries to access it.
I could care less about most of the columns on the tables because that infomation alone tells the user nothing. However, any information that allows you to tie one record to another must be kept hidden. By customer requirements, I have to keep 3 years worth of data available at all times. So with weekly updates, normal processing tables, analytic tables and various other tables I do have over 3000 tables that need to be watched. Since FGA is object level is there another way to audit when someone tries to access restricted columns?
October 19, 2004 - 2:47 pm UTC
if you have them locked down -- they would get a "failed sql syntax parse" since they would not even be able to query them.
However, if you want to use FGA, you would have to add the policy per table (sort of like with auditing in general). You could easily automate this by writing sql from the data dictionary (eg: a query could easily generate the dbms_fga call for all table/columns named "X")
Audit based on v$session
Rory, October 21, 2004 - 12:38 am UTC
Hi Tom,
We have a DTR system. It runs on 9ias for the front end. Employees time in and time out using the browser in the terminal. This time in or time out would then update the dtr table for their respective records. The problem is, how can I audit if the employee updates the dtr table without using the terminal, perhaps using toad or sqlplus in windows passing to the back so that they may be able to put their desired time in or time out. sort of cheating. would there be a way of auditing the update on the table if the program in v$session is toad or sqlplus or anything different from f90runtime. Hope you get the picture. thanks.
October 21, 2004 - 6:47 am UTC
you could use proxy authetication (search for that on otn.oracle.com in the documentation section). you would use a stored procedure that would ensure the user was connected via the proxy (something they would not be able to do from anything but your application)
you could grant access to the tables via a ROLE and make that ROLE be an application role, only enabled if they run a certain procedure. this procedure could do something like inspect the sys_context( 'userenv', 'ip_address' ) and verify the connect was coming from the middle tier machines (to which end users would not have access) before enabling role. If no role, no update.
You could use stored procedures and not let end users have access to the tables themselves. These stored procedures could do whatever checks you wanted (eg: the ip_address check).
Auditing
Rory, October 21, 2004 - 11:24 pm UTC
Thanks Tom. Is the solution you gave me applicable if all users use the "dtr" user as the login in the database. All users user the same login. When they time in in the dtr system, all they do is input their id and password. the dtr apps is already connected to the database via "dtr" user. when they time in, dtr user in the database updates the table for their time in or time out. can the role be disabled if i was login in the database as dtr user himself? I mean, wont I be able to update the table even I was the owner coz I connected via sqlplus or toad and not via apps? thanks again.
October 22, 2004 - 4:02 pm UTC
i gave you multiple 'possible things to consider', not sure which one you mean.
did you see the "ip address" thing there. do you let your users actually log into the machines running your application servers? you have a finite number of app servers, put their ip addresses into a table, the stored procedure that enables the role would ask the question -- "so, is this connection coming from a trusted host - if yes, ENABLE ROLE, else tell them what a loser they are and don't enable the role"
FGA Trails to another table
Naveen, October 29, 2004 - 7:05 am UTC
Hi..
Can i force FGA Trails to go to another user table.?
I mean to ask, i want the FGA trails to be in a table that i define, as a user table and access the records from my applicaitons.
October 29, 2004 - 8:49 am UTC
No, auditing is a system function -- much like "create table is". audting goes where auditing goes.
FGA on Report DB
Rich, March 22, 2005 - 1:16 pm UTC
Hi Tom,
There is a requirement to create a "Reports DB" which is
essentially a copy of the Prod DB to be used for Ad-Hoc
and Analytical Reporting.
I can think of two solutions:
1) Create Logical Standy DB of the Prod DB for the
Reports DB
2) Use replication to refresh the Reports DB
I would prefer to use option #1 since it would be easier
to setup and all security policies would be in effect in
the Logical Stby (no need to recreate them). FGA is
enabled in the Prod DB (I assume it will also be enabled
in the Reports DB).
My question is: How should we manage the FGA audit trail
in the Reports DB? Users will be reading off this for
their reports ... hence, FGA audit trail between Prod and
Reports DB will contain different information.
I would appreciate your thoughts on this. Thank you very
much for this site (can't thank you enough).
March 22, 2005 - 6:02 pm UTC
3) streams.....
but -- what are your needs with regards to managing the audit trail? hard to say what to do, unless I know the goals you need to accomplish?
they are just tables, you can query them both.
FGA at Reports DB
Rich, March 23, 2005 - 7:09 am UTC
Sorry, I guess it wasn't clear what I wanted to ask.
Since the Reports DB will be an image of the Prod DB, we
want all security settings (VPD, FGA, etc) to be
exactly like Prod. What would be the best way to manage
the Reports DB in terms of replicating/updating the
security settings from Prod DB? Creating scripts?
I appreciate your feedback. Thank you again.
March 23, 2005 - 9:06 am UTC
easiest would be not to have a separate instance. (at all)
but logical standby would make sense I suppose -- or streams in a controlled environment.
FGA on Reports DB
Rich, March 23, 2005 - 9:39 am UTC
In the case of a Logical Stby, will changes on the FGA
audit trail on the Prod DB be propagated to the Reports DB?
I guess we want the FGA audit trail of Prod and Reports DB
to be managed separately since they will contain
different entries in the audit trail.
Thank you.
March 23, 2005 - 6:01 pm UTC
you pick the objects to be "stood by", so you have the ability to pick and choose the tables that are copied.
How is FGA in terms of resource consumption?
Charlie B, October 05, 2005 - 11:33 am UTC
Tom -
Thanks for sharing so many insights with all of us. We couldn't do it without people like yourself!
My system's having an audit requirement imposed from outside the company (by the banking industry, specifically). Among other things we need to audit selects so I'm looking at FGA. But I'm concerned because our db host is close to being maxed out from CPU usage. Do you or your readers have any information on FGA's resource consumption? Is it light, like "timed_statistics = true", or should I be worrying?
Our current need would be to audit access to one table only. My guess is that a small percentage of transactions hit that table: probably less than 1%. But I can't find anything on how big a footprint FGA has. The Security Guide doesn't say much, except that it's more efficient than a trigger.
Ora 9204, 64-bit on SunOS 7 (I'm not the sysadmin...). We run CBO and gather stats weekly.
Thanks!
October 05, 2005 - 11:51 am UTC
it will not add significant overhead, but you need to TEST - if you over execute SQL, it'll add a small amount to A LOT OF THINGS (adds up)
test test test - you can measure this precisely in your test environment.
Thanks!
Charlie B., October 05, 2005 - 3:48 pm UTC
Test - absolutely! Thanks for the quick response. Now we just need to figure out how to volume test it. ;-)
October 05, 2005 - 8:18 pm UTC
restore your database to test and run a batch against it?
you have restored your database before (assumption) so you *must* have the space to do it :)
Moving fga_log$ to another tablespace?
Rich, October 06, 2005 - 11:37 am UTC
Hi Tom,
What would be your recommendation for managing the space of the FGA audit trail?
Is it possible to move the fga_log$ table to another tablespace? If yes, I assume that we can create partitions on the table in order to manage adding/dropping partitions.
Thank you.
October 06, 2005 - 12:16 pm UTC
In order to move any of the dictionary tables, you'll need to work with support.
PL-SQL Query
K.Praveen, October 07, 2005 - 2:53 am UTC
I need a Pl-Sql Procedure through which I can select no of columns at run time from a Particular Table from the Dictionary
Null transactionid in dba_fga_audit_trail
Sudhir, April 22, 2006 - 3:31 am UTC
Hi Tom,
I am using FGA (oracle 10g) for getting transactionwise sql log (Select/DML).
The problem I am facing is, many entries in dba_fga_audit_trail contain Transaction id as Null.
What does it mean to have null as transactionid?
Also for such queries can I assume that same SCN belong to same transaction and that with different SCN belong to different?
Thanks in advance,
Sudhir.
FGA and online reorgs
Dave, June 16, 2006 - 3:53 pm UTC
I just discovered a limitation of FGA is that the table cannot be reorged if a policy exists on it. Disabling the policy won't work, you must drop it. Not sure how to balance the FGA with auditing tables while they are being reorged. Any ideas?
SQL> exec dbms_fga.add_policy('SCOTT', 'NORMAN', 'ALL_NORMAN');
PL/SQL procedure successfully completed.
SQL> exec dbms_redefinition.can_redef_table('SCOTT', 'NORMAN', dbms_redefinition.cons_use_rowid);
BEGIN dbms_redefinition.can_redef_table('SCOTT', 'NORMAN', dbms_redefinition.cons_use_rowid); END;
*
ERROR at line 1:
ORA-12090: cannot online redefine table "SCOTT"."NORMAN"
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 1
SQL> exec dbms_fga.disable_policy('SCOTT', 'NORMAN', 'ALL_NORMAN');
PL/SQL procedure successfully completed.
SQL> exec dbms_redefinition.can_redef_table('SCOTT', 'NORMAN', dbms_redefinition.cons_use_rowid);
BEGIN dbms_redefinition.can_redef_table('SCOTT', 'NORMAN', dbms_redefinition.cons_use_rowid); END;
*
ERROR at line 1:
ORA-12090: cannot online redefine table "SCOTT"."NORMAN"
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 1
SQL> exec dbms_fga.drop_policy('SCOTT', 'NORMAN', 'ALL_NORMAN');
PL/SQL procedure successfully completed.
SQL> exec dbms_redefinition.can_redef_table('SCOTT', 'NORMAN', dbms_redefinition.cons_use_rowid);
PL/SQL procedure successfully completed.
Migrate FGA auditing information
A reader, June 22, 2006 - 4:42 pm UTC
Tom,
We recently migrated our database from 9i to 10g. Is there a way to migrate the auditing information? We do not want to lose the auditing information that we had in 9i.
Thanks
June 23, 2006 - 9:47 am UTC
if you did it right, it would still be there - right in aud$ (upgrade)
If you build a new database, load new data into it, it is a "new database", You can contact support and see if they'll support you unloading and reloading aud$ but I seriously doubt it. You'll likely have to move it over into your own database table.
DBMS_FGA will not log audits on INSERTS
Luis Morales, September 05, 2006 - 5:21 pm UTC
Hi Tom,
Since 10g, DBMS_FGA has been extended to log audits on all DML statements in a centralized fashion, almost replacing the auditing triggers we had to write in previous releases. However, INSERT sentences on some tables are NOT actually audited. Metalink Note 352355 points out that this is due to the check constraints on these tables. The note's workaround is to disable the check constraints.
Is there another workaround that will not sacrifice both features?
Thank you,
Luis Morales
ConsiteNicaragua.com
September 05, 2006 - 5:36 pm UTC
be more specific with the note id please
DBMS_FGA will not log audits on INSERTS
A reader, September 05, 2006 - 5:55 pm UTC
Sorry Tom,
Metalink Note Id is 352355.1.
A fix on mentioned Bug 4636523 is yet to be produced. What can we do while we wait?
Thanks,
Luis
September 06, 2006 - 7:44 am UTC
contact support for this, the note says "as of the writing of this note, the patch was not yet available", but that was a while ago.
which USER DEFINED objects must be protected?
Dawar, November 18, 2006 - 6:54 pm UTC
when protecting fine grained auditing (FGA), which USER DEFINED objects must be protected?
cheers,
Dawar
November 20, 2006 - 2:33 am UTC
whichever you deem necessary?????
eh?
how to capture full length of query in FGA
J, January 31, 2007 - 1:15 am UTC
Hi Tom,
My user is requesting to FGA on tables, also they want to capture the query against those table. however, the sqltext column in audit table is varchar2(2000), which is not sufficient at all. I have user query with 10 pages long. Anyway to capture full length of query with FGA?
if query against 4 tables which are all under FGA; how many entries will be shown in audit log table? 4 or 1?
I am in DB 10.2.0.2 version. Thanks!
January 31, 2007 - 10:08 am UTC
You may query sys.fga_log$ upon which dba_fga_audit_trail is built. That view substrings the clob column:
obj$schema, obj$name, policyname, scn, to_nchar(substr(lsqltext,1,2000)),
to_nchar(substr(lsqlbind,1,2000)), comment$text,
DECODE(stmt_type,
1, 'SELECT', 2, 'INSERT', 4, 'UPDATE', 8, 'DELETE', 'INVALID'),
FROM_TZ(ntimestamp#,'00:00') AT LOCAL,
proxy$sid, user$guid, instance#, process#,
xid, statement, entryid
from sys.fga_log$
so if you create your own view of fga_log$ or query it directly (creating a view would be good) you can have at it.
Logging Rollback.
Sujit, March 27, 2007 - 4:56 pm UTC
If we rollback a dml , the records from the audit log table does not go off. Is there any way to find out from the log , that a rollback for a statement occurs.
March 28, 2007 - 11:26 am UTC
what log?? what does "does not go off" mean.
Logging Rollback
Sujit, March 28, 2007 - 4:39 pm UTC
Let me explain in detail:
Say I have added policy to to Fine Grained Auditing on table T for UPDATE queries.
Now each time I execute an update statement a record will go to fga_log$. The record in fga_log$ persists even we rollback the update for which this log is been created.
My question , is there any way to find out that there was a rollback issued aginst this update record which is logged in fga_log$.
I know we can get this info by traversing through flashback_transaction_query and using scn , just like to know if there is any easy way to get the same.
March 30, 2007 - 11:39 am UTC
not that I know of.
fga
reader, April 17, 2007 - 12:06 pm UTC
we have about 400 tables that are heavily used for oltp transactions that need to be audited for each update, insert and delete and also for select. We proposed oracle Fine Grained Auditing. However, there is a concern that FGA would incur a lot of overhead. Is there another way to do it?
Thanks
April 18, 2007 - 10:40 am UTC
well, FGA audits the DML statement - and does so at "open" time. It will not affect the runtime performance of the statement in as much as it will affect the "open the statement".
I'd like to see you do it faster than something internal to the server ;) what I mean by that is - it is highly likely any "custom, we do it ourself" implementation would incur higher resources...
archiving audit records
Reader, April 18, 2007 - 7:58 pm UTC
We have a requirement to archive the audit records every month from FGA tables in such a way that at any point of time there will be two years worth of archived audit records. Can you give some ideas about how to approach this task?
Thanks
Fine grained auditing
A reader, May 02, 2007 - 9:00 am UTC
Tom,
Is there any way fine grained auditing can be used to write to the audit log the actual values of parameters passed to a stored procedure or function? If not, then can it be done by any other auditing mechanism? Database 10gr2.
Thanks
audit DML prior to 10g
A reader, August 08, 2007 - 9:46 pm UTC
Tom,
dbms_fga doesn't log DML in 9i,
any other "standard" oracle way to capture the DML SQL
applied to a table without writing the custom triggers?
Thanks a lot,
David
August 14, 2007 - 10:38 am UTC
no
you can capture the fact that an insert/update/delete was done using AUDIT, but to get the original sql, you would in that release need a trigger.
Fine-Grained Auditing for Real-World Problems
David, August 09, 2007 - 5:45 pm UTC
dbms_fga for single user?
Jim, September 16, 2008 - 9:26 am UTC
Is there a way to enable Fine Grained Auditing just against one user? There is only one user we want to audit and looking through the dbms_fga package description there does not seem to be a way to just affect one user.
September 17, 2008 - 12:03 pm UTC
see note 403837.1 in metalink
More info on note 403837.1
Derek, September 17, 2008 - 3:55 pm UTC
I'd like to add to the last item in the thread that if you use the SYS_CONTEXT function (or the SYSDATE, UID, USER or USERENV functions, or any use of the pseudo columns LEVEL, PRIOR, or ROWNUM) directly in an audit condition of an FGA policy, you may get some errors when the policy is enforced. I ran into this when data transformations (to_char, to_number, to_date, etc.) were applied to a result set. To avoid this, Oracle recommended creating a user defined function to handle the audit conditions, which seems to solve the problem.
it works
Jim, September 18, 2008 - 9:22 am UTC
Using note 403837.1 worked. Instead of using <> for the audit condition as listed in the note, I used = to just pick the one user that needed to be audited.
Thanks
bind variables appearing in FGA audit
shahram samardar, January 26, 2009 - 7:12 pm UTC
Hi Tom
My question is about FGA auditing with Bind variables.
I need to record value of bind variable in dba_fga_audit_trail
for the code like below, the bind variable assigned with :1 is not appearing in audit table.
How can I make a bind variable that has sql type of table of record to appear in audit table?
if there is no way for that, what do you suggest to convert that table of record to a varchar type ( you may suggest how to do) that varchar can be applied to IN clause of select that finally appear in FGA audit bind variable.
Regards,
CREATE OR REPLACE
TYPE GET_RECORD_TYPE AS OBJECT
(object_id number ,
object_key varchar2(40)
)
/
CREATE OR REPLACE
TYPE GET_RECORD_TABLE_TYPE AS TABLE OF GET_RECORD_TYPE;
declare
c SYS_REFCURSOR ;
i_set get_record_table_type := get_record_table_type() ;
begin
i_set.extend ;
i_set(i_set.count) := get_record_type ( 1 , 'shahram' ) ;
open c for 'select bucket_id,object_key, object_id,object_lob, object_metadata, content_type, lob_content_length, metadata_content_length, e_tag from EOR.object where
( bucket_id , object_key ) in ( select bucket_id , object_key from table(:1) )' using i_set ;
end ;
fga and patient data
A reader, November 12, 2009 - 3:57 pm UTC
Hi Tom,
Thanks again for this terrific forum.
Per the question above about HIPAA requirements, suppose we had to show a patient when their personal data was viewed.
Would we be able to get around the 256-policy limit if we had an audit_condition equal to each patient's id?
In other words, if we had over 256 ids, how would we implement fga? Maybe using views for each patient to avoid the limitation?
I think implementing a stored proc for each select on a patient's record would be a huge performance hit.
Thank you for your enlightenment.
November 15, 2009 - 2:22 pm UTC
.. Would we be able to get around the 256-policy limit if we had an
audit_condition equal to each patient's id? ...
why would you do that?????
just turn on FGA for that table. One policy, why would you do a policy per distinct value??
fga to show who viewed a patient's data
A reader, November 16, 2009 - 11:28 am UTC
Hi Tom,
I believe we could only audit select on 256 patients if all patients were stored in 1 table. In other words, if the requirement is to be able to show who viewed a patient's data, wouldn't we need 1 policy for each patient?
November 23, 2009 - 12:29 pm UTC
no, why would you?
We would capture the query, and the binds to the query. The patient should be identifiable from that.
person in emp table wants to know who viewed his/her info
A reader, January 04, 2010 - 4:20 pm UTC
Hi Tom,
I apologize for my basic question.
How would "Allen (empno 7499)" be given a list of who viewed his info in the scott.emp table using FGA? With the policy below, and the query of the emp table below, is there a method to create a report that can be given to the employee to satisfy privacy regulations?
I understand how his info can be audited if a policy is created for his empno but, with a 256-policy limit per table, how would FGA be implemented if any employee has the right to ask the question "Who's viewed my data?" for an extended timeframe (years).
Thanks again for your time.
begin
DBMS_FGA.ADD_POLICY(
object_schema => 'scott',
object_name => 'emp',
policy_name => 'select_scott_emp',
audit_condition => '',
audit_column => '',
statement_types => 'select');
end;
/
select * from scott.emp where empno between 7300 and 7600;
select * from dba_fga_audit_trail order by timestamp desc;
January 04, 2010 - 4:35 pm UTC
you would have to have an understanding of the queries used against this table.
so you could look for queries and their inputs and report back on them.
We record the query and the binds, given the binds and flashback query (up to 5 days) or flashback data archive (as long as you want), you can see what they saw.
Even without flashback - any query that went after their record directly would be easy to spot - just look for the bind = 7499.
As for queries that hit lots of records - say "where dname like '%A%'", the FGA process (even if you created you OWN policy per user - not practical) would tend to err on the side of caution and raise false positives - eg: a query such as:
select * from emp where job like :x
would audit for Allen regardless of your approach (single policy or policy per user - again NOT practicle) because the optimizer cannot decide if Allen would be seen or not - so it assumes it would be.
thank you
A reader, January 04, 2010 - 6:15 pm UTC
FGA
A reader, January 05, 2010 - 8:26 am UTC
Excellent!
Audit schema
just reader, March 25, 2010 - 8:12 pm UTC
Tom,
My database is 11gR1 and I am looking for a way to audit access to columns of another schema. For example SCOTT has SELECT on HR.EMPLOYEES table
I would like to audit every instance of INSERT as SELECT including list of columns that were used in the select list.
Is there a way to achieve that?
INSERT INTO scott.emp select * FROM hr.employees or
INSERT INTO scott.emp SELECT (column list) FROM hr.employees
Thanks in advance
March 26, 2010 - 3:16 pm UTC
You can use fine grained auditing or audit_trail=db,extended to capture the actual SQL
Fine grained auditing to track client's desktop
A reader, August 09, 2011 - 7:52 am UTC
Dear Tom,
In our production system there is a .Net application which is connected to a 10g database. Clients use there machine and connect the application server and accesses database through it.(so the
workflow to reach the database from any desktop is Client's desktop => Application server => Oracle 10g database).
I want to audit some important tables using FGA or standard auditing.
1) Now using standard auditing I can track the "update" operations on that table from application but I can able to track down only the host and terminal of application server as "USERHOST" and "TERMINAL" columns respectively in DBA_AUDIT_TRAIL view.
SQL> select USERHOST,TERMINAL from dba_audit_trail;
USERHOST TERMINAL
-------- --------
WORKGROUP\HMSPROD1 HMSPROD1
I want to track the remote machine(
i.e. tracking the machine host of client's desktop) that is using the application server instead. Can it be done?
2) Does FGA or Fine grained auditing has the power to track any remote OS user or OS host?
August 13, 2011 - 4:05 pm UTC
1) only if the application server pushed that information to us - we have *no clue* what this information is. It would depend entirely on the application server software.
2) see #1, the client application running in the middle tier would have to provide that to us - are they willing to?
given the information i have - which basically amounts to "we are using three tiers" and nothing more - I cannot say. You'd have to tell us what is running in the middle tier and what sort of control you have over it.
SQL Comments from PL/SQL
Mike, February 27, 2012 - 6:39 am UTC
Hi Tom,
While investigating FGA as an audit option for a new application we are deploying, I observed some unexpected behaviour in how SQL comments are recorded in the FGA audit trail (admittedly, this is unexpected for me).
It appears to me that any SQL comment included in statements within a PL/SQL block are stripped out before the audit record is inserted into the FGA trail (whereas comments included in regular SQL statements issued from SQL*Plus are captured in the audit trail).
Can you confirm that this is the case, and advise on the following:
- is there any way to force the capture of comments from PL/SQL?
- if not, do you have any recommendations on how we can populate the source of a particular statement? (i.e. to identify which procedure/function the statement was initiated from?)
Simple test case shown below:
=============================================
-- Create a simple table and two insert procedures (one including a comment and one without)CREATE TABLE testtab1 (id NUMBER);
CREATE OR REPLACE PROCEDURE ins_testtab1
AS
BEGIN
INSERT INTO testtab1 VALUES (1);
END;
/
CREATE OR REPLACE PROCEDURE ins_testtab2
AS
BEGIN
INSERT /* mycomment */ INTO testtab1 VALUES (1);
END;
/
-- Add the FGA policy As SYSSYS@orcl112> exec DBMS_FGA.ADD_POLICY(object_schema=>'TESTUSER', object_name=>'TESTTAB1', statement_types=>'INSERT', policy_name=>'TESTUSER_TESTTAB');
-- First, run two INSERTs from SQL*Plus (one including a comment and one without) - the comment in the 2nd statement is captured as expectedTESTUSER@orcl112> INSERT INTO testtab1 VALUES (1);
TESTUSER@orcl112> INSERT /* mycomment */ INTO testtab1 VALUES (1);
SYS@orcl112> SELECT sql_text FROM dba_fga_audit_trail;
SQL_TEXT
-------------------------------------------------------
INSERT INTO testtab1 VALUES (1)
INSERT /* mycomment */ INTO testtab1 VALUES (1)
-- Now, run each procedure and view the audit records - the comment from the 2nd procedure is not capturedSYS@orcl112> truncate table sys.fga_log$;
Table truncated.
TESTUSER@orcl112> exec ins_testtab1;
PL/SQL procedure successfully completed.
TESTUSER@orcl112> exec ins_testtab2;
PL/SQL procedure successfully completed.
SYS@orcl112> SELECT sql_text FROM dba_fga_audit_trail;
SQL_TEXT
---------------------------------------------------------
INSERT INTO TESTTAB1 VALUES (1)
INSERT INTO TESTTAB1 VALUES (1)
=============================================
February 28, 2012 - 6:54 am UTC
comments are stripped from PLSQL SQL at compile time.
If you want them to stay in there, use /*+ */ - make it look like a 'hint' - just make sure to not put anything resembling a hint in there!
so, two ideas
a) use the /*+ - be careful to not have a hint in there by accident ;)
b) if you are not using dbms_session.set_identifier, you could do this instead of putting a comment in the code:
3 BEGIN
4 dbms_session.set_identifier( $$PLSQL_UNIT || ', ' || $$PLSQL_LINE );
5 INSERT INTO testtab1 VALUES (1);
6 END;
7 /
here is a quick example
ops$tkyte%ORA11GR2> drop table testtab1;
Table dropped.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> CREATE TABLE testtab1 (id NUMBER);
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> CREATE OR REPLACE PROCEDURE ins_testtab1
2 AS
3 BEGIN
4 dbms_session.set_identifier( $$PLSQL_UNIT || ', ' || $$PLSQL_LINE );
5 INSERT INTO testtab1 VALUES (1);
6 END;
7 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> CREATE OR REPLACE PROCEDURE ins_testtab2
2 AS
3 BEGIN
4 dbms_session.set_identifier( $$PLSQL_UNIT || ', ' || $$PLSQL_LINE );
5 INSERT /* mycomment */ /*+ my comment is here, this is not a hint */ INTO testtab1 VALUES (1);
6 END;
7 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec DBMS_FGA.ADD_POLICY(object_schema=>user, object_name=>'TESTTAB1', statement_types=>'INSERT', policy_name=>'TESTUSER_TESTTAB');
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect / as sysdba
Connected.
sys%ORA11GR2> truncate table sys.fga_log$;
Table truncated.
sys%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> INSERT INTO testtab1 VALUES (1);
1 row created.
ops$tkyte%ORA11GR2> INSERT /* mycomment */ INTO testtab1 VALUES (1);
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> SELECT client_id, sql_text FROM dba_fga_audit_trail;
CLIENT_ID
----------------------------------------------------------------
SQL_TEXT
-------------------------------------------------------
INSERT INTO testtab1 VALUES (1)
INSERT /* mycomment */ INTO testtab1 VALUES (1)
2 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect / as sysdba
Connected.
sys%ORA11GR2> truncate table sys.fga_log$;
Table truncated.
sys%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec ins_testtab1;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec ins_testtab2;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> SELECT client_id, sql_text FROM dba_fga_audit_trail;
CLIENT_ID
----------------------------------------------------------------
SQL_TEXT
-------------------------------------------------------
INS_TESTTAB1, 4
INSERT INTO TESTTAB1 VALUES (1)
INS_TESTTAB2, 4
INSERT /*+ my comment is here, this is not a hint */
INTO TESTTAB1 VALUES (1)
2 rows selected.
FGA
A reader, September 26, 2012 - 12:40 pm UTC
Tom:
Can Fine Grained auditing be used in 9i to audit *specific* SQL statements defined in the policy.
I need to audit the usage of some Adhoc Queries (sql scripts) and crystal reports (SQL).
Each script or report has a predefine SQl lik:
select col1, col2, ... from t1, t2, t3
where t1.col1 = t2.col1 and t2.col2=t3.col2...
I do not think FGA can do something like this.
It seems the only way to do it is to call a database function that uses autonomous transaction to insert an audit record for script name or reprot name and update each ad hoc query or report to call that function.
Do you agree?