Access Database Security Question

Question

Hi,

I have a database in Access 2003 that I only want certain people to be able to access. In my database I have a table which lists the people that should be able to access the database. (Tbl_BIRT_Users). The table contains their name, ntlogin and email address. It also has an 'adminstrator' field.

My question has two parts:

1 - On opening the database how can I get it to lookup the ntlogin (environ username) of the person and ensure that that person is authorised to use the database?

2 - I need the database to look at the 'administrator' Yes/No field and grant read only access to non admins and full access to admins.

Thanks!

Answer 1

Hi Steve

Could you not just do something like this

Dim rst as Recordset
Dim sql as string

sql = "SELECT * FROM Tbl_BIRT_Users WHERE ntlogin = '" & Environ("UserName") & "'"
set rst = CurrentDb.OpenRecordset(sql)

    if (rst.bof and rst.eof) then
        /*not a valid user*/
        DoCmd.Quit
    else
       if not rst!Administrator then
         /*make read only*/
       end if
    end if

rst.close

Answer 2

Use an API call to get the login name - API: Get Login name You can change environment variables while at the command prompt and then, if start Access executing from the command prompt, Access will use the spoofed environment variable.

Also there are ways of easily breaking table driven security such as the user taking the backend database home to a retail copy of Access resides, changing the values in the tables and bringing the database back to the office.

Answer 3

Even if you trust the users not to fiddle with their environment variables, please adopt Tony's suggestion anyway. After you add the module he linked, retrieving the user's account name is a simple call to the fOSUserName() function. It's really no more difficult than getting it from the user's environment.

But I want to add to Tony's point about "easily breaking table driven security". Your plan is to check whether the user is one of your authorized users. My suggestion is to place your back end database file in a location where only your authorized users can get at it. Use Windows file system permissions to keep everyone else out. That way you may decide you don't even need to check your table to determine whether the user is authorized. You could still use the table data to determine whether the user is an Admin or regular user. Or you might decide to keep the authorization check if it gives your managers peace of mind ... even though it doesn't really offer much security.

Answer 4

This is the Access security window dressing I use.

Public Function SecurityCode() 
'*  Purpose:    Limits access to program

    Dim sUserID    As String
    Dim sUserName   As String    

'*  Determines user from Windows Login
    sUserID = Environ("USERNAME")


'*  Lookup on BE table of Allowed Users to verify on the list.
     sUserName = DLookup("[UserName]", "tbl_AllowedUsers", "ID = '" & sUserID & "'")


If Len(sUserName) > 0 Then
    'Allowed User, opens Main Switchboard

    'Set global variable for Admin rights
    g_Admin = DLookup("[AdminRights]", "tbl_AllowedUsers", "ID = '" & sUserID & "'")

    DoCmd.OpenForm "Switchboard"
    DoCmd.SelectObject acForm, "Switchboard", True
    DoCmd.RunCommand acCmdWindowHide

Else
    'Not on the Allowed Users list, opens to a Password Page
    DoCmd.OpenForm "frm_LockPage"
    DoCmd.SelectObject acForm, "frm_LockPage", True
    DoCmd.RunCommand acCmdWindowHide
End If


End Function

Answer 5

Try something like the below:

Function RealName()
payroll = Environ("Username")

firstname = DLookup("[first name]", "[Payroll Numbers]", "[persno] = " & payroll)
lastname = DLookup("[Last name]", "[Payroll Numbers]", "[persno] = " & payroll)

If IsNull(firstname) = True Then
RealName = payroll
Else
RealName = firstname & " " & lastname
End If

End Function

You can then enter code in the form_load event to make sure it's a verified user.