N
NU_Dave
I'd like to set RunPermissions to "Owner" on my queries so I can restrict access to the design views of my tables. Problem is, I'm using multi-mdw security so each customer company can have their own administrator to add/delete users, but the design remains protected (See "about-multi-MDW security model" below if necessary).
I have the query owner set to a user ID that I have defined exactly the same in both MDWs (exact same name, exact same PID), but the query still can't read the underlying table when being run by a user who has access only to the query. (Of course I have given the owner ID access to the underlying tables).
Is there something special behind how Access stores the owner ID for the query that is keeping me from being able to use the exact same owner ID definition in both MDWs and get this to work?
Thanks.
PS - please save any 'move to a different platform' suggestions....
About RunPermissions:
Microsoft Access queries have a property called RunPermissions, available through the Query Properties property sheet. This "property" is really a UI construct that maps to the WITH OWNER ACCESS OPTION clause of the SQL property of the Microsoft Jet database engine query. It can have one of two settings: User's or Owner's. If it is set to User's (the default), nothing special happens -- the query executes normally. However, if it is set to Owner's, when the query is executed, only the permissions of the owner of the query are considered when attempting to access tables or queries on which the query is dependent. This very powerful feature allows you to build secure views on your data that can provide row-level and column-level security for your users.
About multi-MDW security model:
From: http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_secure70.asp
Configuring a Remote Workgroup so that Onsite Administrators Can Manage User Accounts Without Gaining Permissions to Your Objects
You have built a multiuser application that will be administered by users at a remote site. You want to lock up your code and other objects to protect your intellectual property and to prevent users from inadvertently breaking your application. Different users at the sites have permissions to different forms in the database because they are authorized to perform different duties. You don't want to manage individual user accounts yourself because you are not on site and they change frequently. How can you grant remote administrators the ability to manage user accounts without at the same time giving them access to your code?
1.. Using the Workgroup Administrator, create a secure workgroup named MySys.mdw. This will be the "master" workgroup for your database.
2.. While logged on with this workgroup, follow the steps to secure a database above.
3.. Create your own custom groups that correspond to the different levels of permissions that you will want your remote users to have. Write down the exact names of these groups (case sensitive) and the personal identifiers (PIDs) you use to create them. You will need these strings later.
4.. Assign the appropriate permissions to these groups. Make sure you don't grant Administer permissions to anything-just grant the Read Data, Write Data, and Open/Run permissions that are necessary for users to run your application and perform their appropriate functions.
5.. Use the Workgroup Administrator to create a new system database that you will distribute with your application. Call it CusSys.mdw. Make sure you use different strings for the name, company name, and workgroup ID than the ones you used for MySys.mdw.
6.. Log on under CusSys.mdw and re-create the exact same group names that now exist in MySys.mdw, using the same case-sensitive names and the same PIDs.
7.. Create a user account for your remote administrator to use, and add him to the Admins group of CusSys.mdw. For this example, call him Fred.
8.. Put a password on the Admin user, and make sure that you have removed the Admin user from the Admins group. Putting a password on the Admin user will force the Log on dialog box to appear, and it will make Fred the effective administrator of the CusSys.mdw workgroup.
9.. Distribute CusSys.mdw with your application. Make sure that the user profile file used to start Microsoft Access for your application points to CusSys.mdw. The Setup Wizard available in the Microsoft Access Developer's Toolkit can help automate this process.
Now Fred will be able to create new user accounts, reset passwords, and add users to the groups that you have already created. As users are added to these groups, they will automatically gain the permissions that you assigned to these group accounts in step 4. This is because the SIDs of the group accounts in MySys.mdw are identical to the SIDs of the custom group accounts in CusSys.mdw. However, the SIDs of the Admins group in the two workgroups are different. (Remember that these SIDs are generated from the strings fed into the Workgroup Administrator, and you used different strings in step 5.) Because the database was originally secured under MySys.mdw, not CusSys.mdw, the Admins group in MySys.mdw has ultimate permission-setting privileges over all the objects in the database. The Admins group of CusSys.mdw does not. So while Fred can add and delete users from the groups you created, he does not have any special privileges to your objects, and he therefore cannot see or modify the design of any of the objects to which you haven't granted him permissions.
I have the query owner set to a user ID that I have defined exactly the same in both MDWs (exact same name, exact same PID), but the query still can't read the underlying table when being run by a user who has access only to the query. (Of course I have given the owner ID access to the underlying tables).
Is there something special behind how Access stores the owner ID for the query that is keeping me from being able to use the exact same owner ID definition in both MDWs and get this to work?
Thanks.
PS - please save any 'move to a different platform' suggestions....
About RunPermissions:
Microsoft Access queries have a property called RunPermissions, available through the Query Properties property sheet. This "property" is really a UI construct that maps to the WITH OWNER ACCESS OPTION clause of the SQL property of the Microsoft Jet database engine query. It can have one of two settings: User's or Owner's. If it is set to User's (the default), nothing special happens -- the query executes normally. However, if it is set to Owner's, when the query is executed, only the permissions of the owner of the query are considered when attempting to access tables or queries on which the query is dependent. This very powerful feature allows you to build secure views on your data that can provide row-level and column-level security for your users.
About multi-MDW security model:
From: http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_secure70.asp
Configuring a Remote Workgroup so that Onsite Administrators Can Manage User Accounts Without Gaining Permissions to Your Objects
You have built a multiuser application that will be administered by users at a remote site. You want to lock up your code and other objects to protect your intellectual property and to prevent users from inadvertently breaking your application. Different users at the sites have permissions to different forms in the database because they are authorized to perform different duties. You don't want to manage individual user accounts yourself because you are not on site and they change frequently. How can you grant remote administrators the ability to manage user accounts without at the same time giving them access to your code?
1.. Using the Workgroup Administrator, create a secure workgroup named MySys.mdw. This will be the "master" workgroup for your database.
2.. While logged on with this workgroup, follow the steps to secure a database above.
3.. Create your own custom groups that correspond to the different levels of permissions that you will want your remote users to have. Write down the exact names of these groups (case sensitive) and the personal identifiers (PIDs) you use to create them. You will need these strings later.
4.. Assign the appropriate permissions to these groups. Make sure you don't grant Administer permissions to anything-just grant the Read Data, Write Data, and Open/Run permissions that are necessary for users to run your application and perform their appropriate functions.
5.. Use the Workgroup Administrator to create a new system database that you will distribute with your application. Call it CusSys.mdw. Make sure you use different strings for the name, company name, and workgroup ID than the ones you used for MySys.mdw.
6.. Log on under CusSys.mdw and re-create the exact same group names that now exist in MySys.mdw, using the same case-sensitive names and the same PIDs.
7.. Create a user account for your remote administrator to use, and add him to the Admins group of CusSys.mdw. For this example, call him Fred.
8.. Put a password on the Admin user, and make sure that you have removed the Admin user from the Admins group. Putting a password on the Admin user will force the Log on dialog box to appear, and it will make Fred the effective administrator of the CusSys.mdw workgroup.
9.. Distribute CusSys.mdw with your application. Make sure that the user profile file used to start Microsoft Access for your application points to CusSys.mdw. The Setup Wizard available in the Microsoft Access Developer's Toolkit can help automate this process.
Now Fred will be able to create new user accounts, reset passwords, and add users to the groups that you have already created. As users are added to these groups, they will automatically gain the permissions that you assigned to these group accounts in step 4. This is because the SIDs of the group accounts in MySys.mdw are identical to the SIDs of the custom group accounts in CusSys.mdw. However, the SIDs of the Admins group in the two workgroups are different. (Remember that these SIDs are generated from the strings fed into the Workgroup Administrator, and you used different strings in step 5.) Because the database was originally secured under MySys.mdw, not CusSys.mdw, the Admins group in MySys.mdw has ultimate permission-setting privileges over all the objects in the database. The Admins group of CusSys.mdw does not. So while Fred can add and delete users from the groups you created, he does not have any special privileges to your objects, and he therefore cannot see or modify the design of any of the objects to which you haven't granted him permissions.