ITPub博客

首页 > 应用开发 > IT综合 > 控制DDL权限及纪录DDL操作的Trigger

控制DDL权限及纪录DDL操作的Trigger

原创 IT综合 作者:fanhongjie 时间:2012-03-21 17:24:49 0 删除 编辑

转自: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); -- 获取执行的
SQL语句
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

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/73920/viewspace-1057673/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-05-22

  • 博文量
    111
  • 访问量
    685243