About resolution of Oracle stored procedure execution permissions issues

Stored procedures are an essential tool in a database system, and stored procedures are precompiled sets of SQL statements that implement a complex function. The advantages of it I will not say more, to talk about the problems I encountered. In the process of developing a project, I need to use a stored procedure to implement a function, which involves determining whether a table has been established, not established by the stored procedure to build this table.

CREATE OR REPLACE PROCEDURE testproc
is
  flag number;
BEGIN
  Select COUNT (*) into the flag from All_tables where table_name= ' TEMP3 ';
  if (flag=0) then
     execute immediate ' Create global temporary table TEMP3 on commit preserve rows as select * from Busi_ Econtract ';
  Else
     execute immediate ' INSERT INTO TEMP3 select * from Busi_econtract ';
  End If;

End;

Writing this stored procedure is relatively simple, in the process of testing execution, the system appears as follows:

The error prompted us to locate the error and found that the stored procedure did not have sufficient permissions when executing the Create table statement. I tried to change the stored procedure to an anonymous stored procedure executed in Pl/sql, since the statement was passed. This indicates that the statement is not a problem and that the problem occurs during the execution of the stored procedure. I am using the DBA account login system, there should be no lack of access to the problem ah. Where does the problem arise? Through the Internet access to information, found that Oracle for the implementation of stored procedures and sql-server different provisions, this rule caused the execution of the table statement insufficient permissions.

Oracle stipulates that, by default, the role of the user in the calling stored procedure does not work, that is, only public permissions are performed when the stored procedure is executed. So when you call create TABLE, you get a hint of insufficient permissions.

Stored procedures are divided into two types, namely, Dr (Definer ' s Rights) Procedure and IR (invoker ' Rights) Procedure. Why are there two kinds of stored procedures? In fact, after considering the following questions to be clear. For example, the user hrch creates a stored procedure drop_table () that deletes the table tar_table, and when the user hrch the call, deletes the table Hrch under the user tar_table; if it was another user Scott called. Delete the tar_table table under user Scott, or delete the tar_table under the user Hrch. Also, if the stored procedure contains a build statement, either user Hrch or user Scott calls will fail because public does not have table permissions, unless the table permission is set for public grant. Therefore, the caller of the stored procedure faces two problems:

Name resolution environment for stored procedures
Execute permissions for Stored procedures

These two issues can be resolved by specifying the Authid property, defined as Dr Procedure and IR Procedure, when defining a stored procedure.

DR Procedure

1. Definition
CREATE OR REPLACE Procedure DEMO (ID in number) Authid Definer as
...

BEGIN

...

End DEMO;
2, the name resolution environment is the schema for the user who defines the stored procedure.
3. When executing the stored procedure, only public permissions are available.

IR Procedure

1. Definition

CREATE OR REPLACE Procedure DEMO (ID in number) Authid Current_User as
...
BEGIN
...

End DEMO;
2, the name resolution environment is the schema that contains the user who invoked the stored procedure.
3. Executing the stored procedure has all the permissions of the caller, that is, the caller's role is valid.

The problem we're dealing with is just using IR procedure to solve the problem quickly, and I'm going to change the code as follows:

CREATE OR REPLACE PROCEDURE testproc authid current_user 
is
  flag number;
BEGIN
  Select COUNT (*) into the flag from All_tables where table_name= ' TEMP3 ';
  if (flag=0) then
     execute immediate ' Create global temporary table TEMP3 on commit preserve rows as select * from Busi_ Econtract ';
  Else
     execute immediate ' INSERT INTO TEMP3 select * from Busi_econtract ';
  End If;

End;

Execute the stored procedure and pass it smoothly.