Backend Security (2003 (11.6566.8036) SP2)

Topic

New Reply

Hi there!

I have just split a DB into front and Backend and will be implementing User level security shortly (Haven’t done this before, but am in the process of reading through the various threads on this site.). In the interim, is there a way of protecting the Backend from being opened separately to the front end by users or unwanted people? Also, will User level security prevent this?

The Backend of course resides on our network and I don’t have any control over folder permissions etc. I know I can set a password on the front end, but this doesn’t carry through to the Backend and if I set a password on the Backend then I get “Not a valid password .” displayed when trying to access tables etc.

I suspect implementing User level security will be the requirement.

Cheers,

Niven

Reply | Quote

Replies

You shouldn’t change folder permissions, even if you could. Access requires that all users have full permissions (read/write/create/delete) on the folder containing a database.

Don’t set a database password on the backend. User-level security is indeed the way to go.
User-level security will prevent unauthorized users from opening the backend.

You probably already found the following links, but I’ll include them for others reading this thread – they provide very useful information about user-level security:
WendellB‘s tutorial with a list of links: The Secrets of Security
Security paper by Jack MacDonald (PDF format)
Access Security FAQ (Word document for download from Microsoft)

Reply | Quote

Thought that would be the way to go!

Thanks again for your invaluable help.

Niven

Reply | Quote

I think I’m getting there with regards to creating a secure DB.

Having set up the front-end with passwords and permissions for Administrators and Users alike (I am finding the terminology of Admin and Admins confusing and almost ambiguous in their nature!), I have found with regard to the back-end, that I could still actually open it with no problem. To get round this I went through the same procedure as for the front-end, i.e. creating a new User with Admin privileges, removed Admin from Admins group and set passwords for both. This means that if someone tries to open the back-end now, they have to enter a password for either the Admin or New Administrator in order to get in.

I have two questions surrounding this:-

1) Does this sound right? (this is in a test phase at present, so haven’t gone for the big bang yet!).

2) Had I not have split the DB first, would applying the Security have been carried through to the back-end when splitting, thus reducing the effort, or would I still have had to perform the same actions as already stated?

Cheers,

Niven

Reply | Quote

1) Yes, you have to secure both the frontend and the backend for security to be effective.

2) As far as I know (I tend to set up databases split from the start), the wizard should create a secured frontend and backend from an already secured database.

Reply | Quote

Just when I thought it was safe to go back into the water…………………..

Front end works nicely by using the “/WRKGRP” switch from the desktop. If I try to open the front end by itself from a folder, then I get a permissions error. So far so good, looks like it

Reply | Quote

Rename your System.mdw to (for example) Secure.mdw. You may want to move it to a shared network folder, or give each user a copy.
Access will probably create a new unsecured System.mdw next time you start it, otherwise you can create a new one yourself.
Create a shortcut to the frontend with a target like this:

“C:Program FileMicrosoft OfficeOffice11MSAccess.exe” “C:AccessFrontend.mdb” /wrkgrp “H:SharedSecure.mdw”

If your backend is now unsecured, you haven’t applied security correctly. As described in the various articles on security I linked to, it is absolutely essential that you take away most or all permissions from the default Admin user, for if a user opens the database with an unsecured mdw, (s)he will log in as Admin.

Reply | Quote

I think I missed something when I ran the User-Level Security Wizard (i.e. I might not actually have run it!!!). Having re-run it on the back-end it is now behaving as I want it to. I.e. If I try and open it from a folder it now cries out with the permissions error and the only way to open it is from the desktop with the “/WRKGRP” switch, which in turn presents the Logon window. Is this the norm that when your front and back-end’s are already split, that you end up with two Secured MDW files (i.e. “DHA_PO_INV_Secure_Users.mdw” & “DHA_PO_INV_Secure_Users_BE.mdw”)?

The other DB’s are now unaffected by this and defaulting to the original System.Mdw file.

Another query is can you present the Logon window inside a form or load up say a splash screen, before the Logon window comes up?

Cheers again,

Niven

Reply | Quote

The Security Wizard creates a new .mdw for you, but you can use the same .mdw for both. The workkgroup security file defines users, groups and group memberships. The database itself stores the permissions for each user and group.

Reply | Quote

Yes! The same .mdw works for both. Very useful and of course much better to keep track of.

Many thanks for your time and patience here. It’s probably not as daunting as it seems, but it is a lot clearer now as to what steps need to be taken in order to get this to work in a logical manner.

Cheers,

Niven

Reply | Quote

The first time it is rather daunting, but when you’ve worked through the steps once or twice, it starts to fall into place.

Reply | Quote

Hans,

I’m not sure if I should have created a new post here, or replied to the following post:-

