Re: Errors executing password change procedure
- From: "Jeffrey Beckstrom" <jbeckstrom@xxxxxxxxx>
- To: "oracle-l-freelist" <oracle-l@xxxxxxxxxxxxx>, <sbecker6925@xxxxxxxxx>
- Date: Wed, 28 Nov 2018 13:06:25 -0500
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@xxxxxxxxx> 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.
Other related posts:
