oracle10g对象审计详解,Oracle 10g审计(audit)实验

Oracle 10g审计(audit)实验 1、AUDIT 的功能 AUDIT 用于监控用户在 DATABASE 的 ACTION 2、AUDIT 的分类 SESSION:在同一个 SESSION,相同语句只产生一个 AUDIT 结果(默认) ACCESS:在同一个SESSION,每个语句产生一个 AUDIT 结果 3、启用 AUDIT (默认不启

Oracle 10g审计(audit)实验　　1、AUDIT 的功能

AUDIT 用于监控用户在 DATABASE 的 ACTION

2、AUDIT 的分类

SESSION:在同一个 SESSION,相同语句只产生一个 AUDIT 结果(默认)

ACCESS:在同一个SESSION,每个语句产生一个 AUDIT 结果

3、启用 AUDIT (默认不启用)

SQL> SHOW PARAMETER AUDIT_TRAIL

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

AUDIT_TRAIL                          STRING                            NONE

SQL> ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;

SYSTEM ALTERED.

SQL> SHUTDOWN IMMEDIATE;

DATABASE CLOSED.

DATABASE DISMOUNTED.

ORACLE INSTANCE SHUT DOWN.

SQL> STARTUP

ORACLE INSTANCE STARTED.

TOTAL SYSTEM GLOBAL AREA  171966464 BYTES

FIXED SIZE                  1279144 BYTES

VARIABLE SIZE              96471896 BYTES

DATABASE BUFFERS           71303168 BYTES

REDO BUFFERS                2912256 BYTES

DATABASE MOUNTED.

DATABASE OPENED.

SQL> SHOW PARAMETER AUDIT_TRAIL

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

AUDIT_TRAIL                          STRING                            DB

AUDIT_TRAIL 参数的取值及含义

AUDIT_TRAIL ENABLES OR DISABLES DATABASE AUDITING.

VALUES:

NONE

DISABLES DATABASE AUDITING.

?

OS

ENABLES DATABASE AUDITING AND DIRECTS ALL AUDIT RECORDS TO THE OPERATING SYSTEM'S AUDIT TRAIL.

?

DB

ENABLES DATABASE AUDITING AND DIRECTS ALL AUDIT RECORDS TO THE DATABASE AUDIT TRAIL (THE SYS.AUD$ TABLE)。

?

DB,EXTENDED

ENABLES DATABASE AUDITING AND DIRECTS ALL AUDIT RECORDS TO THE DATABASE AUDIT TRAIL (THE SYS.AUD$ TABLE)。 IN ADDITION, POPULATES THE SQLBIND AND SQLTEXT CLOB COLUMNS OF THE SYS.AUD$ TABLE.

?

XML

ENABLES DATABASE AUDITING AND WRITES ALL AUDIT RECORDS TO XML FORMAT OS FILES.

?

XML,EXTENDED

ENABLES DATABASE AUDITING AND PRINTS ALL COLUMNS OF THE AUDIT TRAIL, INCLUDING SQLTEXT AND SQLBIND VALUES.

YOU CAN USE THE SQL STATEMENT AUDIT TO SET AUDITING OPTIONS REGARDLESS OF THE SETTING OF THIS PARAMETER.

4、审计的对象：(默认情况：SESSION,对成功和不成功的同时审计)

语句审计

SQL> AUDIT TABLE;

AUDIT SUCCEEDED.

SQL> AUDIT TABLE BY SCOTT;

AUDIT SUCCEEDED.

SQL> AUDIT TABLE BY SCOTT WHENEVER SUCCESSFUL;

AUDIT SUCCEEDED.

----------查看审计设置

SQL> SELECT USER_NAME,AUDIT_OPTION FROM DBA_STMT_AUDIT_OPTS;

USER_NAME            AUDIT_OPTION

-------------------- --------------------

TABLE

SCOTT                TABLE

SQL> CONN SCOTT/TIGER

CONNECTED.

SQL> DROP TABLE RECOVER_TEST;

TABLE DROPPED.

SQL> CREATE TABLE DEPT1 AS SELECT * FROM DEPT;

TABLE CREATED.

SQL> CREATE TABLE EMP1 AS SELECT * FROM EMP;

TABLE CREATED.

SQL> ALTER TABLE DEPT1 ADD CONSTRAINT DEPTNO_PK PRIMARY KEY(DEPTNO)；

TABLE ALTERED.

SQL> ALTER TABLE EMP1 ADD CONSTRAINT EMPNO_PK PRIMARY KEY (EMPNO)；

TABLE ALTERED.

SQL> ALTER TABLE EMP1 ADD CONSTRAINT DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT1(DEPTNO)；

TABLE ALTERED.

SQL> DROP TABLE DEPT1;

DROP TABLE DEPT1

*

ERROR AT LINE 1:

ORA-02449: UNIQUE/PRIMARY KEYS IN TABLE REFERENCED BY FOREIGN KEYS

SQL> DROP TABLE DEPT1 CASCADE CONSTRAINT PURGE;

TABLE DROPPED.

SQL> CREATE USER TOM IDENTIFIED BY TOM;

SQL> CONN / AS SYSDBA

CONNECTED.

SQL> GRANT CREATE SESSION,RESOURCE TO TOM;

GRANT SUCCEEDED.

SQL> CONN TOM/TOM

CONNECTED.

SQL> CREATE TABLE TEST(ID NUMBER(2))；

TABLE CREATED.

SQL> DROP TABLE TEST;

TABLE DROPPED

SQL> CONN / AS SYSDBA

CONNECTED.

SQL> CONN / AS SYSDBA

CONNECTED.

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS';

SESSION ALTERED.

SQL> DESC DBA_AUDIT_TRAIL;

NAME                                      NULL?    TYPE

----------------------------------------- -------- ----------------------------

OS_USERNAME                                        VARCHAR2(255)

USERNAME                                           VARCHAR2(30)

USERHOST                                           VARCHAR2(128)

TERMINAL                                           VARCHAR2(255)

TIMESTAMP                                          DATE

OWNER                                              VARCHAR2(30)

OBJ_NAME                                           VARCHAR2(128)

ACTION                                    NOT NULL NUMBER

ACTION_NAME                                        VARCHAR2(28)

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)

