Access Database Security Alternative

Security Without Work Group Administrator

Access Database Security Alternative: database security can be a challenging aspect to MS Access database creation. Working with the workgroup administration tool and setting the security levels can be confusing at times.

Additionally, if you have multiple Access databases on the computer and you set workgroup security for one then you'll need to join the workgroup for all databases. This can be maddening especially if you are a developer like me with literally 100's of Access databases.

After setting up security with passwords your database is not secure at all since anyone can purchase a Microsoft Office product password cracker/hacker and break into the database. Because of this situation we never setup Access databases with workgroup security. We have created a fairly simple method for implementing user-level and group security with a simple table of permissions/password and a subroutine to set form update properties. We combine this with a few settings in the database to make it difficult for a user to get into the code or into the tables.

Detailed below is an example of one method for implementing a simple yet effective database security scheme for Microsoft Access databases.

Here are the goals of this method:

Avoid use of the Workgroup administrator
Eliminate ability of password cracker/hacker software to break into the database
Easy security maintenance by the database administrator and easy program maintenance for the programmer.

There are four components involved in the implementation of the security method.

An Employees table that contains attributes about the employee and the user password.
A tabbed user interface to facilitate to facilitate implementation of the security method
A subroutine to read and set appropriate security settings on the form and subform
Database settings and VB code to prevent user from breaking directly into the tables and the VB code.

Here is the L_Employees table:

**L_Employees**
Employee_ID	Username	Password	Access_Level
15	AndreaF	*******	A
14	HeidiH	******	M
3	HollyW	********	A
5	JenniferC	*****	M
6	JodyB	********	A
7	JonE	*****	A
1	Joe	***	M
8	KristalD	*******	M
9	MariaH	*****	M
10	MaryC	**********	A
11	MaryW	*******	A
12	RussellN	*****	A
13	ScottR	*******	A
2	TinaC	******	M

Note the Access_Level field - M is for Managers (full access); A is for Artists (partial access).

When the database opens the On Open event is activated and the following VB code runs:

Private Sub Form_Open(Cancel As Integer)
    '    initialize global variables
    set_globals
    '    Hide tabs
    Me.TabCtl0.Pages.Item(0).Caption = "Welcome"
    Me.TabCtl0.Pages.Item(1).Visible = False
    Me.TabCtl0.Pages.Item(2).Visible = False
    Me.TabCtl0.Pages.Item(3).Visible = False
    Me.TabCtl0.Pages.Item(4).Visible = False

username = Environ("Username")

me.username=username

End Sub

Here is the set_globals routine and get_globals functions. Put them into a Module:

Option Compare Database

Global GBL_employee_ID As Long
Global GBL_Access_Level As String

Function set_globals()
GBL_employee_ID = 0
End Function

Public Function get_global(gname As String)
Select Case gname
    Case "Employee_ID"
        get_global = GBL_employee_ID
    End Select
End Function

Place the above code in a new module and you can name the new module anything you like - the important part is the 'Global GBL_Access_Level As String' line that must come after the Option Compare Databse and before the Option Explicit line.

The result is a login form on the welcome screen of the tabbed interface:

(Click to view full size image)

After the correct username and password has been entered then the following subroutine is called. Here is the login subroutine code:

(note that some lines are wrapped because of page width limitations)
Private Sub Login_btn_Click()
' ************************************* login sequence
On Error GoTo local_err
DoCmd.RunCommand acCmdSaveRecord
'
' set global access level to failsafe no access then lookup access level
'
GBL_Access_Level = "X"
GBL_Access_Level = Nz(DLookup("Access_Level", "L_Employees", "Username='" & Nz(Me.USername, " ") & "' and password='" & Nz(Me.Password, " ") & "'"), "X")
'
'    examine results of username/password lookup
'
If GBL_Access_Level = "X" Then
    MsgBox "Invalid Username or Password... try again."
    Exit Sub
End If
'
' get employee id
'
GBL_employee_ID = Nz(DLookup("Employee_ID", "L_Employees", "Username='" & Nz(Me.USername, " ") & "' and password='" & Nz(Me.Password, " ") & "'"), "X")
'
'set welcome to include users name or invalid logon if bad logon
'
Me.TabCtl0.Pages.Item(0).Caption = "Welcome " & Nz(DLookup("employee_name", "L_Employees", "employee_id=" & get_global("Employee_ID")), "Invalid Login")

' setup privs based on artist or manager
'
Form_F_Projects.Requery
'
' call subroutine to set access to forms
'
Call set_privs
'
' reset login screen fields
'
Form_F_Projects.Requery
Me.USername = ""
Me.Password = ""
Me.TabCtl0.Pages.Item(1).SetFocus
Exit Sub
'
' error handler
'
local_err:
    MsgBox "unexpected error= " & Err.Description
    Resume ok_exit
