Re: Errors executing password change procedure

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_at_gmail.com> 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.
>

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