Usernames and Passwords

[jason12776]

I am trying to set up a username and password program to advance the user to one form for certain usernames/passwords and other forms for others. I would also like to have a form for administrators. I am tying this is with a MS-Access 2000 database. I already have some usernames and passwords in the database. I can call a username, but how can I associate a password that is typed with the username that is typed in the database? I do have 2 adodc controls, one comparing the username and one with the password, if that helps.

 

[MasterWarlock]

greetings!
if you have the username and password as fields in a single table. you can validate them simultaneously.
now the thing is, if ur using MsAccess2000, the kind of provider for your ADODB will vary from the usual version 97.
if you want to do it programmatically here's how it will look like:

dim sql as string, conn as string
dim rs as new adodb.recordset
dim dpath as string
dim ok2enter as boolean

dpath = 'the location of the database
conn = "provider=microsoft.jet.oledb.4.0;data source=" & dpath
sql = "SELECT * FROM tblname"
rs.open sql,conn,,,
ok2enter = false
with rs
.movefirst
do until .eof
if .fields("username&quot = txtusername.text and _
.fields("password&quot = txtpassword.text then
ok2enter = true
'the user can enter
endif
.movenext
loop
end with
rs.close

if ok2enter then
'your code calling the form to display
else
'msgbox of invalid entry
endif

 

[jason12776]

Hey, thanks a lot, that worked great.

 

[cdukes]

Do you really need to loop around every single record in the user table and checking for the correct entry. If tblname contained 500,000 records your login process would be rather slow.

What about using a where clause.


sql = "SELECT * FROM tblname WHERE username = '" & txtusername.text & "' and password = '" & txtpassword.text & "'"

rs.open sql,conn,,,
ok2enter = false

' if the recordset is not empty then we hae found
' a record
if not rs.eof
ok2enter = true
'the user can enter
end with

Hope this helps,

Chris Dukes

 

[WilMead]

I recommend a slightly different solution.

You must assure that the authorize/authenticate method makes the decision returning only results. This puts your password package in jepordy less often. Assuring that the decision making process remains in the authorization method standardizes the rules and keeps the implementation in one place.
Change your query to something more along the lines of:[tt]
SELECT AccessLevel FROM Table WHERE UserId= UID AND pwd = PWD[/tt]
An empty recordset means the user is not authorized, id or pwd is wrong. A list means the user has varying degrees of access. Expecyt only 1 row in normal conditions.

Level based Authorization provides a specialized model of authentication. Successful authentication yields varying degrees of access rights. I suggest including fields to your user table, one for the application the other Access level. Using this enables listing Access Levels by User and Application.

An Application table identifying the access levels required by each application function should help with the management of the relationship.

You can build this with two objects, User and Function.
Watch out for reserved words or namespace collisions.

The User object's key members are Authenticate and AccessLevel. The Function object provides Authorize. Other names for this might be Attach or Bind.
A desirable coding sequence might look like this:
[tt]
SET oUser = new ClassBasedOnUser
oUser.Authenticate(UserId, Password)

SET oFunction = new ClassBasedOnFunction
oFunction.Attach (oUser) ' Set Objects performance level
oFunction.Execute ' Do level appropriate work
[/tt]

Attach can do all the work, building the object with the members appropriate to the access level. Query the database for the list of functions the user is authorized to use and have attach load the objects that implement them.
[tt]SELECT FunctionName from Application where USER = UserId AND AccessLevel < UserLevel[/tt]
Or, the level sensitive members, like Execute, can handle the access level variations themselves. Both ways have merits and penalties.


Wil Mead
wmead@optonline.net