Skip to Main Content
  • Questions
  • Auditing of all action in a session not created by specific MACHINE

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Vikas .

Asked: July 29, 2020 - 7:21 am UTC

Answered by: Chris Saxon - Last updated: August 03, 2020 - 2:24 pm UTC

Category: Database Administration - Version: 12 C R2 Standard Edition

Viewed 100+ times

You Asked

Hi Tom

thanks for previous solutions .

Please guide me or share some helpful links to achieve below requirement.

1--> we want to audit all actions of a session not created by application.
2--> Will a cursor having 1000 records will generate 1000 rows in auditing.

Note* We have 12c R2 SE Oracle Database.So no fine grained auditing .
I need to store only information like (action,object_name,schema_name,host,OS_user,IP address,time,date).
"ALL actions" includes everything (alter,create,drop,update,delete etc) like "audit all by schema whenever successful ".


Actual scenario:: An application user is used by many developers ,we want to only audit actions of developer session ,not application.

How can we achieve it with minimum impact on performance.

and we said...

1. You can use unified auditing.

Create a policy that specifies the criteria you use to determine what to audit. You can access sys_context variables here, so you can base it on username, IP, host, ...

This audits everything done by the user U:

grant create session, create table, unlimited tablespace to u
  identified by u;

create audit policy test_audit_policy
  actions all
  when    q'!sys_context('userenv', 'session_user') = 'U'!'
  evaluate per session
  container = current;
  
audit policy test_audit_policy;


Remember it's possible to spoof many (all?) the properties you could use to check if statements come from the application. So a determined developer can find a way to pretend to be the application to bypass your auditing. It's much easier and safer to audit everything.

2. You'll get a row per SQL statement. If you select 1000 rows in one go, you'll have one entry for this.

conn u/u

create table t (
  c1 int
);
insert into t 
with rws as (
  select level x from dual
  connect by level <= 100
)
  select * from rws;
commit;

select count (*) from t;

var cur refcursor;

exec open :cur for select * from t;

print :cur

conn chris

select action_name, sql_text
from   unified_audit_trail
where  dbusername = 'U'
and    event_timestamp > systimestamp - interval '1' minute 
and    action_name <> 'EXECUTE'
order by event_timestamp;

ACTION_NAME          SQL_TEXT
-------------------- ------------------------------------------------------------
LOGOFF               <null>
LOGON                <null>
ALTER SESSION        ALTER SESSION SET TIME_ZONE='Europe/London' NLS_LANGUAGE='AM
                     ERICAN' NLS_TERRITOR

SELECT               select * from v$version where banner like '%Oracle%'
SELECT               select parameter,value from nls_session_parameters
                      union all SELECT 'DB_TIMEZO

ALTER SESSION        ALTER SESSION SET TIME_ZONE = 'Europe/London'
COMMIT               ALTER SESSION SET TIME_ZONE = 'Europe/London'
SELECT               SELECT DBTIMEZONE FROM DUAL
SELECT               select parameter,value from nls_session_parameters
                      union all SELECT 'DB_TIMEZO

SELECT               select sys_context('userenv','service_name') from dual
SELECT               select USER from dual
CREATE TABLE         create table t (
                       c1 int
                     )

INSERT               insert into t
                     with rws as (
                       select level x from dual
                       connect by level <= 10

COMMIT               commit
COMMIT               commit
SELECT               select count (*) from t
SELECT               SELECT * FROM T
COMMIT               <null>
LOGOFF               <null>

and you rated our response

  (2 ratings)

Reviews

Alternate solution

July 30, 2020 - 10:31 am UTC

Reviewer: vikas from India

Hi

Thanks for quick response and as always a new direction to explore.
I'll study making audit policy like this(was not aware of it).

Now as an alternate i was searching I built a simple trigger on LOGON with if condition as below.

create TRIGGER user_auditing
AFTER LOGON ON DATABASE
BEGIN
    IF (upper(SYS_CONTEXT('USERENV','OS_USER')) NOT IN ( 'abc','xyx','ABC','XYZ'))
  THEN
   execute immediate 'AUDIT ALL STATEMENTS IN SESSION CURRENT BY ACCESS WHENEVER SUCCESSFUL';
    END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;


it was working fine for 90% case but user is logging through SQL developer tool so when executing "
select sysdate from dual
" it is generating 400 rows in auditing.

so question :: Can I exclude select from this audit (and how as i didn't get any syntax)

and seen in your code "action all" will audit all system actions and select also as in above case but as per table entry it says do not use 184 185 186 .
select * from AUDITABLE_SYSTEM_ACTIONS  where action in ('184','185','186') and component='Standard';


so question 2 :: Should we use "ALL" option.

Question 3 :: is there any big difference in above 2 method.
Chris Saxon

Followup  

July 30, 2020 - 1:18 pm UTC

Let's step back a bit:

Why exactly are you enabling auditing? What are you hoping to achieve by doing this?

sharing information

July 31, 2020 - 7:35 pm UTC

Reviewer: vikas from India

Hi

Greetings.

Searched over internet about the code you shared.
All doubts cleared (till now ).

Stuck on syntax part.

As we are having flexible number of app servers, I decided to filter over OS_USER as all servers have only 3 users which are used to connect to database.

so modified code as below.

create audit policy test_audit_policy
  actions all
  when  'sys_context(''userenv'', ''os_user'') not in 
  ( ''ABC'',''XYZ'',''abc'',''xvz'')'
  evaluate per session; 


but this still audit these 2 os_user as well.

Please check and let me know where I did it wrong.

Regards
Chris Saxon

Followup  

August 03, 2020 - 2:24 pm UTC

all servers have only 3 users which are used to connect to database

Are they OS users or database users?

Try logging the values for sys_context('userenv', 'os_user') in the trigger - this may well reveal where the issue is.