敗家貓

Oracle_9i_AUDIT_SOP
測試筆記

使用sys登入

@$ORACLE_HOME/rdbms/admin/cataudit.sql;

因為預設是放於 system 這個 tablespace 上面，所以進行搬移的動作

--建立 tablespace (存放地方依情況改變) CREATE TABLESPACE audit_tab DATAFILE   '/storage1/u11/db/oradata/impdb/audit_tab.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M DROP TABLE SYS.AUD$ CASCADE CONSTRAINTS; CREATE TABLE SYS.AUD$ (   SESSIONID        NUMBER                       NOT NULL,   ENTRYID          NUMBER                       NOT NULL,   STATEMENT        NUMBER                       NOT NULL,   TIMESTAMP#       DATE                         NOT NULL,   USERID           VARCHAR2(30 BYTE),   USERHOST         VARCHAR2(128 BYTE),   TERMINAL         VARCHAR2(255 BYTE),   ACTION#          NUMBER                       NOT NULL,   RETURNCODE       NUMBER                       NOT NULL,   OBJ$CREATOR      VARCHAR2(30 BYTE),   OBJ$NAME         VARCHAR2(128 BYTE),   AUTH$PRIVILEGES  VARCHAR2(16 BYTE),   AUTH$GRANTEE     VARCHAR2(30 BYTE),   NEW$OWNER        VARCHAR2(30 BYTE),   NEW$NAME         VARCHAR2(128 BYTE),   SES$ACTIONS      VARCHAR2(19 BYTE),   SES$TID          NUMBER,   LOGOFF$LREAD     NUMBER,   LOGOFF$PREAD     NUMBER,   LOGOFF$LWRITE    NUMBER,   LOGOFF$DEAD      NUMBER,   LOGOFF$TIME      DATE,   COMMENT$TEXT     VARCHAR2(4000 BYTE),   CLIENTID         VARCHAR2(64 BYTE),   SPARE1           VARCHAR2(255 BYTE),   SPARE2           NUMBER,   OBJ$LABEL        RAW(255),   SES$LABEL        RAW(255),   PRIV$USED        NUMBER,   SESSIONCPU       NUMBER ) TABLESPACE audit_tab ; CREATE INDEX SYS.I_AUD1 ON SYS.AUD$ (SESSIONID, SES$TID) LOGGING TABLESPACE audit_tab ; GRANT DELETE ON SYS.AUD$ TO DELETE_CATALOG_ROLE;

建立 audit 的目錄，預設是放於  ?/rdbms/audit ，我是跟 db 的 log 檔放於同一層

[oracle@traindb ~]$ cd $ORACLE_BASE [oracle@traindb oracle]$ cd admin/ [oracle@traindb admin]$ cd impdb/ [oracle@traindb impdb]$ ls bdump  cdump  create  pfile  udump [oracle@traindb impdb]$ mkdir audit [oracle@traindb impdb]$ chmod 775 audit/ [oracle@traindb impdb]$ cd audit/ [oracle@traindb audit]$ pwd /opt/app/oracle/admin/impdb/audit

使用 sys 設定開起 audit 相關的設定

alter system set audit_trail=DB scope=spfile; alter system set audit_sys_operations = true scope=spfile; alter system set audit_file_dest='/opt/app/oracle/admin/impdb/audit' scope=spfile;

重起 db

shutdown immediate; startup ;

設定要 audit 的範圍

