Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Creating a New User from PL/SQL

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.

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US