Question

Write a sql program for password requirment:

I just need sql query.

Passwords are required for all accounts:
Passwords must have a minimum length of 10 characters;
Passwords must have complexity (at least one numeric mixed with alphabetic characters. Upper case and special characters will be allowed but not forced);
Passwords must be changed at least every 90 days; and,
Passwords must be forced to be different than the previous 6 passwords.
Intruder lockout settings are required for all accounts:
Accounts must be locked after 3 incorrect password attempts;
Intruder lockout reset interval must be set to 40 minutes or greater (resets the counter for the intruder lockout threshold); and,
Accounts must remain locked until an administrator resets the account.

Answer 1

Password requirement settings:

For checking all these complexities you will have to add following condition to your query. You can make a function to enforce these complexities to password. This function will contain:

Passwords must have a minimum length of 10 characters:

if length(password) < 10 then
raise_application_error(-20002, 'Password length less than 10');
end if;

Check for the digit in the password:

isdigit:=FALSE;
numdigit:=0;
m:=length(password);
for i in 1..10 loop
for j in 1..m loop
if substr(password,j,1)=substr(digitarray,i,1) then
numdigit:=numdigit + 1;
end if;
if numdigit >= 1 then
isdigit:=TRUE;
goto findlowchar;
end if;
end loop;
end loop;
if isdigit=FALSE then
raise_application_error(-20003, 'Password should contain at least one digit');
end if;

Passwords must be forced to be different than the previous password:

if old_password is not null then
differ:=length(old_password) - length(password);
if abs(differ) < 4 then
if length(password) < length(old_password) then
m:=length(password);
else
m:=length(old_password);
end if;
differ:=abs(differ);
for i in 1..m loop
if substr(password,i,1) != substr(old_password,i,1) then
differ:=differ + 1;
end if;
end loop;
if differ < 4 then
raise_application_error(-20004, 'Password should differ by more than 4 characters');
end if;
end if;
end if;

checking age of the password:

SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
FROM sys.sql_logins
WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -90, GETDATE());