Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5904
  • Last Modified:

Insufficient Privileges when dynamically creating procedure with dbms_sql

Hi experts. I am trying to create a stored procedure at run time using dbms_sql.parse and dbms_sql.execute. Once I have the code ready, I call something like

dbms_sql.parse("CREATE OR REPLACE myProc AS ... etc ... ");

I am getting the error "Insufficient Privileges".

The funny part is, when I create myProc manually I have no problems, and, if once created I run my program again to re-create it (replace it) dynamically, I have no problems either!!!!  Any ideas?

Thanks in advance.
0
diegoful
Asked:
diegoful
  • 12
  • 5
  • 4
  • +4
1 Solution
 
paquicubaCommented:
You don't have rights to the SYS.DBMS_SQL package
0
 
paquicubaCommented:
Grant execute on dbms_sql to user executing this package.

Hope this helps!
0
 
slightwv (䄆 Netminder) Commented:
Any particular reason you aren't using:  execute immediate?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
diegofulAuthor Commented:
Paquicuba, did you read "once created I run my program again to re-create it (replace it) dynamically, I have no problems" ? In that phrase, "dyanmically" means I am using the SYS.DBMS_SQL package. Thanks anyway.
0
 
neo9414Commented:
i think using execute immediate will be better and easier.

eg:

declare
query varchar2(30000);
begin
query := 'create or replace procedure dynamicproc
          as
          num number;
          begin
          num := 5;
          end;';
execute immediate query;          
end;
/
0
 
neo9414Commented:
diegoful:
The funny part is, when I create myProc manually I have no problems, and, if once created I run my program again to re-create it (replace it) dynamically, I have no problems either!!!!  

I think the problem is not because of rights to the SYS.DBMS_SQL package. There is something else...

Try using execute immediate as in the above post.
0
 
diegofulAuthor Commented:
Slightwv: I am not using execute inmediate because my function's code is too long to fit in a VARCHAR2. Thanks anyway.
0
 
diegofulAuthor Commented:
neo9414: I am not using execute inmediate because my function's code is too long to fit in a VARCHAR2. Thanks anyway.
0
 
jrb1Commented:
I try this with a long procedure and I get:

ORA-00972: identifier is too long

Are you sure you have rights to dbms_sql?
0
 
diegofulAuthor Commented:
jrb1: There is a way to pass a CLOB instead of a VARCHAR2 to DMBS_SQL.PARSE. That is the implementation that I am using, although I don't think this has anything to do with my error.

Here is a reference on how to pass a CLOB: http://www.psoug.org/reference/dbms_sql.html

Regards,
Diego.
0
 
slightwv (䄆 Netminder) Commented:
Stupid question......  Is the original code snipit from a PL/SQL block or another procedure/function?  By chance do you have execute permission on it (i.e./ it's not the dbms_sql that's causing the error but the execution of the procedure trying to create the procedure).

Remember:  I said it was stupid before  I asked.....  so, no flames!!!!!!!
0
 
diegofulAuthor Commented:
I said something wrong. There is no way to pass a CLOB to the DBMS_SQL.Parse procedure. However, there is a way to pass a table with lots of lines of code. This allows me to create a very large store procedure. The reference I used is http://www.psoug.org/reference/dbms_sql.html.

EXPERTS: Please remember that once I create the procedure in the traditional way, I have NO PROBLEMS replacing it (re-creating it) using something like

DBMS_SQL.PARSE(my_cursos, my_table_of_plsql_code, my_table_of_plsql_code.FIRST, my_table_of_plsql_code.LAST, FALSE, DBMS_SQL.NATIVE);

It is precisely this statement which is causing the "Insufficient Privileges" error. The first line of code says "CREATE OR REPLACE PROCEDURE myProc AS". Any more ideas?
0
 
diegofulAuthor Commented:
No question is stupid, slightwv. I thank you very much for your interest. I am using the stored procedure execute_plsql_block that you can find in http://www.psoug.org/reference/dbms_sql.html. But I do have execute permissions over that one becase, once I create the procedure in the traditional way, I have no problems replacing it (re-creating it) using that same execute_plsql_block procedure.
0
 
