Wednesday, March 01, 2006

Umm, I forgot my password

Probem solved, with the following convenient password reset procedure found in a large production database, with EXECUTE granted to PUBLIC and a handy public synonym:

CREATE OR REPLACE PROCEDURE reset_user_password(p_username IN VARCHAR2)
   execute immediate 'ALTER USER '||upper(p_username)||' IDENTIFIED BY '||upper(p_username);
Many thanks to Robert De Laat for this submission.


Tim... said...

What a great idea. I shall implement is immediately in all my systems :)

William Robertson said...

You'll never need to worry about forgetting your password again - if it isn't already "TIM", just get any colleague to reset it for you. And they aren't about, just log in as them. Or as SYS.

Robert said...

Yeah, i know.
And to make sure it always works the procedure was owned by system.

Jeff Hunter said...

Yeah, what a great way to change SYS and SYSTEM!!

WTF indeed.

Bob B said...

Oh the possibilities on that one. Not only do they get to change the password of any known user, they can also use sql injection to do whatever they want. Create new users, grant privileges, export any data from the database, etc etc

Harry said...

but it was so cleverly disguised by a misleading name!

Jared said...

This procedure is not a good idea, but I can't see how it can be used for SQL injection.

Injecting SQL into the execute immediate will require a separator (;) in the passed string. Execute immediate does not work with multiple PL/SQL commands in one string.

This will not work in PL/SQL:

v varchar2(200);
v := 'alter user hoser identified by hoser; grant dba to evil_user identified by evil_user; alter user hoser identified by hoser';
execute immediate v;

Perhaps there is a more clever way. Does someone here know how to use SQL injection against this stored proc?

Tim... said...

Robert said...
"And to make sure it always works the procedure was owned by system."

Sissy... It should be owned by SYS.

Infact, I can't see the point in having separate users. Just create all objects in the SYS schema let everyone use that login. I've heard "change_on_install" is a strong password :)



William Robertson said...

I couldn't get SQL injection to work either. It hardly seems worth it though, when you could just connect as SYS/SYS and drop the database.

Scott Swank said...


You're thinking of SQL Server, where everything actually is owned by SYS -- however they pronounce is DBO.

Tim... said...