ORACLE ddl触发器创建同义词

    今天应客户需求，需要对用户 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. -- 创建存取日志的表
   
10. CREATE TABLE SYSTEM.DDL_CREATE_OBJECT_LOG
    
11.    (
    
12.     ID NUMBER,
    
13.     DDL_DATE DATE,
    
14.     USER_NAME VARCHAR2(30),
    
15.     OBJ_NAME VARCHAR2(128),
    
16.     DDL_TYPE VARCHAR2(30),
    
17.     OBJECT_TYPE VARCHAR2(19),
    
18.     OWNER VARCHAR2(30),
    
19.     IS_GRANT CHAR(1) DEFAULT '0',     
    
20.     ERROR_MSG VARCHAR2(2000)
    
21.    ) ;
    
22.    
    
23.  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;
    
19. 
    

                -- 判断对象是否存在, 对象不存在后面赋权会有异常
                v_i := 0;
                SELECT COUNT(*) INTO v_i FROM DBA_OBJECTS WHERE OWNER=v_owner AND OBJECT_NAME=v_obj_name;               
                IF v_i=0 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30150152/viewspace-1845964/