audit ALTER ANY CLUSTER; audit ALTER ANY DIMENSION; audit ALTER ANY INDEX; audit ALTER ANY INDEXTYPE; audit ALTER ANY LIBRARY; audit ALTER ANY OUTLINE; audit ALTER ANY PROCEDURE; audit ALTER ANY ROLE; audit ALTER ANY SEQUENCE; audit ALTER ANY SNAPSHOT; audit ALTER ANY TABLE; audit ALTER ANY TRIGGER; audit ALTER ANY TYPE; audit ALTER DATABASE; audit ALTER PROFILE; audit ALTER RESOURCE COST; audit ALTER ROLLBACK SEGMENT; audit ALTER SESSION; audit ALTER SYSTEM; audit ALTER TABLE; audit ALTER TABLESPACE; audit ALTER USER; audit ANALYZE ANY; audit AUDIT ANY; audit AUDIT SYSTEM; audit BACKUP ANY TABLE; audit BECOME USER; audit COMMENT ANY TABLE; audit CREATE ANY CLUSTER; audit CREATE ANY CONTEXT; audit CREATE ANY DIMENSION; audit CREATE ANY DIRECTORY; audit CREATE ANY INDEX; audit CREATE ANY INDEXTYPE; audit CREATE ANY LIBRARY; audit CREATE ANY MATERIALIZED VIEW; audit CREATE ANY OPERATOR; audit CREATE ANY OUTLINE; audit CREATE ANY PROCEDURE; audit CREATE ANY SEQUENCE; audit CREATE ANY SNAPSHOT; audit CREATE ANY SYNONYM; audit CREATE ANY TABLE; audit CREATE ANY TRIGGER; audit CREATE ANY TYPE; audit CREATE ANY VIEW; audit CREATE CLUSTER; audit CREATE DATABASE LINK; audit CREATE DIMENSION; audit CREATE INDEXTYPE; audit CREATE LIBRARY; audit CREATE MATERIALIZED VIEW; audit CREATE OPERATOR; audit CREATE PROCEDURE; audit CREATE PROFILE; audit CREATE PUBLIC DATABASE LINK; audit CREATE PUBLIC SYNONYM; audit CREATE ROLE; audit CREATE ROLLBACK SEGMENT; audit CREATE SEQUENCE; audit CREATE SESSION; audit CREATE SNAPSHOT; audit CREATE SYNONYM; audit CREATE TABLE; audit CREATE TABLESPACE; audit CREATE TRIGGER; audit CREATE TYPE; audit CREATE USER; audit CREATE VIEW; audit DELETE ANY TABLE; audit DROP ANY CLUSTER; audit DROP ANY CONTEXT; audit DROP ANY DIMENSION; audit DROP ANY DIRECTORY; audit DROP ANY INDEX; audit DROP ANY INDEXTYPE; audit DROP ANY LIBRARY; audit DROP ANY MATERIALIZED VIEW; audit DROP ANY OPERATOR; audit DROP ANY OUTLINE; audit DROP ANY PROCEDURE; audit DROP ANY ROLE; audit DROP ANY SEQUENCE; audit DROP ANY SNAPSHOT; audit DROP ANY SYNONYM; audit DROP ANY TABLE; audit DROP ANY TRIGGER; audit DROP ANY TYPE; audit DROP ANY VIEW; audit DROP PROFILE; audit DROP PUBLIC DATABASE LINK; audit DROP PUBLIC SYNONYM; audit DROP ROLLBACK SEGMENT; audit DROP TABLESPACE; audit DROP USER; audit DROP PROFILE; audit DROP PUBLIC DATABASE LINK; audit DROP PUBLIC SYNONYM; audit DROP ROLLBACK SEGMENT; audit DROP TABLESPACE; audit DROP USER; audit EXECUTE ANY INDEXTYPE; audit EXECUTE ANY LIBRARY; audit EXECUTE ANY OPERATOR; audit EXECUTE ANY PROCEDURE; audit EXECUTE ANY TYPE; audit FORCE ANY TRANSACTION; audit FORCE TRANSACTION; audit GLOBAL QUERY REWRITE; audit GRANT ANY PRIVILEGE; audit GRANT ANY ROLE; audit LOCK ANY TABLE; audit MANAGE TABLESPACE; audit QUERY REWRITE; audit RESTRICTED SESSION; audit SESSION;

我是建立個只能查看 audit 相關資訊的 user ，請參考

CREATE USER PNADMIN   IDENTIFIED BY VALUES 'BB720BE989C82955'   DEFAULT TABLESPACE USERS   TEMPORARY TABLESPACE TEMP   PROFILE DEFAULT   ACCOUNT UNLOCK;   -- 2 Roles for PNADMIN   GRANT CONNECT TO PNADMIN;   GRANT RESOURCE TO PNADMIN;   ALTER USER PNADMIN DEFAULT ROLE NONE;   -- 2 System Privileges for PNADMIN   GRANT CREATE SESSION TO PNADMIN;   GRANT UNLIMITED TABLESPACE TO PNADMIN;   -- 1 Tablespace Quota for PNADMIN   ALTER USER PNADMIN QUOTA UNLIMITED ON USERS;   -- 1 Object Privilege for PNADMIN     GRANT SELECT ON SYS.DBA_AUDIT_TRAIL TO PNADMIN; --這個一定要給

測試方法

select count(*) from dba_audit_trail; --應該會有幾筆

維護 :

1、因為記錄 audit 是記錄於 SYS.AUD$，所以可以使用 imp/dump 、 truncate 進行重整 2、注意 audit 目錄所暫的空間跟 SYS.AUD$  table 的空間使用，可定時刪除或備份

附記:

