I rarely use MS Access security as I disagree with the fundamental approach. MS Access security requires Users to be given the very passwords that can be used to crack data security! I have had a few clients drop their jaw when they realise that. So I won't discuss the internals of this as I am hardly the experienced voice of reason.
The Main method of security I use is Application Controlled security. This means that the application knows the security details, not the User. Strangely enough, this is very simple. It does not matter if you have a MS Access, SQL Server or Oracle backend.
The backend is locked down, available to only one User ID and Password, used by the application.
The User ID and Password is held in a code module and the application is saved as a MDE or ADE file.
Users, if they have to be identified, can be recognised by manual User ID/Password, Network ID and/or PC ID. These are validated by the application when connected to the backend, by interrogating a backend User table.
Most data work these days can be performed using ADO. An ADO connection is established at the beginning of the User session, using the embedded User ID and Password, and closed when the application quits. Forms, Reports, conbo-boxes etc can be fuelled by ADO recordsets, invisible to security intrusion.
If you do have to use linked tables, access queries or Pass-Through queries (SPT's), be aware that they hold the connection string, including the User ID and Password. This can be handles by writing the connection string before running it, and blanking it after use.
Data Security on every project should be defined.
Import files will be held in directories that may only be “seen” by Administrators.
Export files could be automatically E-Mailed and hence will not reside in BMS common directories. Export files that are saved on BMS drives are at the discretion of the Administrator.
The purpose of security may only be to prevent any amendment to the backend database without using the front-end application, hence not benefiting from built-in checks, validation, limitation and business rules.
The sensitivity of data must be considered.
All or part of the data may need protecting due to its commercial or personnel nature.
It may be an auditing requirement to demonstrate that data may not be manipulated illicitly or accidentally, outside of the software application.
If relevant Customer Guidelines exist about Data Security and Privacy, then these must considered.
Statutory requirements such as the UK Data Protection Act should be considered. When deploying to many countries, the statutory requirements for those countries should be considered.
If the application requires back-end security and uses the Network ID to identify the User, an additional program-controlled password may be required to prevent illicit cloning of the Network User ID, to gain access to the system.
The MS Access front end and external libraries should be secured from intrusion by the User. They should be.MDE files, set to auto-run and have program interruption, via the shift key, inhibited.
Database View should be inhibited.
If backend security is to be controlled by the MS Access front-end application, the security details should not be able to be read by the User. This means that Account names and passwords should be hidden in the code. Also, steps must be taken to ensure that other objects, such as table attachments and queries, do not statically bear that information.
MS Backends should be in a folder only visible to authorised Users.
If back-end security is required, then MS Access security should be set.
This will be handled dynamically by the application and will not require Users to be given MS Access ID’s and Passwords. This is important. If Users are given MS Access ID’s and Passwords, they may use this to circumvent the application and open the backend directly.
Also, because of the availability of third-party tools and services which can read the account names and passwords, steps must be taken to prevent this method from being able to open the backend illicitly.
It is not the purpose of this document to define Oracle standards.
Oracle security must be discussed with the Oracle Database Administrator.
It is not the purpose of this document to define MS SQL Server standards.
MS SQL Server security must be discussed with the MS SQL Server Database Administrator.
Database, Import and Export data should only be visible to authorised personnel.
Transaction Processing should be considered where data integrity is important.
A Backup strategy must be defined. This must consider dates of data processes and data availability.
Successful data restoration must not be assumed.
Partial restoration, rather than complete database restoration, must be considered. EG Can the data of just one User or Department be restored without deleting the recent data of other Users or Departments?
Backup and Restoration processes must be tested.
June, 2005