Oracle Fine grained auditing / track select records

In regular object-based auditing, the records can show that a user selected from a specific table, along with other helpful information such as timestamp, client machine name, etc.
What it does not record is what data the user selected from the table.

Audit Trail does not record which particular record was selected. Since reading is not a transaction, the facts are not recorded in Oracle' redo logs, rollback segments or anywhere else.

Also we cannot create trigger on select statement, we can create trigger only on insert / update / delete.



Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table.



Notice that FGA will show the SQL text, regardless of the setting of AUDIT_TRAIL – no “EXTENDED” necessary here



-------------------------

http://www.dba-oracle.com/security/fga_enhancements.htm
http://peerdba.wordpress.com/2011/01/09/fine-grained-auditing-fga/

-------------------------

begin
dbms_fga.drop_policy(
   object_schema => 'LDBO',
   object_name   => 'TRANSACTIONS',
   policy_name   => 'AUDIT_TRANS'
);
END;
/


Below, I create a policy called AUDIT_TRANS that acts as a trigger for any queries against the TRANSACTIONS where anyone views a TRANSACTIONS row where Quantity>1000000.

begin
   dbms_fga.add_policy(
      object_schema   => 'LDBO',
      object_name     => 'TRANSACTIONS',
      policy_name     => 'AUDIT_TRANS',
      audit_condition => 'Quantity>1000000',
      audit_column    => 'QUANTITY',
      handler_schema  => null,
      handler_module  => null,
      enable          => true
   );
end;
/




This was used to turn auditing on only for select statements against the table. The same can be now be rewritten as:

begin
   dbms_fga.add_policy (
      object_schema=>'CLAIM_SCHEMA',
      object_name=>'CLAIMS',
      policy_name=>'LARGE_CLAIM',
      audit_condition=>
        'CLAIM_AMOUNT>500 OR PAID_AMOUNT>500',
      audit_column=>
        'SSN, PROC_CODE',
      statement_types => 'SELECT'

  );
end;
/

To audit insert, delete, and update for the same table on the same policy condition and columns, we can use:

begin
   dbms_fga.add_policy (
      object_schema=>'CLAIM_SCHEMA',
      object_name=>'CLAIMS',
      policy_name=>'LARGE_CLAIM',
      audit_condition=>
        'CLAIM_AMOUNT>500 OR PAID_AMOUNT>500',
      audit_column=>
        'SSN, PROC_CODE',
      statement_types => 'SELECT,INSERT,UPDATE,DELETE'
  );
end;
/

The above code writes an entry into the table fga_log$ when the table is subjected to insert, update, delete, and select statements; when the auditing condition is satisfied and the audit columns are referenced.



select
   timestamp     c1,
   db_user       c2,
   os_user       c3,
   object_schema c4,
   object_name   c5,
   policy_name   c6,
   sql_text      c7
from
   dba_fga_audit_trail
order by
   timestamp;



--------------------------------------

select count(*)  from sys.fga_log$ where dbuid not in ('USER1','USER2');
select dbuid, count(*)  from sys.fga_log$  group by dbuid  where dbuid  not in ('USER1','USER2');
select * from dba_audit_policies;
select * from dba_audit_policies  where  enabled='NO';
select * from dba_audit_policies  where  enabled='YES';
select * from dba_fga_audit_trail where db_user not in ('USER1','USER2');
select distinct object_name, policy_name from dba_fga_audit_trail where db_user not in ('USER1','USER2');
SELECT  policy_name, object_name, statement_type, os_user, db_user FROM dba_fga_audit_trail;
select * from dba_fga_audit_trail where db_user not in ('USER1','USER2');

---------------------------------–syntax for enable and disable of policy –take below select statements and execute.

select ‘begin dbms_fga.disable_policy(object_schema => ”APP_USER”, object_name => ”’ || object_name || ”’, policy_name => ”’ || policy_name ||”’);end; /’
from dba_fga_audit_trail where db_user not in (‘DBA_USER’);

Track User password change Audit Activity

