SQL> CONN /AS SYSDBA;
Connected.
SQL>
SQL> @ 'E:\oracle\ora92\rdbms\admin\cataudit.sql';
Comment created.
Comment created.
View created.
Synonym created.
Grant succeeded.
Comment created.
View created.
Comment created.
Synonym created.
Grant succeeded.
View created.
Synonym created.
Grant succeeded.
Comment created.
View created.
Synonym created.
Grant succeeded.
Comment created.
View created.
Synonym created.
Grant succeeded.
Comment created.
View created.
Comment created.
Synonym created.
Grant succeeded.
View created.
Synonym created.
SQL> SHOW PARAMETER AUDIT_TRAIL;
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
audit_trail string NONE
SQL> ALTER SYSTEM SET AUDIT_TRAIL=TRUE SCOPE=SPFILE;
System altered.
SQL> AUDIT TABLE;
Audit succeeded.
SQL> AUDIT TABLE BY ACCESS;
Audit succeeded.
SQL> SHUTDOWN;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>EXIT
============================================================================================
DBA_AUDIT_TRAIL
==========================================================================================
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jan 28 13:15:04 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> CONN SCOTT/TIGER;
Connected.
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL> DROP TABLE SALGRADE;
Table dropped.
SQL> DROP TABLE SCOTT.BONUS;
Table dropped.
SQL> CONN SCOTT/TIGER;
Connected.
SQL> UPDATE EMP SET SAL=5000 WHERE ENAME='FORD';
1 row updated.
SQL> GRANT DBA TO MOON;
Grant succeeded.
SQL> CONN MOON/KUMAR;
Connected.
SQL> SELECT * FROM SCOTT.EMP;
SQL> CONN /AS SYSDBA;
Connected.
SQL> DESC DBA_AUDIT_TRAIL;
Name Null? Type
----------------------------------------- -------- ----------------------------
OS_USERNAME VARCHAR2(255)
USERNAME VARCHAR2(30)
USERHOST VARCHAR2(128)
TERMINAL VARCHAR2(255)
TIMESTAMP NOT NULL DATE
OWNER VARCHAR2(30)
OBJ_NAME VARCHAR2(128)
ACTION NOT NULL NUMBER
ACTION_NAME VARCHAR2(27)
NEW_OWNER VARCHAR2(30)
NEW_NAME VARCHAR2(128)
OBJ_PRIVILEGE VARCHAR2(16)
SYS_PRIVILEGE VARCHAR2(40)
ADMIN_OPTION VARCHAR2(1)
GRANTEE VARCHAR2(30)
AUDIT_OPTION VARCHAR2(40)
SES_ACTIONS VARCHAR2(19)
LOGOFF_TIME DATE
LOGOFF_LREAD NUMBER
LOGOFF_PREAD NUMBER
LOGOFF_LWRITE NUMBER
LOGOFF_DLOCK VARCHAR2(40)
COMMENT_TEXT VARCHAR2(4000)
SESSIONID NOT NULL NUMBER
ENTRYID NOT NULL NUMBER
STATEMENTID NOT NULL NUMBER
RETURNCODE NOT NULL NUMBER
PRIV_USED VARCHAR2(40)
CLIENT_ID VARCHAR2(64)
SESSION_CPU NUMBER
SQL>
SQL> COLUMN USERNAME FORMAT A15;
SQL> COLUMN UOWNER FORMAT A15;
SQL> COLUMN ACTION_NAME FORMAT A15;
SQL> SELECT USERNAME,OWNER,OBJ_NAME,ACTION_NAME,ACTION FROM DBA_AUDIT_TRAIL;
USERNAME OWNER OBJ_NAME ACTION_NAME ACTION
--------------- --------------- ---------- --------------- ----------
SCOTT SCOTT SALGRADE DROP TABLE 12
MOON SCOTT BONUS DROP TABLE 12
MOON SCOTT EMP SESSION REC 103
SCOTT SCOTT EMP SESSION REC 103
=============================================================================================
DBA_STMT_AUDIT_OPTS [ AUDITING OPTION ]
============================================================================================
SQL> AUDIT CREATE ANY TABLE;
Audit succeeded.
SQL> AUDIT CREATE ANY TRIGGER;
Audit succeeded.
SQL> AUDIT SELECT ON SCOTT.EMP;
Audit succeeded.
SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- ----------- -------------------- ---------- ----------
TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TRIGGER BY ACCESS BY ACCESS
SQL> audit select any table by hr by session;
Audit succeeded.
SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
---------- ---------- -------------------- ---------- ----------
CREATE SESSION NOT SET BY ACCESS
TABLE BY ACCESS BY ACCESS
HR SELECT ANY TABLE BY SESSION BY SESSION
SELECT ANY TABLE BY SESSION BY SESSION
SQL>
============================================================================================
DBA_PRIV_AUDIT_OPTS
============================================================================================
SQL> SELECT * FROM DBA_PRIV_AUDIT_OPTS;
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
-------------------- ----------- ------------------ ---------- ----------
CREATE ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TRIGGER BY ACCESS BY ACCESS
============================================================================================
DBA_AUDIT_OBJECT
============================================================================================
SQL> SELECT USERNAME,OWNER,OBJ_NAME,ACTION_NAME FROM DBA_AUDIT_OBJECT;
USERNAME OWNER OBJ_NAME ACTION_NAME
--------------- --------------- ---------- ---------------
SCOTT SCOTT SALGRADE DROP TABLE
MOON SCOTT BONUS DROP TABLE
MOON SCOTT EMP SESSION REC
SCOTT SCOTT EMP SESSION REC
=====================================================================================
DBA_OBJ_AUDIT_OPTS
============================================================================================
SQL> AUDIT ALL ON HR.EMPLOYEES;
Audit succeeded.
SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,INS,UPD FROM DBA_OBJ_AUDIT_OPTS WHERE OBJECT_NAME ='EMPLOYEES';
OWNER OBJECT_NAME OBJECT_TYPE INS UPD
-------------------- ------------------------------ ----------------- --- ---
HR EMPLOYEES TABLE S/S S/S
No comments:
Post a Comment