ctyrrell@stny.rr.com wrote in
news:1130413036.559524.181990@g14g2000cwa.googlegr oups.com:
[color=blue]
> Thanks for trying to reproduce my problem. You did use my version
> of HasBlankPassword - right? The one with the Stop in it? Other
> wise you would not see the problem - other than the result from
> HasBlankPassword would start to be always false after 255 calls
> for a User who does have a password. That might be a dumb
> question, but in fact I made that very mistake yesterday when I
> first got your HasBlankPassword.
>
> I am trying to confirm whether I am using the latest Jet service
> pack. I have msjet40.dll version 4.0.8618 in C:\Windows\SYstem32.
> According to
http://support.microsoft.com/kb/239114/ this is
> 'Security Bulletin MS04-014'. Since Service Pack 8 had version
> 4.0.8015.0, I assume that what I have incorporates SP8. I did
> notice that I also have msjet35.dll in C:\Windows\System32. I
> assume I am using the msjet40.dll and not the msjet35.dll. Is
> there any way to confirm this through code?[/color]
If you can't get it fixed, you can always do it the way this
function does it, by actually querying the MSysAccounts table in the
System.mdw file.
OK, stepping aside for a minute, it turns out that there was a
change in the way passwords are stored from Jet 3.5 to Jet 4.0. In
Jet 3.5, they were stored in a Binary 255 field. Because of Unicode,
this was changed to Binary 510. The result of that is that the value
being tested is not the same.
First, let me present the methods for Jet 3.5:
Public Function ListUsersWithoutPassword() As String
' works for Jet 3.5 ONLY
Dim strSystemDB As String
Dim db As DAO.Database
Dim strSQL As String
Dim rs As DAO.Recordset
Dim strTemp As String
strSystemDB = DBEngine.SystemDB
Set db = DBEngine.OpenDatabase(strSystemDB)
strSQL = "SELECT MSysAccounts.Name FROM MSysAccounts"
strSQL = strSQL & " WHERE MSysAccounts.FGroup=0"
strSQL = strSQL & " AND MSysAccounts.Password='iR*góÕáæiR*góÕáæ'"
strSQL = strSQL & " ORDER BY MSysAccounts.Name;"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount <> 0 Then
rs.MoveFirst
Do While Not rs.EOF
strTemp = strTemp & ", " & rs!Name
rs.MoveNext
Loop
End If
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
ListUsersWithoutPassword = Mid(strTemp, 3)
End Function
You could use the same approach to test for a specific username:
Public Function HasBlankPassword(strUser As String) As Boolean
' works for Jet 3.5 ONLY
Dim strSystemDB As String
Dim db As DAO.Database
Dim strSQL As String
Dim rs As DAO.Recordset
strSystemDB = DBEngine.SystemDB
Set db = DBEngine.OpenDatabase(strSystemDB)
strSQL = "SELECT MSysAccounts.Name FROM MSysAccounts"
strSQL = strSQL & " WHERE MSysAccounts.Name='" & strUser & "'"
strSQL = strSQL & " AND MSysAccounts.Password='iR*góÕáæiR*góÕáæ';"
Set rs = db.OpenRecordset(strSQL)
HasBlankPassword = (rs.RecordCount <> 0)
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Function
Now, how to make it work in Jet 4.0? Well, I was unable to coerce
the password field values into anything that could be stored as a
string or byte value in a VBA module. But, I discovered that I could
join the MSySAccounts table to itself on the Password field and get
valid results. '
So, the method I used was to do a MakeTable to create a table that
had one record that stored the blank password value. I happened to
do this in a System.MDW file, which makes it easy to link to the
MSysAccounts file.
The result is that this SQL, using a 1-record table
(tmpBlankPassword) with the blank password stored in it, returns the
usernames with blank passwords:
SELECT MSysAccounts.Name
FROM MSysAccounts INNER JOIN tmpBlankPassword
ON MSysAccounts.Password = tmpBlankPassword.Password;
So, with this method, I don't have to know the blank password, I
just have to be able to store it so that it can be compared to the
values in your real MSysAccounts table, and creating this temp table
in your System.mdw is a one-time task. What I did was work with a
copy of the real System.mdw after I'd created a user with a blank
password, then after creating the temp table storing the blank
password value, I exported it to the real System.mdw. Then I could
easily rewrite the functions above to use the join to determine
which users had a blank password.
It's not as pretty as using the workspace test, but it's far simpler
and it will definitely be much more efficient.
Now, what about using undocumented system tables? While I have never
thought it's a good idea to rely on undocumented features of the
system tables in Access, I think this is one that will continue to
work for Jet user-level security. See this from MichKa for a
justification for this:
Can you rely on system tables? How about DAO?
http://www.trigeminal.com/usenet/usenet017.asp
MichKa's point there is that MS has a policy of maintaining complete
backward compatibility, so that anything that works in a previous
version of Jet has to work in a later version. Given that Access 12
is going to a different data format and a different security model,
it's even more unlikely that the structure of Jet system tables will
ever be changed. So, I think this method is pretty safe.
The only thing that might happen is that the encrytion could change,
which would alter the value stored for a blank password, but it
looks like Jet uses a consistent value for all blank passwords
within a version of Jet, so the temp table storing that value should
work, though the actually blank password value could itself be
different in different future versions of Jet.
--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc 
