Oracle Auditing by User

#1

I am trying to audit select statements on a column for everyone except
JohnDoe who works in the HR dept. How do I create the correct
audit_condition??

BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'HR'
,object_name => 'EMPLOYEES'
,policy_name => 'PAYROLL_INFO'
,audit_condition => 'DBUser NOT = 'JohnDoe'
,audit_column => 'SALARY'
,handler_schema => NULL
,handler_module => NULL
,enable => TRUE
,statement_types => 'SELECT'
,audit_trail => DBMS_FGA.DB_EXTENDED
,audit_column_opts => DBMS_FGA.ANY_COLUMNS
);
END;
/

#2

On Apr 22, 9:36*pm, indytoatl <indyto... (AT) gmail (DOT) com> wrote:

Quote:

I am trying to audit select statements on a column for everyone except
JohnDoe who works in the HR dept. How do I create the correct
audit_condition??

BEGIN
* * DBMS_FGA.ADD_POLICY(
* * * * *object_schema => 'HR'
* * * * ,object_name => 'EMPLOYEES'
* * * * ,policy_name => 'PAYROLL_INFO'
* * * * ,audit_condition => 'DBUser NOT = 'JohnDoe'
* * * * ,audit_column => 'SALARY'
* * * * ,handler_schema => NULL
* * * * ,handler_module => NULL
* * * * ,enable => TRUE
* * * * ,statement_types => 'SELECT'
* * * * ,audit_trail => DBMS_FGA.DB_EXTENDED
* * * * ,audit_column_opts => DBMS_FGA.ANY_COLUMNS
* * );
END;
/

,audit_condition => 'sys_Context(''USERENV'',''SESSION_USER'') <> ''
JOHNDOE'' '

I don't have a system to test it on at the moment... notice the
double single-quotes...

#3

On Apr 22, 9:36*pm, indytoatl <indyto... (AT) gmail (DOT) com> wrote:

Quote:

I am trying to audit select statements on a column for everyone except
JohnDoe who works in the HR dept. How do I create the correct
audit_condition??

BEGIN
* * DBMS_FGA.ADD_POLICY(
* * * * *object_schema => 'HR'
* * * * ,object_name => 'EMPLOYEES'
* * * * ,policy_name => 'PAYROLL_INFO'
* * * * ,audit_condition => 'DBUser NOT = 'JohnDoe'
* * * * ,audit_column => 'SALARY'
* * * * ,handler_schema => NULL
* * * * ,handler_module => NULL
* * * * ,enable => TRUE
* * * * ,statement_types => 'SELECT'
* * * * ,audit_trail => DBMS_FGA.DB_EXTENDED
* * * * ,audit_column_opts => DBMS_FGA.ANY_COLUMNS
* * );
END;
/

,audit_condition => 'sys_context(''userenv'',''session_user'') !=
''JOHNDOE'''

should get you what you want (or substitute any of the other
identifiers in the sys_context if needed).

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Oracle Auditing by User

comp.databases.oracle.server comp.databases.oracle.server

Oracle Auditing by User - 04-22-2011 , 08:36 PM

Re: Oracle Auditing by User - 04-23-2011 , 11:37 AM

Re: Oracle Auditing by User - 04-23-2011 , 12:20 PM