Re: Errors executing password change procedure

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.

Unfortunately, in these legacy systems, using AD wasn't enforced or even encouraged. We already have a lookup table in place to verify the username is valid and an employee account with a flag that indicates whether or not they are allowed to change the password. All accounts in the databases are in the table and the majority of accounts have a flag of 'N', so they disallow password changes. I'm sure we'll need to do some additional tweaks, but will test everything against a dev database before rolling it out everywhere.

I finally got past my insufficient privilege error, but only by creating the procedure in the executing users schema. Granting execute on the procedure in a privileged schema failed no matter what I did. This is not really the way I want this to work, so I'll keep working on it. The dbms_output is just for me to use during testing. Got the original code from someone else since I'm new to pl/sql. Will probably need several more tweaks.

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