Securing a MS database
Hi
We've got an Access database ( 2003 version) on windows XP that we send to an external contractor who then enters data into it (in their office) and then periodically e-mail's it back to us. We have designed the database and the contractor's only role is to add information to the database in the fields we have set. What we want to do is to restrict the database only to having them enter data and not to do any other changes e.g. modifying the tables or forms or adding macros etc.
I've looked at the Access help on this subject but find I'm really confused on all the terms and their way of describing how to secure a dbase. It seems the security is quite a complex issue . Is there a simple way to lock all everything in Access except allowing data entry into specified forms? If so how can we do this to ensure our database is safe from modification without our authorization.
Thanks
John
We've got an Access database ( 2003 version) on windows XP that we send to an external contractor who then enters data into it (in their office) and then periodically e-mail's it back to us. We have designed the database and the contractor's only role is to add information to the database in the fields we have set. What we want to do is to restrict the database only to having them enter data and not to do any other changes e.g. modifying the tables or forms or adding macros etc.
I've looked at the Access help on this subject but find I'm really confused on all the terms and their way of describing how to secure a dbase. It seems the security is quite a complex issue . Is there a simple way to lock all everything in Access except allowing data entry into specified forms? If so how can we do this to ensure our database is safe from modification without our authorization.
Thanks
John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
Hi John,
Why not just ask your contractor to enter data into MS-Word, or MS-Excel, and then either e-mail you that file, or export the entered data to a .CSV (Comma Separated Values) file that can then be imported into your (masted) MS-Access database?
BFN,
fp.
Why not just ask your contractor to enter data into MS-Word, or MS-Excel, and then either e-mail you that file, or export the entered data to a .CSV (Comma Separated Values) file that can then be imported into your (masted) MS-Access database?
BFN,
fp.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi thanks for your replies.
If we split the database as you're suggesting then I'm unclear on how we will be able to update our original database that resides on our computer with the updated information the contractor sends to us? The only way for the contractor to get the database to us is via e-mail - there will be no links between our database and their's by any other means other than e-mail exchange. When we receive their update we usually use the database they send us. How would a split database work in this arrangement?
I have been looking at the help files on Access but what they don't give is a simple and easy overview to security so that I can get a handle on what to do.
Cheers,
John
If we split the database as you're suggesting then I'm unclear on how we will be able to update our original database that resides on our computer with the updated information the contractor sends to us? The only way for the contractor to get the database to us is via e-mail - there will be no links between our database and their's by any other means other than e-mail exchange. When we receive their update we usually use the database they send us. How would a split database work in this arrangement?
I have been looking at the help files on Access but what they don't give is a simple and easy overview to security so that I can get a handle on what to do.
Cheers,
John
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The only way to be certain that he cant read the data is to do basically what Fylar suggested at the end:
1. Send him an empty database - it contains no records
2. Allow him to enter the data
3. When you recieve the data back, impotr it into your 'master' database.
1. Send him an empty database - it contains no records
2. Allow him to enter the data
3. When you recieve the data back, impotr it into your 'master' database.
"do basically what Fylar suggested at the end"
Or what I suggested in my first comment.
Or what I suggested in my first comment.
Or you could do both. ;)
I guess that I was trying to re-inforce the idea of security by limiting the amount of accessible data, by using the csv export / import process. Though it was my original intent (and was probably fanpages too), Neither of us really re-inforced the why of doing csv import/ export
I guess that I was trying to re-inforce the idea of security by limiting the amount of accessible data, by using the csv export / import process. Though it was my original intent (and was probably fanpages too), Neither of us really re-inforced the why of doing csv import/ export
ASKER
Hi
yes we do use imports at the moment. we import their updated tables into our database. The only problem with this is that we've got relationships set up between the tables. When we import the updated tables we have to break the relationships first between the tables before it will let us delete a table (or infact rename a table). Therefore we Import the new tables and then re-build the relationships again from scratch which is quite time consuming. If there is a way to import without having to build the relationships again then this sounds like it could be a good option. I'm also reading all the advice given above to understand the technology first so that I'll know what is the best solution for us.
thanks for you help. I've got to grips with understanding the options before I know which answers are the best for us.
Best regards,
John
yes we do use imports at the moment. we import their updated tables into our database. The only problem with this is that we've got relationships set up between the tables. When we import the updated tables we have to break the relationships first between the tables before it will let us delete a table (or infact rename a table). Therefore we Import the new tables and then re-build the relationships again from scratch which is quite time consuming. If there is a way to import without having to build the relationships again then this sounds like it could be a good option. I'm also reading all the advice given above to understand the technology first so that I'll know what is the best solution for us.
thanks for you help. I've got to grips with understanding the options before I know which answers are the best for us.
Best regards,
John
It is probably good advice to remove the relationships & leave them alone.
If your code is robust enough (e.g. using transactions & sufficient error handling) then you need not enforce relationships at all.
BFN,
fp.
If your code is robust enough (e.g. using transactions & sufficient error handling) then you need not enforce relationships at all.
BFN,
fp.
If you're keen... probably the easiest way for you to handle exports & imports, is to use the builtin macro functions.. take a look at transferspreadsheet and outputto
this is not really on topic for this answer, but if it is single tables that you are importing, you can do this without hurting your relationships.
just do a delete from your original table, then import the csv/xls file. (transferspreadsheet)
if you make it in a macro then you can automate the whole process.
Cheers,
Fylar
this is not really on topic for this answer, but if it is single tables that you are importing, you can do this without hurting your relationships.
just do a delete from your original table, then import the csv/xls file. (transferspreadsheet)
if you make it in a macro then you can automate the whole process.
Cheers,
Fylar
ASKER
Hi Fylar,
yes that would be interesting for us to look at, it's not quite off topic because it's associated with the problem. We probably need a way to easily import table data without breaking the relationships. The only problem we can't delete a table without first deleting its relationships.
Thanks,
John
yes that would be interesting for us to look at, it's not quite off topic because it's associated with the problem. We probably need a way to easily import table data without breaking the relationships. The only problem we can't delete a table without first deleting its relationships.
Thanks,
John
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<And, yes, ULS security can be cracked, but so can any other security model ... so while you'll hear a lot of naysayers on this site (and others) decry the viablity of ULS, it's still the most secure method you can use to protect your Access database. >>
Yeah and this is one of them<g> While it may be the "most secure" method, it's worthless in terms of protecting data.
When a quick google search can turn up litterally dozens of sites offering tools for $40 or less, you have to call it worthless. Especially given the complexity and the hassle of implementing and maintaining it.
JimD.
Yeah and this is one of them<g> While it may be the "most secure" method, it's worthless in terms of protecting data.
When a quick google search can turn up litterally dozens of sites offering tools for $40 or less, you have to call it worthless. Especially given the complexity and the hassle of implementing and maintaining it.
JimD.
So we're back to the question: What's the best way to secure your Access data? Assuming the poster must maintain their datastore in a Jet database, that is.
<<So we're back to the question: What's the best way to secure your Access data? >>
Short answer is you don't at least not with JET. If you truely need to secure your data, you use another db engine (ie. SQL Server) or another product.
JimD
Short answer is you don't at least not with JET. If you truely need to secure your data, you use another db engine (ie. SQL Server) or another product.
JimD
...or... don't send it to external contractors without first ensuring they sign a non-disclosure agreement. But that is a different kind of security! :)
Couldn't you just ask the contractor to visit your premises instead?
BFN,
fp.
Couldn't you just ask the contractor to visit your premises instead?
BFN,
fp.
ASKER
....I could ask them to visit our site but they are based in the philippines and I'm based in Europe :-) What we want is a good basic level of security - nothing too complex, using Access, which is a Dbase we are at least partly familiar with.
John
John
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Wow.. this topic has had a myriad of responses in the last couple of hours.
With regard to importing data, you should be able to delete from the table, without dropping the table, then import straight back in over the top of it.
Does it matter if the contractor can see the contents of the csv file coming back & forth also?
Well, I've got to run for now, but it looks like there'll be plenty more help coming.
LSMConsulting.. Re: encrypting everything in & out... probably the coolest response I've seen in this thread yet. ;) Would be a bit of a monster task tho.
Boltweb... Would you consider providing the contractor with a web front end to access your database... This is probably the most secure solution as they won't have local access to the database. Obviously this requires some form of network connectivity between the two, but if you've both got email access, it's pretty safe to assume that you'll both have internet.
This will then boil the security down to the strength of the security on you r web forms.
Cheers,
Fylar.
With regard to importing data, you should be able to delete from the table, without dropping the table, then import straight back in over the top of it.
Does it matter if the contractor can see the contents of the csv file coming back & forth also?
Well, I've got to run for now, but it looks like there'll be plenty more help coming.
LSMConsulting.. Re: encrypting everything in & out... probably the coolest response I've seen in this thread yet. ;) Would be a bit of a monster task tho.
Boltweb... Would you consider providing the contractor with a web front end to access your database... This is probably the most secure solution as they won't have local access to the database. Obviously this requires some form of network connectivity between the two, but if you've both got email access, it's pretty safe to assume that you'll both have internet.
This will then boil the security down to the strength of the security on you r web forms.
Cheers,
Fylar.
ASKER
Hi Fylar
if there was a way for us to simply provide access to our database via web forms this might be the ideal solution since we won't have synchronization issues and the back end of the database would be under our control. However I do not know how we can do this and give web face access?
John
if there was a way for us to simply provide access to our database via web forms this might be the ideal solution since we won't have synchronization issues and the back end of the database would be under our control. However I do not know how we can do this and give web face access?
John
boltweb:
Your contractor could not directly connect to the database on your network (you cannot connect to an .mdb file via an IP address), so you'd have to put your db on a webserver somewhere and build forms which would allow them access to it ... you could possibly convert your project to Data Access Pages (DAP), but this does limit your functionality somewhat. However, if you do move to a web-based solution, you'll be much better off moving your data store to a true client/server type database engine like MSSQL or MySQL ...
Your contractor could not directly connect to the database on your network (you cannot connect to an .mdb file via an IP address), so you'd have to put your db on a webserver somewhere and build forms which would allow them access to it ... you could possibly convert your project to Data Access Pages (DAP), but this does limit your functionality somewhat. However, if you do move to a web-based solution, you'll be much better off moving your data store to a true client/server type database engine like MSSQL or MySQL ...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
P.S I would compress the database in an password-protected RAR file when sending it through email, Access passwords are easily obtainable.
ASKER
Hi Everyone,
thanks for you all your helpfull and useful comments, Its going to take me a couple of day to read up on these suggestions to find out which is the best way to go. Thanks again for you replies. I'll be back soon.
regards,
John
thanks for you all your helpfull and useful comments, Its going to take me a couple of day to read up on these suggestions to find out which is the best way to go. Thanks again for you replies. I'll be back soon.
regards,
John
I'm going to take a punt and go completely off topic.
For a one stop shop that you can get this whole thing running... Take a look at Oracle Express Edition with Oracle Application Express
http://www.oracle.com/technology/software/products/database/xe/index.html
http://www.oracle.com/technology/software/htdocs/devlic.html?url=http://download.oracle.com/otn/java/appexpress/apex_2.2.zip
I've been using their earlier product (called HTML DB - which has a web interface which is very similar to the MS Access interface -> click & drag to join tables etc)
As far as setting up a web based application, it's very quick and easy. I'm sure that I'm opening the floodgates for every other alternative, and honestly, you can configure your MS access db to be accessed by a web application too. You'll need to set up your odbc connections to enable this, but you can surely access this using an apache webserver and php also. I suggested HTML DB as you can install those 2 products and deploy a web application almost immediately.
Sorry for going off topic.
Cheers.
Fylar
For a one stop shop that you can get this whole thing running... Take a look at Oracle Express Edition with Oracle Application Express
http://www.oracle.com/technology/software/products/database/xe/index.html
http://www.oracle.com/technology/software/htdocs/devlic.html?url=http://download.oracle.com/otn/java/appexpress/apex_2.2.zip
I've been using their earlier product (called HTML DB - which has a web interface which is very similar to the MS Access interface -> click & drag to join tables etc)
As far as setting up a web based application, it's very quick and easy. I'm sure that I'm opening the floodgates for every other alternative, and honestly, you can configure your MS access db to be accessed by a web application too. You'll need to set up your odbc connections to enable this, but you can surely access this using an apache webserver and php also. I suggested HTML DB as you can install those 2 products and deploy a web application almost immediately.
Sorry for going off topic.
Cheers.
Fylar
.... of you're gonna go off topic might I suggest SQL Server 2005 Express... at least it comes from the same vendor!!!
The floodgates have opened :)
The floodgates have opened :)
Yeah, I certainly could have suggested SQL Server Express, but as I don't know how easy the forms / application server side of things are to set up, with SQL Server Express, I recommended what I know.
:)
Anyway.. Bring on the flood.
John.. As the person asking the question, is this of use to you? Are you interested in alternate technology solutions, and do you have a preference for any particular vendor?
Cheers,
Fylar
:)
Anyway.. Bring on the flood.
John.. As the person asking the question, is this of use to you? Are you interested in alternate technology solutions, and do you have a preference for any particular vendor?
Cheers,
Fylar
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks everyone for your replies - there were so many replies that it took me while to award the points - hope I have managed to do it fairly based on the contributions that you made to the solutions
Thanks a lot!
John
Thanks a lot!
John
ASKER
can you explain what you mean by Split font-end/back-end design? What is an MDE file?
thanks,
John