Tearing my hair out - Security for end/back end database

Keith Nichols

Registered User.
Local time
Today, 14:02
Joined
Jan 27, 2006
Messages
431
Hi,

I have a split database. The front end with forms etc is distributed to users and the back end with tables sits on a shared drive.

I have been trying to set the security on this and quite frankly it's driving me mad. I managed to get the security set with 5 user groups and about 100 users - it all worked well on my PC but when I distributed it, the copies open with the Admin as user, no password required, resulting in no security whatsoever.

Further reading showed me that I had to change the owner of all objects from Admin to a new adminitrator, who I named Custodian1. This I did. I then created a new database while logged on as Custodian1. So, the new database has all objects owned by Custodian1 and the database itself is owned by Custodian1.

The book said I had to delete the Admin user but, after asking if I was sure that I wanted to delete the user, Access would not let me.

I went through the Workgroup security wizzard a number of times to try to reset things that way, all to no avail. I then did the whole ownership change thing with the back end database and relaunched the wizzard. Still unable to delete the Admin user.

My final attempt was to use the wizzard and create a new WIF rater than edit the existing file. And still, I cannot delete the Admin user and distributed copies have no security.

I don't know how this ties in, but the distributed copies do not seem to be linked to the WIF although they find the Back End database, in the same shared drive/directory with no problems. If I set a password on a distributed FE database copy, it then cannot be opened as the users are not defined.

I'm obviously missing something fundamental to the whole process. A further difficulty is that I have the live database FE/BE, working copies FE/BE, The security wizzard backups FE/BA, and all the directories have similar names. On top of that, I have my normal none database duties to distract me. I'm finding it very hard to keep track of where I am or what I am doing.

Any help most gratefully appreciated and received.

Regards,

Keith.
 
I find it easier to start out with one database that has everything in it [including the security]. Then I copy the db. Rename them so that one is the front end, one is the back end. The security will still be in both since they are exact copies. Then remove the shared tables from the front end, remove everything else from the back end [except the tables that will be shared]. Then relink the shared tables from the front end to the back end.

Using custom shortcuts will make it easier for the users to open the front end with the correct workgroup. You can customize the shortcuts target field with all the required file and workgroup info.
 
Do the users have the mdw file in their shortcut?

Place the security file in the same folder as the BE (for accessibility reasons) and have them use a shortcut with something like :

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "D:\Data\Access\Helpdesk Benelux\Helpdesk Benelux.mdb" /WRKGRP "D:\Data\Access\Helpdesk Benelux\Secured.mdw"
 
ghudson said:
I find it easier to start out with one database that has everything in it [including the security]. Then I copy the db. Rename them so that one is the front end, one is the back end. The security will still be in both since they are exact copies. Then remove the shared tables from the front end, remove everything else from the back end [except the tables that will be shared]. Then relink the shared tables from the front end to the back end.

Using custom shortcuts will make it easier for the users to open the front end with the correct workgroup. You can customize the shortcuts target field with all the required file and workgroup info.

Ghudson,

I think my problems stem from the front/back split so your suggestion of recombining and then setting the security makes sense. Would you recomend re-creating the security (WIF, User Groups, Users, etc) or is it likely to work re-using the one already made. I only ask I as I have laboriously keyed in around 130 users and would rather avoid the re-work.

The second suggestion to use a custom shortcut has me a little confused. Do you mean I should ask all users to place the distributed Front End in identical directories (C:\Programme Files\MyDatabase for instance) and then create a shortcut to that for distribution?

Also, the books I have read to date (Microsoft 2003 Inside Track & Microsoft Access 2003 Inside Out, this second book only used as reference so far) tell me that I must put the WIF on a shared drive or users won't have access to it. I currently keep it in a subdirectory of the Back End database. Is there some way of customising the short cut to the Front End database that will instruct it to look for the WIF where the Back End is kept?

Light bulb moment - should my WIF be in the same directory as the BE database? Maybe that is my problem?

Kind regards,

Keith.
 