diegofulAuthor Commented:
Thank you very much to you all, you all have given me good advices. It just happens that your advices have not been helpful for the particular situation I am facing. I promise not to answer with flames from now on. It's just that I am desperate.
0
 
neo9414Commented:
try this
(replaced varchar2 by long. long can handle 2GB of data).


declare
query long;
begin
query := 'create or replace procedure dynamicproc
          as
          num number;
          begin
          num := 5;
          end;';
execute immediate query;          
end;
0
 
jrb1Commented:
Interestingly, I apparently was able to pass a clob to dbms_parse, but it's probably impliciltly converting.

Can you test it by breaking the procedure down to a do-nothing shell?  Something like:

create or replace procedure myproc as i number; begin i := 1; end;

Run this through your procedure and see if it errors out?  It would be easier to debug this I think.
0
 
diegofulAuthor Commented:
jrb1: It seems that whatever i pass to "execute immediate" is being implicitly converted to a VARCHAR2. Therefore, your code runs ok, but when i try this:

declare
query long;
begin
query := 'create or replace procedure dynamicproc
          as
          num number;
          begin '
         || lpad('/*  My Comment */', 40000) ||
          'num := 5;
          end;';
execute immediate query;          
end;

I get:  ORA-06502: PL/SQL: numeric or value error: character string buffer too small

0
 
diegofulAuthor Commented:
I am sorry, that last comment was for NEO9414. Now, jrb1, when I try to create the simplest stored procedure, even passing a varchar instead of clob, I get the same error "Insufficient Privileges".

So, basically, we can be sure that EXECUTE IMMEDIATE performs different actions than DBMS_SQL.PARSE or it uses a different security context.

The thing is I can create a procedure dynamically using EXECUTE IMMEDIATE, but I seem to lack privileges when I do it using DBMS_SQL.PARSE. Don't know how to overcome this.
0
 
