query.. can this be done

[Shift838]

I have been asked to find out if a query can be done to do the following.

We have a database that houses our passwords (access 2003) for our various domain user accounts.

Management want to be able to run a report linked to a query that will check the passwords to make sure they conform to our password criteria of:

Alphanumeric
Upper and Lower case

Can this be done with a query and the sent to a report?

If so how?

 

[Shift838]

I renamed it to Module1 and now get

Invalid Use of Null for the following line:

>>> for x = 1 to len(vpass)


select case chr(mid(vpass,x,1))
case 48 to 57
NumberOK = true
case 65 to 90
upperok = true
case 97 to 122
lowerok = true
case else
charsok = false
end select
next x
pwok = lenghtok and upperok and lowerok and charsok and numberok

 

[Shift838]

ok, I changed a couple of things as follws and it seems to be running. but now how can I change the output. I do not want it to list passwords, but just if it meets each criteria, so a upper and lower case if true would return say a column for true, numeric the same and 8+ characters the same.

changed to:

Function pwok(vPass)
pwok = False
Dim lengthOK As Boolean
Dim UpperOK As Boolean
Dim LowerOK As Boolean
Dim NumberOK As Boolean
Dim charsok As Boolean
Dim x As Integer

lengthOK = False
UpperOK = False
LowerOK = False
NumberOK = False
charsok = True


If Len(nspword) >= 8 Then lengthOK = True


For x = 1 To nspword
Select Case Chr(Mid(vPass, x, 1))
Case 48 To 57
NumberOK = True
Case 65 To 90
UpperOK = True
Case 97 To 122
LowerOK = True
Case Else
charsok = False
End Select
Next x
pwok = lengthOK And UpperOK Or LowerOK And charsok And NumberOK

End Function

 

[lupins46]

That would suggest that you have someone with a blank password.

If passwords can be blank then the routijne needs to be modified to exit immediately.
......
CharsOK = true
if isnull(vpass) then exit sub '*******add this
if len(vpass)>=8 then lengthok = true

for x = 1 to len(vpass)
select

 

[Shift838]

I got it to work, I just need to know now how to format the ouput as follows:

I do not want it to list passwords, but just if it meets each criteria, so a upper and lower case if true would return say a column for true, numeric the same and 8+ characters the same.

 

[lespaul]

you mean something like this?:

SELECT iif(Not PWOK(nspword), "Bad Password", "Good Password) as PasswordValid, field1, field2 from nsaplog

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Shift838]

All this does is prompt me for a value for field1 and field2 then give me "Bad Password" for all my records, which I know is not correct.

I want it to of course use the PWOK function to determine
if:

passwords are >7 characters if not give a password field for length that woudl return "Not complaint" or "Compliant" depending on if it has >7 or <8 characters.

Check to see if there are upper/lower case alpha and return "Not complaint" if both do not exist or "Compliant" if one of them exist, it has to have one of them.

Check to see if there is atleast 1 number in the passoword and return "Not complaint" or "Compliant" in the field.

I only want the ones displayed that do not meet all criteria, so say ID 1 meets all 3 criteria it is not listed int he report, but if ID 2 meets all but on, then it is listed int the report and shows what meets and what does not.

 

[lespaul]