MartijnR said:
Do the users have the mdw file in their shortcut?

Place the security file in the same folder as the BE (for accessibility reasons) and have them use a shortcut with something like :

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "D:\Data\Access\Helpdesk Benelux\Helpdesk Benelux.mdb" /WRKGRP "D:\Data\Access\Helpdesk Benelux\Secured.mdw"

Marti,

If I have got this right, the Windows created shortcut should have the path etc to open the Front End. I go into that and add [ /WRKGRP "full filepath\Secured.mdw" ]?

To use this, I guess I have to distribute the Front end with the shortcut and instruct users to save it to a particular directory. If a user ignored this instruction, the shortcut wouldn't work and I can see that it is possible a user would find the front end file, double click and I'd be back where I started?

I may have just understood something. Is the shortcut you are talking about a shortcut inside the Front End database to the back end, rather than the Windows shortcut to the FE?

Regards,

Keith.
 
Locate and Read the Microsoft FAQ on Access Security

BEFORE you do what MartijnR suggests, you need to properly secure the database. If users can get into it without using your security file, you have *not* (yet) secured it properly - unless you deliberately granted permissions, or retained the default permissions set for the Users group and/or the Admin user.

MSAccess security is an oft mis-understood and improperly applied aspect of of the software. There is a FAQ available on the microsoft site. I suggest you locate it and read it at least twice before you start! There are also some links to it from this forum. I suggest you search this forum for "security". Pat Hartman's advice in this thread is invaluable - make at least two back up copies before you start - and follow the instructions in the FAQ to the letter.

HTH

regards,

John
 
john471 said:
BEFORE you do what MartijnR suggests, you need to properly secure the database. If users can get into it without using your security file, you have *not* (yet) secured it properly - unless you deliberately granted permissions, or retained the default permissions set for the Users group and/or the Admin user.

MSAccess security is an oft mis-understood and improperly applied aspect of of the software. There is a FAQ available on the microsoft site. I suggest you locate it and read it at least twice before you start! There are also some links to it from this forum. I suggest you search this forum for "security". Pat Hartman's advice in this thread is invaluable - make at least two back up copies before you start - and follow the instructions in the FAQ to the letter.

HTH

regards,

John

Hi John,

I am not surprised that this aspect of Access is oft missunderstood. To a newbie such as myself, it is bafflingly convoluted. I sank further into the mire today and am still no further forward, although I think my knowledge is deepenning!

I shall follow up the reading you recommend as I'm clearly not going to get this by hit and miss. It seems to be that it is either spot on or won't work at all.

One thing that is currently baffling me in regards to al this is the difference between securing a database and securing Access itsef. Once I have openned a database with a WIF, I get the log in dialogue for all other databases openned by that vrsion of Access on that PC. My ambition is to protect my database only and from whichever computer it is accessed from without causing confusion or problems for users when they look at other databases.

Regards,

Keith.
 
If your db is properly secured with a spefic workgroup file and user permissions nobody will be able to open your db unless they have used a custom shortcut to open your "secured" db with the correct shortcut.

You are making things harder on yourself by creating a different workgroup name for each user. 130 users should be easy to maintain if you only have a few user workgroups to maintain based on their groups needs. Each "group" of users should have a different workgroup name to log into the db with. The permissions for each group should be based on the users needs. You can identify the individual users [if needed] by using the Environ() function.

The front end and the workgroup file need to be installed onto the users hard drive. Each front end must be linked to the one back end on the server.
 
Keith,

I mostly work with AC97, and I suspect, as you state that you are reading books relating to AC2K3, you are probably using that version. So, with the caveat that things may be a little different between the two versions, and I am writing with an AC97 background...

Your workstation is joined to an access security (workgroup) file. This happens as part of a standard install, it is not an option. Typically (on WIN2K) this is C:\WINNT\SYSTEM32\SYSTEM.MDW

It has a standard account (username) called "Admin", and two Groups, named "Users" and "Admins". All user accounts are always (and irrevocably) members of the "Users" group. The Admin (User) account is also a member of the "Admins" group.

