RE: Errors executing password change procedure

That's what I thought was wanted, so the user running it could change their own password. Maybe I misread somewhere along the line.

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu | c: (585) 339-8659

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

-----Original Message-----
From: Tim Hall [mailto:tim_at_oracle-base.com] Sent: Wednesday, November 28, 2018 1:39 PM To: Scott Canaan
Cc: sbecker6925_at_gmail.com; jbeckstrom_at_gcrta.org; Oracle-L Freelists Subject: Re: Errors executing password change procedure

That will run using the roles of the person who calls it, not the roles of the user that owns it. :)
On Wed, Nov 28, 2018 at 6:25 PM Scott Canaan <srcdco_at_rit.edu> wrote:
>
> What about adding “authid current_user” as part of the create or replace procedure line?
>
>
>
> CREATE OR REPLACE PROCEDURE haalochangepassword(
> p_username IN VARCHAR2,
> p_password IN VARCHAR2) authid current_user AS
>
>
>
> Scott Canaan ‘88
> Sr Database Administrator
> Information & Technology Services
> Finance & Administration
>
> Rochester Institute of Technology
> o: (585) 475-7886 | f: (585) 475-7520
>
> srcdco_at_rit.edu | c: (585) 339-8659
>
> CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.
>
>
>
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sandra Becker
> Sent: Wednesday, November 28, 2018 1:13 PM
> To: jbeckstrom_at_gcrta.org
> Cc: oracle-l
> Subject: Re: Errors executing password change procedure
>
>
>
> Still getting insufficient privileges and I know the user has "alter user privileges" since I can do it from command line in the database.
>
>
>
> On Wed, Nov 28, 2018 at 11:08 AM Sandra Becker <sbecker6925_at_gmail.com> wrote:
>
> 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.
>
>
>
> --
>
> Sandy B.
��i��0���zX���+��n��{�+i�^ Received on Wed Nov 28 2018 - 19:48:47 CET