Errors executing password change procedure

[Sandra Becker]

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;
/

[Jeffrey Beckstrom]

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.
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;
/

[Sandra Becker]

I'll try that, thanks.

Post by Jeffrey Beckstrom
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.
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.

[Sandra Becker]

Still getting insufficient privileges and I know the user has "alter user
privileges" since I can do it from command line in the database.

Post by Sandra Becker
I'll try that, thanks.

Post by Jeffrey Beckstrom
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.
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.

[Tim Hall]

Remember that roles are handled differently by stored procedures,
compared to anonymous blocks and SQL. If your procedure users definer
rights (the default), it can't use privs via a role.

Still getting insufficient privileges and I know the user has "alter user privileges" since I can do it from command line in the database.

Post by Sandra Becker
I'll try that, thanks.

Post by Jeffrey Beckstrom
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.
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.

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

[Tim Hall]

I think this demonstrates what I mean.

conn / as sysdba
alter session set container=pdb1;

-- Create users.
create user test1 identified by test1;
grant create session, create procedure, alter user to test1;

create user test2 identified by test2;
grant create session to test2;

create user test3 identified by test3;
grant create session to test3;


-- Test basic password change.
conn test1/***@pdb1
alter user test2 identified by test3;

conn test2/***@pdb1
--Works!



-- Create a procedure to do it.
conn test1/***@pdb1
CREATE PROCEDURE change_passwd AS
BEGIN
EXECUTE IMMEDIATE 'alter user test2 identified by test4';
END;
/

grant execute on change_passwd to test3;


-- Test it.
conn test3/***@pdb1
exec test1.change_passwd;

conn test2/***@pdb1
-- It works!


-- Cleanup
conn / as sysdba
alter session set container=pdb1;

drop user test1 cascade;
drop user test2 cascade;
drop user test3 cascade;

Post by Tim Hall
Remember that roles are handled differently by stored procedures,
compared to anonymous blocks and SQL. If your procedure users definer
rights (the default), it can't use privs via a role.

Still getting insufficient privileges and I know the user has "alter user privileges" since I can do it from command line in the database.

Post by Sandra Becker
I'll try that, thanks.

Post by Jeffrey Beckstrom
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.
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.

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

[Scott Canaan]

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: [phone] | f: [phone]
***@rit.edu<mailto:***@rit.edu> | c: [phone]

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-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Sandra Becker
Sent: Wednesday, November 28, 2018 1:13 PM
To: ***@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 <***@gmail.com<mailto:***@gmail.com>> wrote:
I'll try that, thanks.

On Wed, Nov 28, 2018 at 11:06 AM Jeffrey Beckstrom <***@gcrta.org<mailto:***@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.
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.

[Tim Hall]

That will run using the roles of the person who calls it, not the
roles of the user that owns it. :)

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: [phone] | f: [phone]
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.
Sent: Wednesday, November 28, 2018 1:13 PM
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.
I'll try that, thanks.
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.
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.

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

[Scott Canaan]

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: [phone] | f: [phone]
***@rit.edu | c: [phone]

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:***@oracle-base.com]
Sent: Wednesday, November 28, 2018 1:39 PM
To: Scott Canaan
Cc: ***@gmail.com; ***@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. :)

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: [phone] | f: [phone]
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.
Sent: Wednesday, November 28, 2018 1:13 PM
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.
I'll try that, thanks.
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.
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.

[Sandra Becker]

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.

Post by Scott Canaan
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: [phone] | f: [phone]
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-----
Sent: Wednesday, November 28, 2018 1:39 PM
To: Scott Canaan
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. :)

Post by Scott Canaan
What about adding “authid current_user” as part of the create or replace

procedure line?

Post by Scott Canaan
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: [phone] | f: [phone]
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.

Post by Scott Canaan
Sent: Wednesday, November 28, 2018 1:13 PM
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.

Post by Scott Canaan
I'll try that, thanks.
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.

Post by Scott Canaan
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.

Post by Scott Canaan
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.

Post by Scott Canaan
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';

Post by Scott Canaan
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.

[Sandra Becker]

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.

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';
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.
*Sent:* Wednesday, November 28, 2018 12:56 PM
*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.
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: [phone] | f: [phone]
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-----
Sent: Wednesday, November 28, 2018 1:39 PM
To: Scott Canaan
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. :)

Post by Scott Canaan
What about adding “authid current_user” as part of the create or replace

procedure line?

Post by Scott Canaan
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: [phone] | f: [phone]
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.

Post by Scott Canaan
Sent: Wednesday, November 28, 2018 1:13 PM
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.

Post by Scott Canaan
I'll try that, thanks.
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.

Post by Scott Canaan
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.

Post by Scott Canaan
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.

Post by Scott Canaan
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';

Post by Scott Canaan
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>

[Sandra Becker]

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

Sandy

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';
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.
*Sent:* Wednesday, November 28, 2018 12:56 PM
*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.
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: [phone] | f: [phone]
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-----
Sent: Wednesday, November 28, 2018 1:39 PM
To: Scott Canaan
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. :)

Post by Scott Canaan
What about adding “authid current_user” as part of the create or replace

procedure line?

Post by Scott Canaan
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: [phone] | f: [phone]
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.

Post by Scott Canaan
Sent: Wednesday, November 28, 2018 1:13 PM
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.

Post by Scott Canaan
I'll try that, thanks.
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.

Post by Scott Canaan
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.

Post by Scott Canaan
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.

Post by Scott Canaan
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';

Post by Scott Canaan
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>

[Tim Hall]

Thoughts:

1) I'm assuming you know this is dangerous, but you're going to do it
anyway, so let's leave that as said.

2) There are probably about 30+ users you need to exclude from being
altered. Check out the partial list displayed here. You have to keep
an eye on how this changes over time, and with various options
installed or you are going to get into trouble.

https://docs.oracle.com/en/database/oracle/oracle-database/18/ladbi/oracle-database-system-privileges-accounts-and-passwords.html

3) You are concatenating stuff together to make a command. That's an
SQL injection waiting to happen. :) You should either avoid this by
using bind variables in your DDL where possible, or check the contents
of the variables is not dodgy. The DBMS_ASSERT package can help with
this.

https://oracle-base.com/articles/10g/dbms_assert_10gR2#SCHEMA_NAME

You will have to sanitise the password also if you are going to concatenate.

4) Changing another users password is a pretty privileged thing to do.
The procedure will need to be owned by a user that has the ability to
perform this action. You can then grant execute on this procedure to
another user, without having to give them excessive privileges.

5) When you think you've closed every avenue for attack, check it
again because someone will figure out how to use this to hack your DB.
:)

Cheers

Tim...

Post by Sandra Becker
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.

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

[Mladen Gogala]

Post by Sandra Becker
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.

Hi Sandra!

You can create the procedure belonging to the user SYSTEM and grant an
execute rights to your users. The default is so called "definer's
rights" procedure, and that is what your security concerns are about. 
The "definer's rights procedure" can access any object that its owner
can access. Personally, I would create the procedure to unlock/change
password for users not containing the string 'SYS'. An alternative would
be to create a role LUSER and only allow the operations if the username
to process is a member of the role LUSER. If you create another user,
call it ORAPHB, you can grant the execute privilege on the
SYSTEM.CHANGE_LUSER_PASSWORD procedure and that would be it. The
procedure can access anything that the user SYSTEM can access.

Regards

[c***@fjandrade.com]

Why don´t create a nice APEX app that sends emails with the new password autogenerated?
You validate the info with a table inside the app.

FJA

-----Original Message-----
From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> On Behalf Of Mladen Gogala
Sent: Thursday, November 29, 2018 10:41 AM
To: oracle-***@freelists.org
Subject: Re: Errors executing password change procedure

Post by Sandra Becker
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.

Hi Sandra!

You can create the procedure belonging to the user SYSTEM and grant an execute rights to your users. The default is so called "definer's rights" procedure, and that is what your security concerns are about. The "definer's rights procedure" can access any object that its owner can access. Personally, I would create the procedure to unlock/change password for users not containing the string 'SYS'. An alternative would be to create a role LUSER and only allow the operations if the username to process is a member of the role LUSER. If you create another user, call it ORAPHB, you can grant the execute privilege on the SYSTEM.CHANGE_LUSER_PASSWORD procedure and that would be it. The procedure can access anything that the user SYSTEM can access.

Regards


--
Mladen Gogala
Database Consultant
Tel: [phone]

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




---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

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

[Tim Hall]

That sounds strangely familiar. Can you see my screen? :)

Post by c***@fjandrade.com
Why don´t create a nice APEX app that sends emails with the new password autogenerated?
You validate the info with a table inside the app.
FJA
-----Original Message-----
Sent: Thursday, November 29, 2018 10:41 AM
Subject: Re: Errors executing password change procedure

Post by Sandra Becker
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.

Hi Sandra!
You can create the procedure belonging to the user SYSTEM and grant an execute rights to your users. The default is so called "definer's rights" procedure, and that is what your security concerns are about. The "definer's rights procedure" can access any object that its owner can access. Personally, I would create the procedure to unlock/change password for users not containing the string 'SYS'. An alternative would be to create a role LUSER and only allow the operations if the username to process is a member of the role LUSER. If you create another user, call it ORAPHB, you can grant the execute privilege on the SYSTEM.CHANGE_LUSER_PASSWORD procedure and that would be it. The procedure can access anything that the user SYSTEM can access.
Regards
--
Mladen Gogala
Database Consultant
Tel: [phone]
--
http://www.freelists.org/webpage/oracle-l
---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
--
http://www.freelists.org/webpage/oracle-l

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