AUDITING ALL

CREATE TABLE emp (
 empno     NUMBER(4) NOT NULL,
 ename     VARCHAR2(10),
 job       VARCHAR2(9),
 mgr       NUMBER(4),
 hiredate  DATE,
 sal       NUMBER(7,2),
 comm      NUMBER(7,2),
 deptno    NUMBER(2)
);


INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1);
INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Larry', 50001);
COMMIT;


CONN sys/password AS sysdba

BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'HR',
    object_name     => 'EMP',
    policy_name     => 'SALARY_CHK_AUDIT',
    audit_condition => 'SAL > 50000',
    audit_column    => 'SAL');
END;

CONN audit_test/password
SELECT sal FROM emp WHERE ename = 'Tim';
SELECT sal FROM emp WHERE ename = 'Larry';

CONN sys/password AS SYSDBA
SELECT sql_text
FROM   dba_fga_audit_trail;

SQL_TEXT
------------------------------------------
SELECT sal FROM emp WHERE ename = 'Larry'

1 row selected.



========================================================
create table audit_employees(username varchar2(22),change_time timestamp,Ip_add varchar2(22), changable_values varchar2(60));


CREATE OR REPLACE TRIGGER sys.hrsalary_audit
AFTER UPDATE OF salary
ON hr.employees
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :old.salary != :new.salary THEN
INSERT INTO sys.audit_employees
VALUES (sys_context('userenv','os_user'), sysdate,
sys_context('userenv','ip_address'),
:new.employee_id ||
' salary changed from '||:old.salary||
' to '||:new.salary);
END IF;
END;


================================
=====================
show parameter audit_trail

show parameter AUDIT_SYS_OPERATIONS
--------------------------------------------------
@c:\oracle\ora92\RDBMS\ADMIN\cataudit.sql
----------------------------------------------------
ALTER SYSTEM SET AUDIT_TRAIL=true SCOPE=SPFILE;
-------------------------------------------------------
SELECT * FROM DBA_OBJ_AUDIT_OPTS
WHERE OWNER = 'HR' AND OBJECT_NAME LIKE 'EMPLOYEES';

-----------------------------------------------------------
AUDIT SELECT, INSERT, DELETE,UPDATE
ON hr.employees
BY ACCESS
WHENEVER SUCCESSFUL;
-----------------------------------------------------------
conn to hr and do some task about SELECT, INSERT, DELETE,UPDATE

-----------------------------------------------------------
conn again sys as sysdba
and fire the command

select * from aud$;

in this command u shd note the action# no and fire the given command and u can trace the user task

select * from audit_actions
where action=3;
/

============
==============================
Define a simple policy called DUP_ACCESS:

BEGIN
  DBMS_FGA.ADD_POLICY(
         OBJECT_SCHEMA => 'SCOTT',
         OBJECT_NAME   => 'EMP',
         POLICY_NAME   => 'DUP_ACCESS');
END;
/

Another example:

Define policy named FGA_EMP to audit the DBA's (oracle user) attempts to access to salary (emp.sal column) information:

SQL> exec DBMS_FGA.ADD_POLICY( -
>         object_schema   => 'SCOTT', -
>         object_name     => 'EMP', -
>         policy_name     => 'FGA_EMP', -
>         audit_column    => 'SAL', -
>         audit_condition => 'SYS_CONTEXT(USERENV,OS_USER) = oracle ', -
>         statement_types => 'SELECT', -
>         audit_trail     => DBMS_FGA.DB);
PL/SQL procedure successfully completed.

Sample select to violate the policy:

SQL> SELECT sal FROM EMP WHERE rownum < 1;
no rows selected

Query audit trail:

SQL> SELECT policy_name, object_name, statement_type, os_user, db_user FROM dba_fga_audit_trail;
POLICY_NAM OBJECT_NAM STATEME OS_USER    DB_USER
---------- ---------- ------- ---------- ----------
FGA_EMP    EMP        SELECT  oracle     SCOTT

Remove FGA policy:

