Thursday, April 27, 2006

Fun with password verification

Prompted by a question in the OTN forums I have been having a little play around with the Password Verification functionality. It's quite easy: we just create a function under SYS and add it to the users' profile(s). Oracle provide a sample function that applies some standard rules.

SQL> @C:\oracle\ora92\rdbms\admin\utlpwdmg.sql

Function created.


Profile altered.

SQL> alter user a identified by a
2 /
alter user a identified by a
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password same as or similar to user


SQL> alter user a identified by b
2 /
alter user a identified by b
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 4


SQL> alter user a identified by abcd
2 /
alter user a identified by abcd
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20002: Password too simple


SQL> alter user a identified by a1b2
2 /
alter user a identified by a1b2
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one digit, one character and one punctuation


SQL> alter user a identified by a1b_
2 /

User altered.

SQL> alter user a identified by a1b_
2 /
alter user a identified by a1b_
*
ERROR at line 1:
ORA-28007: the password cannot be reused


SQL>

So that's quite nice. But wait a minute, what about this:

SQL> alter user a identified by b1b_
2 /

User altered.

SQL>

Isn't there supposed to be some similarity checking? It is definitely there in the utlpwdmg.sql script. So why isn't it working? Turns out it does work but only when the user changes their own password. By the way, notice the REPLACE syntax we need now that we have enabled the PASSWORD_VERIFY_FUNCTION.

SQL> conn a/b1b_
Connected.
SQL> alter user a identified by b2b_ replace b1b_
2 /
alter user a identified by b2b_ replace b1b_
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20004: Password should differ by at least 3 characters


SQL>

This part of the checking doesn't work for SYS or any other user with the ALTER USER privilege. There's some helpful in Metalink note #114930.1 which explains Oracle's password management policy. Amongst other things the note says that:

"Oracle recommends that you do not change passwords using the ALTER USER statement because it does not fully support the password verification function."

Apparently we should use the SQL*Plus PASSWORD command or make explicit calls to OCIPasswordChange() to change passwords instead. But apart from the fact that SYS can bypass some of the similarity checking I'm not sure what the differences are. For normal users I think ALTER USER with the REPLACE clause remains a valid option (although using SQL*Plus PASSWORD is probably friendlier).

5 comments:

Un mot par jour said...

Hi Andrew,
I think this is the best option to validate passwords but probably there is a problem when the validation is done through the application. Again I prefer that Oracle do the dirty work.

Saludos!

Nigel said...

Hi

If you're the DBA, how would you know the user's password anyway? Wouldn't it be good practice to:

ALTER USER scott
IDENTIFIED BY blah
PASSWORD EXPIRE;

Then 'blah' is a one-time password - and the verification gets done when the user changes it when s/he next logs in.

APC said...

>> Wouldn't it be good practice to:

Good practice and what the database allows us to do are not (alas) always the same thing. Of course the DBA ought not to be setting the user's real password.

The real point is, I suppose, that any user with the ALTER USER privilege won't be subject to the full password verification:

SQL> conn system/mangler!1
Connected.
SQL> alter user system identified by "mangler!2"
2 /

User altered.

SQL>

Anonymous said...

toshiba pa3154u-1brs battery
toshiba portege 2000 battery
toshiba portege r100 battery
toshiba pa3098u battery
toshiba satellite 1200 battery
toshiba satellite 3000 battery
toshiba pa3331u battery
toshiba satellite m30 battery
toshiba satellite m35 battery

toshiba pa3009ur-1bar battery
toshiba tecra 8100 battery
toshiba pa3465u-1brs battery
toshiba pa3399u battery
toshiba satellite m40 battery
toshiba satellite m45 battery
toshiba satellite m50 battery
toshiba satellite m55 battery
toshiba pa3166u-1bas battery

toshiba satellite 1900 battery
toshiba satellite 1905 battery
toshiba pa3383u-1brs battery
toshiba pa3383u battery
toshiba satellite a70 battery
toshiba satellite p30 battery
toshiba satellite p35 battery
toshiba pa3382u-1bas battery
toshiba pa3384u-1bas battery

toshiba satellite a60 battery
toshiba satellite a65 battery
toshiba pa33842u-1brs battery
toshiba pa3384u-1bas battery
toshiba pa3356u-1brs battery
toshiba portege m300 battery
toshiba portege m500 battery
toshiba pa3128u battery
toshiba pa3191u battery

toshiba portege m200 battery
toshiba portege m205 battery
toshiba pa3123-1bas battery
toshiba satellite 5000 battery
toshiba pa3291u battery
toshiba satellite p20 battery
toshiba satellite p25 battery
toshiba pa2487ur battery
toshiba pa2487u battery

toshiba pa3250u battery
toshiba satellite 2430 battery
toshiba satellite a30 battery
toshiba pa3356u-1bas battery
toshiba satellite a50 battery
toshiba satellite a55 battery
toshiba pa3399u-1bas battery
toshiba satellite a100 battery
toshiba satellite m100 battery

Anonymous said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情人歡愉用品,情趣用品,AIO交友愛情館,情人歡愉用品,美女視訊,情色交友,視訊交友,辣妹視訊,美女交友,嘟嘟成人網,按摩棒,震動按摩棒,微調按摩棒,情趣按摩棒,逼真按摩棒,G點,跳蛋,跳蛋,跳蛋,性感內衣,飛機杯,充氣娃娃,情趣娃娃,角色扮演,性感睡衣,SM,潤滑液,威而柔,香水,精油,芳香精油,自慰,自慰套,性感吊帶襪,情趣用品加盟,情人節禮物,情人節,吊帶襪,成人網站,AIO交友愛情館,情色,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,七夕情人節,色情,A片,A片下載,免費A片,免費A片下載,情色電影,色情網站,辣妹視訊,視訊聊天室,情色視訊,免費視訊聊天,視訊聊天,美女視訊,視訊美女,美女交友,美女,情色交友,成人交友,自拍,本土自拍,情人視訊網,視訊交友90739,生日禮物,情色論壇,正妹牆,正妹,成人網站,A片,免費A片,A片下載,免費A片下載,AV女優,成人影片,色情A片,成人論壇,情趣,免費成人影片,成人電影,成人影城,愛情公寓,色情影片,保險套,舊情人,微風成人,成人,成人遊戲,成人光碟,色情遊戲,跳蛋,按摩棒,一夜情,男同志聊天室,肛交,口交,性交,援交,免費視訊交友,視訊交友,一葉情貼圖片區,性愛,視訊,嘟嘟成人網

愛情公寓,情色,舊情人,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,一葉情貼圖片區,情色小說,色情,色情遊戲,情色視訊,情色電影,aio交友愛情館,色情a片,一夜情,辣妹視訊,視訊聊天室,免費視訊聊天,免費視訊,視訊,視訊美女,美女視訊,視訊交友,視訊聊天,免費視訊聊天室,情人視訊網,影音視訊聊天室,視訊交友90739,成人影片,成人交友,美女交友,微風成人,嘟嘟成人網,成人貼圖,成人電影,A片