if you want to track password change or other activity with sql then

ALTER SYSTEM SET AUDIT_TRAIL=DB_EXTENDED SCOPE=SPFILE;

THERE IS SQL_TEXT COLUMN IN DBA_AUDIT_TRAIL VIEW.

ORACLE AUDIT FOR ALTER COMMAND

CREATE TABLE DBA_AUDIT_TAB_KSH (USERNAME VARCHAR2(10), SQL_TEXT VARCHAR2(2000),TIMESTAMP DATE);

CREATE OR REPLACE TRIGGER DBA_AUDIT_KSH

BEFORE ALTER ON SCHEMA

DECLARE

sql_text ora_name_list_t;

stmt VARCHAR2(2000);

n integer;

dt date;

BEGIN

null;

IF (ora_dict_obj_type IN ( 'TABLE') )

then

n:= ora_sql_txt(sql_text);

FOR i IN 1..n LOOP

stmt := stmt || sql_text(i);

END LOOP;

dt:=TO_DATE(SYSDATE,'DD-MM-YYYY HH24:MI:SS');

INSERT INTO DBA_AUDIT_TAB_KSH (username,sql_text,timestamp) VALUES (user,stmt,dt);

END IF;

END DBA_AUDIT_KSH;

/

Oracle Auditing

select name,value from v$parameter where name='audit_trail';

ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;

Shutdown

startup

--

select * from dba_priv_audit_opts;

select * from dba_audit_session;

select * from dba_audit_trail;

select * from dba_stmt_audit_opts

union

select * from dba_priv_audit_opts;

select * from dba_audit_exists;

select * from dba_audit_object;

select * from dba_audit_session;

select * from dba_audit_statement;

select * from dba_audit_trail;

select * from dba_obj_audit_opts;

select * from dba_priv_audit_opts;

select * from dba_stmt_audit_opts;

----

audit all by KGUPTA2 by access;

noaudit all by KGUPTA2;

audit create session by access;

audit audit system by access;

audit grant any privilege by access;

audit grant any object privilege by access;

audit grant any role by access;

audit create user by access;

audit create any table by access;

audit create public database link by access;

audit create any procedure by access;

audit alter user by access;

audit alter any table by access;

audit alter any procedure by access;

audit alter database by access;

audit alter system by access;

audit alter profile by access;

audit drop user by access;

audit drop any procedure by access;

audit drop any table by access;

audit drop profile by access;

audit select table, insert table, update table, delete table by payroll by access;

--

Auditing user activity with the Oracle audit command

Oracle has syntax for auditing specific user activity. To audit the activity of user KGUPTA2 we could issue these audit commands:

Audit all Oracle user activity.

This audits everything including DDL (create table), DML (inserts, updates, deletes) and login/logoff events:

audit all by kGUPTA2 by access;

Audit all Oracle user viewing activity:

audit select table by KGUPTA2 by access;

Audit all Oracle user data change activity:

audit update table, delete table,insert table by KGUPTA2 by access;

Audit all Oracle user viewing activity:

audit execute procedure by KGUPTA2 by access;

AUDIT INSERT, UPDATE ON LDBO.ACCOUNTS by access;

AUDIT ALL ON LDBO.ACCOUNTS_SEQUENCE;

Setting Default Auditing Options: Example The following statement specifies default auditing options for objects created in the future:

AUDIT ALTER, GRANT, INSERT, UPDATE, DELETE ON DEFAULT;

Any objects created later are automatically audited with the specified options that apply to them, if auditing has been enabled:

If you create a table, then Oracle Database automatically audits any ALTER, GRANT, INSERT, UPDATE, or DELETE statements issued against the table.

If you create a view, then Oracle Database automatically audits any GRANT, INSERT, UPDATE, or DELETE statements issued against the view.

If you create a sequence, then Oracle Database automatically audits any ALTER or GRANT statements issued against the sequence.

If you create a procedure, package, or function, then Oracle Database automatically audits any ALTER or GRANT statements issued against it.

