Create links in ACCDB frontend to tables in secured MDB backend?
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?
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?
ASKER
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:
Or
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.
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.
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
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
ASKER
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
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
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.
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.
Just to add another voice: I've done exactly what you mention (the ACCDB/ACCDE with a secured MDB backend), and it worked fine.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
<<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.
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.
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