Writing pl/sql wrapper for 'alter user'

From: <Lisa_Koivu_at_gelco.com>
Date: Mon, 22 May 2000 10:10:33 -0500
Message-Id: <10505.106235@fatcity.com>

Hello -

I've been working on a PL/SQL package to give the ability to change passwords to someone.
It does not work and fails with 1031: insufficient privileges.

I wrote the package as SYSTEM.
The user can execute the package. It fails. I granted execute on DBMS_SQL to the user. Still fails. I finally tried granting alter user (thus usurping the entire reason I did this) but it still does not work! When I do 'alter user' at the command line as the executing user, it works.

Any ideas? I do not see anything different in the doco about the ALTER USER privilege - like if there's another priv I must grant in order for this to work. Below is the guts of my code (it's short). this is driving me BATTY because I'm usually pretty good at PL/SQL.

Any suggestions or ideas are appreciated. Thanks
Lisa

CREATE OR REPLACE PACKAGE BODY XX
AS
PROCEDURE CHANGE_PASSWORD (in_user_id IN VARCHAR2,

                        in_new_pw IN VARCHAR2,
                        out_text OUT VARCHAR2)

IS

---

• Allow changing an existing user's password
• Catch when execution is trying to change the
• password for a nonexistent user id.

  ---

        user_does_not_exist EXCEPTION;
        PRAGMA EXCEPTION_INIT (user_does_not_exist, -1918);
        invalid_password EXCEPTION;
        PRAGMA EXCEPTION_INIT (invalid_password, -988);

        cursor_handle   INTEGER;
        return_value    INTEGER;

        BEGIN

                cursor_handle:=DBMS_SQL.OPEN_CURSOR;

                DBMS_SQL.PARSE (
                        cursor_handle,
                        'ALTER USER ' ||  in_user_id ||
                        ' IDENTIFIED BY '|| in_new_pw,
                        DBMS_SQL.NATIVE);

                return_value:=DBMS_SQL.EXECUTE(cursor_handle);

                DBMS_SQL.CLOSE_CURSOR(cursor_handle);

        EXCEPTION
                WHEN invalid_password
                THEN
                        DBMS_SQL.CLOSE_CURSOR(cursor_handle);
                        out_text:='New password is not valid. For example,

password can not start with a number.';

                WHEN user_does_not_exist
                THEN
                        DBMS_SQL.CLOSE_CURSOR(cursor_handle);
                        out_text:='User currently does not exist in the

database.';

                WHEN OTHERS
                THEN
                        DBMS_SQL.CLOSE_CURSOR(cursor_handle);
                        out_text:='Database error:' || TO_CHAR(SQLCODE) ||
                                ' ' || SUBSTR(SQLERRM,1,25);

Received on Mon May 22 2000 - 10:10:33 CDT