neo9414Commented:
yeah..it takes 32Kb only :-(
0
 
paquicubaCommented:
<< I am getting the error "Insufficient Privileges". >>
Can you post the accompanying messages ?
Thanks
0
 
johnsoneSenior Oracle DBACommented:
My guess is that you are granted the RESOURCE role, which has the CREATE PROCEDURE privlege.

PL/SQL does not understand privileges granted through a role, and therefore does not think you have the CREATE PROCEDURE privlege.

As a privileged user do this:

GRANT CREATE PROCEDURE TO <user>;

That should solve the insufficient privileges problem.
0
 
diegofulAuthor Commented:
The complete error says:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SQL", line 1522
ORA-06512: at "SYS.DBMS_SQL", line 33
ORA-06512: at "GRLUSER.EXECUTE_PLSQL_BLOCK", line 48
ORA-06512: at "GRLUSER.RECREATE_SYNC_PROC", line 676
ORA-06512: at line 7

There is no more info than that. Line 48 in "GRLUSER.EXECUTE_PLSQL_BLOCK" says:
dbms_sql.PARSE(ds_cur, sql_table, sql_table.FIRST, sql_table.LAST, FALSE, dbms_sql.NATIVE);
0
 
diegofulAuthor Commented:
Johnsone has the answer. However, John, can you explain why EXECUTE IMMEDIATE allows the creation of procedures without having to grant the privilege directly to the user while DBMS_SQL does not?
0
 
paquicubaCommented:
PL/SQL doesn't see ROLES. EXECUTE IMMEDIATE and DBMS_SQL should behave the same way:

SQL> SET ROLE NONE
  2  /

Role set.

Elapsed: 00:00:00.00
SQL> CREATE OR REPLACE PROCEDURE TEST_DBMS_SQL( p_sqlstr in VARCHAR2)
  2  is
  3    tCursor PLS_INTEGER;
  4    RetVal  NUMBER;
  5  BEGIN
  6    tCursor := dbms_sql.open_cursor;
  7    dbms_sql.parse(tCursor, p_sqlstr, dbms_sql.NATIVE);
  8    RetVal := dbms_sql.execute(tCursor);
  9    dbms_sql.close_cursor(tCursor);
 10  END;
 11  /

Procedure created.

Elapsed: 00:00:00.01
SQL> EXEC TEST_DBMS_SQL('SELECT * FROM DUAL' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> EXEC TEST_DBMS_SQL('CREATE SYNONYM TAB_A FOR TABLE_A' );
BEGIN TEST_DBMS_SQL('CREATE SYNONYM TAB_A FOR TABLE_A' ); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 906
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "ALEX.TEST_DBMS_SQL", line 7
ORA-06512: at line 1


Elapsed: 00:00:00.01
SQL> CREATE OR REPLACE PROCEDURE TEST_DBMS_SQL( p_sqlstr in VARCHAR2)
  2  is
  3  BEGIN
  4    EXECUTE IMMEDIATE p_sqlstr;
  5  END;
  6  /

Procedure created.

Elapsed: 00:00:00.31
SQL> EXEC TEST_DBMS_SQL('SELECT * FROM DUAL' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> EXEC TEST_DBMS_SQL('CREATE SYNONYM TAB_A FOR TABLE_A' );
BEGIN TEST_DBMS_SQL('CREATE SYNONYM TAB_A FOR TABLE_A' ); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "ALEX.TEST_DBMS_SQL", line 4
ORA-06512: at line 1


Elapsed: 00:00:00.01
SQL> @SYSTEM
Connected.
SQL> GRANT CREATE SYNONYM TO ALEX;

Grant succeeded.

Elapsed: 00:00:00.00
SQL> @ALEX
Connected.
SQL> EXEC TEST_DBMS_SQL('CREATE SYNONYM TAB_A FOR TABLE_A' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> CREATE OR REPLACE PROCEDURE TEST_DBMS_SQL( p_sqlstr in VARCHAR2)
  2  is
  3    tCursor PLS_INTEGER;
  4    RetVal  NUMBER;
  5  BEGIN
  6    tCursor := dbms_sql.open_cursor;
  7    dbms_sql.parse(tCursor, p_sqlstr, dbms_sql.NATIVE);
  8    RetVal := dbms_sql.execute(tCursor);
  9    dbms_sql.close_cursor(tCursor);
 10  END;
 11  /

Procedure created.

Elapsed: 00:00:00.15
SQL> DROP SYNONYM TAB_A;

Synonym dropped.

Elapsed: 00:00:00.01
SQL> EXEC TEST_DBMS_SQL('CREATE SYNONYM TAB_A FOR TABLE_A' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
0
 
paquicubaCommented:
As you can see in the above posting, the procedure works with DML and fails with DDL, that is, you must explicitly grant privileges to the user to perform DDL.
0
 
JankovskyCommented:
The matter of the problem could be an authentization method of the dynamic sql within PL/SQL package/procedure.

You can make either definer or current_user authentization:

CURRENT_USER indicates that the methods of the class execute with the privileges
of CURRENT_USER. This clause is the default and creates an invoker-rights class.
This clause also specifies that external names in queries, DML operations, and
dynamic SQL statements resolve in the schema of CURRENT_USER. External names
in all other statements resolve in the schema in which the methods reside.

DEFINER indicates that the methods of the class execute with the privileges of the
owner of the schema in which the class resides, and that external names resolve in
the schema where the class resides. This clause creates a definer-rights class.

example:

CREATE OR REPLACE PROCEDURE drop_it (kind IN VARCHAR2, name IN
VARCHAR2)
AUTHID CURRENT_USER
AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;
/

My note: The default Definer authid doesn't work correctly in any case. It's recommended to specify it explicitelly.
0
 
diegofulAuthor Commented:
Thank you all for your comments. Granting direct privileges to the user is the answer. I still don´t know why EXECUTE IMMEDIATE and DMBS_SQL behave differently. Anyway... Other comments where also very enlighting, but I had to give the points to the problem solver.

Regards,
Diego
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 12
  • 5
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now