0

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!

Jorge
  • 485
  • 4
  • 15
  • If you are doing this to learn to use Regexp in the database, that's fine, but instead of storing passwords or sending them to the database as is, please consider hashing the password in the UI, and only sending the hashed value over the network to store in your database, applying validation rules at user input. – Haleemur Ali Nov 06 '20 at 07:34
  • Automatic down vote for storing user passwords as clear text. This is how people get hacked. Even if your question is only for an assignment, you need to learn that this is a horrible, immoral coding practice. At the very least, use something like `STANDARD_HASH` instead of plaintext. – Jon Heller Nov 07 '20 at 04:52

2 Answers2

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
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
-1

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.

Popeye
  • 35,427
  • 4
  • 10
  • 31