今天应客户需求,需要对用户 A 下面的表,用户 B 只能够查询,更新,插入的权限, A 用户的过程和包,B用户只能够执行, 同时 C 用户只能查询A用户的表, 由于 A 用户的表在不断增加,所以想到了创建DDL触发器
1. 创建四个角色:
2. 将上面的三个角色赋给B用户,同时给B用处创建同义词的权限
3 创建表和序列,用于记录创建对象的日志
4. 创建存储过程,主要是执行动态赋权语句, 被触发器调用
5. 创建数据库级别的触发器,记录新建的对象,同时调用上面的存储过程
1. 创建四个角色:
- CREATE ROLE A_INSERT;
- CREATE ROLE A_UPDATE;
- CREATE ROLE A_SELECT;
- CREATE ROLE A_EXECUTE;
- GRANT A_INSERT TO B;
- GRANT A_UPDATE TO B;
- GRANT A_SELECT TO B;
- GRANT A_EXECUTE TO B;
- GRANT CREATE SYNONYM TO B;
- GRANT A_SELECT TO C;
- GRANT CREATE SYNONYM TO C;
- -- 创建sequence
- CREATE SEQUENCE SYSTEM.S_DDL_CREATE_OBJECT_LOG
- MINVALUE 1
- NOMAXVALUE
- START WITH 1
- INCREMENT BY 1
- CACHE 20;
-
- -- 创建存取日志的表
- CREATE TABLE SYSTEM.DDL_CREATE_OBJECT_LOG
- (
- ID NUMBER,
- DDL_DATE DATE,
- USER_NAME VARCHAR2(30),
- OBJ_NAME VARCHAR2(128),
- DDL_TYPE VARCHAR2(30),
- OBJECT_TYPE VARCHAR2(19),
- OWNER VARCHAR2(30),
- IS_GRANT CHAR(1) DEFAULT '0',
- ERROR_MSG VARCHAR2(2000)
- ) ;
-
- ALTER TABLE SYSTEM.DDL_CREATE_OBJECT_LOG ADD CONSTRAINT PK_DDL_CREATE_OBJECT_LOG PRIMARY KEY(ID);
- -- 创建存储过程
- CREATE OR REPLACE PROCEDURE P_CREATE_GRANT_PRIVILE(v_owner varchar2) is
- type type_cur_job is ref cursor;
- cur_job type_cur_job;
- v_id SYSTEM.DDL_CREATE_OBJECT_LOG.id%TYPE;
- v_obj_name SYSTEM.DDL_CREATE_OBJECT_LOG.obj_name%TYPE;
- v_object_type SYSTEM.DDL_CREATE_OBJECT_LOG.object_type%TYPE;
- v_i number;
- begin
- OPEN cur_job FOR
- select id, obj_name, object_type
- from SYSTEM.DDL_CREATE_OBJECT_LOG
- where is_grant='0' and ddl_type = 'CREATE' AND object_type IN('TABLE','SEQUENCE','PROCEDURE','PACKAGE') and owner = v_owner ;
-
- LOOP
- FETCH cur_job INTO v_id, v_obj_name, v_object_type;
-
- 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=0 THEN
UPDATE SYSTEM.DDL_CREATE_OBJECT_LOG SET IS_GRANT='2' WHERE ID=v_id;
continue;
END IF;
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;
-
- -- 对 role 赋权
- IF v_object_type = 'TABLE' THEN
- EXECUTE IMMEDIATE 'GRANT SELECT ON '||v_owner||'.'||v_obj_name||' TO A_SELECT';
- EXECUTE IMMEDIATE 'GRANT UPDATE ON '||v_owner||'.'||v_obj_name||' TO A_UPDATE';
- EXECUTE IMMEDIATE 'GRANT INSERT ON '||v_owner||'.'||v_obj_name||' TO A_INSERT';
-
- -- 对 readonly 用户赋权 'TABLE'
- v_i := 0;
- SELECT COUNT(*) INTO v_i FROM DBA_OBJECTS WHERE OWNER='C' AND OBJECT_NAME=v_obj_name;
-
- IF v_i=0 THEN
- EXECUTE IMMEDIATE 'CREATE SYNONYM C.'||v_obj_name||' FOR '||v_owner||'.'||v_obj_name;
- END IF;
- ELSIF v_object_type = 'SEQUENCE' THEN
- EXECUTE IMMEDIATE 'GRANT SELECT ON '||v_owner||'.'||v_obj_name||' TO A_SELECT';
- ELSIF v_object_type IN ('PROCEDURE','PACKAGE') THEN
- EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||v_owner||'.'||v_obj_name||' TO A_EXECUTE';
- END IF;
-
-
- -- 对 APP 用户赋权 'TABLE','SEQUENCE','PROCEDURE','PACKAGE'
- v_i := 0;
- SELECT COUNT(*) INTO v_i FROM DBA_OBJECTS WHERE OWNER='B' AND OBJECT_NAME=v_obj_name;
-
- IF v_i=0 THEN
- EXECUTE IMMEDIATE 'CREATE SYNONYM B.'||v_obj_name||' FOR '||v_owner||'.'||v_obj_name;
- END IF;
-
- UPDATE SYSTEM.DDL_CREATE_OBJECT_LOG SET IS_GRANT='1' WHERE ID=v_id;
- COMMIT;
- END LOOP;
- CLOSE cur_job;
-
- end P_CREATE_GRANT_PRIVILE;
- -- 创建触发器
- create or replace trigger TRG_GRANT_PRIVILEGE_TO_APP
- after CREATE on database
- declare
- v_job number;
- begin
- insert into SYSTEM.DDL_CREATE_OBJECT_LOG(
- ID,
- ddl_date,
- user_name,
- obj_name,
- ddl_type,
- object_type,
- owner,
- is_grant)
- values(
- SYSTEM.S_DDL_CREATE_OBJECT_LOG.NEXTVAL,
- sysdate,
- user,
- NVL(ora_dict_obj_name,'-'),
- NVL(ORA_SYSEVENT,'-'),
- NVL(ora_dict_obj_type,'-'),
- NVL(ora_dict_obj_owner,'-'),
- '0'
- );
-
- IF ora_dict_obj_owner = 'A' AND ORA_SYSEVENT = 'CREATE' THEN
-
- dbms_job.submit(v_job,'P_CREATE_GRANT_PRIVILE('''||ora_dict_obj_owner||''');',sysdate+(1/24/60/60));
- END IF;
-
- end GRANT_PRIVILEGE_TO_APP;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30150152/viewspace-1845964/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30150152/viewspace-1845964/