Home » RDBMS Server » Security » audit the logs for selecting particular column (10.2.0)
audit the logs for selecting particular column [message #454008] Mon, 03 May 2010 07:37 Go to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
I have a requirement of implementing audit logs in my application. Insert,update,delete operations can be logged using triggers. How to audit logs in case of Read / Select operation on tables on a particular column? Is there any specific way of implementing audit logs for Read operation on tables on a particular column?
Re: audit the logs for selecting particular column [message #454010 is a reply to message #454008] Mon, 03 May 2010 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 66800
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Why don't use Oracle built-in audit?
2/ You can't audit SELECT unless you enter the fine-grained auditing world.

Regards
Michel
Re: audit the logs for selecting particular column [message #454054 is a reply to message #454008] Mon, 03 May 2010 21:51 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
I want to audit only 4 tables and in that 4 columns each.If I use fine-grained audit i can audit whole table.I want to audit only 4 columns in those tables.
Re: audit the logs for selecting particular column [message #454059 is a reply to message #454054] Tue, 04 May 2010 00:01 Go to previous messageGo to next message
Michel Cadot
Messages: 66800
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, if you use FGA you can audit what you want, this is why it is named "fine-grained".

Regards
Michel
Re: audit the logs for selecting particular column [message #454067 is a reply to message #454008] Tue, 04 May 2010 00:38 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
I m following the below procedure for FGA.

1) I m changing audit_trail parameter to db or db_extended.

2) I am connecting to our schema user.

3) I m giving auditing select permissions to a particular table but i m not able to select particular column.

audit select on emp by access;

but i want to give ...

audit select on empno column on emp table.

4) I want to write the auditing details in one of my user table with the details like username,tablename,timestamp,column name etc.

Please advice how to do the above procedure.
Re: audit the logs for selecting particular column [message #454072 is a reply to message #454067] Tue, 04 May 2010 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 66800
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is standard audit not Fine-Grained one.
Please search on documentation for this and come back if you can't implement it.

Regards
Michel
Re: audit the logs for selecting particular column [message #454163 is a reply to message #454008] Tue, 04 May 2010 05:39 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
I have read the FGA document http://www.databasejournal.com/features/oracle/article.php/3640201/Oracle-10g-Security-Part-1-Fine-Grained-Auditing.htm, may i know how to enable the FGA and what is the procedure to follow.

Re: audit the logs for selecting particular column [message #454166 is a reply to message #454163] Tue, 04 May 2010 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 66800
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you didn't read the listing attached to the paper as he clear show how to do it.
You should read Oracle documentation as well.

Regards
Michel

Re: audit the logs for selecting particular column [message #454167 is a reply to message #454008] Tue, 04 May 2010 05:49 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
When I add policy it is giving following error.
How to enable the feature.

ERROR at line 1:
ORA-00439: feature not enabled: Fine-grained Auditing
ORA-06512: at "SYS.DBMS_FGA", line 17
ORA-06512: at line 2
Re: audit the logs for selecting particular column [message #454171 is a reply to message #454167] Tue, 04 May 2010 05:59 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
ORA-00439: feature not enabled: string
Cause: The specified feature is not enabled.

Action: Do not attempt to use this feature.



sriram Smile

[Updated on: Tue, 04 May 2010 05:59]

Report message to a moderator

Re: audit the logs for selecting particular column [message #454172 is a reply to message #454171] Tue, 04 May 2010 06:01 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Copy and paste your sql session (what you did and what you got)
post the out put for the following
select * from v$version;


sriram Smile
Re: audit the logs for selecting particular column [message #454174 is a reply to message #454167] Tue, 04 May 2010 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 66800
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must have an Entreprise Edition.

Regards
Michel
Re: audit the logs for selecting particular column [message #454177 is a reply to message #454008] Tue, 04 May 2010 06:16 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
I have run the following block as sys user.

SQL> BEGIN
2 DBMS_FGA.ADD_POLICY(
3 object_schema => 'IPDC'
4 ,object_name => 'SERVICEPROGRAM'
5 ,policy_name => 'IPDC_POLICY'
6 ,audit_condition => null
7 ,audit_column => 'AUTHENTICATIONSEED'
8 ,handler_schema => 'IPDC'
9 ,handler_module => NULL
10 ,enable => TRUE
11 ,statement_types => 'SELECT,INSERT,UPDATE,DELETE'
12 ,audit_trail => DBMS_FGA.DB_EXTENDED
13 ,audit_column_opts => DBMS_FGA.ANY_COLUMNS
14 );
15 END;
16 /
BEGIN
*
ERROR at line 1:
ORA-00439: feature not enabled: Fine-grained Auditing
ORA-06512: at "SYS.DBMS_FGA", line 17
ORA-06512: at line 2


Output of the below query

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.


We are using standard edition.
Re: audit the logs for selecting particular column [message #454178 is a reply to message #454177] Tue, 04 May 2010 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 66800
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to upgrade to EE or you can't use this feature.

Regards
Michel
Re: audit the logs for selecting particular column [message #454179 is a reply to message #454008] Tue, 04 May 2010 06:24 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
We are using Standard Edition in production servers as well.Now it is not possible. please is there any auditing feature other than FGA to implement in standard edition.
Re: audit the logs for selecting particular column [message #454213 is a reply to message #454179] Tue, 04 May 2010 07:56 Go to previous message
Michel Cadot
Messages: 66800
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Standard auditing: AUDIT statement with its limits.

Regards
Michel
Previous Topic: Audit logs
Next Topic: MANAGE GROUP OF USERS
Goto Forum:
  


Current Time: Sun Jan 26 10:44:13 CST 2020