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

Craig Harper <caharper_at_hooked.net> 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

Craig,

The solution is to use the DBMS_SQL package.

You can write it yourself, or, a while back, Thomas Kyte from Oracle posted the code for a procedure he called execute_immediate (I think that is what it was named). Anyway, it contains all the DBMS_SQL logic required to execute a command such as "Create User", "Create Table", "Grant...", etc. All you have to do is pass the SQL to the procedure -- e.g. "Execute_Immediate('Create User.....')".

I would provide you the explicit DBMS_SQL commands except I am at home - no manuals nor a copy of the procedure. If no one else responds, e-mail me and I will send you a copy of his post, or, if Thomas is out there, maybe he would like re-post the procedure.

I developed an application that creates/drops users, grants/revokes, etc. Thomas's code plays an integral part -- various procedures build the SQL commands and then pass the SQL to a variation of the "execute_immediate" procedure.

One item of interest is that I had to *explicitely* grant the "Create User" privilege (as well as the other privileges related to the application) to the procedure owner. For example, during development, a DBA user owned the procedure; but, I still received an error message that the user did not have the privileges required to create a user. Even though the account was a DBA account, and I could create a user via SQL*Plus when logged on as the DBA account, the procedure could not until I explicitely granted the "Create User" privilege. Who knows, maybe I am missing out on something; but, this is what I had to do to get it to work.

Larry G. Elkins
elkinsl_at_flash.net Received on Tue May 28 1996 - 00:00:00 CEST