No question at this time
DBA Top 10
1 A. Kavsek 11100
2 M. Cadot 7600
3 B. Vroman 5100
4 P. Wisse 5000
5 T. Boles 2000
5 . Lauri 2000
7 J. PĂ©ran 1100
8 J. Schnackenberg 700
9 R. Pattyn 600
10 T. P 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48435
Total active users1504
Act. users last 24h5
Act. users last hour0
Registered user hits last week139
Registered user hits last month778
Go up

Oracle11g schema table access audit
Next thread: RefCursor vs Views
Prev thread: Data Migration

Message Score Author Date
Hi, How can I know if someone(user) select a r...... BASHYAM RAJA Sep 21, 2019, 12:15
If you have enabled fine-grained auditing you ca...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Michel Cadot Sep 21, 2019, 12:40
To capture both the SQL statement that caused the ...... BASHYAM RAJA Sep 21, 2019, 12:42
Thanks Michel, How to capture only FGA audit c...... BASHYAM RAJA Sep 21, 2019, 12:50
SQL text is in DBA_FGA_AUDIT_TRAIL.SQL_TEXT Bin...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Sep 21, 2019, 12:58
Thank you Michel.... BASHYAM RAJA Sep 22, 2019, 10:56

Follow up by mail Click here


Subject: Oracle11g schema table access audit
Author: BASHYAM RAJA, Bahrain
Date: Sep 21, 2019, 12:15, 24 days ago
Os info: Windows 2012
Oracle info: Oracle11g 11.2.0.4
Message: Hi,

How can I know if someone(user) select a record in oracle database 11g table using sql tools(SQL*Plus,Toad,SQLDeveloper,Forms6i,Report6i).
We enable trigger only for insert, update & delete to capture DML access.

I want to capture select statement record on important table columns accessed by end user and developers on live schema . We enabled oracle standard audit & FGA.
DBA_AUDIT_TRAIL table only gives table name and ousername and timestamp, it is not capturing which record(ie. where condition columns parameter).

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Oracle11g schema table access audit
Author: Michel Cadot, France
Date: Sep 21, 2019, 12:40, 24 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message:
If you have enabled fine-grained auditing you can select the column on which auditing is done and know the associated statements.
The view is DBA_FGA_AUDIT_TRAIL.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Oracle11g schema table access audit
Author: BASHYAM RAJA, Bahrain
Date: Sep 21, 2019, 12:42, 24 days ago
Message: To capture both the SQL statement that caused the audit, and any associated bind variables
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Oracle11g schema table access audit
Author: BASHYAM RAJA, Bahrain
Date: Sep 21, 2019, 12:50, 24 days ago
Message: Thanks Michel,

How to capture only FGA audit column name and assoicated bind variables from DBA_FGA_AUDIT_TRAIL table.

Best Regards,
Raja
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Oracle11g schema table access audit
Author: Michel Cadot, France
Date: Sep 21, 2019, 12:58, 24 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
SQL text is in DBA_FGA_AUDIT_TRAIL.SQL_TEXT
Bind variables are in DBA_FGA_AUDIT_TRAIL.SQL_BIND (truncated to 4000 bytes).
Column names are defined by the audit policy given in DBA_FGA_AUDIT_TRAIL.POLICY_NAME and then DBA_AUDIT_POLICY_COLUMNS.POLICY_COLUMN.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Oracle11g schema table access audit
Author: BASHYAM RAJA, Bahrain
Date: Sep 22, 2019, 10:56, 23 days ago
Message: Thank you Michel.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here