PLSQL can execute other user objects, but the process does not work (authid CURRENT_USER)

Problem description:

For example, the table rm_circuit is a user, grant B user select, insert, update, delete permissions, in B using the stored procedure to execute dbms_stats.gather_table_stats prompt: ora-020000: unable to analyze table "". "rm_circuit", insufficient privileges or does not exist. however, in PLSQL, execute begin dbms_stats.gather_table_stats ('A', 'rm _ circuit '); end; under user B. If you do not have the permission, how can B use PLSQL to execute it?

Cause:

Procedure requires the displayed authorization. Although the table has been authorized, it is invalid during the process.

Solution:

1. Add Table authorization operations during the process;

2. Add authid CURRENT_USER in the process.

 

I have encountered authid CURRENT_USER several times before, but I have not studied it carefully. Today, I spent more than half a day testing and testing the use of this item. The details are as follows:

Authid CURRENT_USER
If authid CURRENT_USER is added to the package or process, it is executed as the caller. It is executed as a definer by default.
Definer identity: During execution, the current user only has permissions other than role permissions.
Caller identity: During execution, the current user has all permissions granted (including role and system permissions ).

Test 1:
Gwm users have the following role permissions and system permissions:

Execution User: gwm. Analyzed table: B $ I _exch_info table under the lttfm user.
1. Execute the following statements in common PLSQL statements. The following statement analysis table shows that the user has no explicit permission to analyze any tables, however, this user has the DBA role permission to analyze any table.
Begin dbms_stats.gather_table_stats ('lttfs', 'B $ I _exch_info'); end; -- execution successful

2. Use a process to analyze the table. Execution failed. It indicates that the task is executed as the definer by default during the execution process, and the role permission is invalid.
Create or replace procedure p_authiduser_test is
Begin
Dbms_stats.gather_table_stats ('lttfs', 'B $ I _exch_info ');
End p_authiduser_test;

An error is prompted during execution:
SQL> exec gwm. p_authiduser_test;
Begin gwm. p_authiduser_test; end;
 
ORA-20000: unable to analyze table "lttfm". "B $ I _exch_info", insufficient privileges or does not exist
ORA-06512: In "SYS. dbms_stats", line 13046
ORA-06512: In "SYS. dbms_stats", line 13076
ORA-06512: In "gwm. p_authiduser_test", line 6
ORA-06512: In line 2

3. Grant the gwm user the permission to analyze any table and the execution is successful. It indicates that only explicitly authorized permissions are granted to the identity execution process of the definer.
Grant analyze any to gwm;

SQL> exec gwm. p_authiduser_test;
PL/SQL procedure successfully completed

4. You can also revoke the permission to analyze any table and add authid CURRENT_USER to the process. The following procedure can be executed successfully. It indicates that the process can recognize role permissions as the caller.
Revoke analyze any from gwm;
Create or replace procedure p_authiduser_test authid CURRENT_USER is
Begin
Dbms_stats.gather_table_stats ('lttfs', 'B $ I _exch_info ');
End p_authiduser_test;

Test 2: Run with another user
Revoke analyze any from gwm;
To ltwebgis users
The following process cannot be executed successfully. Still as prompted in Test 1 2:
Create or replace procedure p_authiduser_test is
Begin
Dbms_stats.gather_table_stats ('lttfs', 'B $ I _exch_info ');
End p_authiduser_test;

If you authorize gwm to analyze any table permissions or directly add authid CURRENT_USER to the process, you can also successfully create a table under the ltwebgis user.
SQL> exec gwm. p_authiduser_test;
PL/SQL procedure successfully completed

Test 3: test the impact of authid CURRENT_USER on user operations during execution
Test process: the user who created the process: gwm
Create or replace procedure p_authiduser_test is
V_session_user varchar2 (255 );
V_current_user varchar2 (255 );
V_current_schema varchar2 (255 );
Begin
Select sys_context ('userenv', 'session _ user') session_user,
Sys_context ('userenv', 'current _ user') CURRENT_USER,
Sys_context ('userenv', 'current _ scheme') current_schema
Into v_session_user, v_current_user, v_current_schema
From dual;
Dbms_output.put_line ('session _ User: '| v_session_user );
Dbms_output.put_line ('current _ User: '| v_current_user );
Dbms_output.put_line ('current _ Schema: '| v_current_schema );
/* Dbms_stats.gather_table_stats ('lttfs', 'B $ I _exch_info ');*/
End p_authiduser_test;

Gwm user executes this process:
SQL> set serveroutput on;
SQL> exec p_authiduser_test;
Session_user: gwm
CURRENT_USER: gwm
Current_schema: gwm

Go to the ltwebgis user for execution:
SQL> set serveroutput on;
SQL> exec gwm. p_authiduser_test;
Session_user: ltwebgis
CURRENT_USER: gwm
Current_schema: gwm

The modification process is as follows: add authid CURRENT_USER

Create or replace procedure p_authiduser_test authid CURRENT_USER is
V_session_user varchar2 (255 );
V_current_user varchar2 (255 );
V_current_schema varchar2 (255 );
Begin
Select sys_context ('userenv', 'session _ user') session_user,
Sys_context ('userenv', 'current _ user') CURRENT_USER,
Sys_context ('userenv', 'current _ scheme') current_schema
Into v_session_user, v_current_user, v_current_schema
From dual;
Dbms_output.put_line ('session _ User: '| v_session_user );
Dbms_output.put_line ('current _ User: '| v_current_user );
Dbms_output.put_line ('current _ Schema: '| v_current_schema );
/* Dbms_stats.gather_table_stats ('lttfs', 'B $ I _exch_info ');*/
End p_authiduser_test;

Run the following command under the gwm User:
SQL> set serveroutput on;
SQL> exec p_authiduser_test;
Session_user: gwm
CURRENT_USER: gwm
Current_schema: gwm

Go to the ltwebgis user for execution:
SQL> set serveroutput on;
SQL> exec gwm. p_authiduser_test;
Session_user: ltwebgis
CURRENT_USER: ltwebgis
Current_schema: ltwebgis

Test 3 shows that:
1. If gwm is executed by the current user, whether or not the authid CURRENT_USER is added, that is, whether it is executed as the definer or caller, the session opened by the user, the current mode is gwm, the only difference is that the permissions obtained by different identities are different.
2. If other ltwebgis users are used for execution, if authid CURRENT_USER is not added, but the user who opens the session is ltwebgis, the current user and current mode of the execution process are both gwm; however, if authid CURRENT_USER is added, the operation is performed by the ltwebgis user. The tables used for creating the tables are owned by the ltwebgis user.

Create a table in the following process: This process is performed by the gwm user.
Create or replace procedure p_authiduser_test authid CURRENT_USER is
Begin
Execute immediate 'create table fyzh_test (ID number, name varchar2 (255 ))';
End p_authiduser_test;
For gwm user execution, no matter whether or not authid CURRENT_USER is added, the created table is under gwm user; but if ltwebgis user executes this process, no
If authid CURRENT_USER is used, the table created during execution is gwm (the user executing the process is still gwm). If authid CURRENT_USER is added,
The created table is under the ltwebgis user (as shown in the above conclusion, with the authid CURRENT_USER, the user performing the operation under the ltwebgis user is ltwebgis ).

 

Then there was a problem:

After the authid CURRENT_USER is added to the process, you can manually execute the process. However, you still find that the job cannot be executed successfully and the system still prompts that you do not have sufficient permissions.

It seems that the job cannot recognize authid CURRENT_USER. You can only explicitly authorize the user to solve the problem.