Regular expression for password with at least two uppercase and two lowercase letters in Oracle

Question

I was having a problem when performing a constraint for my database in oracle 18 xe,

what I need is that the password has

• at least 5 characters
• at least two uppercase letters
• at least two lowercase letters
• at least one digit

I've done enough searching but can't find the right way, so far i wear this

ALTER TABLE USER 
ADD CONSTRAINT  PASSWORD_CHECK
REGEXP_LIKE(PASSWORD, '^.*[0-9]', 'c')
AND REGEXP_LIKE(PASSWORD, '^.*[A-Z]{2,}', 'c');
AND REGEXP_LIKE(PASSWORD, '^.*[a-z]{2,}', 'c');
AND REGEXP_LIKE(PASSWORD, '^[a-zA-Z0-9]{5,}$', 'c');

thanks for your support!

Answer 1

Alas, if Oracle's regex engine supported lookaheads, you could have just used a single pattern:

^(?=.*[A-Z].*[A-Z])(?=.*[a-z].*[a-z])(?=.*[0-9]).{5,}$

However, it doesn't support lookarounds, so we are pretty much stuck with your current approach. Your current patterns have some slight issues, and also REGEXP_LIKE can work with a partial match, so no need for ^ and $ anchors in most cases here. Consider this version:

ALTER TABLE USER 
ADD CONSTRAINT  PASSWORD_CHECK
REGEXP_LIKE(PASSWORD, '[0-9]', 'c')                   -- 1+ digits
AND REGEXP_LIKE(PASSWORD, '[A-Z].*[A-Z]', 'c');       -- 2+ uppers
AND REGEXP_LIKE(PASSWORD, '[a-z].*[a-z]', 'c');       -- 2+ lowers
AND REGEXP_LIKE(PASSWORD, '^[a-zA-Z0-9]{5,}$', 'c');  -- length >= 5

If you don't have the restriction that the password can only be numbers and letters, then you may replace the final condition with just this:

LENGTH(PASSWORD) >= 5

Answer 2

You can use the NOT NULL and CHECK constraint on the PASSWORD column as follows:

CREATE TABLE USERS (PASSWORD VARCHAR2(10) NOT NULL); -- NOT NULL

ALTER TABLE USERS ADD CONSTRAINT PASSWORD_CHECK
CHECK (REGEXP_COUNT(PASSWORD, '[0-9]') >= 1      -- 1 digit
       AND REGEXP_COUNT(PASSWORD, '[A-Z]') >= 2  -- 2 or more uppercase characters    
       AND REGEXP_COUNT(PASSWORD, '[a-z]') >= 2  -- 2 or more lowercase characters         
       AND LENGTH(PASSWORD) >= 5);               -- minimum length must be 5

NOT NULL constraint on the PASSWORD column will be required as the CHECK constraint will not be applied on the NULL values and it will allow inserting the NULL values if the NOT NULL constraint on the column is not created.