Re: Fun with ALTER_USER
- From: "Jeffrey Beckstrom" <jbeckstrom@xxxxxxxxx>
- To: "oracle-l-freelist" <oracle-l@xxxxxxxxxxxxx>,<gus.spier@xxxxxxxxx>
- Date: Thu, 28 Feb 2019 15:41:39 -0500
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
Other related posts:
