2 raven13:
According to gminter (AKA Олег Филимонов), package is created with AUTHID CURRENT_USER, which means it is executed on caller's, not package owner's behalf. Based on that I can assume user HAS update, delete and insert, but most likely via role. Why? We all are used to the fact "role based privileges are disabled in stored procedures". However it is not entirely true. It changed since Oracle introduced AUTHID CURRENT_USER. In that case role based privileges or a caller are NOT IGNORED:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
SQL> connect u1/xxx@xxx
Connected.
SQL> select * from tbl1;
X
--------------------------------------------------------------------------------
This is U1.TBL1
SQL> create role r1
2 /
Role created.
SQL> grant select on tbl1 to r1
2 /
Grant succeeded.
SQL> grant r1 to scott
2 /
Grant succeeded.
SQL> connect system/xxx@xxx
Connected.
SQL> create or replace
2 function func1
3 return varchar2
4 is
5 v_x varchar2( 100 );
6 begin
7 execute immediate 'select x from u1.tbl1' into v_x;
8 return v_x;
9 end;
10 /
Function created.
SQL> create or replace
2 function func2
3 return varchar2
4 authid current_user
5 is
6 v_x varchar2( 100 );
7 begin
8 execute immediate 'select x from u1.tbl1' into v_x;
9 return v_x;
10 end;
11 /
Function created.
SQL> grant execute on func1 to scott
2 /
Grant succeeded.
SQL> grant execute on func2 to scott
2 /
Grant succeeded.
SQL> connect scott/xxx@xxx
Connected.
SQL> set serveroutput on
SQL> select * from u1.tbl1;
X
--------------------------------------------------------------------------------
This is U1.TBL1
SQL> exec dbms_output.put_line(system.func1)
BEGIN dbms_output.put_line(system.func1); END;
*
ERROR at line 1 :
ORA- 00942 : table or view does not exist
ORA- 06512 : at "SYSTEM.FUNC1" , line 6
ORA- 06512 : at line 1
SQL> exec dbms_output.put_line(system.func2)
This is U1.TBL1
PL/SQL procedure successfully completed.
As you can see, user scott calling func1 which has AUTHID DEFINER can not select form U1.TBL1 since user scott has SELECT on U1.TBL1 via role R1. However user scott calling func2 which has AUTHID CURRENT_USER can select form U1.TBL1 even though select is granted via role R1.
SY
