1

Topic: ACCESSIBLE BY for EXECUTE IMMEDIATE

Good afternoon. There is such problem: it would be desirable to implement "white lists" for performance of procedures, but a problem that procedures are caused through dynamic SQL. Even if procedure from the white list from which it is fulfilled EXECUTE IMMEDIATE, all the same produces an error "PLS-00904: insufficient privilege to access object...". Someone can faced it. Whether it is possible ? That it was clearer: procedure And Would cause procedure through dynamic SQL. Procedure And is in "white lists" of procedure B.

2

Re: ACCESSIBLE BY for EXECUTE IMMEDIATE

Lemkoleg wrote:

it would be desirable to implement "white lists" for performance of procedures, but a problem that procedures are caused through dynamic SQL

RTFM ACCESSIBLE BY Clause: Usage Notes (FAQ)
[spoiler]

Database PL/SQL Language Reference wrote:

The ACCESSIBLE BY clause allows access only when the call is direct. The check will fail if the access is through static SQL, DBMS_SQL, or dynamic SQL.

[/spoiler]

3

Re: ACCESSIBLE BY for EXECUTE IMMEDIATE

Elic;
It is sad. Thanks

4

Re: ACCESSIBLE BY for EXECUTE IMMEDIATE

Lemkoleg wrote:

It is sad.

To a heap. We cannot carry out the task referring on accessible unit in session accessor'a:

SQL> create or replace
2 procedure p1
3 is
4 begin
5 insert
6 into tbl
7 values (' P1 ');
8 end;
9 /
Procedure created.
SQL> begin
2 dbms_scheduler.create_job (
3 job_name => ' JOB_XX ';
4 job_type => ' PLSQL_BLOCK ';
5 job_action => ' begin p1; end; ';
6 enabled => FALSE
7);
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> create or replace
2 procedure p3
3 is
4 begin
5 dbms_scheduler.run_job (
6 job_name => ' JOB_XX ';
7 use_current_session => TRUE
8);
9 end;
10 /
Procedure created.
SQL> exec p3
PL/SQL procedure successfully completed.
SQL> create or replace
2 procedure p3
3 is
4 begin
5 dbms_scheduler.run_job (
6 job_name => ' JOB_XX ';
7 use_current_session => FALSE
8);
9 end;
10 /
Procedure created.
SQL> exec p3
PL/SQL procedure successfully completed.
SQL> create or replace
2 procedure p1
3 accessible by (
4 procedure p3
5)
6 is
7 begin
8 insert
9 into tbl
10 values (' P1 ');
11 end;
12 /
Procedure created.
SQL> create or replace
2 procedure p3
3 is
4 begin
5 dbms_scheduler.run_job (
6 job_name => ' JOB_XX ';
7 use_current_session => TRUE
8);
9 end;
10 /
Procedure created.
SQL> exec p3
BEGIN p3; END;
*
ERROR at line 1:
ORA-06550: line 1, column 763:
PLS-00904: insufficient privilege to access object P1
ORA-06512: at "SYS.DBMS_ISCHED", line 238
ORA-06512: at "SYS.DBMS_SCHEDULER", line 568
ORA-06512: at "SCOTT.P3", line 4
ORA-06512: at line 1
SQL> create or replace
2 procedure p3
3 is
4 begin
5 dbms_scheduler.run_job (
6 job_name => ' JOB_XX ';
7 use_current_session => FALSE
8);
9 end;
10 /
Procedure created.
SQL> exec p3
PL/SQL procedure successfully completed.
SQL>

SY.