How to security the backend mdb

[Ben]

Hi all,

I am building a front end using vstudio. But I plan on using the Access
as a backend mdb. The backend is nothing more than tables. I want to
be able to lock this mdb down because in production it will be sitting
out on the network and I don't want any one to open the tables and
modify the data in them. Is there a way to lock down the tables/queries
in this mdb file from unauthorized data tempering?

Thanks,

Ben

 

[David H]

These should get you started. If you're not too far along, you may want to
consider using SQLServer Express (its free BTW). I'm pretty much to the point
where from here on out, I'll be using it instead of an Access backend.

http://www.xs4all.nl/~sbpoley/tech/access-security.html
http://support.microsoft.com/default.aspx/kb/132143
http://blogs.techrepublic.com.com/10things/?p=552

 

[Ben]

Hi David,

I wanted to go the SQLServer Express route, too. But I don't have
access to install the SQLServer Express on the server, whereas I can
simply drop the mdb on the server. As a follow up question, how would
you it? Can you share some more info? Thanks so much for your help.

Ben

 

[Keith Wilby]

Hi all,

I am building a front end using vstudio. But I plan on using the Access
as a backend mdb. The backend is nothing more than tables. I want to be
able to lock this mdb down because in production it will be sitting out on
the network and I don't want any one to open the tables and modify the
data in them. Is there a way to lock down the tables/queries in this mdb
file from unauthorized data tempering?

There's a useful link on my web site but quite how user-level security sits
with a bespoke FE I don't know.

Keith.
www.keithwilby.co.uk

 

[David H]

" As a follow up question, how would you it? "

Can you restate that?

 

[Ben]

Hi David,

You mentioned that you will be using SQLServer Express instead of an
Access back end. The problem is that I don't have access to the network
server so that I can install on the SQLServer Express on it. I thought
you have to install the SQLServer Express on the server. It's not a
file based database like Access, in which I can just drop the mdb file
on the server without installing anything. How would you use the
SQLServer Express instead of Access as backend?

Thanks,

Ben

 

[David H]

Yes it does have to be installed an whichever machine is being used as the
server. Nicely, though, its free. Your IT department should be able to do it
on your behalf.

Access has the ability to connect to pretty much any database product to
retrieve and update records.

 

[Ben]

Thanks David, Dale and Keith ( in no particular order) for your
thoughtful suggestions and links.

Ben

 

[David H]

Have you thought about taking the admin.mdb file concept one step further by
connecting to it during startup to check the user id and then immediately
disconnecting? If the code resides in a .MDE, no one would ever know about
the file.

None of the methods previously mentioned or those I add below will deter a
determined hacker or someone that is serious about causing serious damage to
your data. But if you use a combination of them, you can deter most users
from getting direct access to the data.

In addition to the ideas mentioned in the third hyperlink below, I
occassionally do:

1. Change the file name extension from mdb or accdb to something less
obvious. You can still link to files with a file name extension that does
not match the standards, but this makes it more difficult for someone to find
your data.

2. Occassionally, I have created a "Admin.mdb" file which contains a single
table with the network userids of the individuals who have permission to open
my backend data files. I generally put this file in an obscure folder that
my "users" don't know about. Then, in the backend data file, I use the
startup() function mentioned in the techrepublic article to check to see
whether the userid of the person attempting to open the backend is in my
Users table in the Admin mdb file. If not, I close the application.

3. Another method I have used is store the UNC path of the backend database
as a property within the front end. I add a table which contains the names
of the tables to be linked to the application, and I relink these tables
every time the application loads, and drop them from the FE everytime the
application closes. This can be a bit annoying if you have a lot of tables,
but the benefit is that your users will have a more difficult time
identifying just where the backend data resides. Obviously, this will not
help if your users have access to the database window or navigation pane.

HTH
Dale

These should get you started. If you're not too far along, you may want to
consider using SQLServer Express (its free BTW). I'm pretty much to the point
where from here on out, I'll be using it instead of an Access backend.

http://www.xs4all.nl/~sbpoley/tech/access-security.html
http://support.microsoft.com/default.aspx/kb/132143
http://blogs.techrepublic.com.com/10things/?p=552

Hi all,

[quoted text clipped - 8 lines]

Ben

 

[David H]

On my last project, we had entertained the idea of reaching out to
ActiveDirectory to see if the current Windows user id belonged to a specific
group and then use that to grant access. In the end, we went with a method
that just gets the Windows user id and then provides the user with a list of
menu functions that they're authorized to. The Main Menu uses a list box with
menu functions instead of a typical switchboard.

None of the methods previously mentioned or those I add below will deter a
determined hacker or someone that is serious about causing serious damage to
your data. But if you use a combination of them, you can deter most users
from getting direct access to the data.

In addition to the ideas mentioned in the third hyperlink below, I
occassionally do:

1. Change the file name extension from mdb or accdb to something less
obvious. You can still link to files with a file name extension that does
not match the standards, but this makes it more difficult for someone to find
your data.

2. Occassionally, I have created a "Admin.mdb" file which contains a single
table with the network userids of the individuals who have permission to open
my backend data files. I generally put this file in an obscure folder that
my "users" don't know about. Then, in the backend data file, I use the
startup() function mentioned in the techrepublic article to check to see
whether the userid of the person attempting to open the backend is in my
Users table in the Admin mdb file. If not, I close the application.

3. Another method I have used is store the UNC path of the backend database
as a property within the front end. I add a table which contains the names
of the tables to be linked to the application, and I relink these tables
every time the application loads, and drop them from the FE everytime the
application closes. This can be a bit annoying if you have a lot of tables,
but the benefit is that your users will have a more difficult time
identifying just where the backend data resides. Obviously, this will not
help if your users have access to the database window or navigation pane.

HTH
Dale

These should get you started. If you're not too far along, you may want to
consider using SQLServer Express (its free BTW). I'm pretty much to the point
where from here on out, I'll be using it instead of an Access backend.

http://www.xs4all.nl/~sbpoley/tech/access-security.html
http://support.microsoft.com/default.aspx/kb/132143
http://blogs.techrepublic.com.com/10things/?p=552

Hi all,

[quoted text clipped - 8 lines]

Ben