取消 audit 的指令 noaudit ALTER ANY CLUSTER; noaudit ALTER ANY DIMENSION; noaudit ALTER ANY INDEX; noaudit ALTER ANY INDEXTYPE; noaudit ALTER ANY LIBRARY; noaudit ALTER ANY OUTLINE; noaudit ALTER ANY PROCEDURE; noaudit ALTER ANY ROLE; noaudit ALTER ANY SEQUENCE; noaudit ALTER ANY SNAPSHOT; noaudit ALTER ANY TABLE; noaudit ALTER ANY TRIGGER; noaudit ALTER ANY TYPE; noaudit ALTER DATABASE; noaudit ALTER PROFILE; noaudit ALTER RESOURCE COST; noaudit ALTER ROLLBACK SEGMENT; noaudit ALTER SESSION; noaudit ALTER SYSTEM; noaudit ALTER TABLE; noaudit ALTER TABLESPACE; noaudit ALTER USER; noaudit ANALYZE ANY; noaudit AUDIT ANY; noaudit AUDIT SYSTEM; noaudit BACKUP ANY TABLE; noaudit BECOME USER; noaudit COMMENT ANY TABLE; noaudit CREATE ANY CLUSTER; noaudit CREATE ANY CONTEXT; noaudit CREATE ANY DIMENSION; noaudit CREATE ANY DIRECTORY; noaudit CREATE ANY INDEX; noaudit CREATE ANY INDEXTYPE; noaudit CREATE ANY LIBRARY; noaudit CREATE ANY MATERIALIZED VIEW; noaudit CREATE ANY OPERATOR; noaudit CREATE ANY OUTLINE; noaudit CREATE ANY PROCEDURE; noaudit CREATE ANY SEQUENCE; noaudit CREATE ANY SNAPSHOT; noaudit CREATE ANY SYNONYM; noaudit CREATE ANY TABLE; noaudit CREATE ANY TRIGGER; noaudit CREATE ANY TYPE; noaudit CREATE ANY VIEW; noaudit CREATE CLUSTER; noaudit CREATE DATABASE LINK; noaudit CREATE DIMENSION; noaudit CREATE INDEXTYPE; noaudit CREATE LIBRARY; noaudit CREATE MATERIALIZED VIEW; noaudit CREATE OPERATOR; noaudit CREATE PROCEDURE; noaudit CREATE PROFILE; noaudit CREATE PUBLIC DATABASE LINK; noaudit CREATE PUBLIC SYNONYM; noaudit CREATE ROLE; noaudit CREATE ROLLBACK SEGMENT; noaudit CREATE SEQUENCE; noaudit CREATE SESSION; noaudit CREATE SNAPSHOT; noaudit CREATE SYNONYM; noaudit CREATE TABLE; noaudit CREATE TABLESPACE; noaudit CREATE TRIGGER; noaudit CREATE TYPE; noaudit CREATE USER; noaudit CREATE VIEW; noaudit DELETE ANY TABLE; noaudit DROP ANY CLUSTER; noaudit DROP ANY CONTEXT; noaudit DROP ANY DIMENSION; noaudit DROP ANY DIRECTORY; noaudit DROP ANY INDEX; noaudit DROP ANY INDEXTYPE; noaudit DROP ANY LIBRARY; noaudit DROP ANY MATERIALIZED VIEW; noaudit DROP ANY OPERATOR; noaudit DROP ANY OUTLINE; noaudit DROP ANY PROCEDURE; noaudit DROP ANY ROLE; noaudit DROP ANY SEQUENCE; noaudit DROP ANY SNAPSHOT; noaudit DROP ANY SYNONYM; noaudit DROP ANY TABLE; noaudit DROP ANY TRIGGER; noaudit DROP ANY TYPE; noaudit DROP ANY VIEW; noaudit DROP PROFILE; noaudit DROP PUBLIC DATABASE LINK; noaudit DROP PUBLIC SYNONYM; noaudit DROP ROLLBACK SEGMENT; noaudit DROP TABLESPACE; noaudit DROP USER; noaudit DROP PROFILE; noaudit DROP PUBLIC DATABASE LINK; noaudit DROP PUBLIC SYNONYM; noaudit DROP ROLLBACK SEGMENT; noaudit DROP TABLESPACE; noaudit DROP USER; noaudit EXECUTE ANY INDEXTYPE; noaudit EXECUTE ANY LIBRARY; noaudit EXECUTE ANY OPERATOR; noaudit EXECUTE ANY PROCEDURE; noaudit EXECUTE ANY TYPE; noaudit FORCE ANY TRANSACTION; noaudit FORCE TRANSACTION; noaudit GLOBAL QUERY REWRITE; noaudit GRANT ANY PRIVILEGE; noaudit GRANT ANY ROLE; noaudit LOCK ANY TABLE; noaudit MANAGE TABLESPACE; noaudit QUERY REWRITE; noaudit RESTRICTED SESSION; noaudit SESSION;