ITPub博客

首页 > 数据库 > Oracle > ORACLE ddl触发器创建同义词

ORACLE ddl触发器创建同义词

原创 Oracle 作者:warren20 时间:2015-11-25 01:36:46 0 删除 编辑
    今天应客户需求,需要对用户 A 下面的表,用户 B 只能够查询,更新,插入的权限, A 用户的过程和包,B用户只能够执行, 同时 C 用户只能查询A用户的表, 由于 A 用户的表在不断增加,所以想到了创建DDL触发器
1. 创建四个角色:
  1. CREATE ROLE A_INSERT;
  2. CREATE ROLE A_UPDATE;
  3. CREATE ROLE A_SELECT;
  4. CREATE ROLE A_EXECUTE;
2. 将上面的三个角色赋给B用户,同时给B用处创建同义词的权限
  1. GRANT A_INSERT TO B;
  2. GRANT A_UPDATE TO B;
  3. GRANT A_SELECT TO B;
  4. GRANT A_EXECUTE TO B;
  5. GRANT CREATE SYNONYM TO B;
  1. GRANT A_SELECT TO C;
  2. GRANT CREATE SYNONYM TO C;
3 创建表和序列,用于记录创建对象的日志
  1. -- 创建sequence
  2. CREATE SEQUENCE SYSTEM.S_DDL_CREATE_OBJECT_LOG
  3. MINVALUE 1
  4. NOMAXVALUE
  5. START WITH 1
  6. INCREMENT BY 1
  7. CACHE 20;

  8. -- 创建存取日志的表
  9. CREATE TABLE SYSTEM.DDL_CREATE_OBJECT_LOG
  10.    (
  11.     ID NUMBER,
  12.     DDL_DATE DATE,
  13.     USER_NAME VARCHAR2(30),
  14.     OBJ_NAME VARCHAR2(128),
  15.     DDL_TYPE VARCHAR2(30),
  16.     OBJECT_TYPE VARCHAR2(19),
  17.     OWNER VARCHAR2(30),
  18.     IS_GRANT CHAR(1) DEFAULT '0',     
  19.     ERROR_MSG VARCHAR2(2000)
  20.    ) ;
  21.    
  22.  ALTER TABLE SYSTEM.DDL_CREATE_OBJECT_LOG ADD CONSTRAINT PK_DDL_CREATE_OBJECT_LOG PRIMARY KEY(ID);
