[AccessD] Access User Level Security.

This is how to setup a database so that some tables can only be read, some can only be written to, but not updated.

First, Open Access, but don't create a database. (I'm doing this in Access 2003, this should be the same back to 2000, in 97, you need to hit StartyRunRwrkgadm to open the work group administrator for Access 97).

Click ToolsCSecuritySWorkGroup Administrator
Click the Create Button.  Fill in your name, Organization and put in a WorkGroup ID (1234 will work just fine).
Select a path and name your .mdw (workgroup) file.  I'm going to create C:\AccessD.mdw 
You'll get some prompts telling you it created it, click ok or yes until all the dialog boxes are gone.

Access is now using the AccessD.mdw for it's workgroup (instead of the default system32\system.mdw).  What we need to do now is create an admin account for this work group, and sign into it.  So first:

Click ToolsCSecuritySUser and Group Accounts
The first tab is the User tab, and we want to click the New button.  This asks us for a Name and a Personal ID.  The name is the account name we will use to log into the database with, and the Personal ID is a number we supply it.  I typically use birthdays, if I am setting up accounts for specific people (though I rarely do that for people other then me).  So, in this case, I put in Drew for the Name, and 9161972 as my PersonalID.

Once you create this user, it should be the users selected in the dropdown box.  (If not, select it).  Below the user and add, delete, clearpassword buttons are two listboxes.  The one on the left shows all of the groups (right now, just Admins and Users), and the one on the right shows the groups the 'selected' user belongs too.  Click Admins in the left box, and then click the Add box.  Now 'Drew' is a member of both Admins and Users.

By default, Access logs into EVERY database using Admin as the user name, and a blank password.  We need to log in as Drew, so we need to put a password on the Admin account.  Click the Change Logon password tab.  It should show Admin as the 'User Name'.  Skip the Old Password box (since the password is blank), and put in 'test' (without quotes) in the New Password and Verify boxes, click Ok.
 
Now close Access and start Access again.  Click create a new file, and then select Blank Database.  I created one on my c drive called AccessDSecured.  We now are prompted for a name and password (because we set a password on the admin account).  Put in Drew as the username and leave the password blank (we haven't set one yet).
 
We now have a blank database.  Let's go set a password:
 
Click ToolsCSecuritySUser and Group Accounts
Click the Change Logon Password tab. Again, skip the Old Password, and put AccessD in the New Password and Verify fields, and click OK.
 
Now we need to setup the defaults for this blank database:
 
Click Tools-->Security-->User and Group Permissions
Under the permissions tab, there is a combo box in the middle right of the window. Select Database from that list.
Select the user Drew, and make sure that the three check boxes available are all checked. (after each change, we need to click apply. If you don't, you'll be prompted to apply the changes)
Select the Admin user, it should have nothing checked.We want Open/Run to be the only checked item ,click apply.
Switch the 'List:' option to Groups.
Admins has all three checked. Uncheck everything, click apply.
Users has all three checked. Uncheck everything, click apply.
Switch object type to 'Table'.
The rightside listbox has <new tables/queries>. We want that. We should still have Users as the User/group name selected. Uncheck everything, click apply.
Select the Admins group (in the left listbox). Uncheck everything (just an FYI, unchecking Read Design clears all the checks), click apply
Switch 'List:' option back to Users. Admin should have nothing checked Check Read Design and Read Data, click apply.  Drew should have everything checked.
Check Object Type to Form (Queries are already set with what we did with Table).
Drew should have all checked, Admins and Users should have nothing checked. Admin should have Open/Run checked.
Switch Object Type to Report. Same thing.  Drew should have all checked, Admins and Users should have nothing checked and Admin should have only Open/Run checked. (in the last two steps, Admins and Users will have everything checked, so you will be changing those as I instructed.)
Switch Object Type to Macro, and again, set it up the same way. Drew has everything checked, Admin has 'Open/Run' checked, Admins and Users have nothing checked.
 
Click OK.
 
Create a new table in design view. Let's add a few fields (whatever you want).  Name it tblDefaultReadOnly.  Create another table, add whatever fields you want, and name it tblAddDataOnly.
 
Click Tools-->Security-->User and Group Permissions. 
Set Object Type to Table. 
Select tblAddDataOnly (in the right listbox)
Switch 'List:' to Users.
Select Admin from the Left listbox.
Note, it has Read Design and Read Data checked (and nothing else). That is because we set the defaults for New tables and queries in the steps above.  We want to allow data to be entered in this table, so check the Insert Data box, and click apply.  (If you uncheck Read design and Read data first, then check Insert Data, you'll note that read design and read data check themselves again, can't put data in if you can't see the design of the table, and can't read the data in the table)
 
Click Ok.
 
We are still logged in as Drew, so let's go into both the tables we created, and add some test data.
 
Click Tools-->Security-->WorkGroup Administrator
Click Join. Select the original system.mdw (should be in your system32 folder)
 
Our database is now setup so that anyone can open it, without a prompt (using their default .mdw).  To create a shortcut to specifically use our 'secured' .mdw, it should be "C:\Program Files\Microsoft Office\Office11\msaccess.exe" "C:\AccessDSecured.mdb" /wrkgrp "C:\AccessD.mdw" /user "Drew" /pwd "AccessD" 

All one line, and the first part should be the path to MSAccess.exe on that machine. (Leaving the /user and /pwd tags off, will prompt the user for a username and password (leaving the /user tag in will put that user name in automatically, but will require the user to enter a password)).

Notice now, that when you open the database (as a normal user), you can't enter, change, or delete any data in tblDefaultReadOnly.  We can't change or delete data in tblAddDataOnly, but we can enter a new record (but can't change it once it's saved.).

I have put the demo I wrote doing this at http://www.marlow.com/AccessD.zip

Drew 

 
 
 
 

The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI BusinessSensitve material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.