Re: Auditing

On Jul 20, 8:20 am, The Magnet <a..._at_unsu.com> wrote:
> On Jul 14, 6:11 pm, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On Jul 12, 9:06 pm, The Magnet <a..._at_unsu.com> wrote:
>
> > > I'm looking to audit DML & Selects on a single table in a given
> > > schema.  I'm not looking to audit everything in the database.  We are
> > > on 10g R2.  I set the AUDIT_TRAIL parameter to DB,EXTENDED in the
> > > init.ora, but it audits everything.  Even if I enter a ton of NOAUDIT
> > > commands, it continues to audit.
>
> > > I'm not interested in that.  I'm only interested in the one table.
> > > What or how must I set the parameters to do this?  I see lots of
> > > auditing options, but none that are as narrow as I want.
>
> > > Thanks!
>
> > This should work for you:
>
> > SQL> audit select, insert, update, delete on emp;
>
> > Audit succeeded.
>
> > SQL>
> > SQL> select sessionid, userid, sqltext from sys.aud$;
>
> > no rows selected
>
> > SQL>
> > SQL> select * from emp;
>
> >      EMPNO ENAME      JOB              MGR HIREDATE         SAL
> > COMM     DEPTNO
> > ---------- ---------- --------- ---------- --------- ----------
> > ---------- ----------
> >       7369 SMITH      CLERK           7902 17-DEC-80
> > 800                    20
> >       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600
> > 300         30
> >       7521 WARD       SALESMAN        7698 22-FEB-81       1250
> > 500         30
> >       7566 JONES      MANAGER         7839 02-APR-81
> > 2975                    20
> >       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250
> > 1400         30
> >       7698 BLAKE      MANAGER         7839 01-MAY-81
> > 2850                    30
> >       7782 CLARK      MANAGER         7839 09-JUN-81
> > 2450                    10
> >       7788 SCOTT      ANALYST         7566 09-DEC-82
> > 3000                    20
> >       7839 KING       PRESIDENT            17-NOV-81
> > 5000                    10
> >       7844 TURNER     SALESMAN        7698 08-SEP-81
> > 1500          0         30
> >       7876 ADAMS      CLERK           7788 12-JAN-83
> > 1100                    20
>
> >      EMPNO ENAME      JOB              MGR HIREDATE         SAL
> > COMM     DEPTNO
> > ---------- ---------- --------- ---------- --------- ----------
> > ---------- ----------
> >       7900 JAMES      CLERK           7698 03-DEC-81
> > 950                    30
> >       7902 FORD       ANALYST         7566 03-DEC-81
> > 3000                    20
> >       7934 MILLER     CLERK           7782 23-JAN-82
> > 1300                    10
>
> > 14 rows selected.
>
> > SQL>
> > SQL> select sessionid, userid, sqltext from sys.aud$;
>
> >  SESSIONID USERID                         SQLTEXT
> > ---------- ------------------------------
> > --------------------------------------------------------------------------- -----
> >       2281 BING                           select * from emp
>
> > SQL>
>
> > David Fitzjarrell
>
> But, if I set the AUDIT parameter in my init.ora it audits
> everything.  Is there a way to basically turn everything off except
> what I want to audit?- Hide quoted text -
>
> - Show quoted text -

I'm not finding that to be true:

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
audit_file_dest                      string      C:\ORACLEXE\APP\ORACLE
\ADMIN\X
                                                 E\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB_EXTENDED

 SESSIONID USERID                         SQLTEXT
---------- ------------------------------
--------------------------------------------------------------------------------
      2281 BING                           select * from emp

SQL> It appears the only audit information I have given the settings shown is for the EMP table I explicitly audited. This is on 10.2.0.3; I can't test this on 11.2 yet.

David Fitzjarrell Received on Wed Jul 20 2011 - 19:52:12 CEST