ECONTEXT_ID                                        VARCHAR2(64)

SESSION_CPU                                        NUMBER

EXTENDED_TIMESTAMP                                 TIMESTAMP(6) WITH TIME ZONE

PROXY_SESSIONID                                    NUMBER

GLOBAL_UID                                         VARCHAR2(32)

INSTANCE_NUMBER                                    NUMBER

OS_PROCESS                                         VARCHAR2(16)

TRANSACTIONID                                      RAW(8)

SCN                                                NUMBER

SQL_BIND                                           NVARCHAR2(2000)

SQL_TEXT                                           NVARCHAR2(2000)

SQL> SELECT USERNAME,USERHOST,TIMESTAMP,OBJ_NAME,ACTION_NAME FROM DBA_AUDIT_TRAIL;

USERNAME             USERHOST             TIMESTAMP            OBJ_NAME             ACTION_NAME

-------------------- -------------------- -------------------- -------------------- ---------------

TOM                  SUN10G               20120915 09:55:14    TEST                 CREATE TABLE

SCOTT                SUN10G               20120914 23:21:24    EMP1                 CREATE TABLE

SCOTT                SUN10G               20120914 23:21:01    DEPT1                CREATE TABLE

TOM                  SUN10G               20120915 09:55:26    TEST                 DROP TABLE

SCOTT                SUN10G               20120915 09:51:23    DEPT1                DROP TABLE

SCOTT                SUN10G               20120915 09:50:57    DEPT1                DROP TABLE

SCOTT                SUN10G               20120914 23:06:26    RECOVER_TEST         DROP TABLE

-------------审计结果存放在名为 AUD$ 的基表中，可以通过 DBA_AUDIT_TRAIL 视图查询

SQL> SELECT COUNT(*) FROM AUD$;

COUNT(*)

----------

7

SQL> DESC AUD$;

--------------删除审计结果

SQL> DELETE FROM AUD$;

7 ROWS DELETED.

SQL> COMMIT;

COMMIT COMPLETE.

--------------关闭审计

SQL> NOAUDIT TABLE;

NOAUDIT SUCCEEDED.

2、权限审计

SQL> AUDIT CREATE TABLE;

AUDIT SUCCEEDED.

SQL> AUDIT SELECT ANY TABLE, CREATE ANY TRIGGER;

AUDIT SUCCEEDED.

SQL> AUDIT SELECT ANY TABLE BY HR BY SESSION;

AUDIT SUCCEEDED.

SQL> CONN SCOTT/TIGER

CONNECTED.

SQL> SELECT TABLE_NAME FROM USER_TABLES;

TABLE_NAME

------------------------------------------------------------------------------------------

DEPT

EMP

BONUS

SALGRADE

EMP1

SQL> CREATE TABLE DEPT1 AS SELECT * FROM DEPT;

TABLE CREATED.

SQL> CONN / AS SYSDBA

CONNECTED.

-------- CREATE TABLE 操作审计