Re: How to issue CREATE USER command from a stored proc?

>Craig Harper wrote:
>>
>> I am trying to figure out how to execute a CREATE USER and
>> GRANT commands from a stored proceedure. I would like to
>> create the username (user_id) from a sequence generator, then
>> GRANT create session to the new user. Does anyone know if this
>> is possible? If so how I keep getting errors when I try.
>>
>> Thanks
>>
>> --

As I recently discovered, the hard way. Remember that all priviledges must be granted directly to the user creating the procedure. Including system privledges!! If you want to create or alter user you must grant these system privledges directly to the account creating the procedure. They can not be granted through a role (even the DBA role).

So to create a user using DBMS_SQL first: grant create user to xxx. Where xxx is the user creating the procedure. To grant system privledges from within the procedure the owner of the procedure must be granted the system privledge with grant option.

Hope this helps.

Wendy Schlegel

-- 
INTELSAT