Re: Fun with ALTER_USER

From: Gus Spier <gus.spier_at_gmail.com>
Date: Thu, 28 Feb 2019 16:21:41 -0500
Message-ID: <CAG8xnidOd7bDtiLO5YiiQZhVZbD-Xjozb6b2DMCq1FdH1mT08A_at_mail.gmail.com>

Thanks! I'll have to address the SQL Injection concern in version 2.

Here's the output from the latest go-round:

SQL> set serveroutput on long 2000
SQL> CONN DEVADMIN/DEVADMIN_PASSWORD
SQL> BEGIN SYS.ALTER_USER('service_account',"newpassword1");
      2  END;
      3 /

ALTER USER <service_account> IDENTIFIED BY "<newpassword>"; altered

That's the string I wanted to see. It just doesn't take effect. SQL> CONN <service_account>/<newpassword> ERROR: ORA-01017 Invalid username or password; Logon denied.

WARNING: You are no longer connected to oracle

Once upon a time, there was a password column in the DBA_USERS view. It actually held the hash value, rather than the password itself. I miss those times, I could have checked the password for the service account both BEFORE and AFTER I ran the procedure. If the hash value had changed, I would know that the procedure is not necessarily the problem. <SIGH/>

Thanks again.

Gus

On Thu, Feb 28, 2019 at 4:03 PM Harel Safra <harel.safra_at_gmail.com> wrote:

> Hi,
> When you connect as DEVADMIN serveroutput is set back to off - this means
> that you don't see any potential errors that you catch in the exception
> block.
> You need to set if back to on after the connect, or better yet loose the
> exception block completely so the error gets propagated out and can't be
> ignored by the application.
>
> btw, concatenating user input directly into sql command strings leaves you
> open to SQL injection attacks.
>
> Harel
>
>
> On Thu, Feb 28, 2019 at 10:56 PM Gus Spier <gus.spier_at_gmail.com> wrote:
>
>> The double quotes around the password allow the password to contain
>> special characters and/or multibyte characters. See Guidelines for
>> Securing Passwords
>> <https://docs.oracle.com/cd/E11882_01/network.112/e36292/guidelines.htm#DBSEG10005>
>>
>> Regards,
>>
>> Gus
>>
>> On Thu, Feb 28, 2019 at 3:41 PM Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>
>> wrote:
>>
>>> Why do you have multiple quotes around the password?
>>> >>> Gus Spier <gus.spier_at_gmail.com> 2/28/19 3:25 PM >>>
>>> Hi, Listers!
>>>
>>> I am finding out that I am not as smart as I thought I was. Here's the
>>> situation:
>>>
>>> After a long period of unpleasantness, local leadership decided that
>>> "service accounts", Oracle usernames representing mission applications,
>>> must change their Oracle passwords at least once every 180 days. The
>>> consequence is, that once ever 180 days, the applications, with their now
>>> obsolete passwords, can no longer log into the database. The developers
>>> have asked for a tool, or utility that they can use to change the database
>>> passwords themselves. Once they have changed the database password, they
>>> can edit their code to use the correct password and All Will Be Right With
>>> The World.
>>>
>>> It fell in my lap to produce the tool. It certainly seems that it should
>>> work. But I suspect there are complications with which I am unfamiliar.
>>>
>>> SQL> host uname -a
>>> Linux devdb001 3.10.0-957.1.3.017.x64_64 #1 SMP Thu Nov 15 17:36:42 UTC
>>> 2018 x64_64 x64_64 x64_64 Gnu/Linux
>>>
>>> SQL*Plus: Relase 11.2.0.4.0 Production on Thu Feb 28 18:43:22 2019
>>> Copyright (c) 1982, 2013 Oracle. All rights reserved
>>>
>>> Connected to Oracle Database 11g Enterprise Edition Release
>>> 11.2.0.4.0-64bit Production
>>> With Partitioning, OLAP, DataMining and Real Application Testing options.
>>>
>>> conn / as sysdba
>>> CREATE USER DEVADMIN IDENTIFIED BY <DEVADMIN_PASSWORD>
>>> DEFAULT TABLESPACE USERS
>>> TEMPORARY TABLESPACE TEMP
>>> QUOTA 50M ON USERS;
>>> GRANT CREATE SESSION TO DEVADMIN;
>>>
>>> SET SERVEROUTPUT ON LONG 2000
>>>
>>> CREATE OR REPLACE PROCEDURE ALTER_USER(p_username IN VARCHAR2, p_newword
>>> IN VARCHAR2)
>>> AUTHID DEFINER
>>> AS
>>> l_stmt varchar2(200);
>>> BEGIN
>>> l_stmt := 'ALTER USER '||p_username||' IDENTIFIED BY "'||p_newword||'";';
>>> DBMS_OUTPUT.PUT_LINE(l_stmt);
>>> EXECUTE IMMEDIATE l_stmt;
>>> DBMS_OUTPUT.PUT_LINE('altered');
>>> EXCEPTION
>>> when others then
>>> DBMS_OUTPUT.PUT_LINE('error in procedure: '||sqlerrm);
>>> END ALTER_USER;
>>>
>>> GRANT EXECUTE ON ALTER_USER TO DEVADMIN;
>>>
>>> CONN DEVADMIN/DEVADMIN_PASSWORD
>>> BEGIN SYS.ALTER_USER('service_account',"newpassword1");
>>> END;
>>> /
>>>
>>> PL/SQL procedure successfully completed
>>>
>>> SQL> CONN <service_account>/<newpassword1>
>>> ERROR: ORA-01017 Invalid username or password; Logon denied.
>>>
>>> WARNING: You are no longer connected to oracle
>>>
>>> Examination of the alert log and audit logs reveal nothing about this.
>>>
>>> Questions: Is there some extra precaution that prevents user DEVADMIN
>>> from succeeding in changing <service_account>'s password? If there is, is
>>> there a work-around?
>>>
>>> Where can I start looking at logs to begin to understand what is failing?
>>>
>>> Thanks for any hlp you might be able to offer!
>>>
>>> Regards,
>>>
>>> Gus
>>>
>>>
>>>

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

Received on Thu Feb 28 2019 - 22:21:41 CET