特殊的权限授予需求

场景：HZQ用户自己创建的表不能有grant 权限，需要A用户能对HZQ表用户进行表授权。

CREATE OR REPLACE TRIGGER TR_TABLE_GRANT
 BEFORE GRANT ON database
 DECLARE
 v_owner varchar(30);
 v_table_name varchar(30);
 v_oper_user varchar(30);
BEGIN
    v_owner := SYS.DICTIONARY_OBJ_OWNER;
    v_table_name := SYS.DICTIONARY_OBJ_NAME;
    v_oper_user := ora_login_user;
 IF( v_owner = 'HZQ' and v_oper_user not in ('DBADMIN','A'))
  THEN
    RAISE_APPLICATION_ERROR( -20001, 
                             ' No grant privilege on '||v_owner||'.'||v_table_name||' !!!' );
  END IF;  
END;
/

结果显示

自己创建的表不能授权
SQL> conn hzq/hzq
Connected.
SQL> create table t1(id int);

Table created.
SQL> grant select on hzq.t1 to b;
grant select on hzq.t1 to b
                    *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001:  No grant privilege on HZQ.T1 !!!
ORA-06512: at line 11
目前只有sys用户可以授权

注意dbadmin有dba权限
SQL> conn dbadmin/pass
Connected.
SQL> grant select on hzq.t1 to c;

Grant succeeded.
即使sys用户也没有授予权限
SQL> conn / as sysdba
Connected.
SQL> grant select on hzq.t1 to b;
grant select on hzq.t1 to b
                    *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001:  No grant privilege on HZQ.T1 !!!
ORA-06512: at line 11

现在dbadmin将hzq.t1级联授予给a,a在触发器允许授予hzq用户表权限，a用户可以授予权限
SQL> grant select on hzq.t1 to c with grant option;

Grant succeeded.

SQL> grant select on hzq.t1 to a with grant option;

Grant succeeded.
SQL> conn c/c
Connected.
SQL> grant select on hzq.t1 to dbadmin;
grant select on hzq.t1 to dbadmin
                    *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001:  No grant privilege on HZQ.T1 !!!
ORA-06512: at line 11


SQL> conn a/a
Connected.
SQL> grant select on hzq.t1 to dbadmin;

Grant succeeded.