Re: ORA 1031 - insufficient privileges & Java

Naven schreef:

> Hi,
>
> i have some tricky problem and no resolution for it:
>
> I created the following stored procedure for user Scott:
>
> CREATE OR REPLACE PROCEDURE updateProcedure ( L_SQL VARCHAR2 ) authId
> CURRENT_USER
> AS PRAGMA AUTONOMOUS_TRANSACTION;
> v_cur number;
> v_stat number;
> BEGIN
> /* EXECUTE IMMEDIATE L_SQL_QUERY; */
> v_cur := dbms_sql.open_cursor;
> dbms_sql.parse (v_cur,L_SQL,DBMS_SQL.native);
> v_stat := dbms_sql.execute(v_cur);
> dbms_sql.close_cursor(v_cur);
> END;
>

Bad idea to write code to create code - some very specific exceptions. Google why!

> >From SQL*Plus i can test it without problems:
>
> begin
> updateProcedure('CREATE OR REPLACE FUNCTION getValue RETURN VARCHAR2 IS
> calcul VARCHAR2(2000);Begin calcul := ''myValue''; RETURN calcul;
> END;');
> end;
>
Oh really? You do realize pl/sql does not use role privileges? Did you issue 'set role none' before testing?

> Then, i have a piece of Java code i loaded via javaload into the same
> user Scott:
>
[snip!] Why the diversion via java?!?

> -> this fails with an SQLException and ORA 1031 insufficient privileges
>
> What is missing there? This worked fine already on another Oracle
> database, but fails on this
> new installation. I think this is an issue of granting privileges, but
> which one? I tried the "grant
> execute on any procedure to Scott", without success. Even promoting
> Scott to dbadmin does
> not help!

See comments on roles above.
Execute any procedure is a security risk class I. Does scott own updateprocedure? If not, grant execute on it to scott - not a role.
Also, grant scott execute on sys.dbms_sql - again, not a role! Don't know how java and 'authid current user' work together. Check the working
environment for system grants to scott. Received on Thu Oct 19 2006 - 13:53:42 CEST