Re: Create user via stored proc/package ?

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 01 Jul 1999 16:55:48 GMT
Message-ID: <377b9c6b.14562659@inet16.us.oracle.com>

On Thu, 01 Jul 1999 14:23:12 GMT, adrianh_at_globalnet.co.uk (Adrian Harrison) wrote:

>I'm a bit confused about using DBMS_SQL package as although I've used Oracle for quite a while now
>it's mainly been via VB!
>
>What I was hoping to do was to call a function to create a new user, modify a user etc...
>
>So for example -
>
>
> CREATE OR REPLACE PROCEDURE create_user (username IN VARCHAR2, password IN VARCHAR2)
> IS
> BEGIN
> CREATE USER username identified BY password;
> /* worry about roles etc.. later */
> END;
>
>How woud this procedure look using the DBMS_SQL package?

you could create a procedure like...

  procedure execute_immediate( p_command varchar2 ) is     l_cursor number;
    l_status number;
  begin
    l_cursor := dbms_sql.open_cursor;
    dbms_sql.parse( l_cursor, p_command, dbms_sql.native );     l_status := dbms_sql.execute( l_cursor );     dbms_sql.close_cursor( l_cursor );
  exception
    when others then

      if dbms_sql.is_open( l_cursor ) then
        dbms_sql.close_cursor( l_cursor );
      end if;
      raise;

  end execute_immediate;

then your create_user procedure could be...

  procedure create_user(

    p_username varchar2,
    p_password varchar2,
    p_default_ts varchar2 default null,
    p_temp_ts varchar2 default null ) is

  --
    l_command long;
  begin
    l_command := 'create user ' || p_username ||

                 ' identified by ' || p_password;

    if p_default_ts is not null then
      l_command := l_command || ' default tablespace ' || p_default_ts;

    end if;

    if p_temp_ts is not null then
      l_command := l_command || ' temporary tablespace ' || p_temp_ts;     end if;

    execute_immediate( l_command );

  end create_user;

NOTE: Make sure the owner of the execute_immediate procedure has the privilege 'CREATE USER' granted directly to them.

hope this helps.

chris.

>
>Any ideas
>
>Thanks
>
>Adrian Harrison
>
>On Wed, 30 Jun 1999 12:58:23 +0100, "Steve B" <steve.bright_at_capgemini.co.uk> wrote:
>
>>You can use the DBMS_SQL package to write dynamic sql statements, including
>>DDL..
>>
>>Note : Since you will be running these statements from within stored
>>procedures then grant create / alter user etc must be expicitly granted to
>>the user running the procedures, and not granted via roles..
>>
>>hope this helps
>>
>>
>>
>>Cheers
>>
>>Steve Bright
>>
>>steve.bright_at_capgemini.co.uk
>>
>>Adrian Harrison wrote in message <3779f20e.11439609_at_news.globalnet.co.uk>...
>>>Using VB6 Enterprise & Oracle 7.34
>>>
>>>Is it possible to use "CREATE USER", "ALTER USER" etc.. statements in a
>>stored procedure or package
>>>so that I can call them from my program thereby providing full user
>>maintainence ?
>>>
>>>If not what's the best I can achieve?
>>>
>>>Thanks
>>>
>>>Adrian Harrison
>>

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jul 01 1999 - 11:55:48 CDT