Mama Mia ! Oracle Database Password Complexity and Seventies\u00a0Euro-Pop<\/a>]<\/p>\n <\/p>\n
From Mike Smithers I specially like the small PL\/SQL to test the password function. I dare to reproduce here:<\/p>\n
\r\nset lines 130\r\nset serveroutput on size unlimited\r\nspool verify_test.log\r\nDECLARE\r\n--\r\n-- script to test check_password_fn\r\n--\r\n TYPE typ_passwords IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;\r\n tbl_passwords typ_passwords;\r\n \r\n l_old_password VARCHAR2(30) := 'Waterloo1';\r\n \r\n l_dummy BOOLEAN;\r\nBEGIN\r\n --\r\n -- setup list of passwords to test with...\r\n --\r\n tbl_passwords(1) := 'simple'; -- too short\r\n tbl_passwords(2) := 'mike1234'; -- same as user\r\n tbl_passwords(3) := '4321ekim'; -- mike backwards\r\n tbl_passwords(4) := '1oolretaW'; -- old password backwards\r\n tbl_passwords(5) := 'Waterloo18'; -- too similar to old password\r\n tbl_passwords(6) := 'Brotherhood_0f_Man'; -- dictionary\r\n tbl_passwords(7) := 'P455w0rd'; -- simple\r\n tbl_passwords(8) := 'Sm0king_15_0nly_Vice'; -- should pass.\r\n tbl_passwords(9) := 'm1K31234'; -- should fail - too similar to user\r\n tbl_passwords(10) := 'W4terl001'; -- should fail - too similar to old password\r\n tbl_passwords(11) := 'the_day_before_you_came'; -- should fail - does not have any uppercase or numbers\r\n tbl_passwords(12) := 'ONLYSeventeen'; -- should fail - no numbers\r\n tbl_passwords(13) := '48840000'; -- should fail - no letters\r\n tbl_passwords(14) := 'ABBA0000'; -- should fail - no lowercase letters\r\n tbl_passwords(15) := 'abba0000'; -- should fail - no uppercase letters\r\n tbl_passwords(16) := 'Mamma_Mia1'; -- should pass\r\n \r\n-- tbl_passwords(1) := 'W4terl001'; \r\n-- tbl_passwords(2) := 'P455w0rd'; -- simple\r\n \r\n --\r\n -- Nested block required to account for failures...\r\n --\r\n FOR i IN 1..tbl_passwords.COUNT LOOP\r\n BEGIN\r\n l_dummy := check_password_fn(\r\n username => 'MIKE',\r\n old_password => l_old_password,\r\n new_password => tbl_passwords(i));\r\n DBMS_OUTPUT.PUT_LINE('Test '||i||' - password allowed.');\r\n EXCEPTION\r\n WHEN OTHERS THEN\r\n DBMS_OUTPUT.PUT_LINE('Test '||i||' ERROR : '||SQLERRM);\r\n END;\r\n END LOOP;\r\n DBMS_OUTPUT.PUT_LINE('Test run completed.');\r\nEND;\r\n\/\r\nspool off\r\n\r\n<\/pre>\nOne the requirements I had was:<\/p>\n
– The password should include three of the four following sets: English uppercase letters, English lowercase letters, base 10 digits, special characters like ‘$’,’#’,’!’,’?’<\/p>\n
<\/p>\n
The password function provided by Oracle does not allows to say this choice of “three out of four”, so I developed myself and added to the Oracle default function the following code:<\/p>\n
\r\n--\r\n-- Finally, make sure that the password contains\r\n-- characters from 3 of the four categories\r\n-- UPPERCASE, lowercase, numbers and punctuation characters.\r\n--\r\nIF \r\n SIGN(REGEXP_INSTR(new_password, '[[:upper:]]'))+\r\n SIGN(REGEXP_INSTR(new_password, '[[:lower:]]')) +\r\n SIGN(REGEXP_INSTR(new_password, '[[:digit:]]')) +\r\n SIGN(REGEXP_INSTR(new_password, '[[:punct:]]')) < 3\r\nTHEN\r\n RAISE_APPLICATION_ERROR( -20006,'Password must contain characters from three of the following four categories: English uppercase, lowercase, base 10 digits, non-alphabetic characters like ! $ # %');\r\nEND IF;\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"This week I had to implement a password verify function at a client. My sources of inspiration were both: – Stefan Oehrli blog entry on [Oracle 12c new password verify function] – Mike Smithers blog entry on [Mama Mia ! Oracle Database Password Complexity and Seventies\u00a0Euro-Pop] From Mike Smithers I specially like the small […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,9],"tags":[],"class_list":{"0":"post-60","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-oracle","7":"category-security","8":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/60","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=60"}],"version-history":[{"count":5,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/60\/revisions"}],"predecessor-version":[{"id":65,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/60\/revisions\/65"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=60"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=60"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=60"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}