Copyright © 2008 ASC Associates, Technical Support support@ascassociates.biz , Sales Enquiries sales@ascassociates.biz
Access Database Security

In this article we look at different approaches that can be taken to implementing security in an Access Database. A sample database is provided which includes examples of program code to support security functions, and links are provided to key resources providing further information.

Here are some aspects of security that will help you decide how best to implement security : -

If you want an overview of how Access 2003 can support different approaches to security then take a look at Access 2003 -Overview of Access security (MDB)

Just How Sensitive Is Your Data

If your data is so sensitive that you are paranoid about it going off the company site (sales lead database for example), then you need to put the data out of reach in a server like SQLServer or locate the files where they are not easily available. Even if you diligently build a secure database, there are sites on the internet where for very little money you can buy tools which will crack database security (although there are genuine reasons for using such services). The other alternative is to have a database so big that it would be difficult to take away!


Can I Use Network Directory Security

Limiting usage of directories is often used in combination with Access security to secure applications. This is one of the simplest options to choose in implementing security. The only down-side of relying on this in large organisations is that usage of the directory must be managed.

Since Access 2000, you need to ensure that all users of your network folder have Read/Write/Create/Delete permissions on the folder this is explained in Introduction to .ldb files in Access 2000. Or to be more specific they need Delete permissions so the locking file gets deleted after 'last person leaves the application' and they need Create permissions, so the first person using the application can create the locking file. The locking file is the AccessDatabaseName.LDB file which controls the sharing of the Access Database.

As a result of requiring these permissions It is difficult to prevent people from deleting the database file without going to the trouble of revoking Delete permissions on all the access database files. Revoking the permissions presents a further problem when you discover that when the database gets compacted, the permissions revert back their defaults as this process involves creating a new file Compacting Access file resets NTFS permissions.


Using A Database Password

This is a nice simple method for preventing anyone but yourself and a few other individuals from opening the database, this is often used with spreadsheets. But where a database will be used by many different people it is too coarse a form of security.


Integrating To The Operating System Security

My own preferred mechanism for implementing security is to setup a full Access Model of security, defining for example one Username to be used by all general system users. Then I create a shortcut with the Access Username and Password details embedded in the short-cut. System users are then e-mailed the short-cuts which they keep in their own private folder.

The only issue that remains is to identify who the user is! Luckily there is a nice little function which will extract the users Windows Login name! Which you can then read and write into a table to track who is using the system.

The code used for this needs to be pasted into a module and is as follows (also in the sample database) : -

Option Compare Database
Option Explicit
Declare Function UKAUG_GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nsize As Long) As Long

Sub CodeTip3()
' This code users the windows API to get an NT Login Name
Dim USER As String
USER = Space(255)
If UKAUG_GetUserName(USER, Len(USER) + 1) <> 1 Then
USER = ""
Else
USER = Trim$(USER)
USER = Left(USER, Len(USER) - 1)
End If
MsgBox "NT Login Name is " & USER
End Sub


Locking Down An Application

If you search in the online help for "StartupShowDBWindow", you will find an excellent block of code in the Example link in the help system to a routine called Sub SetStartupProperties(). This wonderful bit of code allows you to lock out the database window and many other features.

Just make sure before implementing this code that you have a button somewhere to unlock everything, otherwise you will be faced the particularly nasty problem of being locked out of your own database. I have also included the code for this in the sample database.


Reading A Design

If you prevent users from having permissions to read a design, then you should make sure that you have good error handling, so that if an error occurs you know where the code went wrong. But I am sure that you all have error handling code in all your procedures!


Re-linking Tables

In complex applications you do not want users or possibly administrators to have design rights on objects in the database. However, if administrators are to re-link tables then they will need design rights to do this.

The simple solution here is to give the required users permissions to modify the design of the tables in the application front-end, but not the back-end file. This will enable them to refresh the links but limits any design change to those few properties that can be edited on linked tables. If this is not acceptable then read-on.

It is a restriction in Access that you can not allow a user to execute code with owners permissions. So that in order to re-link tables or get at some hidden information you end up having to embed hidden passwords in the code (Note if you are planning to use SQLServer, this will no longer be a problem, as stored procedures execute with owners permissions). If I have to do this, then I put the code in a special separate module, and disallow any user from reading the design.

If you are using Data Access Objects (DAO) Library code, then this is achieved by creating a new workspace, logging into the workspace, then completing the task.

Sub CodeTip4()
Dim wks As DAO.Workspace
Dim db As DAO.Database
Set wks = DBEngine.CreateWorkspace("SecretWorkSpace", "Username", "Password")
Set db = wks.OpenDatabase(CurrentDb.Name)

Dim tdef As TableDef
For Each tdef In db.TableDefs
' Code to relink the tables goes here
Debug.Print tdef.Name
Next
Set wks = Nothing
End Sub

For alternative methods to the above and a very detailed explanation of this subject refer to the following ACC2000: Microsoft Access Security FAQ Available in Download Center.

You may also come across the term RWOP (Run With Owner Permissions), this feature allows you to create a query which executes with owner permissions (so users would not need permissions on the underlying tables). This only works for saved queries (excludes SQL written in code).


Miscellaneous Security Functions

There are a number of different built in functions to support security. For example to find out the Access User Account use either of the following :-


Working With ADP's And Linking To SQLServer/SQLExpress

If you are linking to a SQLServer either from an Access MDB or using an ADP, then you have the to decide how you will use SQLServer security. Creating Secure Data Access Pages. Here are some suggestions of how to approach this are of development.

Using Trusted Connections is a fairly simple solution. In this case, each user needs to be a Member of an NT Group, and the NT Group is added into the SQLServer and mapped to a database user account in your SQLServer database. The advantage here is that users will not be prompted for any passwords, and no passwords get embedded in the system. The disadvantage is you need to set-up and manage the NT Group.

Probably the simplest approach is to ensure that the SQLServer is installed with Mixed Security, this means that in your database you will have a login name and password. Then allow the login and password to be saved in the links to the SQLServer. Whilst it is possible for someone to extract the SQLServer login information from links, in many applications this is both unlikely to happen and posses a minimum threat. The advantage is that once set-up you have no ongoing management issues. If deploying applications to different client this provides a very simple solution. It is inadvisable to use the "sa" system administration account for this purpose.


Other Links

The examples of code in this article can be Downloaded from this Link.


by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz


Copyright © 2008 ASC Associates, Technical Support support@ascassociates.biz , Sales Enquiries sales@ascassociates.biz