创建新表，自动授权trigger

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

需求

一个用户下三个表，开发人员不定时进行rename表名称，create原表名称 as old_table 插入少量数据,另一个业务用户需要访问该表，由于表名称rename导致经常需要手工授权。

需求转型12.1,新创建的表，自动给开发用户进行授权，由于用户很多，因此新表对角色进行授权。

一、模拟场景

目标表hr.t1,业务用户scott
SQL> create table hr.t1 as select * from hr.employees;
SQL> grant select on hr.t1 to scott;
SQL> conn scott/tiger
已连接。
SQL>
SQL> select count(*) from hr.t1;
COUNT(*)
----------SQL> conn hr/hr
已连接。
SQL> alter table t1 rename to t1_20190612_bak;
表已更改。
SQL> create table t1 as select * from T1_20190612_BAK where rownum<=;
表已创建。
SQL> conn scott/tiger
已连接。
SQL> select count(*) from hr.t1;
select count(*) from hr.t1
*
第行出现错误:
ORA-: 表或视图不存在SQL> select GRANTEE,OWNER,TABLE_NAME,GRANTOR,PRIVILEGE from dba_tab_privs where table_name='T1_20190612_BAK';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
---------- ---------- --------------- -------------------- ----------
SCOTT HR T1_20190612_BAK HR SELECT
表名称rename后,授权对象自动更换为修改后的对象名称。

二、测试解决方案

2.1 同义词

排除权限不足，测试同义词rename后的对象是否会跟随rename修改
SQL> grant select any table to scott;
SQL> create table t2 as select * from T1_20190612_BAK;
SQL> conn scott/tiger
SQL> create or replace synonym t2 for hr.t2;
同义词已创建。
SQL> select count(*) from t2;
COUNT(*)
----------SQL> alter table t2 rename to T2_20190612_BAK;
SQL> select count(*) from t2;
select count(*) from t2
*
第行出现错误:
ORA-: 同义词转换不再有效SQL> select OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms where table_name='T2';
OWNER SYNONYM_NA TABLE_OWNE TABLE_NAME
---------- ---------- ---------- ------------------------------
SCOTT T2 HR T2
SQL> select owner,object_name,object_type,status from dba_objects where owner='SCOTT' and object_name='T2';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- -------------------- ------------------- -------
SCOTT T2 SYNONYM VALID
SQL> create table t2 as select * from T2_20190612_BAK where rownum<=;
SQL> select count(*) from t2
COUNT(*)
----------

总结，本次第一个需求，使用同义词即可。但是第二个需求，如果需要对数据库新表进行授权如何操作呢？

2.2 创建触发器

http://blog.itpub.net/27042095/viewspace-741198/
http://blog.itpub.net/25016/viewspace-926488/ORA_DICT_OBJ_NAME:用于返回DDL操作所对应的数据库对象名
ORA_DICT_OBJ_OWNER:用于返回DDL操作所对应的对象的所有者名。
ORA_DICT_OBJ_TYPE:用于返回DDL操作所对应的数据库对象的类型。--11.2.0.4
create or replace trigger GRANT_NEWTABLE
after create on database
DECLARE
v_owner varchar();
v_table_name varchar();
v_object_type varchar();
v_sql varchar2();
begin
v_owner := SYS.DICTIONARY_OBJ_OWNER;
v_table_name := SYS.DICTIONARY_OBJ_NAME;
v_object_type := SYS.dictionary_obj_type;
IF (v_owner in ('HR') and v_object_type='TABLE') THEN
v_sql := 'grant select,insert,update,delete on ' || v_owner || '.' ||v_table_name || ' TO rolea';
DBMS_SCHEDULER.create_job (
job_name => 'test',
job_type => 'PLSQL_BLOCK',
job_action => 'Begin profile(''' || v_sql || '''); end ;',
start_date => SYSTIMESTAMP,
repeat_interval => NULL ,
end_date => NULL,
enabled => TRUE,
comments => 'Run DDL from the trigger');
end if;
end;
/
--12.1(由于DBMS_SCHEDULER JOB创建语法不同导致,上述11g版本输入无法执行)
create or replace trigger GRANT_NEWTABLE
after create on database
DECLARE
v_owner varchar();
v_table_name varchar();
v_object_type varchar();
v_sql varchar2();
begin
v_owner := SYS.DICTIONARY_OBJ_OWNER;
v_table_name := SYS.DICTIONARY_OBJ_NAME;
v_object_type := SYS.dictionary_obj_type;
IF (v_owner in ('HR') and v_object_type='TABLE') THEN
v_sql := 'grant select,insert,update,delete on ' || v_owner || '.' ||v_table_name || ' TO rolea';
DBMS_SCHEDULER.create_job (
job_name => 'test',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
execute immediate '''||v_sql||''';
end;',
start_date => SYSTIMESTAMP,
repeat_interval => NULL ,
end_date => NULL,
enabled => TRUE,
comments => 'Run DDL from the trigger');
end if;
end;
/