Oracle database stored Procedures and permissions

When executing a stored procedure, we may experience permission problems

defined by rights stored procedures
Caller rights stored procedures

When a stored procedure is created in a database, the definition permission is the default mode
when the specified Authid Current_ The user keyword is the caller's permission stored procedure
The most fundamental difference between the two is whether role can take effect in stored procedures

㈠ permissions Stored procedure issues
The definition of permission stored procedure role is invalid, you must have explicit authorization
even if you have a DBA Role, or you cannot access tables for different users

 sys@emrep> Grant Connect,resource to U1 identified by U1;
Grant succeeded.

Sys@emrep> Grant DBA to U2 identified by U2;
Grant succeeded.
Sys@emrep> Conn u1/u1 Connected.

U1@emrep> CREATE TABLE T as select * from User_objects;
Table created.
Sys@emrep> Conn U2/U2 Connected.
  u2@emrep> Create or Replace procedure P_test 2 as 3 begin 4 Delete from u1.t;
  5 commit;
  6 end;

7/warning:procedure created with compilation errors.
U2@emrep> Show Error; Errors for PROCEDURE p_test:line/col ERROR------------------------------------------------------------------------ 
-4/3 pl/sql:sql Statement ignored 4/18 pl/sql:ora-00942:table or view does not exist u2@emrep> Conn U1/U1
Connected.

U1@emrep> Grant all in T to U2;

Grant succeeded.
U1@emrep> Conn U2/U2 Connected.
  u2@emrep> Create or Replace procedure P_test 2 as 3 begin 4 Delete from u1.t;
  5 commit;
  6 end; 7/procedure Created. 
 

The

㈡ caller rights stored procedure issue
Caller rights stored procedure role compilation is not visible, but the runtime is visible
Avoid direct authorization with dynamic SQL and defer check of permissions to runtime

 u2@emrep> Conn u1/u1. Connected u1@emrep> all in t from revoke;

Revoke succeeded.
U1@emrep> Conn U2/U2 Connected.
  u2@emrep> Create or Replace procedure P_test 2 Authid current_user 3 as 4 begin 5 Delete from u1.t;
  6 commit;
  7 End;

8/warning:procedure created with compilation errors.
U2@emrep> Show Error; Errors for PROCEDURE p_test:line/col ERROR------------------------------------------------------------------------ -5/3 pl/sql:sql Statement ignored 5/18 pl/sql:ora-00942:table or view does not exist u2@emrep> create or R
  Eplace procedure p_test 2 Authid current_user 3 as 4 begin 5 Execute immediate 6 ' delete from u1.t ';
  7 commit;
  8 End;

9/procedure created.

u2@emrep> exec p_test;

Pl/sql procedure successfully completed.

  U2@emrep> Select COUNT (*) from u1.t; COUNT (*)----------0