Using user-level security on a shared database?

[mark_87]

Hi,



I am going to put my access database on a network drive so that four different computers can have access it. However will user-level security work if I implement it? For example, from one of the computers I implement 2 different usernames and passwords. If one of the other computers tries to access the database will they also be presented with the logon screen?

If not, how can I go about setting this up? And please bare in mind I'm an amateur!


Thank you,

Mark

[Sullitec]

Hi Mark_87

There are a few points to consider when deploying Access databases across a network and depending on your network configuration a few different methods you can adopt.

The very first thing you need to be aware of is that User Level Security (ULS) was omitted from Access 2007 onwards as better more secure methods are available. Any database with an .accdb extension will not support the ULS that was available upto Access 2003 however if you have .mdb file the ULS will still work in 2007-2010 so long as you don't convert the file to the newer format.

The next thing to know is that if you are placing your database on a network for multiple users to access sometimes simultaneously its essential that you split the front and back ends of your database. (Back End = Tables) (Front End = Everything else i.e: forms, reports, queries, macros etc etc.)

Once this is done, the back end resides on your network server and a copy of the front end is stored locally on each machine you wish to have access to it. To use the database your users just open the front end.

The reason for this is multiple users cannot access an unsplit database at the same time because of record locking.

There a few ways to implement ULS in a Access 2007 / 2010 database, one being by coding it in, I have uploaded a template for you to pull apart and learn from.

The other is by using Security Groups via Active Directory to control your ULS. The latter is definitely better but it depends on whether you have a Domain Controller with Active Directory running your network. See the link for more information.

Any other questions about the template or Active Directory stuff, give me a shout. Hope it helps some.

Access Security Blog Article on USL in Access 2007/2010

[mark_87]

Many thanks for the reply!

I looked at the template and I was very impressed, however after about 2 hours of trying to implement it into my database I failed miserably!

Now I know ULS in earlier versions of Access isn't that secure, but would it work on a network drive, after the front end back end stuff? The main issue isn't actually trying to protect the file from hackers, just stopping people from accidentally editing the data when making use of the databse.

[mark_87]

I tried ULS and it seems pretty straightforward!

However one other query, what order would I do things in? Would I first implement ULS and then split it to front/back, putting the back on the network drive and the front on each computer I wanted to use the database? Or would I split it first, implement the ULS and then put the front/back in the relevant locations?

[Sullitec]

Hi Mark_87

I looked at the template and I was very impressed, however after about 2 hours of trying to implement it into my database I failed miserably!

Don't worry about it, its not the most simple method in the world if you're not comfortable with VBA. The template was purely for you to use as a illustration to see how it could be done. The code is commented so you can see what each bit does and try to work out the logic. Thanks for the compliment though

Now I know ULS in earlier versions of Access isn't that secure, but would it work on a network drive, after the front end back end stuff?

Yes it would still work, your database needs to be in 2003 type format (.mdb) and you need to set up ULS before you split the database. (Splitting it should always be the last the last step before deployment pretty much anyway)

So in this order -> Build Database -> Set up ULS -> Split Database-> Compile Front End(FE) & Back End(BE) as MDE -> Link Tables -> Deploy

Note: When splitting a database with ULS set up don't use the Split Database Wizard in Access because you may get some undesired results. Best way to do it is just to make a copy of your original database and in one copy delete all the tables and the other copy delete everything BUT the tables, then link the two together using the Linked Table Manager in Access.

Compiling as an MDE will protect your application from unwanted edits i.e users cant tinker around under the hood so to speak (editing form layouts, queries, code etc). Remember to always keep a backup of your original file(s) incase of any problems

putting the back on the network drive and the front on each computer I wanted to use the database?

Yes this is the right way to do it, don't use shortcuts otherwise everyone will still be accessing the same file. An actual copy of the Front End on each machine is required.

Hope this is useful for you, let me know how you get on

Sully

[mark_87]

Yes very useful thank you!

ONE LAST FINAL QUESTION! Can I split the database up and link them front/back right now now on my computer or is it required to be done from the computer I'm putting it on? And also if it does can I copy the front end from one onto the others or does the linking need to be done on each computer individually?

[Sullitec]

Can I split the database up and link them front/back right now now on my computer or is it required to be done from the computer I'm putting it on?

You can theoretically split it anytime you like, but I wouldn't do it until it is complete otherwise if you have make changes to the table structure you will have to edit not only the Back End but also all the dependent objects in the Front End (Forms/Queries/Reports etc.)

Best practice would be to split the database on your machine when it is complete. Move the Back End to the location it will be stored on the server. Then link the two so that the paths remain correct when you copy the front end for other users. If that makes sense.

You should be using a shared folder on your server mapped to the PC's that will require the front end using the same drive letter. So your linked table path might read

S:\Shared Folder\DataStore\dbase1_BE.mdb

So as long as each machine can access that folder on the S Drive the paths will only need setting once.

And also if it does can I copy the front end from one onto the others or does the linking need to be done on each computer individually?

If you follow the rule I just mentioned then you will only have to link it once. (When you split it). Then the front end linked tables will be configured correctly for every machine. Otherwise yes you will have to re-link the tables every time you put it on a new machine.

Hope all that was in english lol, let me know if it doesnt make sense to you

Cheers

Sully