| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: 'insufficient privileges' from DBMS_SQL.PARSE call
A copy of this was sent to fanda_at_bigfoot.com
(if that email address didn't require changing)
On Thu, 15 Apr 1999 14:54:33 GMT, you wrote:
>
>
>> >
>> > Procedure created.
>> >
>> > SQL> execute beng0008;
>> > CREATE VIEW sec_psf_effective_view AS SELECT * FROM sec_psf WHERE psf_part
><>
>> > psf_assy
>> > begin beng0008; end;
>> >
>> > ERROR at line 1:
>> > ORA-01031: insufficient privileges
>> > ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
>> > ORA-06512: at "SYS.DBMS_SQL", line 32
>> > ORA-06512: at "CENTRA3.BENG0008", line 14
>> > ORA-06512: at line 1
>> >
>> > Is this an Oracle configuration problem? How do I get over it?
>>
>> You need execute privilege on DBMS_SQL. Talk to your DBA.
>>
>
>I got the same problem, and execute priv. on DBMS_SQL doesn't help. The
>error stack indicates that the problem is in the DBMS_SYS_SQL object, and
>line 239 is the same place as in my appl.
>I have to execute all my create statements as sys when I'm using dbms_sql
>inside a procedure.
>Other ddl-statements like 'drop view' or 'drop table' works allright!
>
>BUT, the statements parses without errors when it's outside a procedure.
>For instance, this works fine:
>sqlplus> exec dbms_sql.parse(dbms_sql.open_cursor,'Create sequence tmpseq',
>dbms_sql.v7);
>
roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.
You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.
grant <priv> to <OWNER>;
>but this one fails:
>sqlplus> CREATE OR REPLACE PROCEDURE tmp
>sqlplus> BEGIN
>sqlplus> dbms_sql.parse(dbms_sql.open_cursor,'Create sequence tmpseq',
>dbms_sql.v7);
>sqlplus> END;
>sqlplus> /
>
>sqlplus> exec tmp;
>
>Annoying ...
>
>************
>fanda
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |