How do you block users from accessing tables directly?

Question

First, I'm not a MS Access developer. However I've got a new job and have to do some MS Access development. I'll be working with another developer who has experience at this; at least more than I have.

One thing he showed me is that users will get into this MS Access application, which goes into the forms, do whatever it is they do there and then bang out of the forms application to get direct access to the tables of the database. (The Access application is a front end to a SQL Server 2005 database.) Since the end users have direct access to the SQL tables, well you can just imagine what sort of mischief they can get into. (The Access application was written by a contractor who left with the application unfinished.)

So my question is this: how can we prevent end users from getting out of the Access application to directly interact with the SQL tables? I would think this is possible, but like I said I'm not an Office developer so I've no idea how it would be done, nor even what sort of things I'd look for.

The Access application is written in MS Access 2007.

Answer 1

@rod Generally the level of security MS Access is providing is not very impressive. But it gives you some sort of security preventing novice users accessing unwanted information.

look for:

• Compiling the database to ACCDE, MDE
• provide custom ribbon with your own buttons
• Disable the "Navigation" pane : http://www.access-programmers.co.uk/forums/showthread.php?t=187697
• Disable the settings via right click.
• Use AutoExe function to check if NavigationPAne is deactivated, and reboot database if necessary
• Disable the "bypass startup option" key: http://www.access-programmers.co.uk/forums/showthread.php?t=91984
• prevent database to load if the DB is not in .accde or .mde format. Again this is within the AutoEXE function to check.

Some useful research/investment would be.

• Custom ribbon creator for MS access which will help you to provide your own buttons/ribbons (I used ribboncreator)
• Providing membership/user account. since you already have dedicated SQL server you can save user credentials in the back-end tables hiding from front-end. Check user has access by writing stored procedures/functions.
• Write function to gather errors, activities and uploads to a LOG table and monitor activities. use web-services + MSXML2.XMLHTTP + async for this task.}

Answer 2

Create a UI for the users to navigate your application. The most basic thing is just a form that has buttons to open all other forms the users need to access in your application.

Then use the ribbon to navigate to "File" -> "Options" -> "Current Database". Select the form from the step above as "Display form" for the application and then uncheck all the following options "Use Access Special Keys", "Display Navigation Pane", "Allow Full Menus", "Allow Default Shortcut Menus".

This will start you Access application and show the selected form without any of the standard UI for working with tables or the design of other objects in your application.

But please be aware that this is just protection against normal users making accidental mistakes by changing stuff they are not supposed to change. This will not deter a malicious and knowledgeable attacker.

If you want open your application for development, hold down the SHIFT-Key while opening the file.

Answer 3

Another option outside of Access is to deny an AD group with read/write access to your SQL tables the DELETE permission. I was able to do this successfully using Access 2013 and Microsoft SQL Server Management Studio 2012. This post discusses the command.

DENY DELETE ON tablename to [DOMAIN\groupname];

Users in the AD group are able to modify and delete data through the UI we have created, but unable to open the table, select a line, and hit the delete key. They receive the following error: "ODBC--delete on a linked table 'tablename' failed....The DELETE permission was denied on the object ...."