Oracle database stored procedures and permissions

When executing a stored procedure, we may encounter permission problems ● definer permission Stored Procedure ● when the caller permission stored procedure creates a stored procedure in the database, the definer permission is the default mode. When the AUTHIDCURRENT_USER keyword is specified, the most fundamental difference between the caller permission stored procedures is whether the role can take effect in the stored procedure.

When executing a stored procedure, we may encounter permission problems ● definer permission Stored Procedure ● when the caller permission stored procedure creates a stored procedure in the database, the definer permission is the default mode. When the AUTHID CURRENT_USER keyword is specified, the most fundamental difference between the caller permission and the stored procedure is whether the role can take effect in the stored procedure.

When executing a stored procedure, we may encounter permission problems.

● Definer permission Stored Procedure
● Caller permission Stored Procedure

When creating a stored procedure in a database, the User-Defined permission is in the default mode.
After the AUTHID CURRENT_USER keyword is specified, it is the stored procedure of caller permission.
The most fundamental difference between them is whether role can take effect in the stored procedure.

(I) Permission stored procedure of the definer
The role of the definer permission stored procedure is invalid and must be explicitly authorized.
Even if you have dba role, you still cannot access tables of 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/u1Connected. u1 @ EMREP> create table t as select * from user_objects; Table created. sys @ EMREP> conn u2/u2Connected. 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 -------- Errors 4/3PL/SQL: SQL Statement ignored4/18PL/SQL: ORA-00942: table or view does not existu2 @ EMREP> conn u1/u1Connected. u1 @ EMREP> grant all on t to u2; Grant succeeded. u1 @ EMREP> conn u2/u2Connected. u2 @ EMREP> create or replace procedure p_test 2 as 3 begin 4 delete from u1.t; 5 commit; 6 end; 7/Procedure created.

(Ii) caller permission stored procedures
The role compilation process of caller permission storage is invisible, but visible at runtime.
Dynamic SQL is used to avoid direct authorization, and permission check is postponed to running

U2 @ EMREP> conn u1/u1Connected. u1 @ EMREP> revoke all on t from u2; Revoke succeeded. u1 @ EMREP> conn u2/u2Connected. 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 -------- Errors 5/3PL/SQL: SQL Statement ignored5/18PL/SQL: ORA-00942: table or view does not existu2 @ EMREP> create or replace 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