PL/SQL Package Execution Privileges

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Thu, 6 Feb 2003 17:44:36 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA70345905C@lnewton.leeds.lfs.co.uk>

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!

-- 

jeremy

Received on Thu Feb 06 2003 - 11:44:36 CST