To properly secure a database you *MUST* create (from scratch, i.e. not copy and paste, or file-copy etc) a new workgroup Information File. In AC97 you do this using a separate exe called WRKGADM.EXE. (You also use this exe to change the security file to which you are "joined".) I believe the way you get to this functionality changed with later versions and it is now under one of the menu options in the main Access application. You *MUST* enter in your own data in the three fields (Name, Organisation, WorkgroupID). Write down the values you use. Take a screen shot. Keep it in a safe place; you will need to use EXACTLY (case sensitive too) the same values if the file ever becomes irreparably un-useable (or deleted along with all of your backups :eek: ) and you have to create it again from scratch. Also, anyone knowing these values can use them to create their own security file which would give them full rights to your "secured" database. Treat these values as you would treat an only set of keys.

When you launch any database Access will, unless instructed otherwise, refer to the security file to which you are "joined". By default it will also use the "Admin" account and no password. If this (default) attempt fails, it will ask you for a username and password. You deliberately cause this default attempt to fail by setting a password on the Admin account !!! That is how you "activate" the security.

To "instruct it otherwise" you can do as MartijnR showed, and nominate a (different) security file (/WRKGRP) in a shortcut or command/batch file.

Therefore, to not affect other databases, ensure your users remain "joined" to the default security file (on their own PCs), and then over-ride that default to point to your own security file in the shortcut you give them to access your database.

If users can change PCs, best if your security file lives on the network, in the same location as the Back End database, that way if they change their password it will apply to any PC they may log in from. If you give everyone a local copy of the security file, they will surely get confused by passwords not being in sync between PCs. In fact, I recommend putting the sole (except for backups, of course) copy of the security file in the same location as the back end data regardless of wether or not you think users may ever use a different PC. Makes it much easier to administer, and avoids certain possible user confusions. Some will suggest a local copy on each PC, but I would caution against it.

BTW reading this post is NOT a substitute for finding and reading the Microsoft FAQ !

HTH

John.
 
I've just seen that whilst I was typing up a reply ghudson has beaten me to it. My comments about some people recommending a local copy of the workgroup file were not specifically aimed at ghudson
, he just happened to to post whilst I was compiling my generic statement.

I was mostly trying to shed some light on what you said was your current baffle.

Also:-
Keith Nichols said:
The book said I had to delete the Admin user but, after asking if I was sure that I wanted to delete the user, Access would not let me.
You don't delete the Admin account (as you've found out, you simply can't). But you should remove it from the Admins group (after you've placed your own account - custodian1 in your case - into the Admins group).

If you created your WIF file properly in the first place (i.e. from scratch, and using your own values for the three fields) no, you don't have to create another one. Rather than have numerous workgroup files, I create as many groups as I need with the one file (like "Users" is one group, I may create another called "Supervisors" and another called "DataReaders" etc for as many varying roles as there are of users). Always, always, always, assign rights to groups and never, never, never to individual user accounts. To confer permissions on user accounts, you simply make them a member of the required group(s). Even if there is only one user who should have a set of rights, still create a group - when that user moves on it is easier (and better practice) to make someone else (a new person) a member of the group than re-assign a whole set of object rights to a new user account, or even worse, tell Fred that he should use Flossie's username and password, because she used to do his new job :eek:.

Anyway; enough from me for now.

HTH

Regards

John
 
ghudson said:
You are making things harder on yourself by creating a different workgroup name for each user. 130 users should be easy to maintain if you only have a few user workgroups to maintain based on their groups needs. Each "group" of users should have a different workgroup name to log into the db with. The permissions for each group should be based on the users needs.

Hi GHudson,

Maybe I mistated what I have done to date. I hve 5 user groups, Admins, 3 with permissions with around 2, 10 and 80 members respectively & users with read only permissions making up the balance. So, I created the groups, assigned the permissions and assigned the users to groups.

