ANBOB™

无论出于安全、特性、性能、支持周期都需要考虑升级数据库，但是也会导致有些功能改变而影响软件使用或管理方式，升级后经验格外重要，因为oracle官方提供的功能无法模拟各行业生产环境中所有的应用场景， 尤其是从最近要面临的11g升级19c大版本升级，防止踩雷，像wm_concat 在新版本不支持一样。

之前我在《oracle 12c new feature: RESOURCE role without unlimited tablespace》 分享过升级到12c 后，新创建的用户给RESOURCE因为UNLIMITED TABLESPACE权限的缺失依旧不能在表空间中创建对象。这里分享一下在database级创建trigger的变化即使给了DBA ROLE 依旧ORA-01031。

SQL>   CREATE OR REPLACE TRIGGER "ANBOB"."DTR_DDLEVENTS"
  2  AFTER DDL ON DATABASE
  3  DECLARE
  4  --
  5  -- author: weejar
  6  -- date : 2016-5-13
  ...
  48  /
  CREATE OR REPLACE TRIGGER "ANBOB"."DTR_DDLEVENTS"
                            *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> grant create any trigger to ANBOB;
Grant succeeded.

SQL> @roles ANBOB

GRANTEE                   GRANTED_ROLE                           ADM DEF
------------------------- -------------------------------------- --- ---
ANBOB                      APP_OPERATOR                           YES YES
ANBOB                      PDB_DBA                                NO  YES
ANBOB                      DBA                                    NO  YES
ANBOB                      APP_SELECTOR                           YES YES
ANBOB                      AUDROLE                                YES YES


SQL> CREATE OR REPLACE TRIGGER "ANBOB"."DTR_DDLEVENTS"
                            *
ERROR at line 1:
ORA-01031: insufficient privileges

NOTE:
可以看到anbob用户给了dba,pdb_dba 角色和create any trigger， 创建trigger on database仍旧提示无权限。

如果注意过preupgrade中给的输出应该知道原因了。原因是因为ADMINISTER DATABASE TRIGGER 的权限到12.2以后需要直接给用户权限了。

VERSION <=12.1
To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have the CREATE TRIGGER system privilege.
To create a trigger in any schema on a table in any schema, or on another user’s schema (schema.SCHEMA), you must have the CREATE ANY TRIGGER system privilege.

In addition to the preceding privileges, to create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.By default DBA and IMP_FULL_DATABASE role has this privileges.Also sys user has this privileges by default.

VERSION  >= 12.2
In 12.2 , direct grant of “administer database trigger” is needed for the trigger owner.

$ORACLE_BASE/product/19.0.0/dbhome_1/jdk/bin/java -jar $ORACLE_BASE/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT

(AUTOFIXUP) Directly grant ADMINISTER DATABASE TRIGGER privilege to the
owner of the trigger or drop and re-create the trigger with a user that was granted directly with such. You can list those triggers using:

SELECT OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE
TRIM(BASE_OBJECT_TYPE)='DATABASE' AND 
OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER').

There is one or more database triggers whose owner does not have the right privilege on the database.
The creation of database triggers must be done by users granted with ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted directly.

SQL> grant administer database trigger to ANBOB;
Grant succeeded.
SQL>   CREATE OR REPLACE TRIGGER "ANBOB"."DTR_DDLEVENTS"
  2  AFTER DDL ON DATABASE
  3  DECLARE
...

Trigger created.

打赏

微信扫一扫，打赏作者吧～