How to Resolve ORA-01031 for EXECUTE IMMEDIATE in Store Procedures

  • by

ORA-01031

Let's see how I reproduce ORA-01031 for statements using EXECUTE IMMEDIATE by the following example.

First of all, I create a role for a user.

SQL> conn / as sysdba
Connected.
SQL> create role hr_role;

Role created.

Then I grant CREATE ANY DIRECTORY and DROP ANY DIRECTORY to the role, then grant this role to the user subsequently.

SQL> grant create any directory, drop any directory to hr_role;

Grant succeeded.

SQL> grant hr_role to hr;

Grant succeeded.

Things look fine when we create or drop directories by the user.

SQL> conn hr/hr
Connected.
SQL> create directory tmp_path as '/u02/tmp';

Directory created.

SQL> drop directory tmp_path;

Directory dropped.

SQL> create directory tmp_path as '/u02/tmp';

Directory created.

Let's try to wrap them in stored procedures, which is also called named PL/SQL blocks or programming units.

SQL> create or replace procedure drop_create_tmp_dir is
begin
execute immediate 'drop directory tmp_path';
execute immediate 'create or replace directory tmp_path as ''/u02/tmp''';
end drop_create_tmp_dir;
/
  2    3    4    5    6
Procedure created.

But when we execute the named PL/SQL, we got ORA-01031 at line 3.

SQL> exec drop_create_tmp_dir;
BEGIN drop_create_tmp_dir; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "HR.DROP_CREATE_TMP_DIR", line 3
ORA-06512: at line 1

Solutions

This is because the system privileges inherited from role cannot be used in named stored procedures with definer's right.

Now we have several options, the first one is to grant all necessary privilege to the user directly, the second one is to use invoker's right, and the last one is to use anonymous PL/SQL blocks.

1. Directly Granting to User

By SYS:

SQL> grant create any directory, drop any directory to hr;

Grant succeeded.

Back to the procedure, we can execute it again.

By HR:

SQL> exec drop_create_tmp_dir;

PL/SQL procedure successfully completed.

The better thing is that we don't have to modify the procedure.

2. Use Invoker's Right

Another way to solve ORA-01031 for statements in EXECUTE IMMEDIATE is to use invoker's right to define the procedure.

Let's revert the granting by SYS.

SQL> revoke create any directory, drop any directory from hr;

Revoke succeeded.

Then we created the procedure with AUTHID CURRENT_USER clause.

SQL> conn hr/hr
Connected.
SQL> create or replace procedure drop_create_tmp_dir authid current_user is
begin
execute immediate 'drop directory tmp_path';
execute immediate 'create or replace directory tmp_path as ''/u02/tmp''';
end drop_create_tmp_dir;
/
  2    3    4    5    6
Procedure created.

Try to execute the procedure.

SQL> exec drop_create_tmp_dir;

PL/SQL procedure successfully completed.

We did it.

3. Anonymous PL/SQL Block

What we mean in the above is that role privileges cannot penetrate NAMED stored procedures. That is to say, you can use role privileges in anonymous PL/SQL blocks. For instance, we can rewrite the stored procedure to an anonymous PL/SQL as this:

begin
execute immediate 'drop directory tmp_path';
execute immediate 'create or replace directory tmp_path as ''/u02/tmp''';
end;
/

You can save and use it as a normal SQL script file.

For the same reason, CREATE TABLE in EXECUTE IMMEDIATE can also throw ORA-01031.

Leave a Reply

Your email address will not be published. Required fields are marked *