SOS - Database Security?

[Roe]

Under the FAQ 7.c. I found a nice piece of coding from elizabeth to capture the ID of users from the network in MS Access. I have pasted this coding into a module but being very very new to access I'm not sure if
1. I need to create a table with a user names?
2. Do I have to place the fields on a form?
3. Where or what do I link this bit of coding too?

Being new to this game I'm a complete novice feel as if I'm walking on a mine field here.

Coding can be found below, any help greatly appreciated.

Option Compare Database
Option Explicit

Declare Function wu_GetUserName Lib "advapi32.dll" Alias _
   "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) _
   As Long
   
Function ap_GetUserName() As Variant
   
   Dim strUserName As String
   Dim lngLength As Long
   Dim lngResult As Long

   '-- Set up the buffer
   strUserName = String$(255, 0)
   lngLength = 255

   '-- Make the call
   lngResult = wu_GetUserName(strUserName, lngLength)
   
   '-- Assign the value
   ap_GetUserName = Left(strUserName, InStr(1, strUserName, Chr(0)) - 1)
   
End Function

'---------------------------------------

'To get the Login ID, just assign it to a variable:
'    strUserName = ap_GetUserName
'or use it in a sql string:
'    ... Where fldUserName = """ & ap_GetUserName & """;"

'example
'One good example of using the Login ID is to keep users out of a database using this ID.  Create a table with the valid user id's and check the list using an AutoExec macro with the RunCode command.
'You will need to:
 '- Disable the shift key by running the ap_DisableShift function in the debug window (see following Code)
 '- Check the Login ID in a function (see following Code for ValidateUser) and kick the user out if it does not match.

'---------------------------------------

Public Function ValidateUser()

'Confirm that the user should be able to enter this database
    Dim db As DAO.Database, rst As DAO.Recordset
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT fldUser FROM tblValidUsers WHERE fldUser = """ & UCase(ap_GetUserName) & """;")

    'Check to see if the user name is in the list of valid users
    If rst.EOF Then
        MsgBox "You are not authorized to use this database!" & vbNewLine & _
            "Please contact the ************* Department for permission.", _
            vbCritical, "No Authorization"
        rst.Close
        Set rst = Nothing
        Set db = Nothing
        Application.Quit
    End If
       
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    
End Function

'---------------------------------------
'SHIFT BYPASS: (AND ENABLE)
'---------------------------------------

Function ap_DisableShift()
'This function will disable the shift at startup causing
'the Autoexec macro and Startup properties to always be executed

    On Error GoTo errDisableShift
    
    Dim db As DAO.Database
    Dim prop As Property
    Const conPropNotFound = 3270
    
    Set db = CurrentDb()
    
    'This next line disables the shift key on startup.
    db.Properties("AllowByPassKey") = False
    
'function successful
Exit Function

errDisableShift:
    'The first part of this error routine creates the "AllowByPassKey
    'property if it does not exist.
    If Err = conPropNotFound Then
    Set prop = db.CreateProperty("AllowByPassKey", _
        dbBoolean, False)
        db.Properties.Append prop
        Resume Next
    Else
        MsgBox "Function 'ap_DisableShift' did not complete successfully."
        Exit Function
    End If

End Function

Function ap_EnableShift()
'This function will enable the shift key at startup causing
'the Autoexec macro and Startup properties to be bypassed
'if the user holds down the shift key when opening the database.

    On Error GoTo errEnableShift
    
    Dim db As Database
    Dim prop As Property
    Const conPropNotFound = 3270
    
    Set db = CurrentDb()
    
    'This next line disables the shift key on startup.
    db.Properties("AllowByPassKey") = True
    
'function successful
Exit Function

errEnableShift:
    'The first part of this error routine creates the "AllowByPassKey
    'property if it does not exist.
    If Err = conPropNotFound Then
    Set prop = db.CreateProperty("AllowByPassKey", _
        dbBoolean, True)
        db.Properties.Append prop
        Resume Next
    Else
        MsgBox "Function 'ap_DisableShift' did not complete successfully."
        Exit Function
    End If

End Function

Roe

 

[lameid]

I guess it all depends on what you want to do. Coding is an intermediate to advanced topic so you might not want to delve in right away but here it goes...

ap_GetUserName is a function that utilizes the Windows API (Advanced Programming Interface) to retrieve the username logged onto the computer. In other words Access didn't have the function built in so someone decided to ask the OS how to get it.

A funtion is code that returns a value.

I'm stuck in the world of Access 97 but I assume this should work in 2000 too..
Push Ctrl+G to get to the immediate window after saving the module with the code in it. In the bottom pane type "? ap_GetUserName ()" and press enter. This will display your windows username and not your network username (the question mark tells the imediate window tells access to display the result; "? 3 + 2" would return "5&quot.
If you wanted to know who entered each record, you might add a field named username to your table. You might think to add ap_GetUserName to the default value which would be ideal if it would work. Access won't let you use functions that aren't built in as default values in Tables. Solution? Create a form based on the table to enter data and set the control bound to username to ap_GetUserName.
The contents of the field should read "= ap_GetUserName ()".

Because it is very easy to use a different windows username, I prefer to get the network username instead of the windows username.

I manipulated code I found in a Microsoft KB article to get the network username. It is listed below and includes comments regarding the MS KB article used.

Declare Function WNetGetUser& Lib "Mpr" Alias "WNetGetUserA" (lpName As Any, ByVal lpUserName$, lpnLength&)
Function NetworkUser() As String
'Hacked up code from ACC: How to Retrieve Workgroup Information Under Win32
'PSS ID Number: Q148835
'needs declaration: Declare Function WNetGetUser& Lib "Mpr" Alias "WNetGetUserA" (lpName As Any, ByVal lpUserName$, lpnLength&)
Dim cbusername As Long, username As String
Dim ret As Long
username = ""

' Windows 95 or NT - call WNetGetUser to get the name of the user.
username = Space(256)
cbusername = Len(username)
ret = WNetGetUser(ByVal 0&, username, cbusername)
If ret = 0 Then
' Success - strip off the null.
username = Left(username, InStr(username, Chr(0)) - 1)
Else
username = ""
End If
NetworkUser = username
End Function



I hope this helped at least a little.