post 518,355

However, it does concern the next aspect of security and testing to see if a user has a null password, i.e. when that user’s password has been cleared and they are logging back into the system, they do not need a password to log on. Is there a way of testing for this when they have logged on and to force them to enter a new password before they can access the DB?

I have set up a general form to accept the Old, New and Confirmation of New passwords so as to allow them to change their passwords within the system (amongst the various built in checks is one to stop them from entering a Null password.), so I would like to force them into this form, if possible, at the startup stage.

Is this feasible?

Cheers,

Niven

Reply | Quote

Do you mean that you want to check what the current user’s password is when he/she opens the database? Virus writers would *love* that. For security reasons, you cannot retrieve the password from within Access. Again, you could use a small application as mentioned in post 608,930 to manage login and passwords.

Reply | Quote

Point taken re virus writers etc.

My concern is that if a user’s password is cleared from the Admin end, the user should have to enter a new password somewhere and not login without one. My thoughts have now led me towards re-setting their passwords, where they forgotten, from an Administrator form, which 1) Clears the Old Password as in the built in Access function and 2) Creates a temporary replacement which I or an Administrator tells them of and they can change when they’ve logged back in.

I would like to do this from all in one form, rather than using the built in Access menu and then going to a form to set the temporary password.

Am I able to do this in VB? Or is this another hackers dream come true? I’m just stuck on the clearance side now.

Cheers,

Niven

Reply | Quote

To clear or change a user’s password, you need to be logged in as a member of the Admins group. You can then use code from ACC2000: How to Change User Passwords Programmatically Using DAO.

Reply | Quote

Hans,

Many thanks for pointing me to that link, I actually had the line I wanted to clear the users password, but had mistyped it and thought it didn

Reply | Quote

I didn’t force my users to create a password if their password was null (I just warned them), but I did check for it. All you need to do in code is attempt to change the password from a null password to a null password (i.e. there is no net change, but you are still “going through the motions” of changing their password). If the code succeeds then they had (and still have) a null password and you need to prompt them for a new one. If the code fails then their password wasn’t (and still isn’t) null.

It’s rather fiddly these days for me to dig out my old code, but if that isn’t clear enough I’ll see if I can track it down for you.

Reply | Quote

Many thanks your reply.

I’ve actually sorted the code I was looking for, having first typed it in incorrectly! I will bear your method in mind if I have problems with passing over temporary passwords.

Cheers,

Niven

Reply | Quote

Just when I thought it was safe to go back in the water…………………….

…….I have a problem re 2003 and 2000. I thought the people I rolled out front ends to were all 2003 users, but it comes to pass that some of them are still 2000. Whilst the unsecured version did not have a problem with the individual users, the secured version is allowing the 2000 users to log on once, but when logging off and back on again, the Start Up form flashes up then disappears. Access is open, but with the limted functionality set up for this secured app. All you can do is close the App and then get an error window with a “Cancel” button thrown up saying:-

“MSACCESS.exe has generated errors and will be closed by Windows. You will need to restart the program.”

“An error log is being created.”

I’m a bit puzzled as to what to do next, as getting the users upgraded to 2003 may take some time.

Cheers,

Niven

Reply | Quote

I’d try to get some time on a machine with Office 2000 to test on. It could be a simple matter of re-compiling the code on that machine, or it could be more complicated, so you’ll have to experiment.

You may have to give the Access 2000 users their own frontend, or to give all users a frontend that has been tested on Access 2000.

Reply | Quote

Sorry for not coming back sooner, been off sick past two days.

However, I feel a twilight zone coming on…………….

All users had a problem with opening a form which allows them to perform Invoice updates. In the process of opening the form, the selected invoice record is copied to a temporary log table and if it is updated then the update and original record are copied out to a full log file with the user’s details etc. These tables are hidden and when I originally applied permissions to the users group (called Housing), these tables didn’t show up in the list. Consequently no permissions were assigned and so when they tried to open the update form, nothing happened. Having diagnosed and applied the permissions, the form now opens without any problems, enabling the users to carry out their tasks.

Now the interesting bits. Having done this, copied over the front ends and sat down on a 2000 PC, there is no longer a problem with logging in. I have tried all three 2000 users several times and everything is working fine. I can only assume that the permissions here had something to do with it, but am not sure how, as these tables are not accessed at startup. I took the permissions away from the tables in order to replicate the login problem, but lo and behold, things are still working ok! I am now puzzled as to whether that cured the original problem or whether something else came into play.

