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@xxxxxxx> 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@xxxxxxx | 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@xxxxxxxxxxxxxxx]
Sent: Wednesday, November 28, 2018 1:39 PM
To: Scott Canaan
Cc: sbecker6925@xxxxxxxxx; jbeckstrom@xxxxxxxxx; 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@xxxxxxx> wrote:
procedure line?
What about adding “authid current_user” as part of the create or replace
attachments, is intended only for the person(s) or entity to which it is
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@xxxxxxx | c: (585) 339-8659
CONFIDENTIALITY NOTE: The information transmitted, including
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.
oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sandra Becker
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:
Sent: Wednesday, November 28, 2018 1:13 PMuser privileges" since I can do it from command line in the database.
To: jbeckstrom@xxxxxxxxx
Cc: oracle-l
Subject: Re: Errors executing password change procedure
Still getting insufficient privileges and I know the user has "alter
wrote:
On Wed, Nov 28, 2018 at 11:08 AM Sandra Becker <sbecker6925@xxxxxxxxx>
wrote:
I'll try that, thanks.
On Wed, Nov 28, 2018 at 11:06 AM Jeffrey Beckstrom <jbeckstrom@xxxxxxxxx>
create the procedure as owned by a privileged user and grant execute of the
I don't believe you alter the current user in a procedure. Why not just
procedure as required.
if expired and/or account is locked. Per the requirements, I have created
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
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.
tweak it to meet our requirements. Any help would be greatly appreciated. I
I'm new to writing PL/SQL, so I found an example and have been trying to
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.
||p_password||' ACCOUNT UNLOCK';
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 '
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.
