Re: Create oracle users PL/SQL procedure

From: Violin <violin.hsiao_at_mail.pouchen.com.tw>
Date: 23 Apr 2002 00:32:51 -0700
Message-ID: <d22954a4.0204222332.6f7d5148@posting.google.com>

Hi Vincent ,

I think Sybrand is right , please think more about the security and safety....
BUT if you really need a procedure , you can use dynamic sql for that.

Here is a sample code:
-- Test account : SYSTEM/MANAGER
-- Needed privileges : create user , grant any privilige , alter user ,
-- Needed Roles with admin option : connect , resource

CREATE OR REPLACE PROCEDURE NewUser

    (U_Name       IN CHAR,
     U_Password   IN CHAR,
     D_Tablespace IN CHAR)

is

    v_Cursor Number;
    v_CreateUser VARCHAR2(500);
BEGIN
    v_Cursor := DBMS_SQL.OPEN_CURSOR;
    v_CreateUser := 'CREATE USER ' || U_Name || ' IDENTIFIED BY ' || U_Password ||

                   ' DEFAULT TABLESPACE ' || D_Tablespace ||
                   ' TEMPORARY TABLESPACE TEMPORARY_DATA' ||  
                   ' PROFILE DEFAULT';

    BEGIN
    DBMS_SQL.PARSE(v_Cursor, v_CreateUser, DBMS_SQL.NATIVE);     DBMS_SQL.PARSE(v_Cursor, 'GRANT CONNECT , RESOURCE TO ' || U_Name, DBMS_SQL.NATIVE);
    DBMS_SQL.PARSE(v_Cursor, 'ALTER USER ' || U_Name || ' DEFAULT ROLE ALL', DBMS_SQL.NATIVE);
    END;
END;
/

SQL> exec newuser('AA','AA','USER_DATA');

PL/SQL procedure successfully completed.

SQL> connect aa/aa
Connected.
SQL> Violin.
violin.hsiao_at_mail.pouchen.com.tw

"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 - 02:32:51 CDT