Also having thought the security was working fine and Dandy, one of the users ran the system up from their front end folder without having to log in, the default user being Admin and the workgroup file it’s using, being the default System.mdw. I thought I had locked this down both front and back end but must have missed something when applying the Security. I expected the front and back ends to be encrypted as it were with the security settings I had applied and could only be opened via the newly created Security.mdw file, accessed from the desktop by a wrkgrp switch. The only thing I did to the created workgroup file was to rename it as something that was in keeping with the DB. (Security.mdw is pretty non specific and if others are going to be created, I thought it would avoid confusion.).

I am now baffled again. Am I going to have to go through the Security setup process again?

Cheers,

Niven

Reply | Quote

Did you remember to strip the Admin user’s permissions and remove the Admin user from the Admins group? If not, then you’re wide open. You also have to be using the appropriate mdw when you apply the security in the first place.

Reply | Quote

Yep! Having set me up as a user and member of Admins, I created a password for Admin logged off then back on again as Admin, then removed Admin from the Admins group along with all it’s permissions.

I then logged out and back on again as myself and set a password. I also created a new Users group called Housing, for which I later set relevant permissions.

After this I ran the User-Level Security Wizard, created a new Workgroup Information file (the filename was greyed out, so I had to accept Security.mdw) and noted the Workgroup ID. I followed through the rest of the instructions which involved securing all database objects and creating new users and adding these to the Housing group.

Having created this Security.mdw file, which I requested had an Icon created on the desktop, I copied it to a network directory and renamed it “Security_DHA_PO_INV.mdw”. Finally, I modified the desktop icon to point to this new location and filename and rolled it out to the users.

I also ran the security wizard for the back end and effectively went through the same steps.

At this point the original System.mdw file has a username of me and a password set that means if I try and run any other DB, I get the login prompt come up. This is not ideal for those other DB’s so I cleared my password and added Admin back into the Admins group.

Before I did this, if I tried to run the Secured DB from a folder I got a permissions error come up. The users do not. When they run the DB up either from opening Access or from a folder, their System.mdw file kicks in and they login as Admin.

Any thoughts?

Cheers,

Niven

Reply | Quote

System.mdw is the default workgroup on any machine. You probably had names greyed out because you had already renamed your system.mdw to me.mdw. You may need to start over with an appropriately named workgroup file in the first place, leaving the System.mdw as the default for non-secured databases and making sure the security is properly applied.

The mdw just holds information on the users and groups and their passwords. The database itself knows how the permissions are to be applied for those users and groups. If your users can log in from an unsecured mdw and have full permissions, then you missed something in applying the permissions to the databases in the first place.

Afterthought: you did remember to change the owner of the databases from Admin didn’t you? If not, that’s your problem because you can’t lock the owner out.

Reply | Quote

Yep! Changed owner to me.

In the interim I have rerun the Security over the Front and the Backend going through all the usual steps and it has done the trick for both frontend and backend (I must have missed something first time round), all bar one instance. That instance is for the back end on one users PC. I have been round all users and tested both from Access and from the folders that I get a permissions message. ONE user is causing me a problem re the backend. I double click on the backend and it goes straight in, once again defaulting to Admin. I was on cloud nine until that happened. The only thing that comes to mind here is whether that user logged in (I have my suspicions!) whilst I ran the security and somehow he has escaped the locking down. What puzzles me now is this is the same file for everyone else and he does not have any Admin permissions etc.

Sorry, any thoughts on this?

Cheers,

Niven

Reply | Quote

Do you mean that each user is not using a shared backend? Otherwise, I don’t see how that is possible unless perhaps the user has a prior copy of the mdw.

Reply | Quote

Each user is sharing the same Backend.

I think I’ve sussed it now (how many times have I said that before!). Having re-checked the backend’s permissions, I looked at the Ownership of the objects etc again and it had all changed to “unknown”. At what point this happened I don’t know. Subsequently, I have created a new empty DB, as me being the owner and an Administrator, imported all the objects from the original back end, gone through the usual Admin and Users scenario and rerun the Security Wizard which has locked the backend down nicely. I’ve tested trying to open it up from a number of other P.C.’s from its respective folder and as all the users involved from their own and I am getting blocked by the permissions message each time. Also I’ve moved the backend.mdw file to a location inaccessible by anyone but me (not my C: Drive).

Here’s a big thank you to you and Hans for your patience. I’m putting together my own checklist/sequence of events so I don’t (hopefully) end up with these nightmares again.

Cheers,

Niven

Reply | Quote

I’m back again.

Having been off sick for the past two days, I’ve come back to everyone working fine which is very nice. But oh jolly dee, I can’t log on anymore at the front end. Apparently, as an Administrator, I no longer have any permissions to “use the ‘H:HassocDatabasesDHADHA PURCHASE ORDER and INVOICE System_be.mdb’ object” (which I assume to be the backend). I was then asked to “Have your system administrator or the person who created this object establish the appropriate permissions for you.” The additional administrator I created also gets the same message. Normal users go into the system with no problem and when I left on Monday night I had no problem getting on and behind the scenes as myself. I have no problem logging on from the desktop to the backend using my Administrator account.

