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?

 

[lupins46]

You are not seriously suggesting that you want a report listing out the passwords are you?

 

[Shift838]

no, we will not list the passwords just whether they conform to our password policy, I just need to check password complexity as follows:

1. Upper/lowercase
2. Numeric
3. atleast 8 characters

If the passwords meet these 3 then they meet otherwise they do not. I figured we query the table for user id and password and the check the password somehow for the above. let it dish out a report stating the ID and whether it meets or not. pretty simple I would think, I just don't know how to start it.

 

[HockeyFan]

All you should need to do would be to create a query, then in the criteria row at the bottom, just put the test for each of the items you have listed above. for example, for uppercase, you could use the ucase function, for numeric, use the numeric function, and for 8 characters, use the len function. You can put all three criteria in the same query. then your results will show only the records that match your criteria. After that, you can then do an unmatched query between that list and your original list. You can also have a cross reference table in your database that would list the people's real names, where their passwords don't conform.

 

[lupins46]

Test for length:
len(password)>=8

case: (requires two tests)
strcomp(ucase(password),password,0)= false
and strcomp(lcase(password),password,0) = false

number:
mmmm.... probably requires a function to be written to check each character.

hasnumber(password) = true

Function HasNumber(vPass)
HasNumber = false
dim x
for x = 1 to len(vpass)
if chr(mid(vpass,x,1)) >=48 and chr(mid(vpass,x,1)) <=57 then
'ok
HasNumber = true
exit for
else
end if
next x
end function

 

[Shift838]

could we use a charset query to check the entire field for say a-z, A-Z and 0-9 ?

 

[Shift838]

meant charset

 

[lupins46]

You could build all the tests into one function, if that's what you mean. You would need flags to match each rule, wouldn't you.
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
lengthok = false
upperok = false
lowerok = false
numberOK = false
CharsOK = true

if len(vpass)>=8 then lengthok = true

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]

how would I make the flags for the rules and get that into the query? sorry but i am not good in access.

 

[lespaul]

paste the code above in a module (it may need some tweaking), then something like this in your query:

SELECT * from tablename where Not PWOK(PasswordField)

Leslie

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

 

[Shift838]

I get a undefined function 'PWOK' in expression now..

 

[lupins46]

The function needs to be placed into a standard module, not a form module.

 

[Shift838]

I put this in a standard module I thought. I'm not sure I know the difference between standard module and form module.. I went to modules and created a new one, does not give me an option for standard or form..

 

[lupins46]

Can you compile your code OK?
In athe code mode, do Debug.compile ......
Does it produce any errors?

 

[Shift838]

compiles with no errors.

 

[lespaul]

what's the SQL of the query you are trying to run?

Leslie

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

 

[Shift838]

SELECT * from nsaplog where Not PWOK(nspword)

 

[lupins46]

If you click into the Immediate/Debug window in the code area and do:

?pwok("abcd")
and press enter, what do you get?

 

[Shift838]

expected variable procedure not module

 

[lupins46]

Have you renamed the module?
If so change it to Module1 or something similar.