Re: Errors executing password change procedure

Thanks everyone for all your suggestions and help. I've got a procedure working now with the "grant execute on...".

Sandy

On Wed, Nov 28, 2018 at 12:53 PM TJ Kiernan <tkiernan_at_castiarx.com> wrote:

> I built something like this recently for an application where every
> application user has a database account. I can’t say I miss all the “THE
> DATABASE FORGOT MY PASSWORD” tickets.
>
>
>
> The owner of the procedure needs ALTER USER. The ALTER SESSION is not
> necessary. But there’s a lot more to this, such as control over what
> username is passed (or people will be able to reset passwords of others,
> which is bad).
>
>
>
> Depending on whether you have a rule that says “ALL DB USERS HAVE THE SAME
> USERID AS THEIR AD ACCOUNT,” you may need a lookup table to translate AD
> user to DB user. Then your P_USERNAME is used to lookup the database
> username, which is then substituted in your ALTER USER statement. Probably
> wand double quotes surrounding the username and password just in case.
>
>
>
> EXECUTE IMMEDIATE 'ALTER USER "'||p_username||'" IDENTIFIED BY "'
> ||p_password||'" ACCOUNT UNLOCK';
>
>
>
> General PL/SQL feedback:
>
>
>
> Write packages, not independent functions/procedures.
>
> Your WHEN OTHERS is not great. It’s useful when you’re running it via
> interactive SQL session, but the GUI most likely can’t pick up on
> DBMS_OUTPUT(). It’ll be better to just let any exceptions that you don’t
> anticipate raise naturally.
>
>
>
> HTH,
>
> T. J.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Sandra Becker
> *Sent:* Wednesday, November 28, 2018 12:56 PM
> *To:* srcdco_at_rit.edu
> *Cc:* Tim Hall <tim_at_oracle-base.com>; jbeckstrom_at_gcrta.org; oracle-l <
> oracle-l_at_freelists.org>
> *Subject:* Re: Errors executing password change procedure
>
>
>
> Users already have the ability to change their passwords, assuming they
> can actually login. The problem, according to the project owner, arises
> when they don't know their current password, it has expired, or they locked
> their account. They want a GUI that end_users can access to change their
> password in any situation. I'm told it will have checks in place to ensure
> they can change only their own password. Opening a ticket with the DBA
> team is too difficult and time consuming. Project owner's words, not mine.
>
>
>
> On Wed, Nov 28, 2018 at 11:48 AM Scott Canaan <srcdco_at_rit.edu> wrote:
>
> 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.
>
>
>
> --
>
> Sandy B.
>
> <http://gfidisc.castiarx.com>
>
> CONFIDENTIALITY NOTICE: This message may contain confidential information,
> including Protected Health Information as defined under the Health
> Insurance Portability and Accountability Act of 1996, intended only for the
> use of the individual or entity identified above. If the receiver of this
> message is not the intended recipient, you are hereby notified that any
> dissemination, distribution, use or copying of this message is strictly
> prohibited. If you have received this message in error, please immediately
> notify the sender by replying to his/her e-mail address noted above and
> delete the original message, including any attachments. Thank you.
>
> <http://gfidisc.castiarx.com>
>

-- 
Sandy B.

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