Re: Fun with ALTER_USER

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