Re: Fun with ALTER_USER

From: Gus Spier <gus.spier_at_gmail.com>
Date: Thu, 28 Feb 2019 16:32:32 -0500
Message-ID: <CAG8xnic5GSK8+t1DZhxicmWOQTZathyrgurdrbWbOisJ-CwYUg_at_mail.gmail.com>



Thanks, Joe! That did the trick!

I truly appreciate all the suggestions and helpful hints. You guys are brilliant!

Regards,

Gus

On Thu, Feb 28, 2019 at 4:23 PM Sweetser, Joe <JSweetser_at_icat.com> wrote:

> I think you **don’t** need the trailing semi-colon on l_stmt. I believe
> that is implied when you use execute immediate.
>
>
>
> Hth,
>
> -joe
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Harel Safra
> *Sent:* Thursday, February 28, 2019 2:03 PM
> *To:* gus.spier_at_gmail.com
> *Cc:* Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>; oracle-l-freelist <
> oracle-l_at_freelists.org>
> *Subject:* 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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.oracle.com%2Fcd%2FE11882_01%2Fnetwork.112%2Fe36292%2Fguidelines.htm%23DBSEG10005&data=02%7C01%7CJSweetser%40icat.com%7C56f89bb872f34c596c3708d69dc05c06%7C5d3bf30e9adb4c17b2425c17523e6e5e%7C0%7C0%7C636869846843458872&sdata=2SpHvrM2Mg2ihPpx4425Yr2m7G31aLUicRZjaOgSvI4%3D&reserved=0>
>
>
>
> 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
>
>
>
>
>
> This e-mail transmission and any attachments that accompany it may contain
> information that is privileged, confidential or otherwise exempt from
> disclosure under applicable law and is intended solely for the use of the
> individual's to whom it was intended to be addressed. If you have received
> this e-mail by mistake, or you are not the intended recipient, any
> disclosure, dissemination, distribution, copying or other use or retention
> of this communication or its substance is prohibited. If you have received
> this communication in error, please immediately reply to the author via
> e-mail that you received this message by mistake and also permanently
> delete the original and all copies of this e-mail and any attachments from
> your computer. Please note that coverage cannot be bound or altered by
> sending an email. You must receive written confirmation from a
> representative of our firm to put coverage in force or make changes to an
> existing policy.
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Feb 28 2019 - 22:32:32 CET

Original text of this message