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...
On Wed, Nov 28, 2018 at 4:56 PM Sandra Becker <sbecker6925@xxxxxxxxx> wrote:
--
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.
