ALTER USER x identified by y replace z, only permission?

Question

Background

We have a web-application which logs into an Oracle database as a generic application user, however for historical reasons we have all of our user-users stored as Oracle database users (we authenticate against the Oracle database to log them onto the web-application). We have other, Oracle Forms, applications which use the database user more directly (they cannot be removed).

I am trying to allow users to change their own password via the web-application generic-user.

Question

How do I grant the web-application generic user the ability to alter specific user's passwords without granting it the ability to alter super-users' passwords? If someone stole the web-application's credentials somehow we want damage limitation.

The Oracle Database kind of supports this via the:

 alter user user123 identified by new_password123 replace old_password123;   

Which would work great for our purposes as we ask users for their old password when altering it to a new password.

However with or without the "replace" syntax we need to grant the same permission, "alter user," which grants that generic web-application user far too much power (e.g. change ANY user's password, even without knowing the old one).

So is there some way of setting permissions up so you can change any password you want but ONLY if you know the old password?

Solution

The simplest approach would generally be to create a stored procedure which is owned by a superuser that implements the actual password reset and then to grant the generic application user privileges on that stored procedure. Your stored procedure can then implement whatever logic you'd like to determine which passwords can be reset, who can reset them, what sort of audit trail you need, etc.

For example, something like

CREATE OR REPLACE PROCEDURE superuser.reset_password( 
  p_username IN VARCHAR2,
  p_old_password IN VARCHAR2,
  p_new_password IN VARCHAR2
)
AS
BEGIN
  <<determine whether p_username is a normal user>>

  <<determine whether the person that is logged in should be able
    to reset p_username's password>>

  EXECUTE IMMEDIATE 'ALTER USER ' || p_username || 
                      ' IDENTIFIED BY ' || p_new_password ||
                      ' REPLACE ' || p_old_password;

  <<write to a log table indicating whose password was reset and
    who did the resetting>>
END;

GRANT EXECUTE ON superuser.reset_password
   TO application_generic_user;