今天应客户需求，需要对用户 A 下面的表，用户 B 只能够查询，更新，插入的权限， A 用户的过程和包，B用户只能够执行，同时 C 用户只能查询A用户的表，由于 A 用户的表在不断增加，所以想到了创建DDL触发器
1. 创建四个角色：

CREATE ROLE A_INSERT;
CREATE ROLE A_UPDATE;
CREATE ROLE A_SELECT;
CREATE ROLE A_EXECUTE;

2. 将上面的三个角色赋给B用户，同时给B用处创建同义词的权限

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;

3 创建表和序列，用于记录创建对象的日志

-- 创建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);

4. 创建存储过程，主要是执行动态赋权语句，被触发器调用

-- 创建存储过程
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;

-- 对 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;

5. 创建数据库级别的触发器，记录新建的对象，同时调用上面的存储过程

-- 创建触发器
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博客

ORACLE ddl触发器创建同义词

最新文章