Re: Errors executing password change procedure

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.



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