Insufficient Privileges in Procedure

Hello all. Here is what I want to do. It's not that difficult but I can't seem to do it. I want a security administrator role to be able to create, drop and alter users and grant roles only through procedures and not have to grant him anything other than execute on the procedures. I also don't want him being a member(owner) of the role that he is granting.

As an example the Create Users proc looks like this:

create or replace
procedure p_hrs_create_user(uid_i in varchar2,ppp_i in varchar2) AUTHID CURRENT_USER
is

   cur1 INTEGER;
begin

   cur1 := DBMS_SQL.open_cursor;
   DBMS_SQL.PARSE(cur1, 'CREATE USER ' || uid_i || ' IDENTIFIED BY ' || ppp_i || ' DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP', DBMS_SQL.native);

   DBMS_SQL.close_cursor (cur1);
   commit;

   cur1 := DBMS_SQL.open_cursor;
   DBMS_SQL.PARSE(cur1, 'GRANT HRSCLERK TO ' || uid_i, DBMS_SQL.native);

   DBMS_SQL.close_cursor (cur1);
   commit;
end p_hrs_create_user;

Whenever I use the security administrator ID to run it I get "insufficient privileges".

I've also tried:

create or replace
procedure p_hrs_create_user(uid_i in varchar2,ppp_i in varchar2) AUTHID CURRENT_USER
is

   begin
   execute immediate 'CREATE USER ' || uid_i || ' IDENTIFIED BY ' || ppp_i || ' DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP';

   commit;

   execute immediate 'GRANT HRSCLERK TO ' || uid_i;    commit;
end p_hrs_create_user;

Same result.

I've also tried it without the AUTHID or with AUTHID DEFINER.

Does anyone have any suggestion? Thanks.

• Rocco

(my above email address will be active for 1 week or you can answer me here. thanks.) Received on Tue May 25 2004 - 14:27:27 CDT