Assigning a role in Stored Procedure

From: <mmellin_at_my-dejanews.com>
Date: Fri, 11 Dec 1998 17:16:42 GMT
Message-ID: <74rk1m$u57$1@nnrp1.dejanews.com>

I have create a stored procedure is which I Create a user and try to assign it to a role. I have system privs to Create User and grant any role. I am getting the error that the role is missing, but after the procedure runs I can grant the rights sucessfully. GRANT RNOW_USER_ROLE TO "user"; Why wont it recognize the name of the role in PL\SQL. Also when the username entered has a numeric at the first point in the string I get "missing user or role name". Here is the code. Any help would be great.

CREATE OR REPLACE PROCEDURE RESPONSENOW.SP_ADDUSER

(username_in       IN VARCHAR2,
 password_in      IN VARCHAR2,
 tablespace_in      IN VARCHAR2,
 temp_tablespace_in     IN VARCHAR2)
 IS
  adduser_cursor      INTEGER;
  exec_user           INTEGER;
  grantrole_cursor    INTEGER;
  exec_role           INTEGER;

  BEGIN
  adduser_cursor := dbms_sql.open_cursor;   dbms_sql.parse(adduser_cursor,

'CREATE USER '||username_in||'

	   IDENTIFIED BY '||password_in||'
	   DEFAULT TABLESPACE '||tablespace_in||'
	   TEMPORARY TABLESPACE '||temp_tablespace_in||'
	   QUOTA UNLIMITED ON '||tablespace_in||'
	   QUOTA UNLIMITED ON '||temp_tablespace_in, DBMS_SQL.V7);

  exec_user := dbms_sql.execute(adduser_cursor);   dbms_sql.close_cursor(adduser_cursor);

 grantrole_cursor := dbms_sql.open_cursor;  dbms_sql.parse(grantrole_cursor,

'GRANT rnow_user__role

        TO '||username_in, DBMS_SQL.V7);  exec_role := dbms_sql.execute(grantrole_cursor);  dbms_sql.close_cursor(grantrole_cursor);

END; -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Dec 11 1998 - 11:16:42 CST