If I have understood you correctly, I like the idea of only using workgroups and not bothering with users. The databse does not contains sensitive information so I am only protecting it from being eroneously updated by those not in the right position to do so. If I were to pass out my 4 workgroups to the appropriate personnel, presumeably with 1 user assigned to each group, would multiple users be able to log on simultaneously? Would they all have to create passwords on their own PCs or would they all use the same password?

As it is, with 130 odd people, it is a bit of a grind, but doable, to create accounts for all of them and assign them to user groups, so this is possibly the way to go.

Kind regards,

Keith.
 
john471 said:
BTW reading this post is NOT a substitute for finding and reading the Microsoft FAQ !

Hi John,

I have tracked down the FAQ and it pulls together a lot of what has been said on this forum and in the books i have read. Having been messing about with this for days, I think I got a grasp of most of what I need from that. I suspect that I am a gnats privates away from success in this matter and will try again in the am.

One thing I gleaned was that Access uses the workgroup file that you joined in that session until you close the programme down. It then uses the whatever it is instructed to the next time it fires up.

So here is a significant question, for me at least: Do I have to place a shortcut to Access specifying the default workgroup onto every PC? Or will the existing link be ok?

My company has some sort of network version of Access 2003, though some users are still working with Access 2000 loaded on their PCs, and I don't know anything about this technically. The programme is started with a shortcut that points to the c:\ drive but it is grayed out and uneditable. The icon in the MSoffice directory of C does not seem to be the programme, but some network link and I cannot add comand line switches to it.

Regards,

Keith.
 
Were the questions about setting up the shortcuts ever complete answered in this thread, because I am still fighting my shortcuts. Currently, no matter if I set up the fe or be, it points to the Master file.
 
Well, they are answered, but seeing your post...

What do you mean by Master file?

To put it pretty straightforward (I think). If you can doubleclick the front-end and you get in the db, security is not set.

In my experience, give every user his/her own frontend (otherwise you'll have problems editting it later, since it most likely will be in use).

Put the mdw file in the same networkdirectory as the backend. No sense in giving everyone a seperate mdw file, you'll have problems later if you want to update it. Offcourse you can redistribute it also, but it seems a bit 'dirty' to me. Like the backend, everyone should use the same mdw file (less space, easier administration).

Assigning rights to users might seem like a good idea in the short run(less work). You WILL be swearing a lot for a very long time in the long run. ALWAYS use groups!!
 
MartijnR said:
Put the mdw file in the same networkdirectory as the backend. No sense in giving everyone a seperate mdw file, you'll have problems later if you want to update it. Offcourse you can redistribute it also, but it seems a bit 'dirty' to me. Like the backend, everyone should use the same mdw file (less space, easier administration).
You will not have to worry about the users locking up the record locking file [.mdw] file if each user has their own copy in the same directory as the front end [which should be installed on the users hard drive]. You only need to give the users the latest version of the front end and the workgroup file for when you have made design changes [updates] to the front end or the security.
 
MartijnR said:
Well, they are answered, but seeing your post...

What do you mean by Master file?

I think he is referring to the Workgorup Information File [mdw] for his project and the system default WIF as he is having the same problem getting the shortcuts to work as I am.

groovyjoker said:
Were the questions about setting up the shortcuts ever complete answered in this thread, because I am still fighting my shortcuts. Currently, no matter if I set up the fe or be, it points to the Master file.

No. :eek:

The longer answer is yes, my questions were answered but I am in the same position as you, I cannot get the shortcuts to find the .mdw. I know the shortcut addressing is ok as I have been able to open databases located in the same directories, both on the local pc and over the network.

For some reason that nobody has touched on here, neither the Access 2000 or 2003 users were able to automatically connect to the wif or be reconnected to the default wif using shortcuts. I have tried all the permutations offered (and a few others of my devising). That guru Ghudson has checked my shortcut text and found it to be ok.

I have tested the security itself, and I am happy that works as intended when I manualy connect to the wif.:)

The users do not want to use passwords for databases not related to mine, or indeed face restrictions that were not intended on the other databases. So, my work around has been to restrict the issue of the database to managers who have full data rights anyway and to clear the Admin password so that anyone can open it.

