Auditing

#1

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!

#2

On Jul 12, 9:06*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:

Quote:

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

#3

On Jul 14, 6:11*pm, ddf <orat... (AT) msn (DOT) com> wrote:

Quote:

On Jul 12, 9:06*pm, The Magnet <a... (AT) unsu (DOT) 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?

#4

On Jul 20, 8:20*am, The Magnet <a... (AT) unsu (DOT) com> wrote:

Quote:

On Jul 14, 6:11*pm, ddf <orat... (AT) msn (DOT) com> wrote:

On Jul 12, 9:06*pm, The Magnet <a... (AT) unsu (DOT) 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. *Weare
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
SQL> set linesize 200
SQL> /

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Auditing

comp.databases.oracle.tools comp.databases.oracle.tools

Auditing - 07-12-2011 , 11:06 PM

Re: Auditing - 07-14-2011 , 06:11 PM

Re: Auditing - 07-20-2011 , 10:20 AM

Re: Auditing - 07-20-2011 , 12:52 PM