Re: Insufficient Privileges in Procedure

There might be some "gotchas" in this proposed solution, so obviously, test, test and test.

If you don't want the "security administrator role" (or the account that has this role granted to it) to be a member of the roles being granted, then you shouldn't use the AUTHID .

The Owner of the Procedure would need to be a member of the roles (has been granted the roles with the admin option of course). Also the roles will have to all be granted directly to the owner of the procedure and not thru some other role.

Note: I see you might need to read up more on this, because in your example, you are creating users. This requires the "create user" priv to be granted directly to the owner of the Procedure. Granting a role that has this privilege to the owner of the Procedure will NOT work.

If you are on at least Version 8i, I would look up "execute immediate" instead of the "old way" of doing dynamic Sql (DBMS_SQL).

Also, you will need an account that has the "security administrator role" granted to it (which then gives an account the privileges to execute the Procedure).

-- 

"Rocco" <poxono76521_at_att.net> wrote in message
news:qc77b0p2c9g5too00854f8e525dpvtjaog_at_4ax.com...


> 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.)