SEQUENCE--- ALTER,AUDIT,GRANT,SELECT

TABLE OR VIEW -- ALTER,AUDIT,COMMENT,DELETE,GRANT,INDEX,INSERT,LOCK,RENAME,SELECT,UPDATE

audit update table, delete table,insert table by FRED by access;

---------------------------

audit all on ldbo.tbllocktable;

noaudit select on ldbo.tbllocktable;

select obj_name, sessionid, username, ses_actions, timestamp from dba_audit_trail where obj_name='TBLLOCKTABLE';

you'll get a result like (columns have been shortened for readability):

OBJ_NAME SESSIONID USERNAME SES_ACTIONS TIMESTAMP

-------- ---------- -------- ------------------- ------------------

TBLLOCKTABLE 23242623 LDBO -S-------------- 10-JUL-10

TBLLOCKTABLE 23122413 UIPL6724 ---------S------ 10-JUL-10

TBLLOCKTABLE 23092613 USSB0256 ---------S------ 10-JUL-10

TBLLOCKTABLE 23242311 LDBO ---------S------ 10-JUL-10

TBLLOCKTABLE 23092651 UIPL6722 ---------S------ 10-JUL-10

TBLLOCKTABLE 23242678 LDBO -S-------------- 10-JUL-10

The TIMESTAMP column indicates the time of the first audited action within the session. The SES_ACTIONS column is a session summary—a string of 16 characters, one for each action type in the order ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, EXECUTE, READ. (Positions 15, and 16 are reserved for future use). The characters are: - for none, S for success, F for failure, and B for both.

select obj_name, sessionid, username, ses_actions, timestamp from dba_audit_trail where obj_name='TBLLOCKTABLE';

-S--------------

---------S------

---------S------

---------S------

---------S------

-S--------------

----------S-----

----------S-----

Oracle User Login Lock Wrong Attempt details

select username,osuser,machine,blocking_session from v$session where username='LDBO';

select username,os_username,to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",terminal,
---UTL_INADDR.get_host_address(terminal) "IP",
decode(returncode,0,'Successful',1017,'WrongAttempt',28000,'Locked',28009,'SYS Login',1005,'Fail_NULL',28001,'EXPIRED',28031,'Roles_Exceeded',returncode) Login_Status
from dba_audit_session where trim(Timestamp) > trunc(sysdate-1)
and username='AHE'
-----and os_username like '%UNICON\%'
order by timestamp desc;

select username,password,account_status,to_char(lock_date, 'Dy DD-Mon-YYYY HH24:MI:SS') lock_date,expiry_date
from dba_users where account_status like '%LOCKED%' order by 3 desc,lock_date desc;

