Re: Fun with ALTER_USER

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