Does anyone have any ideas as to:-

1) What might have happened here?

and

2) How do I go about gaining access to the front end again?

The additional Administrator isn’t a general user of the system and hadn’t actually been set up on or told he was an Administrator, so there would have been no fiddling around as it were.

I had previously added the User and Permissions Security features to my toolbar for convenience and although I wasn’t allowed into the system, I was allowed to access the User facility (permissions was greyed out). I added a user to the Admins group and then ran up the front end again and tried to log in as that user, but got the same permissions message again.

Is it possible the the Admin permissions have been lost? Or is it something to do with the Frontend linking with the Backend?

Cheers,

Niven

Reply | Quote

Are you opening the database using a shortcut that specifies the correct secured workgroup information file (.mdw)?

Did you transfer ownership of all database objects to a new user in the Admins group before taking away all permissions from the default Admin account?

Reply | Quote

Right!

Since posting, I’ve walked away for a while, come back, sat down and from my login and out of desperation added another User to the Admins group. Having done this I was then able to Log on successfully as that user. My next step was to check the permissions and ownership of the DB from that user and confirmed that I own the DB as well as having full permissions to everything.

The only thing I could think of to try next, was to add myself to the group I had created for all the users, called Housing (which has minimal permissions). I duly did this logged off and then back on again as myself and hey presto! I can get in again, as myself and with full Admin rights.

I don’t know if this throws any light on the problem???? But In setting up Security for both the Front and Back ends, I have ended up with two Security files, 1 for the Front and 1 for the Back and use these to open up Front and Back respectively, although I can use the Front to open up the Back as well (but not Vice Versa).

I will probably remove myself from the Housing group just to see if the problem re-occurs, but as things are working, I may just let it be!

Cheers,

Niven

Reply | Quote

If possible, it would be best to use one mdw file for both front end and back end. But if it works now, you might stay with the current setup.

Reply | Quote

Wasn’t sure whether to post this here or set up a new thread, but basically it’s a closing query (I hope!) re Front end and Back end Security.

It’s not so much a problem, but a request for some advice on where is the best place to put the user Front ends, i.e. their local C: Drives or on the Network.

Currently I have set the users up so they each have their own folder with their copy of the Front end on the Network (the user folders hang off the folder where the Back end is stored) and this for me is quite a convenient setup for copying over updated Front ends (I use a Dos batch file to perform the operation), rather than going round each workstation and running a similar batch file.

I’m not sure if this is a personal preference or a best practice scenario, therefore I would be most grateful for your thoughts.

Cheers,

Niven

Reply | Quote

The important thing is to give each user an individual copy of the frontend. I personally prefer your setup: frontend in a personal network folder. It makes maintenance easier, and allows users with a roaming profile to log in on different PCs.

Reply | Quote

Great stuff!

Nice to know I’m on the right track at last!

Cheers,

Niven

Reply | Quote

That’s a good point i had not considered.

Are there any performance issues running the frontend from the network rather than the user’s pc?

Reply | Quote

Since the frontend usually doesn’t contain data, only queries, forms, reports etc.., there shouldn’t be a significant difference in performance between a frontend on a local hard disk or in a network folder.

Reply | Quote

Agreed if you are running on a 100MBit LAN – but at 10MBit there is a perceptible improvement in response time for complex forms – lots of subforms especially – in having the front-end on the local hard drive. But maintenance can be a real pain – so you typically need a tool of some sort – we use our own custom tool called cbLauncher. It really gets to be an issue when you are using custom DLLs or ActiveX controls – they need to be registered, and running those from a network drive makes me nervous. Also, we deploy a separate copy of the .MDW file to each user – we had fairly serious corruption problems with a large number of users sharing one security file. FWIW.

Reply | Quote

I don’t think you can intercept the login prompt from within Access – the database is opened after you log in.

You could create a small application in (say) VB6 or VB.net that displays a custom login form, then starts Access with a command line as suggested for a shortcut, but with extra arguments /user and /pwd.

For example:

Shell """C:Program FileMicrosoft OfficeOffice11MSAccess.exe"" ""C:AccessFrontend.mdb"" /wrkgrp ""H:SharedSecure.mdw"" /user TheBoss /pwd TopSecret", vbMaximizedFocus

Reply | Quote

I thought this might be the case. I’ll certainly have a look at this sometime.

Thanks again,

Niven

Reply | Quote