select distinct username "USER ID",osuser,machine,UTL_INADDR.get_host_address(terminal)"System IP", decode(username,'USSB3409','ANILKUMAR','USSB0065','GAURAVSINGH','USSB0737','KAVITA', 'USSB0580','MAHINDERSINGH', 'USSB0624','RAJENDERSINGH','USSB0625','RAKESHKUMAR','UIPL6716','RAKESHLAL','USSB0500','RUPINDERKAUR','UIPL6713','SHASHINATH','USSB0502','SURENDERKUMAR','UIPL6715','VIRENDERSINGH','UIPL6714','SANJAYKUMARSISODIA','UIPL7249','BHAVYASINGH','UFIPL00152','GOVINDGUPTA','UIPL7244','INDIRARAWAT','USSB2693','JYOTI','UIPL7258','MANTUKUMARSINGH','USSB0080','PRIYADAS','USSB2720','RAHUL','UIPL7210','ROBINTYAGI','UFIPL00154','ROHITGUPTA','USSB1740','SHAKSHI','UIPL7259','VINODKUMAR','USSB0991','ARJUNSINGH','UFPL00058','DHEERAJTANEJA','UFIPL00151','PAWANKUMARJINDAL','UIPL7759','PRABHAKARSINGH','USSB0948','PRIYANKARANA','USSB6549','RAJESHSHARMA.','USSB6788','RAMKRISHANKUMAR','USSB6790','ROSHANKUMAR','USSB0550','SACHINSUGANDHA','UIPL7261','SHOBHITAGARWAL','USSB1470','SIDDHARTHBHATIA','USSB6996','KANIKAKHURANA','UFIPL00153','DEVESH','UIPL10283','SWATISALUJA','UIPL10287','NITINGOYAL','UIPL9767','GAURAV','UFIPL00156','AMITKUMARSINGHAL','UIPL10305','PRANJEETKAUSHIK','UFPL00173','PUNEETSHARMA','UIPL10672','SUMITRAJORA','UFPL00188','DEEPAKGUPTA','UFPL00195','VINEETSABHLOK','USSB0019','PRASHANTUPADHYAY','USSB0022','NEERAJGROVER','USSB0109','SAURABHAATRE','USSB0626','SHAHABUDDIN','USSB2701','NITESH','UCPL0491','POONAMSINGH','UCPL0116','DURGESHKESHRI','USSB0211','SAURABHCHAUDHARY','USSB0431','VIRENDERSINGHCHANDEL','USSB5498','AKANSHARAWAL','USSB7030','SATYANARAYANA','UIPL6793','JAGDEEPSINGH','USSB3700','JAYAACHAR','UIPL6778','NEERAJSHARMA','USSB6047','NEHAGOEL','UIPL6798','ROHANSHARMA','UIPL6828','SACHINTYAGI','USSB4173','SEEMAGAMBHIR','USSB4766','SHAKSHIARORA','UCPL0117','SHAMPAMUKHERJEE','UIPL6736','SONIABENWAL','USSB6621','TANUSHREESOMANI','USSB1236','ASHIMAARORA','UIPL6771','SHELLY','USSB5708','KULDEEPKAUR','UIPL6801','SHWETA','UIPL6800','ROHITLUTHRA','USSB7428','AMRESHKUMAR','UIPL10160','SONIASABHARWAL','USSB7444','ANILSHARMA','UIPL10515','SAPNAGUPTA','UIPL10402','KHUSHALRAZDAN','UIPL6742','GITTUKATARIA','UIPL6746','MUNNASINGH','USSB0013','AMITTYAGI','UIPL6722','DEEPANSHUMALHOTRA','USSB5695','HARENDRASHARMA','UIPL6727','MANISHVERMA','USSB1112','PRATIMASINGH','USSB5374','PULKIT','USSB1253','RAHULTYAGI','USSB5274','VARUNKANT','UFPL00178','SUBHASHCHANDRAGEHLOT','UFPL00028','SUBHASHSHARMA','USSB6901','KSHITIJR.GUPTA','UCPL0154','RACHITJAIN','USSB6097','SHEKHARSAXENA','UIPL6712','YESHPALTHAKUR','USSB6002','SANDHYASHARMA','UFIPL00130','ANJALIMUKHIJA','UCPL0111','AMITUPADHYAY','USSB6045','RAJAN','UFIPL00136','SANDEEPSIR','USSB0064','AMITJAIN','UIPL6831','ANKITASRIVASTAVA','USSB6805','SHASHIBHUSHAN','USSB6950','SUNIIGABA','USSB7222','NILESHTRIPATHI','USSB0849','OMPRAKASHBANDERWAL','USSB0458','ROHTASAGARWAL','RMS','RMS','USSB1366','NEETARASTOGI','USSB0256','ANANDJOSHI','USSB1032','SATISHSHARMA','USSB1113','SUNILKUMARSINGHAL','UIPL6721','VISHALGUPTA','UIPL6724','YATENDRASINGHBISHT','USSB6241','KULDEEPSHARMA','USSB5495','RAGHIBHUSAIN','USSB1031','TARUNCHHOKRA','UIPL6717','NEERAJKAUSHIK','USSB6508','ACHINANAND','USSB7530','KHYATIADLAKHA','USSB7565','BHUWANSHARMA','USSB7550','NEHASINGH','LDBO','DBA') Name
from v$session where program ='ld.exe' order by 5;

