RE: Fun with ALTER_USER
Date: Fri, 1 Mar 2019 03:47:57 -0500
Message-ID: <089001d4d00b$78ebe450$6ac3acf0$_at_rsiz.com>
Stop. Do not pass go, do not collect $200.
Read Bryn’s paper on preventing sql injection errors by design:
http://www.oracle.com/assets/how-to-write-injection-proof-plsql-1-129572.pdf
Possibly it will make your entire job producing this easier. And you are likely to simultaneously smile and grimace whilst reading it.
Seriously, building a gate with a sign “please notice this gate is not locked and we have no cameras here” only works if it actually is en route to a honeypot trap. Maybe THAT is version 2.
The bad “hacker” community (real hackers are white hats, so quotes in this context) works at this full time.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Gus Spier
Sent: Thursday, February 28, 2019 4:22 PM
To: Harel Safra
Cc: Jeffrey Beckstrom; oracle-l-freelist
Subject: Re: Fun with ALTER_USER
Thanks! I'll have to address the SQL Injection concern in version 2.
Here's the output from the latest go-round:
SQL> set serveroutput on long 2000
SQL> CONN DEVADMIN/DEVADMIN_PASSWORD SQL> BEGIN SYS.ALTER_USER('service_account',"newpassword1");
2 END; 3 /
ALTER USER <service_account> IDENTIFIED BY "<newpassword>";
altered
That's the string I wanted to see. It just doesn't take effect.
SQL> CONN <service_account>/<newpassword>
ERROR: ORA-01017 Invalid username or password; Logon denied.
WARNING: You are no longer connected to oracle
Once upon a time, there was a password column in the DBA_USERS view. It actually held the hash value, rather than the password itself. I miss those times, I could have checked the password for the service account both BEFORE and AFTER I ran the procedure. If the hash value had changed, I would know that the procedure is not necessarily the problem. <SIGH/>
Thanks again.
Gus
On Thu, Feb 28, 2019 at 4:03 PM Harel Safra <harel.safra_at_gmail.com> wrote:
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://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:
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-lReceived on Fri Mar 01 2019 - 09:47:57 CET