Solved

Create links in ACCDB frontend to tables in secured MDB backend?

Posted on 2014-11-07
10
537 Views
Last Modified: 2014-11-10
I have an MDB with tables secured by an MDW.  Management decrees that it cannot migrate to SQL Server any time soon.  In the meantime, I must build an Access frontend to provide a user interface.

I could build the frontend as an MDB, and then compile it to MDE.  I would rather build it as an ACCDB, and then compile it to ACCDE, in anticipation of swapping out the MDB backend, for a SQL Server backend, down the road.

To do that, however, I would need to create links in an ACCDB/ACCDE frontend to secured tables in an MDB backend.

I am able to open an ACCDB through a shortcut whose "/wrkgrp" switch points to the MDW that secures the MDB.  This forces a logon, through the MDW, to open the ACCDB.

If the user has Open/Read permission for the MDB, and Read Design permission for its tables, then they can link to them from the ACCDB.  They also can read, update, insert, and/or delete MDB data through ACCDB links, depending on how I set table permissions in the MDB.

Conversely, if I open the ACCDB without a shortcut through the MDW, then no logon occurs, and I cannot access MDB data through ACCDB links.

(Compiling the ACCDB results in an ACCDE that behaves identically in all of these respects.)

This seems to achieve my goal, but I cannot find documentation to support this approach.  Am I starting down a path with known dangers, or is this common and acceptable practice?
0
Comment
Question by:billpars
10 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
> is this common and acceptable practice?

It may not be common, but if you have tested it and it works reliably, I cannot see any reason to regard it as not acceptable. I would even say thanks for the tip!

/gustav
0
 

Author Comment

by:billpars
Comment Utility
Gustav,

Thanks for the reply.

That you have never heard of this approach is not reassuring.

A cursory proof of concept with Access 2010 appears to support it, but I am wary of going where no man has gone before.

My confidence would increase if a developer:

Had personally implemented this architecture, and could vouch for its reliability

Or

Could point to documentation that supports it

Will Access 2013 support it?  Do I lack the insight to spot an obvious trap that lies ahead?  Am I overlooking any unanticipated "gotchas"?

Perhaps other posters will weigh in.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Yes,  it's a valid approach and one I've used in the past although slightly differently.   I had a "development". MDW,  which allowed full access.   I'd then hand the db out without this MDW.

The default MDW would be used, which gave no table permissions.  All data access was done via query and "run with owner permissions".  This allows them to access the data in the app,  but since they were not the owner of the tables,  they could do nothing with them outside of the app.

This approach was documented in the security FAQ many years ago.  Still have a copy that I can post tomorrow.
Jim
0
 

Author Comment

by:billpars
Comment Utility
Jim,

Thanks for the timely review and response.

Just to be clear, beyond the twist you described from your own experience (i.e. distributing an ACCDB frontend and secured MDB backend without an MDW), you have knowledge of the specific architecture I describe (i.e. distributing an ACCDB frontend and secured MDB backend with an MDW) in the real world?

I would love to see your copy of the security FAQ that document this approach.  Please post it, if possible.  Are you aware of any other sources of documentation on this (e.g. Microsoft)?

Can you think of any reasons why the approach might fail in Access versions beyond 2010?

Thanks again,
Bill
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Bill,

  The ACCDB doesn't really add anything.  JET/ACE work group security is still in place with a ACCDB and "on".   When Microsoft says it "stripped out" security, all they did was remove the ACL (access control list) from the DB.  There is still a work group file being used (SYSTEM1.MDW) and it's the "default" one, the same as when you had a MDB.  That's why your setup works.  

The customized MDW you have now has the same default accounts and groups SYSTEM1.MDW does, so everything in the ACCDB works, but users are blocked in your MDB because you've changed the permissions on those objects.

Your not going to find direct documentation on this type of setup because it doesn't exist as work group security was officially "removed"  for ACE.  There's also the fact that work group security has not changed at all since day 1.  It still works exactly as it did then, even with ACE DB's

Jim.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 84
Comment Utility
Just to add another voice: I've done exactly what you mention (the ACCDB/ACCDE with a secured MDB backend), and it worked fine.
0
 