select username,password,account_status,lock_date, to_char(expiry_date, 'Dy DD-Mon-YYYY HH24:MI:SS') expir_date
from dba_users where account_status like '%EXPIRED%' order by expiry_date desc;

select username,os_username,terminal "System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name, decode(ses_actions,'---------S------','SELECT','----------S-----','UPDATE',ses_actions) action,action_name
from dba_audit_trail
where trim(Timestamp)=trunc(sysdate)
order by 4 desc;


select username,os_username,terminal,to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name,action_name
,decode(returncode,1917,'Grant Revoke',1918,'ALter',1920,'Creation',returncode) Status
from dba_audit_exists
order by timestamp desc;

select firmnumber,oowncode as username,cclientlevelpassword as password from clemaildetail where oowncode='DP122';
SELECT username, terminal, to_char (timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS'),
decode(returncode,0,'Successful',1017,'WrongAttempt',28000,'Locked',28009,'SYS Login',1005,'Fail_NULL',28001,'EXPIRED',28031,'Roles_Exceeded',returncode) Login_Status
FROM dba_audit_session
WHERE returncode <> 0 AND not exists (SELECT 'x' FROM dba_users WHERE dba_users.username=dba_audit_session.username)
and trim(Timestamp)=trunc(sysdate)
order by timestamp desc;


select username,os_username,terminal "System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name,action_name from dba_audit_object where trim(Timestamp)=trunc(sysdate) order by timestamp desc;

select username,os_username,
UTL_INADDR.get_host_address(terminal)"System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",
obj_name,action_name from dba_audit_statement
--where
----trim(Timestamp)=trunc(sysdate)
--obj_name='UIPL6713'
order by timestamp desc;

select * from dba_stmt_audit_opts;

select * from dba_priv_audit_opts;
select * from dba_audit_object where obj_name='LDBO' order by timestamp desc;


select distinct * from TBLAUDITUSERLOGONDETAILS
where cusername not in ('SYS','CLLVL')
and cusername='LDBO'
and trim(dlogonday)=trunc(SYSDATE)
order by dlogonday desc,clogontime desc;

Select Substr(Upper(User_audit_trail.Os_Username),1,30) as cOS_Username, Rpad(' ',10) as Oowncode, Substr(User_audit_trail.Username,1,30) as cUsername,User_audit_trail.Userhost as cUserhost, Upper(Substr(User_audit_trail.Terminal,1,30)) as cTerminal,User_audit_trail.Timestamp as dTimeStamp, nvl(User_audit_trail.Obj_name,' ') as cObjectName,User_audit_trail.Action_name as cActionname, nvl(User_audit_trail.Sys_Privilege,' ') as cSysPrivilege,nvl(User_audit_trail.Grantee,' ') as cGrantee, nvl(User_audit_trail.Priv_Used,' ') as cPrivUsed From User_audit_trail User_audit_trail
Where User_audit_trail.Os_Username not in ('SYSTEM','SYSMAN','DBSNMP')
and User_audit_trail.Username='LDBO'
and trim(User_audit_trail.Timestamp)=trunc(sysdate)
order by timestamp;


Select Dba_Users.Username as cUsername,Dba_Users.Profile as cProfile, Dba_Users.Account_status as cAccountstatus,Dba_Users.Lock_date as dLockDate,Dba_Users.Expiry_Date as dExpiryDate, Dba_users.Created as dCreationDate
From Dba_Users Dba_Users
Where Dba_users.Username not in ('SYS', 'SYSTEM', 'DBSNMP', 'TSMSYS', 'OUTLN', 'ORACLE_OCM','MGMT_VIEW','SYSMAN', 'WMSYS', 'DIP')
and Dba_Users.Created>='13-MAR-09'
--and trim(dlockdate)!=' '
---and trim(dexpirydate)!=' '
and Dba_Users.Created<=trunc(sysdate) order by 5 desc, 4 desc;

-----------------------------