I'd really appreciate any help anyone can give! I have a split database with security established. How can I limit one of my groups to not be able to open any tables directly in the front end (particularly the table that contains employee information), but still be able to select data from those tables in my data entry form which has numerous combo boxes? I've read the MS Access Security FAQ and am still confused about what are the minimum permissions needed in the back-end for the users group and my new group to allow me to continue to link all the tables to the front end, but prohibit the user from opening the tables in the front end. When I tried to limit the permissions in the backend in the users group and new group, the table links were destroyed for everyone but me. So I put all permissions back and decided to ask you experts. Thanks for your help!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Limit Table Access in Linked Tables
- Thread starter SueD
- Start date
thornmastr
Programmer
No one but the development staff should be able to modify your tables in any way. You don’t even have to let users directly view the tables. Make sure all your forms are attached to queries; never directly to the tables. That way you can control what the users can see and act upon. If most of your employees can see personnel type information, exclude classified information from the queries. Play with security on the query and form level not on the table level.
My suggestion, first, is to read that damned security white paper at least three times and take notes. Then, to protect yourself, make a copy of both you dbf and mdw file. Once having done that, play to your hearts content. If you really mess it up, no big deal. Just restore from your copies and try it again. At first it is a frustrating trial and error farce. After you’ve done it right two or three times it will become a natural part of the development cycle.
Robert Berman
My suggestion, first, is to read that damned security white paper at least three times and take notes. Then, to protect yourself, make a copy of both you dbf and mdw file. Once having done that, play to your hearts content. If you really mess it up, no big deal. Just restore from your copies and try it again. At first it is a frustrating trial and error farce. After you’ve done it right two or three times it will become a natural part of the development cycle.
Robert Berman
The table links weren't really destroyed, it's just that in a front end database, the user must have permission to use both the link in the front end and the table in the back end.
The links have their own set of permissions, which you can set separately from the back end permissions. However, I don't think that's going to help much in this case.
The real solution, I believe, is to not let the users see the Database Window, not let them get into Design View, not let them open the Immediate Window (Debug Window in Access 97), etc.
First, you'll need to build a switchboard form. The switchboard, which can be built with the Switchboard Manager or custom built, has command buttons that let the users open their forms and reports. These will be the only way they can interact with the front end application, so you'll maintain control over what they can do.
Then, optionally but just to be safe, modify your forms' and reports' Record Source properties, and list and combo box Row Source properties, to use only Owner Access queries. Owner Access is a setting in query properties (Run Permissions = Owner's) that specifies that the tables are accessed using the query owner's permissions, rather than the user's. The users will then not need any permissions of their own, so you can remove them.
If you have SQL statements in VBA code, you'll also have to make them Owner Access queries, by adding the WITH OWNERACCESS OPTION clause.
Next, you'll need to create a custom menu bar, toolbars, and shortcut menus that remove the Design View and other inappropriate commands. You'll need at least a custom main menu bar, and a custom shortcut menu bar. Using the Toolbars|Customize command, you can build these toolbars and menu bars by dragging the built-in commands you need to your new command bar. (If you need to modify a command's properties, hold down Shift to get a copy as you drag, because otherwise your changes will also apply to the built-in menu items and buttons.) The custom shortcut menu should be specified in the Shortcut Menu Bar property of each form. You may want a separate one for reports as well.
Once you've done all this and tested all the forms and queries, you're ready to cut off access to the design mode facilities. You do this in the Tools|Startup dialog. Specify your switchboard as the Display Form, your customized main menu as the Menu Bar, and clear the check box for Display Database Window. Leave the other options alone for now.
Now close the database and open it again; your startup form should be displayed, and you can test the application the way the user would see it, with only appropriate menus and toolbars. Look for anything that might let them escape into design mode. Especially, right click everywhere to check the shortcut menus. (Because they're context dependent and normally hidden, it's easy to overlook them.) If you find something you need to fix, you can hit F11 to get to the Database Window, and hit Ctrl-F11 to toggle the main menu back to the Access standard one.
Once you think you've got all the design mode facilities locked away, go back into the Startup dialog and clear the remaining check boxes. From then on, when you open the database normally, there shouldn't be any way to get to the tables or queries directly, nor to bring up any design facilities. Do final testing to verify this. At this point, if you find something to fix, you have to close the database and reopen it while holding down Shift, which causes the Startup properties to be ignored.
When you're ready to distribute or implement, log on to Access as the user you want to be the owner, make a copy of the database, and set its AllowBypassKey property to False (see the Help topic). Only the copy you distribute to the users should have this property setting. The original database will be your design copy. From then on, any changes you need to make will be made in your design copy, and you'll build a new copy of the front end for distribution each time. If necessary, use the Tools|Security menu to change the owner of all the database objects.
This sounds like a lot of work, but it's really not that bad, and can be accomplished in a couple of hours--less after you've done it a time or two. Rick Sprague
The links have their own set of permissions, which you can set separately from the back end permissions. However, I don't think that's going to help much in this case.
The real solution, I believe, is to not let the users see the Database Window, not let them get into Design View, not let them open the Immediate Window (Debug Window in Access 97), etc.
First, you'll need to build a switchboard form. The switchboard, which can be built with the Switchboard Manager or custom built, has command buttons that let the users open their forms and reports. These will be the only way they can interact with the front end application, so you'll maintain control over what they can do.
Then, optionally but just to be safe, modify your forms' and reports' Record Source properties, and list and combo box Row Source properties, to use only Owner Access queries. Owner Access is a setting in query properties (Run Permissions = Owner's) that specifies that the tables are accessed using the query owner's permissions, rather than the user's. The users will then not need any permissions of their own, so you can remove them.
If you have SQL statements in VBA code, you'll also have to make them Owner Access queries, by adding the WITH OWNERACCESS OPTION clause.
Next, you'll need to create a custom menu bar, toolbars, and shortcut menus that remove the Design View and other inappropriate commands. You'll need at least a custom main menu bar, and a custom shortcut menu bar. Using the Toolbars|Customize command, you can build these toolbars and menu bars by dragging the built-in commands you need to your new command bar. (If you need to modify a command's properties, hold down Shift to get a copy as you drag, because otherwise your changes will also apply to the built-in menu items and buttons.) The custom shortcut menu should be specified in the Shortcut Menu Bar property of each form. You may want a separate one for reports as well.
Once you've done all this and tested all the forms and queries, you're ready to cut off access to the design mode facilities. You do this in the Tools|Startup dialog. Specify your switchboard as the Display Form, your customized main menu as the Menu Bar, and clear the check box for Display Database Window. Leave the other options alone for now.
Now close the database and open it again; your startup form should be displayed, and you can test the application the way the user would see it, with only appropriate menus and toolbars. Look for anything that might let them escape into design mode. Especially, right click everywhere to check the shortcut menus. (Because they're context dependent and normally hidden, it's easy to overlook them.) If you find something you need to fix, you can hit F11 to get to the Database Window, and hit Ctrl-F11 to toggle the main menu back to the Access standard one.
Once you think you've got all the design mode facilities locked away, go back into the Startup dialog and clear the remaining check boxes. From then on, when you open the database normally, there shouldn't be any way to get to the tables or queries directly, nor to bring up any design facilities. Do final testing to verify this. At this point, if you find something to fix, you have to close the database and reopen it while holding down Shift, which causes the Startup properties to be ignored.
When you're ready to distribute or implement, log on to Access as the user you want to be the owner, make a copy of the database, and set its AllowBypassKey property to False (see the Help topic). Only the copy you distribute to the users should have this property setting. The original database will be your design copy. From then on, any changes you need to make will be made in your design copy, and you'll build a new copy of the front end for distribution each time. If necessary, use the Tools|Security menu to change the owner of all the database objects.
This sounds like a lot of work, but it's really not that bad, and can be accomplished in a couple of hours--less after you've done it a time or two. Rick Sprague
- Thread starter
- #4
Thanks, Rick, for your detailed response. I have already created a switchboard, as the database has been in use for years, all the forms to which the special group must have access are run by queries which have WITH OWNERACCESS OPTION (RWOP), and I have already tested to be sure that the rest of the forms, reports, queries, and front end links to tables could not be accessed by the group (all the other groups have access to the rest of the application and data, only this group has limited access). I'll try creating new menu bars, toolbars, and new shortcut menus, and uncheck the display database window option.
The database has been in use for quite some time, and I copy my front end design version down to the server for the users to download to their local machines.
My problem came when I tried to change the permissions in the back end for the groups:
USERS group: no permissions on any tables (Read Design, Modify Design, Administer, Read Date, Update Data, Insert Data and Delete Data
ADMINS: all permissions (of course) on all tables
DATA ENTRY (and other basic groups I've established): Read Design, Read Data, Update Data, Insert Data Delete Data on most tables
QUALITY TECHNICIANS (THE SPECIAL GROUP): Read Design permission, Read Data, Update Data, Insert Data, Delete Data only on the tables needed to run the data entry form the techs are to access. This meant that, when I tried to open the front end with the Quality Tech login, links could not be established on the rest of the tables (about 30 tables).
What puzzled me was that all the rest of the groups (data Entry, Managers, HR, etc.) still had all their permissions for all the tables (all except Modify Design and Administer), but when I tried a login from one of the other groups, I couldn't get in with that user/password, either. That's when I put all the permissions back on Users.
According to the Security white paper Section 13 says: "The important thing is to restirct permissions as necessary in the back-end database (where the tables actually reside). this will prevent users from opening the back-end tables directly." Section 16 says: "the simplest thing to do is to grant users full permissions on tables in the front-end and restrict the back-end to the barest minimum needed if users need to relink tables." So, my question is, what is that barest minimum needed if users need to relink tables?
Thanks!
Sue Demick
The database has been in use for quite some time, and I copy my front end design version down to the server for the users to download to their local machines.
My problem came when I tried to change the permissions in the back end for the groups:
USERS group: no permissions on any tables (Read Design, Modify Design, Administer, Read Date, Update Data, Insert Data and Delete Data
ADMINS: all permissions (of course) on all tables
DATA ENTRY (and other basic groups I've established): Read Design, Read Data, Update Data, Insert Data Delete Data on most tables
QUALITY TECHNICIANS (THE SPECIAL GROUP): Read Design permission, Read Data, Update Data, Insert Data, Delete Data only on the tables needed to run the data entry form the techs are to access. This meant that, when I tried to open the front end with the Quality Tech login, links could not be established on the rest of the tables (about 30 tables).
What puzzled me was that all the rest of the groups (data Entry, Managers, HR, etc.) still had all their permissions for all the tables (all except Modify Design and Administer), but when I tried a login from one of the other groups, I couldn't get in with that user/password, either. That's when I put all the permissions back on Users.
According to the Security white paper Section 13 says: "The important thing is to restirct permissions as necessary in the back-end database (where the tables actually reside). this will prevent users from opening the back-end tables directly." Section 16 says: "the simplest thing to do is to grant users full permissions on tables in the front-end and restrict the back-end to the barest minimum needed if users need to relink tables." So, my question is, what is that barest minimum needed if users need to relink tables?
Thanks!
Sue Demick
I think that relinking would just require Read Design on the back end tables. It's the front end where they need Read Design and Modify Design to refresh the links. But that should be no problem, since you're leaving them all permissions on the front end, right?
You said that when you login as, for example, a member of the Data Entry group, you "couldn't get in". This phrasing makes me wonder if they lack Open/Run permission on the back end database itself, and you didn't mention permissions for the database. What message did you get, and at what point?
Also, as background information, is everybody using the same Workgroup Information File, in the network share with the back end database? Are you also conducting your tests using that workgroup, or a copy you made on your local computer? Rick Sprague
You said that when you login as, for example, a member of the Data Entry group, you "couldn't get in". This phrasing makes me wonder if they lack Open/Run permission on the back end database itself, and you didn't mention permissions for the database. What message did you get, and at what point?
Also, as background information, is everybody using the same Workgroup Information File, in the network share with the back end database? Are you also conducting your tests using that workgroup, or a copy you made on your local computer? Rick Sprague
- Thread starter
- #6
All groups, including Users, have Open/Run permission on the Database in the back end, and I still have the problem with all other users.
This is the error message I got occurred just as I opened the front end: “In the next File Open dialog screen identify the directory and the specific data MDB file you wish to attach to.” When I get to the next screen, I can go out and “manually” link to the back end,” I get Error # 3033 for each table in the back end: “You do not have the necessary permissions to use the [back end table name] object. Have your system administrator or the person who created this object establish the appropriate permissions for you. A problem was encountered whey trying to connect to the table [back end table name].”
(Since I’m (currently) the database administrator and also the owner of all the objects, this is a problem….).
After manually selecting the correct back end, and Oking through all the 3033 error messages, out of curiosity, I tried to open the designated data entry form, and I got the following error message: “Record(s) cannot be read, no read permission on [back end table name].” All the groups DID have Read Design, Read Data, etc., permission for that table, but I’m assuming that since I had removed ALL permissions from the User group in the back end, based on the “most restrictive rule,” the Quality Technicians group could not open the tables. That’s when I put all permissions back for the Users group so that people could work over the weekend.
Everyone is using the same workgroup Information File in the network share with the back end database, and I think I’m using the same workgroup to log in from my computer, but that is something I will explore. Thanks for the tip.
Since this database must be available 24/7, I am reluctant to just play with different combinations of permissions in the back end until I hit the right combination, as people tend to become rather upset when they can’t get in due to my blundering about!
Thanks again, Sue
This is the error message I got occurred just as I opened the front end: “In the next File Open dialog screen identify the directory and the specific data MDB file you wish to attach to.” When I get to the next screen, I can go out and “manually” link to the back end,” I get Error # 3033 for each table in the back end: “You do not have the necessary permissions to use the [back end table name] object. Have your system administrator or the person who created this object establish the appropriate permissions for you. A problem was encountered whey trying to connect to the table [back end table name].”
(Since I’m (currently) the database administrator and also the owner of all the objects, this is a problem….).
After manually selecting the correct back end, and Oking through all the 3033 error messages, out of curiosity, I tried to open the designated data entry form, and I got the following error message: “Record(s) cannot be read, no read permission on [back end table name].” All the groups DID have Read Design, Read Data, etc., permission for that table, but I’m assuming that since I had removed ALL permissions from the User group in the back end, based on the “most restrictive rule,” the Quality Technicians group could not open the tables. That’s when I put all permissions back for the Users group so that people could work over the weekend.
Everyone is using the same workgroup Information File in the network share with the back end database, and I think I’m using the same workgroup to log in from my computer, but that is something I will explore. Thanks for the tip.
Since this database must be available 24/7, I am reluctant to just play with different combinations of permissions in the back end until I hit the right combination, as people tend to become rather upset when they can’t get in due to my blundering about!
Thanks again, Sue
Sue,
The message "In the next File Open dialog screen..." isn't coming from Access, it's coming from application code that runs at startup. The 3033 errors that come later are probably the result of attempts by the code to verify that the tables are present, after which it will relink the tables. My guess is that this code is at fault, because it is using a technique that requires more permissions than the minimum necessary for relinking, and that when its attempts then fail it is giving you misleading results.
I'm getting the impression that you're not the original developer of this database. You didn't appear to know that the relinking was done with custom code. Is this correct? If so, what is your actual skill level with Access? Can you develop applications using VBA code? Are you comfortable with the DAO object model? Do you know how to open the front end in design mode, and use the Linked Table Manager? I think you'll have to modify the front end's startup code, and all of these skills will be required. If you don't have these skills yourself, is there somebody at your company who can get involved?
We'll be happy to help out, but unless there is only limited code involved, it may be impractical to try to resolve this problem through Tek-Tips.
If I'm mistaken, and you are the developer, look at the startup code and describe (or copy and paste) the logic from the point where the Open dialog is displayed to the point where the RefreshLink method is done. Something in that code probably requires Modify Design or Administrator permission on the back end table. Rick Sprague
The message "In the next File Open dialog screen..." isn't coming from Access, it's coming from application code that runs at startup. The 3033 errors that come later are probably the result of attempts by the code to verify that the tables are present, after which it will relink the tables. My guess is that this code is at fault, because it is using a technique that requires more permissions than the minimum necessary for relinking, and that when its attempts then fail it is giving you misleading results.
I'm getting the impression that you're not the original developer of this database. You didn't appear to know that the relinking was done with custom code. Is this correct? If so, what is your actual skill level with Access? Can you develop applications using VBA code? Are you comfortable with the DAO object model? Do you know how to open the front end in design mode, and use the Linked Table Manager? I think you'll have to modify the front end's startup code, and all of these skills will be required. If you don't have these skills yourself, is there somebody at your company who can get involved?
We'll be happy to help out, but unless there is only limited code involved, it may be impractical to try to resolve this problem through Tek-Tips.
If I'm mistaken, and you are the developer, look at the startup code and describe (or copy and paste) the logic from the point where the Open dialog is displayed to the point where the RefreshLink method is done. Something in that code probably requires Modify Design or Administrator permission on the back end table. Rick Sprague
- Thread starter
- #8
Rick,
You’re right! I’m not the original designer, I’m just a temp brought in to add new objects, security and training documentation. Nor did I did do the original split or create the workgroup file.
Up to this point, I’ve had no need to take a look at the code referenced in the Autoexec macro (written by the consultant), but I will certainly do so now. The funny thing is, as soon as the problem occurred, I contacted him to see if he had run into this problem before. He did not recognize the message, but that’s hardly surprising.
Thanks again for all your help!
Sue
You’re right! I’m not the original designer, I’m just a temp brought in to add new objects, security and training documentation. Nor did I did do the original split or create the workgroup file.
Up to this point, I’ve had no need to take a look at the code referenced in the Autoexec macro (written by the consultant), but I will certainly do so now. The funny thing is, as soon as the problem occurred, I contacted him to see if he had run into this problem before. He did not recognize the message, but that’s hardly surprising.
Thanks again for all your help!
Sue
- Status
Similar threads
- Locked
- Question
- Locked
- Question