User-Level Security In Access

"Permission Denied". Aaargh! Although frustrating when you are denied access to Access (no pun intended), it can be a godsend if there is someone else trying to access YOUR database and your data, if the security holds firm.

Security is a constant hot-topic in the world of databases, mostly because of increased laws, (the data protection act put paid to many illegal uses of personal data and took steps to correct it). There are also a few high-profile cases where data has been lost or stolen en mass. Whatever the reason - you don't want it to happen to you or your work. At best, you have to start it all over again, at worst - you can leave you (or your customers) open to fraud.

Access makes good use of user-level security. That is, each level of security is decided at user level, then group level, and so on. Take the example of a call centre that has a database of the customers to cold-call that day. The users (the workers) must have access to potential customer's names and phone numbers in order to do their job, but it may be wise not to give them the name and phone number of the senior management team, or the company sponsors, which should be reserved at a higher security level (let's say, the board of directors). Therefore each user will belong to a group, with different security levels. Within the group, further individuals can be granted bespoke rights, and that's how user-level security can be very effective.

Of course, not all Access user-level security functions are for sinister reasons, where data might be stolen or accessed maliciously. Often the security is in place to prevent accidents - if the intern in your office accidentally was given Access to read/write/edit your database up to the very highest level, and accidentally made a couple of wrong commands, the entire system could come to a screeching halt, leaving you (the more experienced user) to fix it. There is also data that is so sensitive, it shouldn't be viewed by accident (the database built for a doctor's surgery, listing a patient's medical record is a good example).

In practise, enforcing user-level security is relatively simple in Access. Information files are kept within workgroups, and each one requires a user password for access. Each user has a unique identifier, so the wrong person cannot get into the wrong workgroup. Access has two security levels - administrators ("Admins") group, and the "Users" group. Both are self explanatory - the users are usually read-only, the Admins can manipulate the Access database. There is a myriad of different, custom levels that can be created between the two or you can create your own groups.

It is not up to Access to decide who should have access to what data - it is up to you, (or your company), the owner of the data, to manage it within data protection laws. Enforcing the access is a lot easier, as Access has a built-in security wizard to help you with this task. Also, remember that there is a sandbox mode, which usually bypasses user-level security for the very reason it's just a sandbox and not the "real thing".

Permissions are the fine-tuning security elements you need to think about for each user. Back to our call centre example, although all workers should have customer telephone numbers, the team leaders on the call centre floor should have access to their team phone numbers so that they can be contacted if off sick, late, or for any other professional reason. The kind of data you have (and data tables) will dictate how you decide on different permissions for different users and workgroups. Another thing to consider is that the users might not have access to their actual permissions, which can also be protected.

The security in Access (from version 2003 onwards) is fairly robust, and unless you are so advanced that you can manipulate registry keys, then the user-level security settings are quite safe. Security is no small issue, and can create big mistakes - some of them costly and legal. Enforce your user-level security for every database you create, and you (and your data) can rest safely in the knowledge of protection.