Securing Your Database (1 Viewer)

I want to secure my database so no one can mess with it or see anything other then what i give them. I have tables imported from a sql server and want to be able to make it so they wont be able to see those, whats the best way to do this?

I could probably write a book on this subject lol.

The easiest way that I find is to do several things:

1 - Create a user access table with usernames, rights levels and a checkbox option for access (uncheck it and they can't open the db).

2 - Create a custom menu bar. Disable all other menu bars and shortcut bars, depending on the level of control you want.

3 - Utilize a front end and a back end. Back end will be your data tables needed for this - user access table, whatever other tables you need, etc. Front end will link to tables in the back end and will house all of your forms, queries, reports, etc. as well as code. There should be NO CODE or anything other than tables in your back end. Possibly a bit of code to prevent the back end from being opened by anyone other than yourself or others assigned to maintain it, but other than that, nothing. The back end should also be password protected to prevent someone maybe accidentally opening it and doing something.

4 - For SQL - the best thing to do is to create the SQL connections in the VBA code only after your program has determined that a user is authorized to open it - you can also limit some people to only certain SQL tables, etc.

5 - Be sure to use MDE files for end user computers. If these are hacked apart they could get to objects - but not the SQL tables. They COULD get access to the VBA code but it is not simple at all unless they pay a company to do it that has already developed software to reconstruct VBA from an MDE.

6 - Using the database options you can prevent it from showing the database window which lists the forms, tables, etc. Also be sure to disable the hot keys like the shift bypass and alt-F11 keys in here.


So basically what your program will need to do is upon opening - check the username against the user access table. If they have no access - it quits the application. This only works if you are on a network so that nobody can just login with whatever username they want. If you aren't on an actual network with server (domain) then you may want to assign usernames/passwords and have them have to type that in and then authenticate.

If they have access - it can check what access level depending on what you want them to be able to do, and show/hide the different menus on your custom menu bar to only allow access to certain forms/reports/whatever.

Also at this point it would create the connections to the SQL tables if they have access.

This is a very simplistic overview but I feel this is one of the most secure ways to control access to Access databases.

Thank you for the reply.

Is there a sql server side solution i could put in place to where a user can only edit entries they have made (i log their user id in the record when they create it) into the database but have the ability to read everything? It sounds like i could do this with a query of some sort but im not totally sure on how to completely secure the data.

I'm not sure on per-record security on the SQL side. One thing with the SQL server is you can prevent/allow access from network logins/computers as you desire as well as whatever security you use in your database.

You could have a query which only pulls up the records which have their username and allow edits, and you can use another query for read only.

Ya thats pretty much what i have right now but it is all stored in Access, is there anyway to do this on the SQL Server.

This is probably the wrong forum for this kind of question.

Yeah I don't know if it's possible to do per-record security on SQL Server. I don't imagine it is. I believe all you can do is object-level security, and from there it is up to your database to connect it or not, and limit access by using queries or VB.

If you protect your front end - the users won't be able to directly access the database window or any objects - just what you allow them to open via menus or forms.

It's not 100% secure - nothing with Access is, but it's generally good enough providing that you have network security in place as well.