Oracle需要显式赋权的权限

1. 说明

刚刚升级了一套9.2.0.8的数据库到10.2.0.5,客户就抛来问题了,在drop表时,报错如下:



初看这个报错应该是权限的问题

2. 检查触发器

SQL> select trigger_name, TRIGGER_TYPE, TABLE_NAME, status from dba_triggers where trigger_name = 'TRG_DROPDENY';
TRIGGER_NAME  TRIGGER_TYPE  TABLE_NAME  BASE_OBJECT_TYPE STATUS
------------- ------------- ----------- ---------------- --------
TRG_DROPDENY  BEFORE EVENT              DATABASE         ENABLED

这个触发器的实际脚本,如下:

CREATE OR REPLACE TRIGGER CUSTOM.trg_dropdeny
   BEFORE DROP or truncate ON DATABASE
BEGIN
   IF ora_dict_obj_name () in
(
'NT_ZQ_AREA_MANAGER',
'JJJ_CWBB_ACCT_TYPE_NEW',
'JJJ_NT_ZQB_DAY_FANKUI',
'JJJ_NT_GKB_DAY',
'JJJ_NTSQ_XXR',
'JJJ_BRANCH_CHANNEL_RELA',
'NT_BRANCH_INFO',
'JJJ_BRANCH'
)
   THEN
      raise_application_error (num      => -20000,
                               msg      =>    'stop!you can not drop '
                                           || ora_dict_obj_name ()
                                           || ' ?!!!!!'
                                           || '.....'
                              );
   END IF;
END;

ora_dict_obj_name是个同义词,基对象是sys用户里的一个function,具体定义如下:

create or replace function sys.dictionary_obj_name return varchar2 is
begin
return dbms_standard.dictionary_obj_name;
end;

这个触发器的作用大致如下,当想要drop或truncate表时,判断一下要drop的对象,如果是重要对象,禁止drop或truncate

3. 检查权限

这里报ORA-04045和ORA-01031错误,我觉得是权限的问题。

查了一下CUSTOM用户的权限。

SQL> select * from dba_role_privs p where p.GRANTEE = 'CUSTOM';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
CUSTOM                         DBA                            NO  YES
CUSTOM                         CONNECT                        NO  YES
CUSTOM                         RESOURCE                       NO  YES
CUSTOM                         SELECT_CATALOG_ROLE            NO  YES

当前CUSTOM用户是DBA权限,那权限应该是很大了。 ON DATABASE级别的触发器,和ADMINISTER DATABASE TRIGGER权限有关。DBA角色已经包括了这个权限

SQL> select * from dba_sys_privs where grantee = 'DBA' and privilege like '%TRIGGER%';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
DBA                            CREATE TRIGGER                           YES
DBA                            DROP ANY TRIGGER                         YES
DBA                            ALTER ANY TRIGGER                        YES
DBA                            CREATE ANY TRIGGER                       YES
DBA                            ADMINISTER DATABASE TRIGGER              YES

猜测可能这个权限需要显式赋权,而不能通过ROLE间接获得。

SQL> grant ADMINISTER DATABASE TRIGGER to custom;

显式赋权后,问题解决。以前还碰到一些权限需要显式赋权,现在忘记了,后面碰到再整理吧

4.dbms_standard测试

这里碰到一个dbms_standard.dictionary_obj_name包,这个包的作用应该是在对对象进行相关DDL操作(这里是drop和truncate)时,获取当前操作对象的名称,简要测试如下:

环境Oracle 12c

SQL> show user
USER is "PEI"
SQL> select * from session_privs; 
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
9 rows selected.
SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------
RESOURCE
SQL> create table pei.drop_log (
  2  object_name varchar2(30)   
  3  ) tablespace tbs_test;
Table created.

如果PEI用户要创建ON DATABASE的触发器,需要赋权ADMINISTER DATABASE TRIGGER权限 :

SQL> show user
USER is "SYS"
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> CREATE OR REPLACE TRIGGER pei.trg_dropdeny
  2     BEFORE DROP or truncate ON DATABASE
  3  BEGIN
  4    insert into pei.drop_log values(dbms_standard.dictionary_obj_name);
  5  END;
  6  /
Trigger created.

测试如下

SQL> show user
USER is "PEI"
SQL> create table t1 as select * from all_objects where  1 = 2;
Table created.
SQL> truncate table t1;
Table truncated.
SQL> select * from drop_log;
OBJECT_NAME
------------------------------
T1

所以12c里的权限要求小很多

关于紫砂壶

感悟技术人生
此条目发表在Oracle故障诊断分类目录,贴了标签。将固定链接加入收藏夹。