CODEWITHSUNDEEP
sql-serverdatabasepasswordspassword-policy
user3085866
user3085866

Reputation: 125

Constraints for UserName and Password fields in tables using MS SQL

I wish to put a constraint on the password field in my table "Administrator" .The constraint I want is that the password should not be less than 6 characters and should have mixed characters (numbers + numeric).

What is the KEYWORD for this constraint and what is the syntax . I have Googledit for some time ,I am in my office and I have to complete this task soon . I am using MS SQL

Here is what I have written,

CREATE TABLE Administrators
(
 Id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
 UserName varchar(255) NOT NULL,
 PassWord varchar(255) NOT NULL,
 Email varchar(255) NOT NULL
);

Upvotes: 0

Views: 6752

Answers (2)

Crono
Crono

Reputation: 10478

If you absolutely must make this verification on the DB layer and don't care your passwords being stored as plain text, use a TRIGGER. It will give you maximum control over how passwords can be accepted or rejected. You can also use Shantanu Gupta's strategy. Again, only if you don't mind passwords stored as plain text.

Now, the right thing to do IMHO would be for you to make this validation in the business logic tier of your application and store the password as a hash.

But since you mentionned in the comments that you don't want the burden of learning how triggers works OR split your app into logical tiers, then you can always do the validation on the client. It's awfully wrong but if you're not willing to learn the right way to do things and you're at ease with selling a cheaply-made application to your customer, it could be the alternative you're looking for.

Upvotes: 1

Shantanu Gupta
Shantanu Gupta

Reputation: 21108

You can have a check constraint on your table and validation logic can be encapsulated in UDF. This way you can change logic anytime you want.

    CREATE TABLE CheckTbl (col1 varchar(20), col2 varchar(20));

    CREATE FUNCTION CheckPassword(@pass varchar(20))
    RETURNS int
    AS 
    BEGIN
       DECLARE @retval int

        if len(@pass)>6 and PATINDEX('%[0-9]%', @pass) >0 and PATINDEX('%[a-zA-Z]%', @pass) >0
        SET @retval = 1
        else
        SET @retval = 0

       RETURN @retval
    END;

ALTER TABLE CheckTbl
ADD CONSTRAINT CheckPasswordRules CHECK (dbo.CheckPassword(col1) >= 1 );

SQL Fiddle

Test cases

insert into CheckTbl (col1, col2) values('as33dasd', '') --success
insert into CheckTbl (col1, col2) values('33sdasd', '') --success
insert into CheckTbl (col1, col2) values('sdasd22', '') --success
insert into CheckTbl (col1, col2) values('33dasd', '') --fail requires length >6

Upvotes: 1

Related Questions