Use of authid CURRENT_USER

We know that the role permissions of users are not available in the stored procedure. In this case, we usually need explicit authorization, such as grant create table to usera. However, this method is too troublesome. Sometimes a lot of authorization is required to execute the stored procedure. In fact, oracle provides us with a way to use the role permission in the stored procedure: Modify the stored procedure. When the authid CURRENT_USER is added, the stored procedure can use the role permission. The following is an example:
 
SQL> select * from V $ version;
Banner
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod
PL/SQL release 10.2.0.1.0-Production
Core 10.2.0.1.0 Production
TNS for 32-bit windows: Version 10.2.0.1.0-Production
Nlsrtl version 10.2.0.1.0-Production
 
SQL> conn sjh/sjh
Connected.
SQL> Create or replace procedure p_test
2 is
3 begin
4 execute immediate 'create table creat_table (ID number )';
5 end;
6/
The process has been created.
 
SQL> exec p_test;
Begin p_test; end;
*
Row 3 has an error:
ORA-01031: insufficient Permissions
ORA-06512: In "sjh. p_test", line 4
ORA-06512: In line 1

SQL>
SQL> select * From dba_role_privs where grantee = 'sjh ';
Grantee granted_role ADM def
------------------------------------------------------------------
Sjh resource no Yes
 
-- In fact, the sjh user has the resource role, that is, the table creation permission.
 
SQL> select * From dba_role_privs where grantee = 'sfx ';
Grantee granted_role ADM def
------------------------------------------------------------------
SFX resource no Yes
SFX connect No Yes
SFX plustrace No Yes
 
SQL> Create or replace procedure p_test
2 authid CURRENT_USER
3 is
4 begin
5 execute immediate 'create table creat_table (ID number )';
6 end;
7/
The process has been created.
 
SQL> exec p_test;
The PL/SQL process is successfully completed.
 
SQL> select * From creat_table;
Unselected row
 
-- The authid CURRENT_USER option is added to the stored procedure. The table is successfully created.