The only residual security is that the ownership of all items was transferred to a new user in the Admins group and the Admin user was removed from admins. Thus a user would have to know the name of the adminstrator to effect any changes to the DB structure. Pretty weak, but in this situation, with none-sensitive data and managers too busy to mess around with my DB, it does for now.:(

I cannot further release the db as users might be tempted to re-allocate themselves or colleagues to different projects without management knowledge or awareness - not good.:mad:

All in all, I am very grateful to those with the experience who took the time to get me as far as I got. Maybe I need one last nudge to get over the hill?


ghudson said:
You will not have to worry about the users locking up the record locking file [.mdw] file if each user has their own copy in the same directory as the front end [which should be installed on the users hard drive]. You only need to give the users the latest version of the front end and the workgroup file for when you have made design changes [updates] to the front end or the security..

I like this idea. When(if?) I get the shortcut problem resolved, I will certanly use this method.

Regards,

Keith.
 
MartijnR said:
Well, they are answered, but seeing your post...

What do you mean by Master file?

To put it pretty straightforward (I think). If you can doubleclick the front-end and you get in the db, security is not set.
I agree that security is not set. It is not getting "carried over" when I split the database.

The Master File is the file from which I am splitting. I have a copy that I split into a backend and frontend. I call it Master. I set my security before I split the database, and the mdw file is located with the Master, in its folder. So is the security (mdw) shortcut.

In my experience, give every user his/her own frontend (otherwise you'll have problems editting it later, since it most likely will be in use).

Everyone will have access to the front end on the shared network. This is not a database that will be in heavy use.

Put the mdw file in the same networkdirectory as the backend.

Yes, tried that. Security did not carry over to the backend.

ALWAYS use groups!!

I have assigned users to groups, and created a separate admin, besides the original admin.

Idea - there is a property called Location of Default Database. I am going to investigate this. When I made a copy of the Master database in order to create my backend, following the instructions to split in this post, did that location ever change to the location of the backend folder? I am going to change the location of the default db for the master to the location of the backend database. Then I will move it to the backend folder. Then I will move the mdw file, and the shortcut. I will see if the security carries over. If not, I will just run with no security.
 
Last edited:
If your "master" db is secured then the security will follow over to any copy you make of the db. I always start out with one db while designing. I adjust the security for the groups and user permissions before I split the db. Then I copy the db twice. I keep the original [just in case], I rename one of the copies to be the front end and I remove the tables that will be shared. I then rename the other copy to be the back end and I remove everything other than the tables that will be shared. Then I go back to the front end and relink the shared tables from the front end to the back end. Of course I have to have a custom shortcut with all the right values in the shortcuts Target: field to open the secured front end and the secured back end while and when I need to work on each. It really is a simple process if you pay attention to what you are doing.

But if your original "master" db file is not properly secured, none of the above will help you.
 
here's a great little program from Headway Systems for building secured database shortcuts, it's free, get it here: http://www.headwaysystems.com/ShortcutBuilder.htm

..although if you haven't implemented security properly, it won't really matter

Hey thanks, appreciate it. And no need to get nasty with a noob. Like I said earlier, there is not one IT guy in this agency who knows Access and I have taken only two classes to develop this database. I am really trying to understand, not jerk anyone off or around.

As for working to implement security properly, this is an issue that was not covered in either class, and it is not covered in the Access 2000 bible, so I am just doing what the wizard tells me to do, and MS Access Help. It seems to work great, and I am sure I am missing something, but whatever it is, it is NOT obvious. My user groups are set up, everyone has their indiviual PID and passwords, I have a Workgroup name and WID, I have included everything in the database in the WIF, I have a bak file that is located in the same location as my secured database, I have created a second Admin with Admin rights, my shortcut works....if its not copied and pasted elsewhere, that is.

Anyway, thanks for the help folks. I have been following ghudson's design to the gnat's rear, and I am just missing something.
 

Users who are viewing this thread

Back
Top Bottom