Re: Create user via stored proc/package ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 01 Jul 1999 16:58:48 GMT
Message-ID: <378c9dd1.72103980@newshost.us.oracle.com>

A copy of this was sent to adrianh_at_globalnet.co.uk (Adrian Harrison) (if that email address didn't require changing) On Thu, 01 Jul 1999 14:23:12 GMT, you 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?
>

try this:

create or replace procedure execute_immediate( sql_stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

end;
/

now you can
cREATE OR REPLACE PROCEDURE create_user (username IN VARCHAR2, password IN vARCHAR2)

		IS
		BEGIN
		execute_immediate( 'CREATE USER ' || username || ' identified BY ' ||
                            password );
		END;

but remember:

roles are never enabled during the execution of a procedure (until Oracle8i and invokers rights which are special cases that sometimes allow roles to be active)

Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.

You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.

grant create user to <OWNER>;

for example.

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

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

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