Re: PL/SQL Package Execution Privileges

In article
<E2F6A70FE45242488C865C3BC1245DA70345905C_at_lnewton.leeds.lfs.co.uk>, Norman.Dunbar_at_lfs.co.uk says...
> If I'm not mistaken :
>
> User B wants to be able to change the password for another user, and you
> don't want to give user b the privs to do so ?
>
> If so, write a 'system' owned proc to change the password for a given
> user such as :
>
> create or replace procedure SysChangePassword(pUserName in
> varchar2(whatever), pPassWord in Varchar2(whatever)) as
> begin
> -- Check for system users here and reject
>
> -- Check for other users we don't want to change the PW for here
>
> -- Finally we can do it
> execute immediate 'alter user '||pUserName||' identified by ' ||
> pPassWord';
> exception
> -- trap bad stuff here
> end;
> /
>
> Give user b execute permissions on the above and all will be well
> because when user b executes the proc it executes with system's privs
> (definer rights not invoker) so system is able to change the password.
>
> Note, you really really really need to trap attempts to change the
> password for SYS, SYSTEM and all the rest otherwise you will be in deep
> stuff at some point. :o)
>
> HTH
>
> Cheers,
> Norman.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>
>
> -----Original Message-----
> From: Jeremy Ovenden [mailto:newspostings_at_hazelweb.co.uk]
> Posted At: Thursday, February 06, 2003 4:28 PM
> Posted To: server
> Conversation: PL/SQL Package Execution Privileges
> Subject: PL/SQL Package Execution Privileges
>
>
> Hi Guys, a quick question. I have a plsql package owned by user A.
>
> Permission to execute this is given to B via a role R.
>
> B executes and appears as USER B in terms of connection to the database
> etc.
>
> No problems so far...
>
> Now I want user B to be able to execute an a new procedure which alters
> the password of a user.
>
> As B doesn't have the privileges to ALTER ANY USER, we get an ORA-1031
> insufficient privileges.
>
> I don't want to grant this privilege - it would allow B to alter (I
> think) and user's password as well as quota, tablespace etc..
>
> Question: if there is enough info here, how should I set this up? There
> are some users in the system that need to be able to change the
> passwords of other users (i.e. to reset them in the event of the user
> forgetting them). This is a web-based application using pl/sql toolkit.
>
> Any contributions gratefully received!
>
>
>

Thanks Norman - would be interested to see Sybrand's view on your approach?

cheers!

BTW is this a top-posting / bottom-posting / anywhere-posting gorup?

-- 

jeremy