Re: Creating a New User from PL/SQL

From: Ervin Fried <ervinf_at_netcom.ca>
Date: Wed, 15 Jul 1998 19:36:58 GMT
Message-ID: <6oj0cq$25g_004@nntp.netcom.ca>

In article <35ACC669.4A64B50A_at_internetivity.com>, Paul Dumais <pdumais_at_internetivity.com> wrote:

>I want to create a New User using a Function which takes as input a
>username and password and return the new user's id.
>Am I doing it the right way???  My main problem right now is getting the
>user# from sys.user$
>Here is what I got so far:
>

some issues first:
- for a stored proc to access a table is has to have rights granted directly, not through a role.

• DDL statements can not have bind parameters
• usernames are stored uppercase in Oracle's tables
• dba_users is more handy than sys.users$

here is my version: (

conn sys
grant select on dba_users to xxx;
grant create user to xxx;

conn xxx
/*

        no input checking performed
        no  exceptions are handled

*/
CREATE or replace FUNCTION

    CreateNewUser ( iUsername varchar2, iPassword varchar2 ) RETURN
    NUMBER
IS

        nUserID  NUMBER;
        nCursor INTEGER;
        nRetval INTEGER;
        sSQL VARCHAR2(1024);
BEGIN
        sSQL := 'CREATE USER ' || iUsername  ;
        sSQL := sSQL || ' IDENTIFIED BY ' || iPassword ;
        
        nCursor := dbms_sql.open_cursor;
        
        dbms_sql.parse( nCursor, sSQL, dbms_sql.v7);
        
        nRetval := dbms_sql.execute( nCursor );
        
        dbms_sql.close_cursor( nCursor );
        
        SELECT user_id into nUserID FROM dba_users
                WHERE username = UPPER(iUsername);
        
        RETURN nUserID;
EXCEPTION
        WHEN OTHERS THEN


        -- some of possible errors


        


        -- from parse


        -- SQLCODE = -1935 - missing user or role name      


        -- SQLCODE = -988  - missing or invalid password(s) (from parse)


        -- SQLCODE = -942  - compile error


        
        


        -- from execute


        -- SQLCODE = -1031: insufficient privileges (to create a user)


        -- SQLCODE = -1920 - user already exists 


        


        -- from select


        -- SQLCODE = -1403 -  no data found 


        


        -- just re-raise for now.


            RAISE;             

END; Received on Wed Jul 15 1998 - 14:36:58 CDT