4. 创建存储过程,主要是执行动态赋权语句, 被触发器调用
  1. -- 创建存储过程
  2.    CREATE OR REPLACE PROCEDURE P_CREATE_GRANT_PRIVILE(v_owner varchar2) is
  3.        type type_cur_job is ref cursor;
  4.        cur_job type_cur_job;
  5.        v_id SYSTEM.DDL_CREATE_OBJECT_LOG.id%TYPE;
  6.        v_obj_name SYSTEM.DDL_CREATE_OBJECT_LOG.obj_name%TYPE;
  7.        v_object_type SYSTEM.DDL_CREATE_OBJECT_LOG.object_type%TYPE;
  8.        v_i number;
  9. begin
  10.          OPEN cur_job FOR
  11.          select id, obj_name, object_type
  12.          from SYSTEM.DDL_CREATE_OBJECT_LOG
  13.          where is_grant='0' and ddl_type = 'CREATE' AND object_type IN('TABLE','SEQUENCE','PROCEDURE','PACKAGE') and owner = v_owner ;
  14.         
  15.          LOOP
  16.                FETCH cur_job INTO v_id, v_obj_name, v_object_type;
  17.                
  18.                EXIT WHEN cur_job%NOTFOUND;

                -- 判断对象是否存在, 对象不存在后面赋权会有异常
                v_i := 0;
                SELECT COUNT(*) INTO v_i FROM DBA_OBJECTS WHERE OWNER=v_owner AND OBJECT_NAME=v_obj_name;               
                IF v_i=THEN 
                  UPDATE SYSTEM.DDL_CREATE_OBJECT_LOG  SET IS_GRANT='2' WHERE ID=v_id;
                  continue;
                END IF;

  1.                 
  2.                -- 对 role 赋权
  3.                IF v_object_type = 'TABLE' THEN
  4.                   EXECUTE IMMEDIATE 'GRANT SELECT ON '||v_owner||'.'||v_obj_name||' TO A_SELECT';
  5.                   EXECUTE IMMEDIATE 'GRANT UPDATE ON '||v_owner||'.'||v_obj_name||' TO A_UPDATE';
  6.                   EXECUTE IMMEDIATE 'GRANT INSERT ON '||v_owner||'.'||v_obj_name||' TO A_INSERT';
  7.                
  8.                   -- 对 readonly 用户赋权 'TABLE'
  9.                   v_i := 0;
  10.                   SELECT COUNT(*) INTO v_i FROM DBA_OBJECTS WHERE OWNER='C' AND OBJECT_NAME=v_obj_name;
  11.                
  12.                   IF v_i=0 THEN
  13.                      EXECUTE IMMEDIATE 'CREATE SYNONYM C.'||v_obj_name||' FOR '||v_owner||'.'||v_obj_name;
  14.                   END IF;
  15.                ELSIF v_object_type = 'SEQUENCE' THEN
  16.                   EXECUTE IMMEDIATE 'GRANT SELECT ON '||v_owner||'.'||v_obj_name||' TO A_SELECT';
  17.                ELSIF v_object_type IN ('PROCEDURE','PACKAGE') THEN
  18.                   EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||v_owner||'.'||v_obj_name||' TO A_EXECUTE';
  19.                END IF;
  20.                
  21.                
  22.                -- 对 APP 用户赋权 'TABLE','SEQUENCE','PROCEDURE','PACKAGE'
  23.                v_i := 0;
  24.                SELECT COUNT(*) INTO v_i FROM DBA_OBJECTS WHERE OWNER='B' AND OBJECT_NAME=v_obj_name;
  25.                
  26.                IF v_i=0 THEN
  27.                   EXECUTE IMMEDIATE 'CREATE SYNONYM B.'||v_obj_name||' FOR '||v_owner||'.'||v_obj_name;
  28.                END IF;
  29.                
  30.                UPDATE SYSTEM.DDL_CREATE_OBJECT_LOG SET IS_GRANT='1' WHERE ID=v_id;
  31.                COMMIT;
  32.          END LOOP;
  33.          CLOSE cur_job;
  34.   
  35. end P_CREATE_GRANT_PRIVILE;
5. 创建数据库级别的触发器,记录新建的对象,同时调用上面的存储过程
  1. -- 创建触发器
  2. create or replace trigger TRG_GRANT_PRIVILEGE_TO_APP
  3.   after CREATE on database
  4. declare
  5.   v_job number;
  6. begin
  7.   insert into SYSTEM.DDL_CREATE_OBJECT_LOG(
  8.          ID,
  9.          ddl_date,
  10.          user_name,
  11.          obj_name,
  12.          ddl_type,
  13.          object_type,
  14.          owner,
  15.          is_grant)
  16.   values(
  17.          SYSTEM.S_DDL_CREATE_OBJECT_LOG.NEXTVAL,
  18.          sysdate,
  19.          user,
  20.          NVL(ora_dict_obj_name,'-'),
  21.          NVL(ORA_SYSEVENT,'-'),
  22.          NVL(ora_dict_obj_type,'-'),
  23.          NVL(ora_dict_obj_owner,'-'),
  24.          '0'
  25.    );
  26.   
  27.   IF ora_dict_obj_owner = 'A' AND ORA_SYSEVENT = 'CREATE' THEN
  28.   
  29.      dbms_job.submit(v_job,'P_CREATE_GRANT_PRIVILE('''||ora_dict_obj_owner||''');',sysdate+(1/24/60/60));
  30.      END IF;
  31.   
  32. end GRANT_PRIVILEGE_TO_APP;

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

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

注册时间:2015-03-03

  • 博文量
    56
  • 访问量
    320928