ORACLE 12C存储过程ALTER Trigger出现ORA-01031错误

1.问题处理

1.1 错误背景

数据库版本： ORACLE 12.2.0.1 CDB模式
涉及对象：

• 数据库用户：USER1（创建用户）、USER2（调用用户）
• 触发器：USER1.TriggerTst（on database）
• 存储过程：USER1.P_callTrigger（使用execute immediate方式alter trigger）

错误场景：
用户USER1和用户USER2调用存储过程USER1.P_callTrigger进行alter trigger操作出现“ORA-01031”错误，错误信息如下：

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "*****",line ****

1.2 错误分析

分别检查对象属主USER1和调用用户USER2的角色权限和对象权限，均拥有dba权限，均拥有ALTER ANY TRIGGER权限

--通过如下语句检查角色权限
select * from dba_sys_privs where grantee='USER1' and privilelge like '%TRIGGER';
--输出包含ALTER ANY TRIGGER/CREATE ANY TRIGGER

select * from dba_role_privs where grantee='USER1' 
--输出包含DBA角色

若无对应权限，可以手动单独显式授权

sqlplus / as sysdba
alter session set container=XXX; 
grant create any trigger to USER1;
grant alter any trigger to USER1;
grant dba to USER1;

1.3 解决方法

检查PLSQL中“系统权限”部分与触发器相关权限，并为用户USER1增加administer database trigger权限，问题解决。

2.权限说明

关于administer database trigger权限，官方文档《SQL Language Reference》“ALTER TRIGGER”章节中有如下说明：

Prerequisites
The trigger must be in your own schema or you must have ALTER ANY TRIGGER system privilege.
In addition, to alter a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER privilege.

3.测试案例

3.1 环境准备

数据库版本：12.2.0.1：

--01.创建用户及授权
create user test identified by  "23456";
grant connect,resource to test;
grant alter  any trigger to test;
grant execute on test2.p_ddltrg to test;
--grant dba to test;
create user test2 identified by  "23456";
grant connect,resource to test2;
grant create any trigger to test2;
grant create any table to test2;
grant create any procedure to test2;
grant alter  any trigger to test2;
--grant dba to test2;

--02.创建测试表
create table test2.t_test(ttime date);

--03.创建触发器
create or replace trigger test2.TrgBfTst
  before ddl on database
begin
  insert into test2.t_test(ttime) values(sysdate);
  --commit;
end;
/

--04.创建存储过程
create or replace procedure test2.p_ddltrg(pi_flag varchar2)
is
  v_i number;
begin
  select max(1) into v_i
    from user_triggers t where t.trigger_name='TRGBFTST';

  if v_i is not null then
    if pi_flag = 'D' then
       execute immediate 'alter trigger TrgBfTst disable';
    elsif pi_flag ='E' then
       execute immediate 'alter trigger TrgBfTst enable';
    end if;

  end if;
end;
/

3.2 测试方法

方式1：直接进行alter trigger

alter trigger test2.TrgBfTst disable;
或
alter trigger test2.TrgBfTst enable;

方式2：调用存储过程进行alter trigger

call test2.p_ddltrg('E');
或
call test2.p_ddltrg('D');

3.3 测试情况

总结：

• CDB模式下：数据库无DBA权限时无法创建触发器TrgBfTst(授予DBA权限后可以创建成功)
• 非CDB模式下：数据库无DBA权限时可以创建触发器TrgBfTst

--授予此权限后可以在存储过程中执行alter trigger
grant administer database trigger to test2;
--revoke administer database trigger from test2;

备注：以下表格中/两边分别为无administer database trigger和有administer database trigger权限时对应的操作是否可以执行

CDB	创建者(DBA权限)	非创建者(DBA权限)	非创建者(无DBA权限)
alter trigger	yes/yes	no/yes	no/no
call p_ddltrg	no/yes	no/yes	no/yes

非CDB	创建者(无DBA权限)	非创建者(DBA权限)	非创建者(无DBA权限)
alter trigger	yes/yes	no/yes	no/no
call p_ddltrg	no/yes	no/yes	no/yes

非CDB	创建者(DBA权限)	非创建者(DBA权限)	非创建者(无DBA权限)
alter trigger	yes/yes	no/yes	no/no
call p_ddltrg	no/yes	no/yes	no/yes