ok, in your table nsaplog are there some fields that you want to return in your query? maybe the person's name or some kind of identifier so that you can see who's passwords don't match? You need to change Field1 and Field2 to ACTUAL FIELDS in YOUR table that you want to see. All this function does is return TRUE the password is valid or FALSE the password isn't valid. The query above returns two fields (which since I didn't know the names of any of yours I substituted) from table nsaplog where the password doesn't conform to the defined rules.

If you want to modify the function to determine - 'you need a number' or 'you need a capital' or 'you need 7 characters' this is possible, but it's not what you have now.

 

[Shift838]

I need additional fields in the database, but do they have to have data in them?

So the function I have you are saying will not determine length, upper/lowercase and numbers?

I am not an access programmer, but this was dropped on me. I know very little about access.

 

[lespaul]

Management want to be able to run a report linked to a query that will check the passwords to make sure they conform to our password criteria of:

Alphanumeric
Upper and Lower case

Can this be done with a query and the sent to a report?

The function that you were helped with returns a true or false value depending on if it meets the password requirements.

This assumes that you have at least ONE table that contains:
1. The password
2. Information on who the password belongs to.

What does management want on this report? A list of all people who's password's don't meet the criteria?

This query will return the name of everyone who's password does NOT comply (assuming you have a table TABLE with a field EMPNAME):

SELECT EmpName from Table WHERE NOT Not PWOK(nspword)

This query will return the name of everyone and an indicator of whether their password conforms:

SELECT EmpName, iif(Not PWOK(nspword), "Not Compliant", "Compliant) From Table

So, you need to find out what information management wants on the report, the tables where the information is and then create a query using those tables AND the function to return a dataset.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Shift838]

neither of these work the way that I need them to. it may be the function. no matter what it returns all my records as non compliant and I know that is wrong.. I want to see:

ID name Lenthgh Alpha (upper/lowercase) Numberic
---------- -------- ----------------------- ----------

idename1 Compliant Compliant or not Non Compliant
Non Non
Compliant Non

If any account meets all three then it will not be shown on the report, only if one or more of them are non compliant.

It may be the function that is the issue.

 

[Shift838]

I did something like this not using a function and was able to determine the lenghth with no problem. is there a way to do this to determine upper/lower case and numeric values? all in the same query??

SELECT NSAPLog.IDName, "Non Compliant" AS Expr1
FROM NSAPLog
WHERE (((Len([nspword]))<8));

 

[lupins46]

There was an error in the function. This is the corrected version.
Function pwok(vPass)
pwok = False
Dim lengthOK As Boolean
Dim UpperOK As Boolean
Dim LowerOK As Boolean
Dim NumberOK As Boolean
Dim charsok As Boolean
Dim x As Integer

lengthOK = False
UpperOK = False
LowerOK = False
NumberOK = False
charsok = True

If IsNull(vPass) Then Exit function
If Len(vPass) >= 8 Then lengthOK = True


For x = 1 To Len(vPass)
Select Case Asc(Mid(vPass, x, 1))
Case 48 To 57
NumberOK = True
Case 65 To 90
UpperOK = True
Case 97 To 122
LowerOK = True
Case Else
charsok = False
End Select
Next x
pwok = lengthOK And UpperOK And LowerOK And charsok And NumberOK

End Function

 

[Shift838]

ok Lupins46, how would we get it display correct as I asked before, id name and then specific if each criteris is met or not?

 

[PHV]

You may try this:

Public Function chkPass(vPass) As String
If Trim(vPass & "") = "" Then 
  chkPass = "Null"
  Exit Function
End If
Dim lengthOK As String
Dim UpperOK As String
Dim LowerOK As String
Dim NumberOK As String
Dim charsOK As String
Dim x As Integer
charsOK = "charsOK "
If Len(vPass) >= 8 Then
  lengthOK = "lengthOK "
Else
  lengthOK = "length=" & Len(vPass) & " "
End If
For x = 1 To Len(vPass)
  Select Case Asc(Mid(vPass, x, 1))
  Case 48 To 57
    NumberOK = "NumberOK "
  Case 65 To 90
    UpperOK = "UpperOK "
  Case 97 To 122
    LowerOK = "LowerOK "
  Case Else
    charsOK = "bad char "
  End Select
Next x
chkPass = lengthOK & UpperOK & LowerOK & charsOK & NumberOK
End Function

And now, the query:

SELECT IDName, chkPass([nspword]) AS TestResult
, IIf(chkPass([nspword]) Like '*lengthOK*','','NON ') & 'Compliant' AS [Length]
, IIf(chkPass([nspword]) Like '*UpperOK LowerOK charsOK*','','NON ') & 'Compliant' AS [Alpha]
, IIf(chkPass([nspword]) Like '*NumberOK*','','NON ') & 'Compliant' AS [Numeric]
FROM NSAPLog
WHERE chkPass([nspword]) <> "lengthOK UpperOK LowerOK charsOK NumberOK ";

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Shift838]

this works almost exactly the way I need it. how can we make it where if either upper or lower case is there it returns compliant. I see if upper case is there and lowercase is not it returns non compliant. Also can we only make it display on the ones that are out of compliance so if all criteria is true it does not display the results for that ID?

 

[PHV]

display on the ones that are out of compliance
It was the intent of the WHERE clause:
WHERE chkPass([nspword]) <> "lengthOK UpperOK LowerOK charsOK NumberOK "

Public Function chkPass(vPass) As String
If Trim(vPass & "") = "" Then 
  chkPass = "Null"
  Exit Function
End If
Dim lengthOK As String
Dim alphaOK As String
Dim NumberOK As String
Dim charsOK As String
Dim x As Integer
charsOK = "charsOK "
If Len(vPass) >= 8 Then
  lengthOK = "lengthOK "
Else
  lengthOK = "length=" & Len(vPass) & " "
End If
For x = 1 To Len(vPass)
  Select Case Asc(Mid(vPass, x, 1))
  Case 48 To 57
    NumberOK = "NumberOK"
  Case 65 To 90, 97 To 122
    alphaOK = "alphaOK "
  Case Else
    charsOK = "bad char "
  End Select
Next x
chkPass = lengthOK & alphaOK & charsOK & NumberOK
End Function

SELECT IDName, chkPass([nspword]) AS TestResult
, IIf(chkPass([nspword]) Like '*lengthOK*','','NON ') & 'Compliant' AS [Length]
, IIf(chkPass([nspword]) Like '*alphaOK charsOK*','','NON ') & 'Compliant' AS [Alpha]
, IIf(chkPass([nspword]) Like '*NumberOK*','','NON ') & 'Compliant' AS [Numeric]
FROM NSAPLog
WHERE chkPass([nspword]) <> "lengthOK alphaOK charsOK NumberOK";

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Shift838]

what is the badchar checking for? If it is checking for special characters I need to remove that..

 

[Shift838]

sorry. meant charsok checking for?

 

[PHV]

So, no function needed:
SELECT IDName
, IIf(Len([nspword]) > 7,'','NON ') & 'Compliant' AS [Length]
, IIf(nspword Like '*[a-z]*','','NON ') & 'Compliant' AS [Alpha]
, IIf(nspword Like '*#*','','NON ') & 'Compliant' AS [Numeric]
FROM NSAPLog
WHERE Not (Len([nspword]) > 7 And nspword Like '*#*' And nspword Like '*[a-z]*')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886