Re: user mgmt (grants etc) via stored procedures

In article <8hmp50$s87$1_at_nnrp1.deja.com>,   orauser_at_my-deja.com wrote:
> 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...
>

see
http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html

and read about autonomous transactions to see if they apply in your environment.

> 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.
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.