Author Comment

by:billpars
Comment Utility
Jim,

Thanks for indulging me.

I understand the ACCDB really does not buy me anything -- other than saving me the trouble of converting frontend source code, from MDB to ACCDB, when the backend migrates to SQL Server.

I also understand that nothing has changed, vis-a-vis the custom MDW securing the custom MDB, even though Access 2010 (instead of Access 2003) now manipulates them.

I am just not used to thinking of *unsecured* frontends, so I need to confirm the following:

1. Because the ACCDB includes table links to a secured MDB, the ACCDB will not open unless a user (with Open/Read permission for the MDB, and Read Design permission for its tables) is logged into the custom MDW.

2. Using my shortcut, the user is logging into the custom MDW, and *simultaneously* opening the ACCDB (i.e. they cannot do the one without doing the other).

3. When the user closes the ACCDB session, they automatically log out of the custom MDW (i.e. exactly as if the ACCDB were an MDB).

Do I correctly grasp all three of these?  If so, then I am good to go -- assuming this behavior is identical in Access 2013 (or its next iteration).

Thanks again,
Bill

EDIT: Scott, I just now saw your reply.  Thanks!  I appreciate the insight.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
Bill,

 Yes, that is pretty spot on except for a couple of points:
 
 For #1, it depends on what you mean by "open".

 From a technical standpoint, the ACCDB itself does open with or without the custom MDW and a user will have access to forms, reports, etc.   But unless they are using the custom MDW, they would not be able to access tables in the BE MDB.  Thus your app functionally might not "open" because your trying to read the BE tables.

 For #2, as noted above, the ACCDB would open even with a standard work group file, Whether or not they can do anything without having access to the BE tables is another story.

 For #3....don't think of it as a log in/out situation.  It's more a matter simply of which MDW is used when attempting to open the DB in question.  The actual log in to JET happens after that.

  By that, I mean you can set a default MDW in the registry (there are also user profiles, but no one has used them since A95 and I'm not even sure they would work anymore), which if you don't specify the command line switch is the one that gets used.   That registry entry can be switched.

 So I could set that to the custom MDW and drop the use of the command line switch.   That would work for everything *unless* you messed with the default accounts and groups in that custom MDW.  If you did, then access to all the ACCDB's would break.   That's what you do when you secure a DB.

Many people have done this in the past ("joined" a work group) that they customized and then break access to some DB's not realizing that if they don't use the command line switch, the work group they joined to is used for everything by default.

But overall, yes, your understanding the situation correctly and it's going to work like you expect.

Jim.
0
 

Author Closing Comment

by:billpars
Comment Utility
Jim,

Thanks for the clarification.

I was mistaken to presume the ACCDB would not open by simply double clicking it.  I assumed the custom permissions (Open/Read for the MDB, and Read Design for its tables) were required for the Navigation Pane even to *display* links to secured tables in the backend, so thanks for setting me straight.

It also was inarticulate for me to think of logging into (and out of) an MDW, vs. simply using it to open a particular MBD/MDE or ACCDB/ACCDE.

Thanks again for a great job of patiently explaining complex ideas.

Bill

PS: I will assume you have no reason to believe the Microsoft requirement for opening database files through MDWs will disappear in any future version of Access -- unless you say otherwise.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<PS: I will assume you have no reason to believe the Microsoft requirement for opening database files through MDWs will disappear in any future version of Access -- unless you say otherwise. >>

 Absolutely none.  Any changes on the desktop side are dead.  I don't believe JET or ACE will change one iota from this point forward.  The last changes made to ACE were done in 2007, and those were not all that significant (attachment data type and MVF's).  Everything new is being done on the web side.    

 Then consider that in regards to work group security and DB rights, it hasn't changed at all since version 1.0.

 I can't see any reason why they would change it now.

Jim.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Ransomware continues to be a growing problem for both personal and business users alike and Antivirus companies are still struggling to find a reliable way to protect you from this dangerous threat.
Find out what Office 365 Transport Rules are, how they work and their limitations managing Office 365 signatures.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now