Re: Create oracle users PL/SQL procedure

Hi,

I read some of the posts in this thread from Daniel, Niall and Sybrand and partly agree and disagree with the comments.

I personally cannot see why end users cant create other users for themselves. However, when designing your procedure to do it, be aware of security issues.

I have done it for a particular project, basically one or two appointed admin users can create other users. In my code, I use dynamis SQL to create the Oracle user and have dynamic SQL to only grant them access to tables which they need to run the application. The grants are done via a predefined role. They cannot see System tables or get up to anything they shouldn't (if they ever got access to tools, they have locked-down PCs).

The users only need a GRANT EXECUTE on the procedures which create a user, they will not need a specific CREATE ANY USER role assigned to them since the procedure will be run with the rights of the owner of the procedure (ie definer rights).

In short, no problem for me, you can try it yourself but be aware of issues.

Mark

"VINCENT ONEILL" <voneill_at_ntlworld.com> wrote in message news:<mZVw8.6487$xn.291466_at_news8-gui.server.ntli.net>...
> Hi,
>
> Does anyone know or have a PL/SQL procedure to create users and add roles ?
> I would like to use a procedure and grant access to it for non DBA users so
> that they do not have to have the system/sys passwords to create user
> accounts.
>
> TIA
>
> Vincent
Received on Tue Apr 23 2002 - 03:17:30 CDT