Re: user mgmt (grants etc) via stored procedures

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;

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

<orauser_at_my-deja.com> schreef in berichtnieuws 8hm6tl$e8d$1_at_nnrp1.deja.com...
> Is it possible to create roles, grants, revokes, create users,
> etc within stored procedures?
>
> I want to be able to do this but it doesnt take the create:
>
> create or replace procedure create_user (uname varchar2) as
> begin
> DBMS_OUTPUT.PUT_LINE('creating user:' ||uname);
> create user uname identified by uname;
> end create_user;
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Jun 07 2000 - 00:00:00 CDT