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@xxxxxxxx> 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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> *On
Behalf Of *Harel Safra
*Sent:* Thursday, February 28, 2019 2:03 PM
*To:* gus.spier@xxxxxxxxx
*Cc:* Jeffrey Beckstrom <jbeckstrom@xxxxxxxxx>; oracle-l-freelist <
oracle-l@xxxxxxxxxxxxx>
*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@xxxxxxxxx> 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@xxxxxxxxx>
wrote:
Why do you have multiple quotes around the password?
Gus Spier <gus.spier@xxxxxxxxx> 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.
