Password username help

[Carl23]

I have an Access 2010 database that I would like to give the Administrator, Manager, User or Viewer access to the database upon correct entry of their username and password on a form called Login. The username and password, as well as other related information is stored in a table call Staff. Pertinent fields of the Staff table are:
1. Employee_Num
2. Staff_Name
3. User_Name
4. Password
5. Status (1=Administrato r 2=manager 3=user 4=viewer status)

Only an administrator shall be given access to the Staff table. I need to be able to add to each form the current username, status, date and time where records are added. Also, I need to add to any record that is edited, the username, status, date and time of the Administrator or Manager. How do I get the username from the Logon form on a record on another form like the patient information form? This is complicated by multiple users will be on different terminals at the same time. If you have a simple article to get started, I would appreciate it.
Thanks,
Carl23

[NeoPa]

Carl23:
Also, I need to add to any record that is edited, the username, status, date and time of the Administrator or Manager.

I'm not sure how you'd do that as your design doesn't seem to support knowing who the Admin or Manager is.

Otherwise, you need to keep a note somewhere, either in a Front-End database if that's how you do things or maybe in a hidden control on a form that stays open throughout the session, of the PK of the [Staff] table. With that PK available across the whole project you're only ever a couple of lines of code away from all the data in the relevant [Staff] record.

NB. Never store passwords in clear text in a table. Always obfuscate them in some way first, preferably with some decent encryption (See AES Encryption Algorithm for VBA and VBScript, RC4 Encryption Algorithm for VBA and VBScript and/or SHA2 Cryptographic Hash Algorithm for VBA and VBScript).

[Carl23]

Hope this helps, the PK of the [Staff] table is the Employee_Num. Would it work to have the username accessible as a drop down combo box that also contains the PK?
Thanks,
Carl23

[Rabbit]

Carl, what's to stop them from using the shift key bypass to skip all the code and get direct access to the tables?

[Carl23]

I was on the Microsoft website at

http://bytes.com/topic/access/answers/940083-password-username-help

does their TIP solve this problem?
Tip To prevent users from bypassing startup options, disable the Bypass (SHIFT) key by using Visual Basic for Applications (VBA) code to set the AllowBypassKey property of the database. For more information about setting the AllowBypassKey property, click the link in the See Also section of this article.
Thanks,
Carl23

[TheSmileyCoder]

A few general pointers first based on my estimation on the scope of your project, most of these you probably (hopefully) allready have in your setup:
You must divide the application into a frontend application for each terminal and a backend database.
You must distribute a compiled version of the frontend.
You must disable the shift bypass key.
You MUST encrypt passwords.

Now onto the specific issue.

In AC2007 and AC2010 the Tempvars collection was added. You can use this to easily store the userID as well as the user role. Once the user has succesfully had his password verified you can store it like so:

tempvars.Add "UserID",2

where 2 should be the primary KEY of the person in your users table.
and recall it like so:
tempvars!UserID

You do the same for your Role (status you called it). Now when a form opens, you can set the properties accordingly, if you for example add this code to a custom module:

Public Sub SetViewMode(objForm As Form)
   Select Case TempVars!Role
      Case 1 'Adminstrator
         objForm.AllowAdditions = True
         objForm.AllowDeletions = True
         objForm.AllowEdits = True
      Case 2
         objForm.AllowAdditions = True
         objForm.AllowDeletions = True
         objForm.AllowEdits = True
      Case 3
         objForm.AllowAdditions = True
         objForm.AllowDeletions = False
         objForm.AllowEdits = True
      Case 4
         objForm.AllowAdditions = False
         objForm.AllowDeletions = False
         objForm.AllowEdits = False
      Case Else 'If all works well the case else will never execute. Its merely a failsafe
         MsgBox "Unauthorized Access"
            DoCmd.Quit
   End Select
         
End Sub

and this code to your forms open event:

Private Sub Form_Open(Cancel As Integer)
  SetviewMode Me
End Sub

You can ofcourse also write the above values for each specific form if required. For instance a addition to the form you use to manage staff could look like:

Private Sub Form_Open(Cancel As Integer)
  If tempVars!Role<>4 then
    Msgbox "This form is for administrators only"
    Cancel=True
    Exit Sub
  End If
  SetviewMode Me
End Sub

Finally on the matter of storing the edit details. For each table, you add the fields: ID_CreatedBy, dt_Created, ID_ChangedBy, dt_Created

In your forms BEFORE_Update event you then add:

If Me.NewRecord Then
  Me.ID_CreatedBy=TempVars!UserID
  Me.dt_Created=Now()
Else
  Me.ID_ChangedBy=TempVars!UserID
  Me.dt_Created=Now()
End If

That became a rather lengthy post. Bear in mind, that this is just examples of how it can be done, there are always several ways to do something.

[NeoPa]

If TempVars is session-level data then beware the likelihood of the session dying. This can happen for a number of reasons, but if it is held available regardless of the active session then that's good advice. It's already really easy to store data at the session level which is globally available so I cannot see why any intelligent person would create a concept of TempVars if they only gave the same features as what's already available.

[Rabbit]

You can disable the shift bypass, but the user, if they know what they're doing, can reenable it.

[TheSmileyCoder]

Tempvars is session level data, that is stored even if a unhandled error occurs. I used a similar home-cooked solution for storing session level data that would be kept in memory, but had a backup placement in a table, in case a unhandled error found its way into my coding.

[NeoPa]

Smiley:
Tempvars is session level data, that is stored even if a unhandled error occurs.

That confuses me. Session level data is lost when the session dies (EG. when an unhandled error occurs and the operator selects "End".) If TempVars are maintained beyond this then I'm not sure they can be described as session-level. Either it's availability is limited to the live session, or it isn't.

[TheSmileyCoder]

Im not sure how else to describe it. If you quit access the tempvars is reset, but a error will not reset it. So I would still use the term session level, unless you have a better word for it.

[NeoPa]

I don't. Nevertheless, the last thisng it would be is session-level, as that indicates exactly what it isn't. I assume when you say about errors resetting things you're talking about when the operator chooses to reset rather than any time an error occurs. Believe me Smiley, I'm not trying to criticise your English. It's worlds beyond my Danish. I'm simply trying to ensure that what is here is clear and precise so that all can understand what is said and the implications.

It does sound, assuming I have understood you correctly (and I think I have now), that this is indeed something which fills an important gap. I wasn't aware of these (obviously from the conversation), but if I had been, I think I would have suggested this approach too. Better than storing values on forms or writing defensive code that repeatedly has to check for the availability of the data before ever using it.

[TheSmileyCoder]

If you have a module with a global (I.e. defined in the general area at the top, and not inside a procedure) variable, it will reset if:
A unhandled error occurs
Someone enters into VBE and hits the reset button

Tempvars are kept in both of the above cases.

[NeoPa]

Smiley:
...it will reset if:
A unhandled error occurs

You probably got that from MS somewhere. It's not quite correct. It only resets if the operator chooses "End" if they are prompted. If "Debug" is chosen the session continues regardless of the unhandled error.

That said, your meaning is very clear and now understood. As I said earlier, I'm convinced it's the way to go for this, as long as v2007 or later is being used.