Need to lock down Access front end (1 Viewer)

Basically, I'm taking over an Access 2007 application (FE with SQL Server BE), and there is a problem in that the previous dev for the app taught a number of people how to convert an .accdr into an .accdb, open the navigation pane with F11, and create queries with the query builder. That includes, unfortunately, action queries.

Locking down the SQL Server back end is not an option, as the network admin refuses to allow anyone the necessary tools to implement any of the security features in SQL Server.

Oh, and there are a couple buttons available to administrators to allow for the creation of new queries and reports as needed without going into the navigation pane. Removing those would cause...let's just say drama.

So, is there some way to completely lock down a runtime like this?

Someone in your organization should be appointed DBA responsibilities, i.e. the person(s) to be responsible for maintenance, backup, security and general availability of the database server. It's not necessarily a full time job in itself but it ought to be clear who takes on that responsibility.

The DBA should be the person to lock down the back-end database for you and help you design a secure application to access it. If that role doesn't exist then you are in a bad place as far as your data is concerned. Quite apart from this one application then who will be responsible for keeping your data secure, backed-up and available going forward?

Bear in mind that there is no such thing as a "default" level of access in SQL Server - someone must have explicitly granted end users the permissions to do the things you don't want them to do. Trying to paper over that by hiding behind one application isn't likely to work. Even Microsoft Excel lets you execute SQL in a database and therefore circumvents any controls you put in your application.

Unfortunately, there is not and will not be a DBA. The closest I get is the network admin, who is the only one with SQL Server. As far as I can tell, everyone has been given full rights to the back end db. (And when I looked into getting Microsoft's free SQL Management program, I was informed I would just have to manage the backend via forcing SQL statements through via Excel.)

I'm pretty sure the database maintenance will be officially my responsibility, along with building, modifying, and maintaining who-knows-how-many other custom apps.

Frothingslosh said:

I'm pretty sure the database maintenance will be officially my responsibility, along with building, modifying, and maintaining who-knows-how-many other custom apps.

Click to expand...

If security is your responsibility then the answer is pretty straightforward. You have control over who can do what in the database by using the SQL Server security model: CREATE USER, GRANT, REVOKE, etc and by using stored procedures to provide finer control over data access logic. There's plenty of information available in the SQL Server documentation, for example: http://msdn.microsoft.com/en-us/library/bb283235.aspx

ButtonMoon said:

If security is your responsibility then the answer is pretty straightforward. You have control over who can do what in the database by using the SQL Server security model: CREATE USER, GRANT, REVOKE, etc and by using stored procedures to provide finer control over data access logic. There's plenty of information available in the SQL Server documentation, for example: http://msdn.microsoft.com/en-us/library/bb283235.aspx

Click to expand...

So I'll be able to manage security on the back end without either SQL Server or the SQL Server Management tool? My understanding was that you needed one of those two in order to set permissions.

As to documentation, again, I can't get SQL Server from the network admin, and I'm pretty sure that will extend to the documentation.

Assuming you can't touch the BE, you can still secure an access FE by the usual steps:

Disable all special keys
Disable Shift-Bypass
Update all Forms with a KeyDown filter to ignore any keys you don't want pressed (ie. F11)
Hide Ribbon / Menus.

way2bord said:

Assuming you can't touch the BE, you can still secure an access FE by the usual steps:

Disable all special keys
Disable Shift-Bypass
Update all Forms with a KeyDown filter to ignore any keys you don't want pressed (ie. F11)
Hide Ribbon / Menus.

Click to expand...

Yeah, that's what I'll have to do and just keep my fingers crossed that the project managers don't get too creative.

Thanks!

Anyone with basic knowledge of MS Access can modify the behavior of a database by removing or altering any of its components: tables, queries, forms, reports, macros, or modules. The easiest and most secure way to prevent this from happening is to save the application as an .accde database; this type of files prevents any modification to its components, and can only be executed.

Continue here: http://www.msaccessdeveloper.pro/#!Securing-the-frontend/c6x8v/55b047890cf22effe2e365f8