SQL> exec DBMS_FGA.DROP_POLICY('SCOTT', 'EMP', 'FGA_EMP');
PL/SQL procedure successfully completed.

Clear FGA audit trail:

SQL> TRUNCATE TABLE fga_log$;
Table truncated.
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Auditing / FGA
Server Setup:-

To Enable:-

audit_trail = none/false/os/db/true/db,extended/xml/xml,extended

To disable:-

audit_trail = none/false

db/true - Auditing i enabled and audit records are stored in AUD$.

db,extended - Auditing records are stored in AUD$ and two more columns sql_bind, sql_text are included.

xml - Auditing records are stored as xml files in auidt_file_dest directory.

xml,extended - with sql_bin and sql_text.

os - Auditing records are stored in audit_file_dest.

audit_sys_operations = true/false -> If set as true then it will also audit the operations perform by sysdba/sysoper and sys use on the database. If false then it will audit only other user.

Eg:-

> alter system set audit_trail = db scope=spfile;
> shut immediate;
> startup;
> audit all by manzoor by access; -- where manzoor is username.
> audit select table , update table, delete table, insert table by
manzoor by access;
> audit execute procedure by manzoor by access;


The audit trail records are stored in AUD$, you can query the result using the dba_views.

dba_audit_trail -> standard auditing AUD$
dba_fga_audit_trail -> Fine grained Auditing FGA_LOG$
dba_common_audit_trail -> Both standard and FGA
V$xml_audit_trail -> if xml or xml,extended

Maintenance:-

Auditing should be planned carefully to control the quantity of audit information. Only audit specific operations or objects of interest.

The database audit trail must be deleted or archived on a regular basis to prevent the sys.aud$ table growing to a unacceptable size. Only dba should have maintenance access to the audit trail. Auditing modifications of the data in the audit trail itself can be achieved using the following statement.

> audit insert, delete, select, update on sys.aud$ by access;

The os audit files should be secured at the os level by assigning the correct file permission.

Fine Grained Auditing

Fine grained auditing extends orcale standard auditing capabilities by allowing the user to audit action based on user_defined predicates. It is independent of the audit_trail parameter. The FGA records are stored in FA_LOG$ table.

begin
dbms_fga.add_policy
(
object_schema => 'MANZOOR',
object_name => 'EMP',
policy_name => 'FGAEXP',
audit_condition => 'SAL > 50000',
audit_column => 'SAL'
);
end;
/

Here we have created a fga policy to audit if any users executes a query to list the rows and if those rows contains the sal > 50000 then it will get recorded in the fga_log$ and can be viewed through the dba_fga_audit_trail column.

SQL> select * from emp;

EMPNO NAME SAL
---------- ------------------------------ ----------
1 Anif 40000
2 Jameel 50000
3 Kannan 60000

SQL> select * from emp where name = 'Jameel';

EMPNO NAME SAL
---------- ------------------------------ ----------
2 Jameel 50000

SQL> select * from emp where name = 'Anif';

EMPNO NAME SAL
---------- ------------------------------ ----------
2 Jameel 40000


SQL> select sql_text from dba_fga_audit_trail;

SQL_TEXT
-----------------------------------------
select * from emp
select * from emp where name = 'Kannan'

Here we can see that the query "select * from emp where name = 'Anif'" is not get
listed in the dba_fga_audit_trail view because the salary is less than 50000.

Auditing DML Operations:

begin
dbms_fga.add_policy
(
OBJECT_SCHEMA => 'MANZOOR',
OBJECT_NAME => 'EMP',
POLICY_NAME => 'FGA_DML',
AUDIT_CONDITION => NULL,
AUDIT_COLUMN => 'SAL',
STATEMENT_TYPES => 'SELECT,INSERT,UPDATE,DELETE'
);
end;
/

SQL> select sql_text from dba_fga_audit_trail;


SQL_TEXT
-----------------------------------------------
select * from emp where name = 'Anif'
insert into emp values (4,'Sahrmi',55555)
update emp set sal = 45000 where name = 'Anif'
delete from emp where sal=45000

here whenever the sal column is being affected by any DML operations it will be audited.