Oracle audit Experiment

Oracle audit lab audit queries what operations a user performs on a table, such as select, delete, insert and so on 1. Open the audit function www.2cto.com SQL> show parameter auditNAME TYPE VALUE =----------- audit audit_file_dest string/u1/oracle/product/10.2.0/db_1/rdbms/audit boolean audit stringaudit_trail string NONE. disabled: audit_trail: none indicates that the audit function has not enabled db activation audit function, audit information and connection information user, t Ime, object... db_extented connection information and the SQL account opening audit function of the current operation: www.2cto.com SQL> alter system set audit_trail = db_extended scope = spfile; System altered. restart the database: SQL> startup force: Check whether the audit function is enabled again: audit_trail string DB_EXTENDED !! 2. Set the table to be audited and operate SQL> audit all on scott. emp by access; audit the scott user's emp table. We will enable another session to modify, query, and delete the scott user's emp table. [oracle @ oracle ~] $ Export ORACLE_SID = cy [oracle @ oracle ~] $ Sqlplus scott/tigerSQL> select * from emp; SQL> delete from emp where ename = 'miller '; below we check whether the audit of SQL> desc dba_audit_trail content are in this view below SQL> select USERNAME, USERHOST, TIMESTAMP, SQL _TEXT FROM dba_audit_trail; USERNAME------------------------------USERHOST--------------------------------------------------------------------------------TIMESTAMP---------SQL_TEXT ------------------------------------------. We can also query the specific execution time: SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss '; query the SQL> select * from emp again in another session. We find that the preceding statements are not good-looking, let's set SQL> col username format a30SQL> col userhost format a30SQL> col timestamp format a30SQL> col SQL _text format a30SQL> set pagesize 60SQL>/username userhost ##timestamp SQL _TEXT ----------------------------------- ------------------------- SCOTT oracle. somnus. com2011-09-22 19:33:32 select * from empSCOTT oracle. somnus. com2011-09-22 19:33:48 delete from emp where ename = 'miller 'SCOTT oracle. somnus. com2011-09-22 19:40:49 select * from emp now we find the specific time for the row-sweeping operation SQL> audit all on scott. emp by access; audit all SQL statements> audit insert, update, delete on scott. dept by access; only audit the insert, update, and delete operations on the dept table SQL> audit select, delete on scott. dept whe Never not successful; audit the query of the dept table, whether or not it is successfully audit all on scott. emp whenever successful; audits all operations on the emp table, only when the operation is successful noaudit all on scott. dept; cancel audit SQL> audit select, delete on scott. dept whenever not successful; SQL> select * from scott. emp; select * from scott. emp * ERROR at line 1: ORA-00942: table or view does not existSQL> select USERNAME, USERHOST, TIMESTAMP, SQL _TEXT FROM dba_audit_trail; USERNAME USERHOST- ----------------------------- -------------------------------- TIMESTAMP SQL _TEXT ------------------------------ -------------------------- SCOTT oracle. somnus. com2011-09-22 19:33:32 select * from empSCOTT oracle. somnus. com2011-09-22 19:33:48 delete from emp where ename = 'miller 'SCOTT oracle. somnus. com2011-09-22 19:40:49 select * from empGZ oracle. somnus. com2011-09-22 19:55:15 can be found that although the query was not successful, but still audited. SQL> audit all on scott. emp whenever successful; SQL> select * from scott. emp; select * from scott. emp * ERROR at line 1: ORA-00942: table or view does not existSQL> grant select on scott. emp to gz; Grant succeeded. SQL> select USERNAME, USERHOST, TIMESTAMP, SQL _TEXT FROM dba_audit_trail; GZ oracle. somnus. com2011-09-22 19:57:38 select * from scott. empGZ oracle. somnus. com2011-09-22 19:58:39 select * from scott. Emp can find that only records of successful queries are recorded !!!! Iii. More subtle audit SQL> desc dbms_fga this is the parameter view for adding policies PROCEDURE ADD_POLICY we add a policy SQL> exec dbms_fga.add_policy (OBJECT_SCHEMA => 'Scott ', OBJECT_NAME => 'emp ',
POLICY _
NAME => 'demo _ emp'); PL/SQL procedure successfully completed. this statement adds a scheme object scott, the scheme name emp, and the Policy Name demo_emp,
Demo_emp policy SQL> desc dba_fga_audit_trail of the emp table under scott; this is the view SQL of the audit content> select timestamp, db_user, userhost, SQL _text from dba_fga_audit_trail; no rows selected no query records now. We will query the emp table in another session. SQL> select * from scott. emp; query audit record again SQL> select timestamp, db_user, userhost, SQL _text from dba_fga_audit_trail; TIMESTAMP DB_USER login Login login USERHOST SQL _TEXT login Login 20:16:09 GZoracle.somnus.com select * from scott. emp has been audited. We found that the current audit is the same as the General Audit. Next we will perform a more detailed audit: SQL> exec dbms_fga.add_policy (OBJECT_SCHEMA => 'Scott ', OBJECT_NAME => 'emp ',
POLICY_NAME
=> 'Demo2 _ emp', AUDIT_COLUMN => 'sal, comm ', STATEMENT_TYPES => 'select, insert,
Update, delete '); PL/SQL procedure successfully completed. This is based on the previous rule, that is, only audit the select, insert,
Update and delete operations because we have added a policy such as demo_emp, which conflicts with the new one. We first disable the first SQL> exec dbms_fga.disable_policy (OBJECT_SCHEMA => 'Scott ', OBJECT_NAME =
> 'Emp', POLICY _ NAME => 'demo _ emp') This is a forbidden POLICY. SQL> exec dbms_fga.able_policy (OBJECT_SCHEMA => 'Scott ', OBJECT_NAME =
> 'Emp', POLICY _ NAME => 'demo _ emp') This is the enabling policy SQL> exec dbms_fga.drop_policy (OBJECT_SCHEMA => 'Scott ', OBJECT_NAME =
> 'Emp', POLICY _ NAME => 'demo _ emp') This is the deletion POLICY. We operate on another session: www.2cto.com SQL> select deptno from scott. emp; DEPTNO ---------- 20 30SQL> select comm from scott. emp; COMM ---------- 300 500 1400 let's look at the audit records again: SQL> select timestamp, db_user, userhost, SQL _text from dba_fga_audit_trail; TIMESTAMP DB_USER login Login USERHOST SQL _TEXT2011-09-22 20:40:04 GZoracle.somnus.com Ect comm from scott. emp we found that only records of the comm column are queried. To some extent, we found that the subtle audit function has been used! Success !!