|
security
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Security without signonI would like to rollout a basic application (1 form, 2 tables) to many users,
but I don't want the users to have to logon to the database with an ID and Password. However, I want to ensure they are only able to enter data in the form and nothing else. Of course, I would liike the administrator to have full access. What is the best way to do this? Thanks BPH wrote:
> I would like to rollout a basic application (1 form, 2 tables) to Normally when applying Access User Level Security a primary requirement is> many users, but I don't want the users to have to logon to the > database with an ID and Password. However, I want to ensure they are > only able to enter data in the form and nothing else. Of course, I > would liike the administrator to have full access. What is the best > way to do this? Thanks to remove all permissions and ownership from the group "Users" and the user "Admin". That is what forces users to use your mdw file and a login. You can however, give limited permissions to the Users group. Then any user (using any mdw file) can open your file, but they are still limited to the permissions that you granted to the Users group. There is no need in this setup to distribute an mdw file. Anyone who needs to log in with higher permissions though still needs to use your mdw file so they can log in as a user with higher authority. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Thank you Rick, That works except that when trying to limit users from being
able to link to the tables in my database, the users are not restricted. I have not split my database (FE/BE) and at this point would prefer not to. I have set up the form to run off a RWOP query, and I have removed all access to the tables from the Users Group. I have put the database on a network drive and have not put the security.mdw file with it, thus users are not prompted for a password. I would expect that any user linking to the database from another database would be stopped by not having sufficient rights inherited from the Users group (I have removed the Admin user from the Admins group). But users are able to link to the table with full rights. Any thoughts??? Show quoteHide quote "Rick Brandt" wrote: > BPH wrote: > > I would like to rollout a basic application (1 form, 2 tables) to > > many users, but I don't want the users to have to logon to the > > database with an ID and Password. However, I want to ensure they are > > only able to enter data in the form and nothing else. Of course, I > > would liike the administrator to have full access. What is the best > > way to do this? Thanks > > Normally when applying Access User Level Security a primary requirement is > to remove all permissions and ownership from the group "Users" and the user > "Admin". That is what forces users to use your mdw file and a login. > > You can however, give limited permissions to the Users group. Then any user > (using any mdw file) can open your file, but they are still limited to the > permissions that you granted to the Users group. There is no need in this > setup to distribute an mdw file. Anyone who needs to log in with higher > permissions though still needs to use your mdw file so they can log in as a > user with higher authority. > > -- > I don't check the Email account attached > to this message. Send instead to... > RBrandt at Hunter dot com > > > BPH wrote:
> Thank you Rick, That works except that when trying to limit users Does the default user "Admin" own the database ir any of the objects within? > from being able to link to the tables in my database, the users are > not restricted. I have not split my database (FE/BE) and at this > point would prefer not to. I have set up the form to run off a RWOP > query, and I have removed all access to the tables from the Users > Group. I have put the database on a network drive and have not put > the security.mdw file with it, thus users are not prompted for a > password. I would expect that any user linking to the database from > another database would be stopped by not having sufficient rights > inherited from the Users group (I have removed the Admin user from > the Admins group). But users are able to link to the table with full > rights. Any thoughts??? Owners have rights above and beyond those that are explicitly granted to them. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com OK, I have changed the owner of the database and the tables to a user (my
user id). As an added precaution, I created a new (non-default) admin group called SBAdmin and assigned full rights to the tables to this group and removed all table rights from the default Admins group. However, users are still able to link to the tables with full rights. Further, if they open the database (I temporarily allowed Shiftkeybypass for testing purposes) they have full rights to the tables as well. The user is using their default security.mdw file but seems to gain access to the tables via the Admins group (all rights are checked for this group, but not checked for the user Admin or for the group Users), despite the fact that I have explicitly removed table access from the Admins group. Is there something I have to do to ensure my access changes take effect? Thanks for your continued help. Show quoteHide quote "Rick Brandt" wrote: > BPH wrote: > > Thank you Rick, That works except that when trying to limit users > > from being able to link to the tables in my database, the users are > > not restricted. I have not split my database (FE/BE) and at this > > point would prefer not to. I have set up the form to run off a RWOP > > query, and I have removed all access to the tables from the Users > > Group. I have put the database on a network drive and have not put > > the security.mdw file with it, thus users are not prompted for a > > password. I would expect that any user linking to the database from > > another database would be stopped by not having sufficient rights > > inherited from the Users group (I have removed the Admin user from > > the Admins group). But users are able to link to the table with full > > rights. Any thoughts??? > > Does the default user "Admin" own the database ir any of the objects within? > Owners have rights above and beyond those that are explicitly granted to > them. > > -- > I don't check the Email account attached > to this message. Send instead to... > RBrandt at Hunter dot com > > > I am thinking that you didn't create a new workgroup file to secure it with.
That is still a requirement. Create a new workgroup file; secure it, but grant limited permissions to the Users Group. Distribute the mdb without your workgroup file. -- Show quoteHide quoteJoan Wild Microsoft Access MVP BPH wrote: > OK, I have changed the owner of the database and the tables to a user > (my user id). As an added precaution, I created a new (non-default) > admin group called SBAdmin and assigned full rights to the tables to > this group and removed all table rights from the default Admins group. > > However, users are still able to link to the tables with full rights. > Further, if they open the database (I temporarily allowed > Shiftkeybypass for testing purposes) they have full rights to the > tables as well. The user is using their default security.mdw file > but seems to gain access to the tables via the Admins group (all > rights are checked for this group, but not checked for the user Admin > or for the group Users), despite the fact that I have explicitly > removed table access from the Admins group. Is there something I > have to do to ensure my access changes take effect? > > Thanks for your continued help. > > "Rick Brandt" wrote: > >> BPH wrote: >>> Thank you Rick, That works except that when trying to limit users >>> from being able to link to the tables in my database, the users are >>> not restricted. I have not split my database (FE/BE) and at this >>> point would prefer not to. I have set up the form to run off a RWOP >>> query, and I have removed all access to the tables from the Users >>> Group. I have put the database on a network drive and have not put >>> the security.mdw file with it, thus users are not prompted for a >>> password. I would expect that any user linking to the database from >>> another database would be stopped by not having sufficient rights >>> inherited from the Users group (I have removed the Admin user from >>> the Admins group). But users are able to link to the table with >>> full rights. Any thoughts??? >> >> Does the default user "Admin" own the database ir any of the objects >> within? Owners have rights above and beyond those that are >> explicitly granted to them. >> >> -- >> I don't check the Email account attached >> to this message. Send instead to... >> RBrandt at Hunter dot com I think that your requirements are mutually exclusive. AFAIK, the
owner of the RWOP query must exist *in* the workgroup file that is in effect during the Access session. In other words, the users must log onto a special workgroup file that contains the query owner. But your main requirement is that they *not* be required to logon. I don't see how those two requirements can co-exist. But I may be wrong... If so, I am sure somebody will correct me. WRT their ability to link to the tables -- if they have read permissions on the tables, they *will* be able to link to them. Can't have it both ways... Split or no split is irrelevant for the security. However, split is highly recommended. On Tue, 29 Nov 2005 10:18:05 -0800, BPH <B**@discussions.microsoft.com> wrote: Show quoteHide quote >Thank you Rick, That works except that when trying to limit users from being **********************>able to link to the tables in my database, the users are not restricted. I >have not split my database (FE/BE) and at this point would prefer not to. I >have set up the form to run off a RWOP query, and I have removed all access >to the tables from the Users Group. I have put the database on a network >drive and have not put the security.mdw file with it, thus users are not >prompted for a password. I would expect that any user linking to the >database from another database would be stopped by not having sufficient >rights inherited from the Users group (I have removed the Admin user from the >Admins group). But users are able to link to the table with full rights. >Any thoughts??? > >"Rick Brandt" wrote: > >> BPH wrote: >> > I would like to rollout a basic application (1 form, 2 tables) to >> > many users, but I don't want the users to have to logon to the >> > database with an ID and Password. However, I want to ensure they are >> > only able to enter data in the form and nothing else. Of course, I >> > would liike the administrator to have full access. What is the best >> > way to do this? Thanks >> >> Normally when applying Access User Level Security a primary requirement is >> to remove all permissions and ownership from the group "Users" and the user >> "Admin". That is what forces users to use your mdw file and a login. >> >> You can however, give limited permissions to the Users group. Then any user >> (using any mdw file) can open your file, but they are still limited to the >> permissions that you granted to the Users group. There is no need in this >> setup to distribute an mdw file. Anyone who needs to log in with higher >> permissions though still needs to use your mdw file so they can log in as a >> user with higher authority. >> >> -- >> I don't check the Email account attached >> to this message. Send instead to... >> RBrandt at Hunter dot com >> >> >> jackmacMACdon***@telusTELUS.net remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security jacksonmacd wrote:
> AFAIK, the True.> owner of the RWOP query must exist *in* the workgroup file that is in > effect during the Access session. > In other words, the users must log That doesn't follow imho. He only has to log on if the Admin user has a> onto a special workgroup file that contains the query owner. password (as you know). If he used a shortcut with the /wrkgrp switch, but the Admin user of that workgroup file did not have a password, he'd be seeing the right user(s), but he would not have to log on. HTH, TC
Show quote
Hide quote
On 29 Nov 2005 20:11:57 -0800, "TC" <aatcbbtcc***@yahoo.com> wrote: You are correct. > >jacksonmacd wrote: > >> AFAIK, the >> owner of the RWOP query must exist *in* the workgroup file that is in >> effect during the Access session. > >True. > >> In other words, the users must log >> onto a special workgroup file that contains the query owner. > >That doesn't follow imho. He only has to log on if the Admin user has a >password (as you know). If he used a shortcut with the /wrkgrp switch, >but the Admin user of that workgroup file did not have a password, he'd >be seeing the right user(s), but he would not have to log on. Whether or not this is applicable to the OP depends on whether he is prepared to distribute the workgroup file plus shortcut. I was assuming that he wanted to use any arbitrary workgroup file without employing a shortcut. He would also be stymied if he wanted double-click access to the MDB file. > **********************>HTH, >TC jackmacMACdon***@telusTELUS.net remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security So if I understand both of you correctly, despite the fact that I have set up
a RWOP query, the fact that the user was not using the Security.mdw file where I established secuirty makes the RWOP setting mute as the user was not using this, rather the form worked because they had underlying access to the table. I understand this, and it seems then that my requirements conflict. However, I still don't understand why my security settings for the tables haven't taken effect for users logging onto the DB without access to my security.mdw file. Remember, I removed all access to the tables from everyone but members of SBAdmins group, but users can link to the table. Coincidentally, when I try to test the access by changing the name of the security.mdw so I effectively simulate a user, then I am restricted from linking the tables and it seems to work as I would like it (I use a default security.mdw file and am on as admin, not the owner - this is strange and frustrating). Even though once I understand this, it seems my design is still flawed because of the points above. Thanks Show quoteHide quote "TC" wrote: > > jacksonmacd wrote: > > > AFAIK, the > > owner of the RWOP query must exist *in* the workgroup file that is in > > effect during the Access session. > > True. > > > In other words, the users must log > > onto a special workgroup file that contains the query owner. > > That doesn't follow imho. He only has to log on if the Admin user has a > password (as you know). If he used a shortcut with the /wrkgrp switch, > but the Admin user of that workgroup file did not have a password, he'd > be seeing the right user(s), but he would not have to log on. > > HTH, > TC > > Sorry... I don't know. I went thru the same steps and *was* denied the
ability to link to the tables when logged on as an unpriviledged user. On Wed, 30 Nov 2005 06:08:02 -0800, BPH <B**@discussions.microsoft.com> wrote: Show quoteHide quote >So if I understand both of you correctly, despite the fact that I have set up **********************>a RWOP query, the fact that the user was not using the Security.mdw file >where I established secuirty makes the RWOP setting mute as the user was not >using this, rather the form worked because they had underlying access to the >table. I understand this, and it seems then that my requirements conflict. > >However, I still don't understand why my security settings for the tables >haven't taken effect for users logging onto the DB without access to my >security.mdw file. Remember, I removed all access to the tables from >everyone but members of SBAdmins group, but users can link to the table. >Coincidentally, when I try to test the access by changing the name of the >security.mdw so I effectively simulate a user, then I am restricted from >linking the tables and it seems to work as I would like it (I use a default >security.mdw file and am on as admin, not the owner - this is strange and >frustrating). > >Even though once I understand this, it seems my design is still flawed >because of the points above. > >Thanks >"TC" wrote: > >> >> jacksonmacd wrote: >> >> > AFAIK, the >> > owner of the RWOP query must exist *in* the workgroup file that is in >> > effect during the Access session. >> >> True. >> >> > In other words, the users must log >> > onto a special workgroup file that contains the query owner. >> >> That doesn't follow imho. He only has to log on if the Admin user has a >> password (as you know). If he used a shortcut with the /wrkgrp switch, >> but the Admin user of that workgroup file did not have a password, he'd >> be seeing the right user(s), but he would not have to log on. >> >> HTH, >> TC >> >> jackmacMACdon***@telusTELUS.net remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security jacksonmacd wrote:
> I think that your requirements are mutually exclusive. AFAIK, the That is not true. The production workgroup file does not need to have the > owner of the RWOP query must exist *in* the workgroup file that is in > effect during the Access session. In other words, the users must log > onto a special workgroup file that contains the query owner. owner in it for a RWOP query to work. -- Joan Wild Microsoft Access MVP Joan, that is good news for me, but then takes me back to my original
question. Do you know why the security I have applied to the tables in my database does not seem to restrict users from linking to the table when they access the database via their default security.mdw? Show quoteHide quote "Joan Wild" wrote: > jacksonmacd wrote: > > I think that your requirements are mutually exclusive. AFAIK, the > > owner of the RWOP query must exist *in* the workgroup file that is in > > effect during the Access session. In other words, the users must log > > onto a special workgroup file that contains the query owner. > > That is not true. The production workgroup file does not need to have the > owner in it for a RWOP query to work. > > -- > Joan Wild > Microsoft Access MVP > > > As I said in my other reply, your symptoms tell me that you either secured
it using system.mdw or a copy of it. There are steps at http://www.jmwild.com/SecureNoLogin.htm -- Show quoteHide quoteJoan Wild Microsoft Access MVP BPH wrote: > Joan, that is good news for me, but then takes me back to my original > question. Do you know why the security I have applied to the tables > in my database does not seem to restrict users from linking to the > table when they access the database via their default security.mdw? > > "Joan Wild" wrote: > >> jacksonmacd wrote: >>> I think that your requirements are mutually exclusive. AFAIK, the >>> owner of the RWOP query must exist *in* the workgroup file that is >>> in effect during the Access session. In other words, the users must >>> log onto a special workgroup file that contains the query owner. >> >> That is not true. The production workgroup file does not need to >> have the owner in it for a RWOP query to work. >> >> -- >> Joan Wild >> Microsoft Access MVP On Wed, 30 Nov 2005 13:13:08 -0500, "Joan Wild"
<jwild@nospamtyenet.com> wrote: >jacksonmacd wrote: I stand corrected.>> I think that your requirements are mutually exclusive. AFAIK, the >> owner of the RWOP query must exist *in* the workgroup file that is in >> effect during the Access session. In other words, the users must log >> onto a special workgroup file that contains the query owner. > >That is not true. The production workgroup file does not need to have the >owner in it for a RWOP query to work. ********************** jackmacMACdon***@telusTELUS.net remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security Joan Wild wrote:
> jacksonmacd wrote: Um, are you sure? An RWOP query is executed with the permissions of the> > I think that your requirements are mutually exclusive. AFAIK, the > > owner of the RWOP query must exist *in* the workgroup file that is in > > effect during the Access session. In other words, the users must log > > onto a special workgroup file that contains the query owner. > > That is not true. The production workgroup file does not need to have the > owner in it for a RWOP query to work. user who owns the query. If that user does not exist in the current workgroup file, those permisions will be "nil", AFAIK. If you can contradict this, I'd be interested in a small example db & mdw (preferably in A97 format) that I could take a look at with my various tools. Cheers, TC
Show quote
Hide quote
> Well TC, I am no MVP, but I can tell you what I am seeing. When the > Joan Wild wrote: > > jacksonmacd wrote: > > > I think that your requirements are mutually exclusive. AFAIK, the > > > owner of the RWOP query must exist *in* the workgroup file that is in > > > effect during the Access session. In other words, the users must log > > > onto a special workgroup file that contains the query owner. > > > > That is not true. The production workgroup file does not need to have the > > owner in it for a RWOP query to work. > > > Um, are you sure? An RWOP query is executed with the permissions of the > user who owns the query. If that user does not exist in the current > workgroup file, those permisions will be "nil", AFAIK. database is distributed without the security file, and a user opens the database, they gain full access to the "restricted" tables via the ADMINS group. Therefore, their permissions are not "nil" but rather FULL. Perhaps this is due to the fact that when I check the owner of all the objects as a user logged in with their default security.mdw file, the owner appears as "<unknown>"? Thus the tables are not secure despite the fact that I have secured them. At this point the RWOP is not the problem. Still looking for ideas on how to ensure the tables are secure. Thanks. Show quoteHide quote > If you can contradict this, I'd be interested in a small example db & > mdw (preferably in A97 format) that I could take a look at with my > various tools. > > Cheers, > TC > > Sorry, I really can't comment on your setup without following through
it all, detail. I just picked up on the comments regarding RWOP queries & the owners thereof. I'm sure the others will help you get it sorted :-) TCTC wrote:
>> I'm positive. I never ship the workgroup file that was used to secure a >> That is not true. The production workgroup file does not need to >> have the owner in it for a RWOP query to work. > > Um, are you sure? An RWOP query is executed with the permissions of > the user who owns the query. If that user does not exist in the > current workgroup file, those permisions will be "nil", AFAIK. database; always a production mdw. The development mdw contains the user that is owner, the production does not. -- Joan Wild Microsoft Access MVP TC
I think Joan is right on this one -- although it contradicts what I *thought* was correct. The attached A97 file has one secured table and one RWOP query. I can open the query when Access is running under my non-secured workgroup file, but I am denied permission to the underlying table. Contrary to the OP -- and what I would expect -- attempting to link to the table from a different MDB file does not succeed. On 1 Dec 2005 18:09:22 -0800, "TC" <aatcbbtcc***@yahoo.com> wrote: **********************>Could you post a small example mdb & mdw where I can get at them? > >TC jackmacMACdon***@telusTELUS.net remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security Jack, I can't see the attachment. I use google groups for most of my
usenet work, maybe they don't allow attachments? Could you email it to me please? My address is aatcbbtccctc <you know what comes here> Yahoo dot com. Thanks, TC TC
I think Joan is right on this one -- although it contradicts what I *thought* was correct. Tried to attach a file to this message, but it was rejected. Can you provide an email address or site for posting? In the demo A97 file, I can open the query when Access is running under my non-secured workgroup file, but I am denied permission to the underlying table. Contrary to the OP -- and what I would expect -- attempting to link to the table from a different MDB file does not succeed. On 1 Dec 2005 18:09:22 -0800, "TC" <aatcbbtcc***@yahoo.com> wrote: **********************>Could you post a small example mdb & mdw where I can get at them? > >TC jackmacMACdon***@telusTELUS.net remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security Sorry, I just saw this. I think our posts got out of synch!
Please send the files to aatcbbtccctc (you know what comes here) Yahoo dooooot com :-) TC If I felt better, I would take the time to do this. However I would think
with your knowledge of security, that you could just as easily test this yourself using Northwind. -- Show quoteHide quoteJoan Wild Microsoft Access MVP TC wrote: > Could you post a small example mdb & mdw where I can get at them? > > TC You know, when you think about it, it makes sense that the owner doesn't
have to be in the mdw. Permissions are stored in the mdb (and the rwop flag). The mdb just needs to know that it is RWOP, and what the owner permissions are, to run the query. -- Show quoteHide quoteJoan Wild Microsoft Access MVP TC wrote: > Could you post a small example mdb & mdw where I can get at them? > > TC Yes, you are right.
The database file does not need the workgroup file, in order to know: - that the query is an RWOP query; - what SID owns the RWOP query, and - what permissions that SID has, to other objects in that database. So, if an RWOP query is executed: "with the permissions of the user who owns that query", then, it would seem that the database file has all the information that it needs to do that, regardless of whether a user with that SID does or doesn't exist in the current workgroup file. With one exception! (luckily for me!!) When you are using a workgroup file in which the owner of the RWOP query /does/ exist, the query would also have the permissions of all of the groups to which that owner/user belonged. For example, the RWOP query might obtain some extra permissions from its owner being a member of the BlahBlah group. But in a workgroup file where the owner of the RWOP query does /not/ exist, those group-based permissions would not be added to the query. So it is quite possible that the RWOP query would work 100% correctly when its owner /does/ exist in the current workgroup, file, but not when that user does not. My error, as I see now, was in saying that an RWOP query will /never/ work when the owner of that query does not exist in the current workgroup file. I should hav said, "an RWOP query will probably BUT NOT NECESSARILY work corectly, when its owner is not a member of the current workgroup file". Gasp! TC TC wrote:
> I disagree. The query would only have permissions for the BlahBlah group, > With one exception! (luckily for me!!) > > When you are using a workgroup file in which the owner of the RWOP > query /does/ exist, the query would also have the permissions of all > of the groups to which that owner/user belonged. For example, the RWOP > query might obtain some extra permissions from its owner being a > member of the BlahBlah group. if that group were given permissions on the query. > So it is quite possible that the RWOP query would work 100% correctly Again I disagree. The RWOP query will work 100% whether the owner is in the > when its owner /does/ exist in the current workgroup, file, but not > when that user does not. mdw or not. Please describe a scenario where it wouldn't. -- Joan Wild Microsoft Access MVP Ok, clearly we won't resolve this by further theory-based discussion.
I'll do some actual experiements & reply in due course. But that will have to fit within the requirements of other work on my plate. Cheers, TC Sorry to post so long after the fact, but I just came upon this very
interesting discussion while researching my own related problem with RWOP queries. After seeing Joan state so unequivocally that RWOP queries will work even if the owner is not in the production mdw, I decided to investigate further why this was not my experience. Turns out that she is right, of course, but I'll share with you why it wasn't working for me. The owner of all of the objects in my front-end mdb, including the queries, was an individual "super user" (i.e., not a group). When I secured the back-end mdb, I gave table permissions to the administrator group (not Admins) that this user was a member of and gave no table permissions explicitly to the individual "super user", assuming that permissions would flow through to the "super user" by virtue of membership in the administrator group. Well, with one important 'Gotcha', it appears as if the permissions do flow through as you would normally expect, as the "Super User" was able to access the tables as per usual. However, anyone using the default System mdw would get error 3112 (no read permission) even though the default Admin user had appropriate permissions on the queries in the secured front-end. When I changed security in the back-end so that the "Super User" was explicitly granted permissions on the tables, then everything worked for users with the default System mdw just as Joan suggested it would. So the 'Gotcha' is that, with RWOP queries, permissions do not flow-through from a Group to a user. If an individual user is specified as the owner of the query, then for that query to work as a RWOP query, that individual owner must have explicit permissions on the back-end table, not permissions that are inherited by virtue of group membership. It will also work if the owner of the query is a group and the group has permissions on the back-end table. You can change the ownership of the query to a group if you want. The important point is that the names must match--the owner of the query must also have explicit permissions on the back-end table, whether that be a group or an individual user. Not my experience. I have never had to give the query owner explicit
permissions on the tables (shouldn't need to since the query owner is the owner of the tables as well). Your super user should not need to inherit any permissions from any group, as the owner trumps all. Your explanation makes sense to me, but only if Super User did not own the tables. Joan Zardoz wrote: Show quoteHide quote > Sorry to post so long after the fact, but I just came upon this very > interesting discussion while researching my own related problem with RWOP > queries. > > After seeing Joan state so unequivocally that RWOP queries will work even if > the owner is not in the production mdw, I decided to investigate further why > this was not my experience. Turns out that she is right, of course, but I'll > share with you why it wasn't working for me. > > The owner of all of the objects in my front-end mdb, including the queries, > was an individual "super user" (i.e., not a group). When I secured the > back-end mdb, I gave table permissions to the administrator group (not > Admins) that this user was a member of and gave no table permissions > explicitly to the individual "super user", assuming that permissions would > flow through to the "super user" by virtue of membership in the administrator > group. > > Well, with one important 'Gotcha', it appears as if the permissions do flow > through as you would normally expect, as the "Super User" was able to access > the tables as per usual. However, anyone using the default System mdw would > get error 3112 (no read permission) even though the default Admin user had > appropriate permissions on the queries in the secured front-end. When I > changed security in the back-end so that the "Super User" was explicitly > granted permissions on the tables, then everything worked for users with the > default System mdw just as Joan suggested it would. > > So the 'Gotcha' is that, with RWOP queries, permissions do not flow-through > from a Group to a user. If an individual user is specified as the owner of > the query, then for that query to work as a RWOP query, that individual owner > must have explicit permissions on the back-end table, not permissions that > are inherited by virtue of group membership. > > It will also work if the owner of the query is a group and the group has > permissions on the back-end table. You can change the ownership of the query > to a group if you want. The important point is that the names must > match--the owner of the query must also have explicit permissions on the > back-end table, whether that be a group or an individual user. Thanks for the response, Joan. You gave me more pause for thought, just when
I thought I had a handle on this. I double- and triple-checked, and the superuser is the owner of the tables in the backend database. I have retested this scenario several times, and my original findings hold, but I can add one more piece to the apparent puzzle. Recall that the superuser is the member of a 'supergroup', and the 'supergroup' has explicit permissions on the tables in the backend database but the superuser does not have any explicit permissions on the tables in the backend database. When I go in as the Admin user using the mdw that I used to secure the database, everything works fine. However, as noted above, when I go in as the Admin user using the default System.mdw, error 3112 is generated (no permissions on the table). If I give the superuser explicit permissions on the table, then this error goes away and everything works fine. Any suggestions on what else I can check? Thanks. Show quoteHide quote "Joan Wild" wrote: > Not my experience. I have never had to give the query owner explicit > permissions on the tables (shouldn't need to since the query owner is > the owner of the tables as well). > > Your super user should not need to inherit any permissions from any > group, as the owner trumps all. > > Your explanation makes sense to me, but only if Super User did not own > the tables. > > Joan > > Zardoz wrote: > > Sorry to post so long after the fact, but I just came upon this very > > interesting discussion while researching my own related problem with RWOP > > queries. > > > > After seeing Joan state so unequivocally that RWOP queries will work even if > > the owner is not in the production mdw, I decided to investigate further why > > this was not my experience. Turns out that she is right, of course, but I'll > > share with you why it wasn't working for me. > > > > The owner of all of the objects in my front-end mdb, including the queries, > > was an individual "super user" (i.e., not a group). When I secured the > > back-end mdb, I gave table permissions to the administrator group (not > > Admins) that this user was a member of and gave no table permissions > > explicitly to the individual "super user", assuming that permissions would > > flow through to the "super user" by virtue of membership in the administrator > > group. > > > > Well, with one important 'Gotcha', it appears as if the permissions do flow > > through as you would normally expect, as the "Super User" was able to access > > the tables as per usual. However, anyone using the default System mdw would > > get error 3112 (no read permission) even though the default Admin user had > > appropriate permissions on the queries in the secured front-end. When I > > changed security in the back-end so that the "Super User" was explicitly > > granted permissions on the tables, then everything worked for users with the > > default System mdw just as Joan suggested it would. > > > > So the 'Gotcha' is that, with RWOP queries, permissions do not flow-through > > from a Group to a user. If an individual user is specified as the owner of > > the query, then for that query to work as a RWOP query, that individual owner > > must have explicit permissions on the back-end table, not permissions that > > are inherited by virtue of group membership. > > > > It will also work if the owner of the query is a group and the group has > > permissions on the back-end table. You can change the ownership of the query > > to a group if you want. The important point is that the names must > > match--the owner of the query must also have explicit permissions on the > > back-end table, whether that be a group or an individual user. > =?Utf-8?B?WmFyZG96?= <Zar***@discussions.microsoft.com> wrote in
news:254F0D98-752F-43B4-9D11-D9076E432BF0@microsoft.com: What about the table links in the front end? Permissions on those> I double- and triple-checked, and the superuser is the owner of > the tables in the backend database. are completely independent of the back-end table permissions. Thanks for the suggestion, David. I rechecked the ownership of the front-end
table objects (which as you state are just links to the back-end) and the owner of these objects is my SuperUser, so I don't think that's it. But please keep the ideas coming. One thing I noticed is that, when a query is set as RWOP (in the RunPermissions property of the query), I cannot change the owner of the query, even when I am logged in as the owner of the query (i.e., as the SuperUser). I would get the message "You don't have permission to change the owner of <Name of query>. To change the owner of a database object, you must have Administer permission for it...." My SuperUser does have Administer permission for all queries. This had me wondering whether somehow my SuperUser was not actually being recognized as the owner of the query. But when I researched this, I found that this behaviour is actually by design, at least in MS Access 2000 (see http://support.microsoft.com/kb/208929) and earlier versions (see http://support.microsoft.com/kb/120885). I am using MS Access 2003, and I could not find a similar article for my version,, but presumably this behaviour continues to be by design. So my situation continues to be that, using RWOP queries, I can achieve what the title of this thread refers to as 'security without signon' only if the owner of the RWOP query is given explicit permissions on the back-end tables rather than inherited permissions by virtue of group membership. This is fine for my purposes, but given Joan's statements above, it sounds as if this should work with inherited permissions as well, in which case I would like to understand why this isn't working for me in case there is an underlying problem with my security implementation. Actually, the more I think about this, the more I am convinced that my findings make sense, which leads me to wonder whether my scenario is somehow different from the one implied by Joan. It is the different experiences of the default Admin user in my scenario that might be the key to understanding what is going on here. As we know, the default Admin user is the same across all mdw files. There are two mdw files at play in my scenario: the one that I created to secure my database (SecureMDW), and the default one that comes with Access (SystemMDW). SystemMDW contains the default Admin user account plus the two default group accounts (Admins, Users). SecureMDW contains these default accounts plus the account for my SuperUser and the account for my SuperGroup, and SuperUser is a member of SuperGroup. If I log in as the Admin user through SecureMDW, inherited permissions (through SuperUser's membership in SuperGroup) work; if I log in as the Admin user through SystemMDW, inherited permissions do not work. What I suspect is happening is that the Admin user (or any user) connecting through SecureMDW can see that SuperUser is a member of SuperGroup because these accounts and this membership is defined within SecureMDW. The Admin user connecting through SystemMDW knows nothing about the existence of accounts for SuperUser or SuperGroup (and certainly does not know that SuperUser is a member of SuperGroup) because these accounts and membership do not exist in SystemMDW. Both Admin users will see that someone named SuperUser is the owner of the objects in the database (such as queries, etc.) because object ownership is part of the permissions that reside with the database file. When SuperUser's back-end table permssions are inherited through SuperGroup, the SecureMDW Admin user will recognize SuperGroup is (because SuperGroup is also a member of SecureMDW) and will be able to trace this inheritance back to recognize that the owner of the query also has permissions on the back-end table; however, SystemMDW Admin user will have no idea who this SuperGroup is (because SuperGroup is not defined in SystemMDW) and, since SuperGroup is different from the name of the owner of the query (SuperUser), this Admin user will conclude that the query owner (SuperUser) does not have any permissions on the back-end table. My only hesitation in thinking that this is reasonably correct is that Joan has asserted above that inherited permissions should work in this context and that her users use the production mdw (which I assume is SystemMDW) and she has never had to give explicit back-end table permissions to the query owner. So I am hoping that her scenario is somehow different from mine; otherwise, I will still have some work to do to understand why inherited permissions are not working for me. Show quoteHide quote "David W. Fenton" wrote: > =?Utf-8?B?WmFyZG96?= <Zar***@discussions.microsoft.com> wrote in > news:254F0D98-752F-43B4-9D11-D9076E432BF0@microsoft.com: > > > I double- and triple-checked, and the superuser is the owner of > > the tables in the backend database. > > What about the table links in the front end? Permissions on those > are completely independent of the back-end table permissions. > > -- > David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/ > The only thing I see different, is that I use the Admins Group in
securemdw, whereas you created a separate SuperGroup. Joan Wild MS Access MVP Zardoz wrote: Show quoteHide quote > Thanks for the suggestion, David. I rechecked the ownership of the front-end > table objects (which as you state are just links to the back-end) and the > owner of these objects is my SuperUser, so I don't think that's it. But > please keep the ideas coming. > > One thing I noticed is that, when a query is set as RWOP (in the > RunPermissions property of the query), I cannot change the owner of the > query, even when I am logged in as the owner of the query (i.e., as the > SuperUser). I would get the message "You don't have permission to change the > owner of <Name of query>. To change the owner of a database object, you must > have Administer permission for it...." My SuperUser does have Administer > permission for all queries. This had me wondering whether somehow my > SuperUser was not actually being recognized as the owner of the query. But > when I researched this, I found that this behaviour is actually by design, at > least in MS Access 2000 (see http://support.microsoft.com/kb/208929) and > earlier versions (see http://support.microsoft.com/kb/120885). I am using MS > Access 2003, and I could not find a similar article for my version,, but > presumably this behaviour continues to be by design. > > So my situation continues to be that, using RWOP queries, I can achieve what > the title of this thread refers to as 'security without signon' only if the > owner of the RWOP query is given explicit permissions on the back-end tables > rather than inherited permissions by virtue of group membership. This is > fine for my purposes, but given Joan's statements above, it sounds as if this > should work with inherited permissions as well, in which case I would like to > understand why this isn't working for me in case there is an underlying > problem with my security implementation. > > Actually, the more I think about this, the more I am convinced that my > findings make sense, which leads me to wonder whether my scenario is somehow > different from the one implied by Joan. It is the different experiences of > the default Admin user in my scenario that might be the key to understanding > what is going on here. > > As we know, the default Admin user is the same across all mdw files. There > are two mdw files at play in my scenario: the one that I created to secure > my database (SecureMDW), and the default one that comes with Access > (SystemMDW). SystemMDW contains the default Admin user account plus the two > default group accounts (Admins, Users). SecureMDW contains these default > accounts plus the account for my SuperUser and the account for my SuperGroup, > and SuperUser is a member of SuperGroup. > > If I log in as the Admin user through SecureMDW, inherited permissions > (through SuperUser's membership in SuperGroup) work; if I log in as the Admin > user through SystemMDW, inherited permissions do not work. > > What I suspect is happening is that the Admin user (or any user) connecting > through SecureMDW can see that SuperUser is a member of SuperGroup because > these accounts and this membership is defined within SecureMDW. The Admin > user connecting through SystemMDW knows nothing about the existence of > accounts for SuperUser or SuperGroup (and certainly does not know that > SuperUser is a member of SuperGroup) because these accounts and membership do > not exist in SystemMDW. > > Both Admin users will see that someone named SuperUser is the owner of the > objects in the database (such as queries, etc.) because object ownership is > part of the permissions that reside with the database file. When SuperUser's > back-end table permssions are inherited through SuperGroup, the SecureMDW > Admin user will recognize SuperGroup is (because SuperGroup is also a member > of SecureMDW) and will be able to trace this inheritance back to recognize > that the owner of the query also has permissions on the back-end table; > however, SystemMDW Admin user will have no idea who this SuperGroup is > (because SuperGroup is not defined in SystemMDW) and, since SuperGroup is > different from the name of the owner of the query (SuperUser), this Admin > user will conclude that the query owner (SuperUser) does not have any > permissions on the back-end table. > > My only hesitation in thinking that this is reasonably correct is that Joan > has asserted above that inherited permissions should work in this context and > that her users use the production mdw (which I assume is SystemMDW) and she > has never had to give explicit back-end table permissions to the query owner. > So I am hoping that her scenario is somehow different from mine; otherwise, > I will still have some work to do to understand why inherited permissions are > not working for me. > > "David W. Fenton" wrote: > >> =?Utf-8?B?WmFyZG96?= <Zar***@discussions.microsoft.com> wrote in >> news:254F0D98-752F-43B4-9D11-D9076E432BF0@microsoft.com: >> >>> I double- and triple-checked, and the superuser is the owner of >>> the tables in the backend database. >> What about the table links in the front end? Permissions on those >> are completely independent of the back-end table permissions. >> >> -- >> David W. Fenton http://www.dfenton.com/ >> usenet at dfenton dot com http://www.dfenton.com/DFA/ >> Thanks Joan. Then presumably your SuperUser/Owner is a member of Admins and
Admins has permissions on the back-end tables? The Admins group, as I understand it, isn't portable across mdw files. So the Admins group in SecureMDW will not be recognized as the same as the Admins group in SystemMDW (unless, perhaps, SystemMDW is copied to make SecureMDW). If this is true, then theoretically the Admin user coming in through SystemMDW (who is a member of the Admins group in SystemMDW) in your scenario should get the same 3112 error that the Admin user coming in through SystemMDW in my scenario gets. This is because your Admin user will still have no way of knowing that your SuperUser/Owner is a member of Admins (as this membership is defined only in your SecureMDW), and will thus not be able to trace the inherited permissions back from Admins through to your SuperUser/Owner. So there must be more to it. Show quoteHide quote "Joan Wild" wrote: > The only thing I see different, is that I use the Admins Group in > securemdw, whereas you created a separate SuperGroup. > > Joan Wild > MS Access MVP > > Zardoz wrote: > > Thanks for the suggestion, David. I rechecked the ownership of the front-end > > table objects (which as you state are just links to the back-end) and the > > owner of these objects is my SuperUser, so I don't think that's it. But > > please keep the ideas coming. > > > > One thing I noticed is that, when a query is set as RWOP (in the > > RunPermissions property of the query), I cannot change the owner of the > > query, even when I am logged in as the owner of the query (i.e., as the > > SuperUser). I would get the message "You don't have permission to change the > > owner of <Name of query>. To change the owner of a database object, you must > > have Administer permission for it...." My SuperUser does have Administer > > permission for all queries. This had me wondering whether somehow my > > SuperUser was not actually being recognized as the owner of the query. But > > when I researched this, I found that this behaviour is actually by design, at > > least in MS Access 2000 (see http://support.microsoft.com/kb/208929) and > > earlier versions (see http://support.microsoft.com/kb/120885). I am using MS > > Access 2003, and I could not find a similar article for my version,, but > > presumably this behaviour continues to be by design. > > > > So my situation continues to be that, using RWOP queries, I can achieve what > > the title of this thread refers to as 'security without signon' only if the > > owner of the RWOP query is given explicit permissions on the back-end tables > > rather than inherited permissions by virtue of group membership. This is > > fine for my purposes, but given Joan's statements above, it sounds as if this > > should work with inherited permissions as well, in which case I would like to > > understand why this isn't working for me in case there is an underlying > > problem with my security implementation. > > > > Actually, the more I think about this, the more I am convinced that my > > findings make sense, which leads me to wonder whether my scenario is somehow > > different from the one implied by Joan. It is the different experiences of > > the default Admin user in my scenario that might be the key to understanding > > what is going on here. > > > > As we know, the default Admin user is the same across all mdw files. There > > are two mdw files at play in my scenario: the one that I created to secure > > my database (SecureMDW), and the default one that comes with Access > > (SystemMDW). SystemMDW contains the default Admin user account plus the two > > default group accounts (Admins, Users). SecureMDW contains these default > > accounts plus the account for my SuperUser and the account for my SuperGroup, > > and SuperUser is a member of SuperGroup. > > > > If I log in as the Admin user through SecureMDW, inherited permissions > > (through SuperUser's membership in SuperGroup) work; if I log in as the Admin > > user through SystemMDW, inherited permissions do not work. > > > > What I suspect is happening is that the Admin user (or any user) connecting > > through SecureMDW can see that SuperUser is a member of SuperGroup because > > these accounts and this membership is defined within SecureMDW. The Admin > > user connecting through SystemMDW knows nothing about the existence of > > accounts for SuperUser or SuperGroup (and certainly does not know that > > SuperUser is a member of SuperGroup) because these accounts and membership do > > not exist in SystemMDW. > > > > Both Admin users will see that someone named SuperUser is the owner of the > > objects in the database (such as queries, etc.) because object ownership is > > part of the permissions that reside with the database file. When SuperUser's > > back-end table permssions are inherited through SuperGroup, the SecureMDW > > Admin user will recognize SuperGroup is (because SuperGroup is also a member > > of SecureMDW) and will be able to trace this inheritance back to recognize > > that the owner of the query also has permissions on the back-end table; > > however, SystemMDW Admin user will have no idea who this SuperGroup is > > (because SuperGroup is not defined in SystemMDW) and, since SuperGroup is > > different from the name of the owner of the query (SuperUser), this Admin > > user will conclude that the query owner (SuperUser) does not have any > > permissions on the back-end table. > > > > My only hesitation in thinking that this is reasonably correct is that Joan > > has asserted above that inherited permissions should work in this context and > > that her users use the production mdw (which I assume is SystemMDW) and she > > has never had to give explicit back-end table permissions to the query owner. > > So I am hoping that her scenario is somehow different from mine; otherwise, > > I will still have some work to do to understand why inherited permissions are > > not working for me. > > > > "David W. Fenton" wrote: > > > >> =?Utf-8?B?WmFyZG96?= <Zar***@discussions.microsoft.com> wrote in > >> news:254F0D98-752F-43B4-9D11-D9076E432BF0@microsoft.com: > >> > >>> I double- and triple-checked, and the superuser is the owner of > >>> the tables in the backend database. > >> What about the table links in the front end? Permissions on those > >> are completely independent of the back-end table permissions. > >> > >> -- > >> David W. Fenton http://www.dfenton.com/ > >> usenet at dfenton dot com http://www.dfenton.com/DFA/ > >> > 
Other interesting topics
Creating a secure database on a network
Basing Object Permissions on Ownership Access Tools Menu Disabled Creating user-level security with MS Access file on a network Access security best practice - Stupid question(s) #2 Access Security Invalid page fault in module RPCRT4.dll Prompting Users for a new password Adobe 7.0 and Office Access 2003 MSAccess Workgroup Administator |
|||||||||||||||||||||||
