Re: user mgmt (grants etc) via stored procedures

I tried the execute immediate in 8i, as follows:

create or replace procedure foo2 (

        dummy_var in varchar2) as
BEGIN
        execute immediate 'create user foo identified by foo'; END;
/
exec foo2('test');

But I get ORA-01031: insufficient privileges

I can type the sql for create user in sqlplus and it works. I am executing this script by cutting/pasting into the same sqlplus window...

In article <960414319.9760.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> You'll need to use dbms_sql to do this in Oracle 7.3 and 8.0 and
 execute
> immediate in Oracle 8i.
> In order to hide the details you could write the following procedure
 in 7.3
> and 8.0
>
> create or replace procedure do_sql(sqlstr in varchar2) is
> cur_handle integer default dbms_sql.open_cursor;
> begin
> dbms_sql.parse(cur_handle, sqlstr, dbms_sql.native);
> dbms_sql.close_cursor(cur_handle);
> exception
> when others then
> dbms_output.put_line(sqlerrm);
> if cur_handle > 0 then
> dbms_sql.close_cursor(cur_handle);
> end if;
> end;
>
> In Oracle 8i this procedure would consist of
> execute immediate sqlstr;
>
> Please note roles are NOT enabled in stored procedures.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jun 08 2000 - 00:00:00 CDT