Access 2K Backend Security

[LarryDeLaruelle]

We created an A2K database and used the security wizard to set up a new .mdw file and implement security.

We created necessary groups, added users and assigned them to the appropriate group.

We then split the db into front/back ends. I thought that the new back end would inherit the same security implementation that was applied to the front end. Not so, I can open the back end with the default system.mdw.

I then tried to associate the back end with the security file created earlier; that option is not available. Also tried creating a blank database and associating that with the new security file; again, no luck.

Question is, how do I now secure the back end with the new security file?

Thanks.

Larry De Laruelle

 

[lameid]

1. Open Acces joined or opening the new workgroup file via the /wrkgrp command line switch.

2. Create a new database file.

3. Change the permissions of new tables (all objects if you are using the frontend) to the most common level making sure to remove all permissions for the admin user and users group.

4. Import all the objects you want.

5. Adjust security appropriately making sure not to give the Admin User or the Users Group any permissions

 

[LarryDeLaruelle]

Labeid:

Thanks for the response; that's about what I finally did.

I used the wrkgadm.exe to join the new security file, opened the back end database and then used the security wizard to apply that security file to the back end.

I'm not done testing yet since I got side tracked on another project. Hopefully, I'll be able to test and verify that that did the trick.

Larry De Laruelle

 

[Ed2020]

For future reference you can sometimes get away with taking a copy of the frontend, deleting all objects and importing your objects from the backend into the new MDB file. It's a bit of a cheat, and may not work all the time (depending on how you have your permissions set), but it's quick and easy.

Ed Metcalfe.

Please do not feed the trolls.....

 

[lameid]

Ed,

I have to completely disagree with you. It takes forever to delete objects in comparison to setting the initial permissions unless there are a huge number of group permissions to set and only a few objects.

 

[Ed2020]

I've never found deleting objects from a database takes forever. Just hold down the Shift key to avoid the "Do you want to delete..." confirmation message and I can empty even my largest systems in a couple of minutes at most...

Ed Metcalfe.

Please do not feed the trolls.....

 

[LarryDeLaruelle]

Thanks all.

I have a question though, why doesn't access automatically apply the same security file to the back end when a database is split? It would seem logical to me that you would, by default, want the same security applied to both parts.

Larry De Laruelle

 

[lameid]

I have never used a wizard to split a database myself, so I am not sure it does not work in any context.

I think both security and the splitting wizard are somewhat afterthoughts to the product, although security has been around for a long time. Often I think addins/wizards just use the default security information to create a file. Someone would have to both realize and spend the time coding to deal with the security. Beyond that I would have to speculate but it does not surprise me either.

 

[Ed2020]

I've never really felt that Access security works particularly well with a split database anyway. The fact that you have to put additional measures in place to prevent users opening the backend and accessing the database window (where they could cause untold damage) seems very silly to me.

Ed Metcalfe.

Please do not feed the trolls.....

 

[lameid]

I use Access security on Split databases without keeping users out of the backend. I just make sure they have appropriate table permissions in the backend. Anyways, you could make the same argument for using Access security altogether because I could still link to the backend and cause untold damage. It really is no different than the SQL server model in capabilities except of course that SQL Server security is at least difficult to break as opposed to easy. The other real problem with Access security is that everyone can delete the entire database file.

 

[Ed2020]

I use Access security on Split databases without keeping users out of the backend. I just make sure they have appropriate table permissions in the backend. Anyways, you could make the same argument for using Access security altogether because I could still link to the backend and cause untold damage.

Agreed, but in my opinion the only appropriate permissions on tables would be *none*. I prefer to not give users their user name and password details but give them a launcher application that passes the login credentials in the command line that starts Access and opens the database. Granted even this won't keep determined malicious people out.

The other real problem with Access security is that everyone can delete the entire database file.

Yep, that's a problem I've had more than once! The launcher helps with this scenario as well - they don't need to know where the files are stored. Again, I accept that somebody who really wanted to could still track them down.

Ed Metcalfe

Please do not feed the trolls.....

 

[lameid]

Agreed, but in my opinion the only appropriate permissions on tables would be *none*.

Annoying but easy enough...

No permissions on the table. Access the backend through a FE that uses RWOP (run with Owner's Permissions) queries. Disable the bypass startup key, and of course remove all database object permissions to both files except for open/run.

If you really wanted to, you could write code to populate unbound forms and code to update the data and specify a password in code. I don't think that is ever a great idea but functional even in Access.

I still think the SQL and Access security models are relatively equivalent with Access having the 2 HUGE liabilities I mentioned. One more thing comes to mind. SQL server has windows authentication which makes it much preffered to Access.

 

[Ed2020]

Alternatively you can set up a separate SYSTEM user with the necessary rights to the tables and do your processing in a workspace opened with the SYSTEM user. It won't work in bound forms, but I don't use bound forms so that's not an issue for me.

Access security is fine for preventing people from getting in and doing damage by mistake (providing you set things up properly) but anyone with access to the workgroup file can grab a copy and easily find a password recovery tool with Google. If somebody really wants to get into the back of an Access database they will.

Ed Metcalfe.

Please do not feed the trolls.....