Microsoft KB Archive/120911

= ACC: Ability to Add Users Without Permission to View Design =

Article ID: 120911

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q120911



SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

You may need to create a database in which some users have the ability to add other users, but don't have the ability to view the design of objects in the database. You can accomplish this by setting the ownership of different objects, and then shipping your application together with a different system database than the one that was used to create it. The system database is usually the System.mdw (or SYSTEM.MDA in version 2.0).



MORE INFORMATION
The following example demonstrates how to make a user named Mary able to create new user accounts but unable to view the design of existing objects in the database:

 Quit Microsoft Access if it is running, and then make a backup copy of your System.mdw file (or SYSTEM.MDA in version 2.0). Using the Workgroup Administrator program, create and join a new workgroup. Make note of the Name, Organization, and Workgroup ID values used to create the new workgroup. These values are combined to create the group Security ID (SID) for the Admins group. Start Microsoft Access, open any database, and

In Microsoft Access 7.0 and 97:

 On the Tools menu, click Security and then click User and Group Accounts. In the User and Group Accounts dialog box click the Change Logon Password tab. Assign a password for the Admin user. On the Tools menu, click Security, and then click User and Group Accounts. On the Users tab, click New, and then create a new user called Developer. Make note of the Personal ID that you assign for the new user. Make the Developer user a member of the Admins group.

In Microsoft Access 2.0

 On the Security menu, click Change Password. Assign a password for the Admin user.</li> On the Security menu, click Users. Click New, and then create a new user called Developer. Make note of the Personal ID that you assign for the new user. Make the Developer user a member of the Admins group.</li></ol> </li> Quit and then restart Microsoft Access. Log on to Microsoft Access as the Developer user.</li>  Create a new database called Mydb.mdb. Create a new module in the data- base, and then enter the following function in the module:

Function TestSecurity MsgBox "This is a test of Security" End Function

Save the module as TestModule. Because you are logged on as Developer, the owner of this new module will be the Developer user. </li> Remove all permissions on the TestModule module from the Users and Admins groups. Make sure that only the Developer user has any permissions on the TestModule module.</li> Quit Microsoft Access, and then use the Workgroup Administrator program to create and join a new workgroup. Make sure to use a different Workgroup ID than you did when you created the workgroup in step 2. If you do not, the module you created in step 5 will not be secure.</li> Start Microsoft Access and open the Mydb.mdb database.</li> Re-create the user named Developer that you created in step 3b. Make sure to use the same Personal ID that you used in step 3b. Make the Developer user a member of the Admins group.</li> Create a new user called Mary. Make Mary a member of the Admins group. Because Mary was not a member of the Admins group in the system database in use when the database was created, Mary will not have permissions on objects that already exist in the database.</li> Assign a password for the Admin user as per step 3a above.</li> Quit and then restart Microsoft Access. Log on to Microsoft Access as Mary and then open the Mydb.mdb database. Note that although Mary is a member of the Admins group, and can create new users, Mary has no permissions on the TestModule module.</li> To test the security, quit and then restart Microsoft Access. Log on to Microsoft Access as Developer, and note that you have permissions on the TestModule module.</li></ol>

<div class="references_section">