ok_exit:
Exit Sub

Below is the subroutine to set privileges and show/hide menu tabs. Note that we use a simple case statement to examine user access level and set visibility of tabs and properties of appropriate forms. For the limited access 'A' - Artists the Reports and Lists tab are hidden since only the managers can run reports and modify list data (including passwords and usernames).

Public Sub set_privs()

Form_F_Projects.Form.AllowAdditions = False
Form_F_Projects.Form.AllowDeletions = False
Form_F_Projects.Form.AllowEdits = False
Form_F_Projects.Form.AllowFilters = False
'
'
Form_F_Project_Actions.Form.AllowAdditions = True
Form_F_Project_Actions.Form.AllowDeletions = True
'
'status field is updateable by everyone
'
Form_F_Project_Status_Only.Form.AllowEdits = True
Form_F_Project_Status_Only.Form.AllowAdditions = True
'
Select Case GBL_Access_Level
Case "M" ' manager

Me.TabCtl0.Pages.Item(1).Visible = True
Me.TabCtl0.Pages.Item(2).Visible = True
Me.TabCtl0.Pages.Item(3).Visible = True
Me.TabCtl0.Pages.Item(4).Visible = True
'
Form_F_Projects.Form.AllowEdits = True
Form_F_Projects.Form.AllowAdditions = True
Form_F_Projects.Form.AllowDeletions = True
'
Form_F_Project_Products.Form.AllowEdits = True
Form_F_Project_Products.Form.AllowAdditions = True
Form_F_Project_Products.Form.AllowDeletions = True
'
Form_F_Project_Status_Only.Form.AllowEdits = True
Form_F_Project_Status_Only.Form.AllowAdditions = True
'
Form_F_Project_Actions.Form.AllowEdits = True
Form_F_Project_Actions.Form.AllowAdditions = True
Form_F_Project_Actions.Form.AllowDeletions = True
'
Form_F_Projects.Requery
Form_F_Project_Products.Requery

Case "A" ' artist

Me.TabCtl0.Pages.Item(1).Visible = True
Me.TabCtl0.Pages.Item(2).Visible = True
'
Form_F_Project_Products.Form.AllowAdditions = False
Form_F_Project_Products.Form.AllowDeletions = False
Form_F_Project_Products.Form.AllowEdits = False
'
Form_F_Project_Status_Only.Form.AllowEdits = True
Form_F_Project_Status_Only.Form.AllowAdditions = True
'
Form_F_Project_Actions.Form.AllowEdits = True
Form_F_Project_Actions.Form.AllowAdditions = True
Form_F_Project_Actions.Form.AllowDeletions = True
'
Form_F_Projects.Requery
Form_F_Project_Products.Requery

End Select

Here is the resulting main menu after security settings - all tabs are available in this case:

(Click to view full size image)

The last part of the security setup for this database is to restrict artists viewing of project data. Artists are only allowed to modify certain fields of their own projects. And, are only allowed to review their own projects.

Here is the query record source for the main projects form:

SELECT M_Projects.Project_ID, M_Projects.ProjectName, M_Projects.Manager_ID, M_Projects.DateOpened, M_Projects.Project_Status_ID, M_Projects.Due_Date, M_Projects.Artist_ID, M_Projects.DateClosed, M_Projects.Agent_ID, M_Projects.Sale_Rep_ID, M_Projects.RouteTo, M_Projects.BillTo_ID, M_Projects.ProjectPaidDate, M_Projects.Project_Type_ID, M_Projects.FarmList, M_Projects.Indicia_ID, M_Projects.Design_ID, M_Projects.Output_Spec_ID, M_Projects.Files_Submitted, M_Projects.Payment_Method_ID, M_Projects.Comments, M_Projects.Date_Modified, M_Projects.Modified_By
FROM M_Projects
WHERE (((M_Projects.Artist_ID) In (select employee_id from Q_Employee_Access_List) Or (M_Projects.Artist_ID)=0));

(note that (M_Projects.Artist_ID)=0) allows new projects to be created)

Below is the SQL code for the Q_Employee_Access_List query referenced above

SELECT L_Employees.Employee_ID
FROM L_Employees
WHERE (((L_Employees.Employee_ID)=get_global('employee_id'))) OR (((get_global('access_level'))="M"));

The combination of these two queries limits access to project data. Artists with an 'A' access level can only see their own projects and Managers with 'M' access level can see all projects. The individual form setting defined in the Set_Privs subroutine (above) determines who can modify data in which forms.

Finally, you can see that once setup, this method for applying security and access levels to databases is fairly easy to implement. The method is also flexible since additional levels access can be added by simply adding more Select Case statements in the Set_Privs subroutine.

We have developed a simplified downloadable Access Security Alternative database example. You can find this running example in our new Access Database Downloads.