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 : -
- How secure does you data need to be?
- Can you use directory security to secure your system?
- Are you worried about someone taking away a copy of the database?
- How easy do you want to make it for users to use your system?
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!
Top of Page
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.
Top of Page
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.
Top of Page
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
Top of Page
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.
Top of Page
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!
Top of Page
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).
Top of Page
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 :-
- CurrentUser()
- DBengine(0).UserName
Top of Page
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.
Top of Page
Other Links
The examples of code in this article can be Downloaded from this Link.
Top of Page
by Andrew Couch Office Access MVP, Director ASC Associates
andy@ascassociates.biz