Re: Errors executing password change procedure

I'll try that, thanks.

On Wed, Nov 28, 2018 at 11:06 AM Jeffrey Beckstrom <jbeckstrom_at_gcrta.org> wrote:

> I don't believe you alter the current user in a procedure. Why not just
> create the procedure as owned by a privileged user and grant execute of the
> procedure as required.
> >>> Sandra Becker <sbecker6925_at_gmail.com> 11/28/18 11:56 AM >>>
> Oracle Enterprise version 12.1.0.2
>
> We have a new requirement to allow users to change their passwords, even
> if expired and/or account is locked. Per the requirements, I have created
> the new user (not allowed DBA privs) that will connect through a GUI and
> execute a password change procedure in another schema that has the
> necessary privileges. This new user has been granted execute privileges on
> the procedure. However, I'm getting an "ORA-01031: insufficient privileges"
> error when I try to execute the procedure as the new user.
>
> I'm new to writing PL/SQL, so I found an example and have been trying to
> tweak it to meet our requirements. Any help would be greatly appreciated. I
> did grant "alter user" to the new user, but I'm not sure I really need that
> and it didn't make a difference to the error.
>
> CREATE OR REPLACE PROCEDURE haalochangepassword(
> p_username IN VARCHAR2,
> p_password IN VARCHAR2) AS
> BEGIN
> -- Check for system users here and reject
> IF UPPER(p_username) IN ('PRIVUSER','SYS','SYSTEM') THEN
> dbms_output.put_line('Password change not allowed');
> ELSE
> EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema = PRIVUSER';
> EXECUTE IMMEDIATE 'ALTER USER '||p_username||' IDENTIFIED BY '
> ||p_password||' ACCOUNT UNLOCK';
> dbms_output.put_line('Password change successful');
> END IF;
> EXCEPTION
> when NO_DATA_FOUND THEN
> raise_application_error(-20000,'No user found');
> when others THEN
> dbms_output.put_line('Password change procedure error: ' || sqlerrm);
> END;
> /
>
> --
> Sandy B.
>
>

-- 
Sandy B.

--
http://www.freelists.org/webpage/oracle-l