Re: DBMS_SQL 8.0.5 Bug?

A copy of this was sent to danhw_at_aol.com (DanHW) (if that email address didn't require changing) On 8 Jun 1999 01:46:01 GMT, you wrote:

>I have a package that uses DBMS_SQL.SQL to create a table. Works fine on 8.0.4,
>but in 8.0.5 it gives me 'insufficent priv'. I can run the script from SQL*Plus
>with no problems, so it has something to do with with the fact it is a package.
>Some other observations:
>1. The table is to be owned by the user [same schema]
>2. SYS nor SYSTEM can run it either.
>3. CREATE table and CREATE index are affected, SELECTs , UPDATEs, INSERTS are
>fine
>4. Error occurs on the DBMS_SQL.PARSE call, not the DBMS_SQL.EXECUTE call
>5. Have rebuilt the DBMS_SQL and DBMS_SYS_SQL packages (repeatedly, actually)
>6. Granted roles explictly to the user to no avail.
>
>This is a new installation...did something go wrong on the install? or is there
>a bug in 8.0.5?
>(8.0.4 on NT, 8.0.5 on Alpha)
>
>Thanks for any suggestions...
>
>Dan Hekimian-Williams

roles are never enabled during the execution of a procedure (until special cases in Oracle8i with invokers rights).

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 CREATE TABLE to <OWNER>;

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

-- 
Opinions are mine and do not necessarily reflect those of Oracle Corporation