控制DDL許可權及紀錄DDL操作的Trigger

轉自：http://space.itpub.net/?uid-24800471-action-viewspace-itemid-719089

--建立一個控制DDL許可權及紀錄DDL操作的Trigger

--需要設定目錄utl_file_dir,此引數只能重啟才能生效

alter system set utl_file_dir='/usr/tmp/log/orginfo/' scope=spfile;

CREATE OR REPLACE TRIGGER SYS.NO_DDL_PRIVILEGE_HDB
BEFORE DDL
ON ORGINFO.SCHEMA
DECLARE
NSTATEMENT NUMBER;
NLINE NUMBER;
NINDEX NUMBER;
TYPE ARR_SQL IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
ARR_SQLSTATEMENT ARR_SQL;
SQLSTATEMENT VARCHAR2(32400);
ENDSTATEMENT VARCHAR2(32600);
LOGMESSAGE VARCHAR2(32767);
LINEMESSAGE VARCHAR2(32767);
SQL_TEXT ORA_NAME_LIST_T;
TERMINAL VARCHAR2(50);
IP_ADDRESS VARCHAR2(23);
OS_USER VARCHAR2(30);
EXETIMESTAMP VARCHAR2(15);
FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
NSTATEMENT := ORA_SQL_TXT(SQL_TEXT); -- 獲取執行的語句
NINDEX := 0;
SQLSTATEMENT := SQL_TEXT(1);
--插入相關資訊到表中
INSERT INTO ORGINFO.EVENT_RECORDS (HOST,
IP_ADDRESS,
OS_USER,
Login_User,
OBJ_TYPE,
OBJ_NAME,
EVENT,
TIMESTAMP)
VALUES (SYS_CONTEXT('USERENV','HOST'),
SYS_CONTEXT('USERENV','IP_ADDRESS'),
SYS_CONTEXT('USERENV','OS_USER'),
ORA_LOGIN_USER,
SYS.Dictionary_Obj_Type,
SYS.Dictionary_Obj_Name,
Sysevent,
TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'));

SELECT SYS_CONTEXT('USERENV','TERMINAL') INTO TERMINAL FROM DUAL;
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') INTO IP_ADDRESS FROM DUAL;
SELECT SYS_CONTEXT('USERENV','OS_USER') INTO OS_USER FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS') INTO EXETIMESTAMP FROM DUAL;

--紀錄執行的DDL終端,IP,使用者和詳細語句
LOGMESSAGE := 'ORA-20001' || ':' || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') || ':' || TERMINAL || ':' || IP_ADDRESS || ':' || OS_USER || ':' || CHR(10) || CHR(13) || SQLSTATEMENT;

--在上每天生成檔案紀錄執行DDL資訊
FILE_HANDLE := UTL_FILE.FOPEN ('/usr/tmp/log/orginfo/', TO_CHAR(SYSDATE,'yymmdd') || 'ddl_hdb.log', 'A',32767);
UTL_FILE.PUTF (FILE_HANDLE,LOGMESSAGE);
UTL_FILE.FCLOSE (FILE_HANDLE);

IF (USER = 'ORGINFO') AND (UPPER(SYS_CONTEXT('USERENV','OS_USER')) NOT IN ('LIMING_GU','YONG_WANG','YANLIN_XU','QILIANG_YUAN')) THEN

RAISE_APPLICATION_ERROR( -20001, 'Sorry,' || SYS_CONTEXT('USERENV','OS_USER') ||',you don''t have privileges to perform. this operation');

END IF;
END;
/

[@more@]控制DDL許可權及紀錄DDL操作的Trigger