DBMS_PARALLEL_EXECUTE 和间接授予程序授权 [英] DBMS_PARALLEL_EXECUTE and indirectly given grants on procedure
问题描述
我刚刚遇到了 DBMS_PARALLEL_EXECUTE 的一些奇怪行为(至少对我而言).查看我的预设(以 SYS 身份执行):
I just bumped into some strange behaviour of DBMS_PARALLEL_EXECUTE (at least for me). See my preset (executed as SYS):
-- Preset
drop user usr1 cascade;
create user usr1 identified by usr1;
create or replace procedure usr1.do_stuff(p1 in number, p2 in number)
is
begin
dbms_output.put_line('I did stuff!');
end;
/
drop user usr2 cascade;
create user usr2 identified by usr2;
grant connect to usr2;
grant create job to usr2;
drop role stuff_doer cascade;
create role stuff_doer;
grant execute on usr1.do_stuff to stuff_doer;
grant stuff_doer to usr2;
所以我创建了 2 个用户，第一个有一个过程，它被赋予 stuff_doer 角色.后来这个角色被赋予usr2.
So I created 2 users, the first one has a procedure which is given to stuff_doer role. Later this role is given to usr2.
然后我检查它为usr2:
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 22 12:14:10 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: usr2@db
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set serveroutput on
SQL> set linesize 400
SQL> exec usr1.do_stuff(1,1);
I did stuff!
PL/SQL procedure successfully completed.
SQL> DECLARE
2 l_task_name VARCHAR2(100) := 'task_name';
3 l_splitter VARCHAR2(4000) := 'select 1, 1 from dual';
4 l_exec_stmt VARCHAR2(1000) := 'begin usr1.do_stuff(:start_id, :end_id); end;';
5 BEGIN
6 FOR line IN (SELECT d.task_name
7 FROM user_parallel_execute_tasks d
8 WHERE d.task_name = l_task_name)
9 LOOP
10 dbms_parallel_execute.drop_task(task_name => line.task_name);
11 END LOOP;
12
13 dbms_parallel_execute.create_task(l_task_name);
14 dbms_parallel_execute.create_chunks_by_sql(task_name => l_task_name
15 ,sql_stmt => l_splitter
16 ,by_rowid => FALSE);
17
18 dbms_parallel_execute.run_task(l_task_name
19 ,l_exec_stmt
20 ,dbms_sql.native);
21
22 COMMIT;
23
24 END;
25 /
PL/SQL procedure successfully completed.
SQL> column status format A20
SQL> select status from user_parallel_execute_tasks where task_name = 'task_name';
STATUS
--------------------
FINISHED_WITH_ERROR
SQL> column status format A20
SQL> column error_code format 900000
SQL> column error_message format A60
SQL> select status, ERROR_CODE, ERROR_MESSAGE from user_parallel_execute_chunks e where e.TASK_NAME = 'task_name';
STATUS ERROR_CODE ERROR_MESSAGE
-------------------- ---------- ------------------------------------------------------------
PROCESSED_WITH_ERROR -06550 ORA-06550: line 1, column 7:
PLS-00201: identifier 'USR1.DO_STUFF' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL>
参见:当我直接执行 do_stuff 过程时 - 它按预期完成.但是当我使用 DBMS_PARALLEL_EXECUTE 时，我得到 identifier must be declaration 错误.我在授予特权时遗漏了什么吗?
See: when I execute do_stuff procedure directly - it finishes as expected. But when I use DBMS_PARALLEL_EXECUTE I get identifier must be declared error. Am I missing something in granting privileges?
我在此处找到了这个短语:CHUNK_BY_SQL、RUN_TASK 和 RESUME_TASK 子程序需要查询，并使用 DBMS_SQL 执行.
我试图明确地dbms_sql.parse我的语句，但它也完成了.
I tried to explicitly dbms_sql.parse my statement but it also finished OK.
任何帮助将不胜感激，因为我没有得到当前的情况.是的，我可以直接授予权限，但这对我来说仍然很棘手.
Any help would be appreciated as I'm not getting current situation. And yes, I can grant privileges directly but still it's something tricky for me.
推荐答案
在 PL/SQL 存储单元中默认不激活角色(使用 Oracle 19 测试，但 很长一段时间以来在旧版本中的行为相同):
Roles are not activited by default in PL/SQL stored units (tested with Oracle 19 but it's the same behaviour in older releases since very long time):
SQL> set serveroutput on
SQL> select banner from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> show user;
USER is "USR2"
SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------
CONNECT
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
STUFF_DOER
SQL> --
SQL> begin
2 for r in (select role from session_roles)
3 loop
4 dbms_output.put_line('role=' || r.role);
5 end loop;
6 end;
7 /
role=CONNECT
role=SELECT_CATALOG_ROLE
role=HS_ADMIN_SELECT_ROLE
role=STUFF_DOER
PL/SQL procedure successfully completed.
SQL> show errors
No errors.
SQL> create or replace procedure sr is
2 begin
3 for r in (select role from session_roles)
4 loop
5 dbms_output.put_line('role=' || r.role);
6 end loop;
7 end;
8 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> exec sr;
PL/SQL procedure successfully completed.
SQL>
注意匿名 PL/SQL(不存储在数据库中)和存储单元(存储在数据库中的过程/函数)之间的区别.
Note the difference between anonymous PL/SQL (which is not stored in the database) and a stored unit (procedure/function stored in the database).
这篇关于DBMS_PARALLEL_EXECUTE 和间接授予程序授权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
