ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 控制DDL权限及纪录DDL操作的Trigger

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

原创 Linux操作系统 作者:Anst_Alex_Yuan 时间:2012-03-20 11:08:51 0 删除 编辑

--创建一个控制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;
/

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

下一篇: 删除数据文件
请登录后发表评论 登录
全部评论

注册时间:2012-03-19

  • 博文量
    14
  • 访问量
    49469