Solutions to Oracle stored procedure execution Permissions

Stored procedures are essential tools in database systems. stored procedures are a collection of SQL statements pre-compiled to implement a complex function. I will not talk about its advantages. Let's talk about the problems I encountered. During project development, I need to use a stored procedure to implement a function. It involves determining whether a table has been created. If it is not created, the stored procedure will create the table.

1. CREATE OR REPLACE PROCEDURETestProc
2. IS
3. Flag number;
4. BEGIN
5. Select Count(*)IntoFlagFromAll_tablesWhereTable_name ='Temp3';
6. If (flag = 0)Then
7. ExecuteImmediate'Create global temporary table TEMP3 on commit preserve rows as select * from BUSI_ECONTRACT';
8. Else
9. ExecuteImmediate'Insert into TEMP3 select * from BUSI_ECONTRACT';
10. EndIf;
12. END;

Writing this stored procedure is relatively simple. The following prompt appears during the test execution:

From the error prompt, we locate the error and find that the stored procedure has insufficient permissions when executing the Create table statement. I tried to change the stored procedure to an anonymous stored procedure and run it in PL/SQL. Since the statement passed. This indicates that this statement is correct and the problem occurs during the stored procedure. I am using a DBA account to log on to the system. It is reasonable that there should be no insufficient permissions. Where else is the problem? By checking the information online, we found that Oracle has different requirements for executing the stored procedure than SQL-Server. This rule causes insufficient permission to execute the table creation statement.

Oracle stipulates that, by default, the role of the user who calls the stored procedure does not work, that is, the role has only the Public permission when executing the stored procedure. Therefore, when calling Create table, there will be a prompt of insufficient permissions.

There are two types of stored procedures: DR (Definer's Rights) Procedure and IR (Invoker's Rights) Procedure. Why are there two stored procedures? In fact, the following questions are clear after consideration. For example, when user hrch creates the Stored Procedure drop_table () for deleting the table tar_table, when user hrch calls it, it deletes the table tar_table under user hrch. What if another user scott calls it? Delete the tar_table table under user scott, or delete the tar_table under user hrch? In addition, if the Stored Procedure contains the table creation statement, both the user hrch and user scott call will fail, because the Public does not have the table creation permission, unless the Public grant table creation permission. Therefore, the caller of the stored procedure faces two problems:

  Name resolution environment of a stored procedure
Stored Procedure execution permission

These two problems can be solved by specifying the AUTHID attribute when defining the stored Procedure, that is, defining DR Procedure and IR Procedure.

DR Procedure

1. Definition
Create or replace procedure DEMO (ID in NUMBER) authid definer
...

BEGIN

...

End demo;
2. The name resolution environment is the Schema of the user who defines the stored procedure.
3. Only the Public permission is allowed to execute the stored procedure.

IR Procedure

1. Definition

Create or replace procedure DEMO (ID in NUMBER) AUTHID CURRENT_USER
...
BEGIN
...

End demo;
2. The name resolution environment is the Schema of the user who calls the stored procedure.
3. When executing the stored procedure, all the permissions of the caller are granted, that is, the caller's Role is valid.

We only need to use IR Procedure to solve the problem quickly. I changed the code to the following:

1. CREATE OR REPLACE PROCEDURETestProc AUTHIDCurrent_user
2. IS
3. Flag number;
4. BEGIN
5. Select Count(*)IntoFlagFromAll_tablesWhereTable_name ='Temp3';
6. If (flag = 0)Then
7. ExecuteImmediate'Create global temporary table TEMP3 on commit preserve rows as select * from BUSI_ECONTRACT';
8. Else
9. ExecuteImmediate'Insert into TEMP3 select * from BUSI_ECONTRACT';
10. EndIf;
12. END;

The stored procedure is successfully executed.