Re: DDL in Dynamic PL/SQL

Dick Willis wrote:
>
> Trying to create a table that I don't know the name of a priori. I am
> therefore using Dynamic PL/SQL in my procedure to perform the CREATE TABLE
> function.
>
> Procedure compiles fine ...
>
> Pseudo code is:
>
> Get table name
> Build SQL string
> Open cursor with DBMS_SQL.OPEN_CURSOR
> Parse SQL statement for cursor DBMS_SQL.PARSE
> Execute on cursor DBMS_SQL.EXECUTE
>
> Problem is that when procedure is called, it fails at the .PARSE step with
> an 'insufficient privilege' error.
>
> Any clues? Am creating the function in an account with the DBA role
> assigned. Also calling it from the same account, so I thought I had the
> privileges nailed. Same code with UPDATE, SELECT, etc. works OK. Seems to
> be solely due to DDL SQL call.
>
> --
> Dick Willis, Senior Engineer
> Synchrony Industrial Controls, Inc.
> rmw_at_synchrony.com
> http://www.synchrony.com

Try giving the user 'CREATE ANY TABLE' directly. Privileges through a role do not apply when executing stored procedures. If this is not practical, you can use the workaround we do:

1. create an 'exec_ddl' procedure in your dba account
2. have the user pass the ddl to execute to this procedure

Again, the dba account needs direct privs, and the user who will own the objects created must have direct privs to those objects referenced in the ddl.

hth

-- 
"Came for the party, left on the run"

Robert Christenson
Gazette Technologies
robertoc#spam~be~gone#@fyiowa.infi.net