Re: Fun with ALTER_USER

The effort is to give the developers, G'bless them, all the latitude they need.

On Thu, Feb 28, 2019 at 3:55 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