Re: Fun with ALTER_USER

From: Rich J <rjoralist3_at_society.servebeer.com>
Date: Thu, 28 Feb 2019 15:34:57 -0600
Message-ID: <9bf8dde4b530b07d68b16abe0c8d878e_at_society.servebeer.com>



On 2019/02/28 14:25, Gus Spier wrote:

> 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;
> /

I'd create the procedure in DEVADMIN instead of SYS, making it AUTHID CURRENT_USER and removing the "p_username" parameter. It'd be arguably better for migrations and upgrades being out of SYS as well as being more secure, as no one with access to the procedure could change any password other than the one they're logged into.

Someone else had mentioned SQL injection. Although binds can't be used in DDL, perhaps DBMS_ASSERT could help prevent issues. See https://oracle-base.com/articles/10g/dbms_assert_10gR2 (e.g. ENQUOTE_NAME). Finally, I'm wondering if your test CONN is uppercasing "newpassword1", since there's no quotes around it. Try connecting without the password, then typing it in when prompted. I'm sure there's a convoluted way to quote that as well...

Rich

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

Original text of this message