Question Help with security ... Admin user still gets in

kurtwjohn

Registered User.
Local time
Today, 04:15
Joined
Dec 9, 2008
Messages
12
I thought I had the security figured out. I setup user level security for a multi-user data base that works great. Users only get to do what their user group has permissions for. Then someone who had access to the network folder, but wasn't setup with a user id and password installed Access, launched Access, and hen proceeded to open the data base as Admin. I made sure the User group has no permissions (I created groups with specific permissions and then assigned users to them). I removed Admin group from the Admin user (which Access 2003 won't let me delete). I then assigned an obscure password to the Admins user. This seemed to work for me. When I pointed myself back to the system.mdw profile I could only get in if I used the right password. Then I copied the data base (but not the .mdw file) to my home computer that has Access 2007 installed. I was surprised when I was able to open the the data base right up. I couldn't get the forms to do anything, but I could go into design view and launch the properties and select a new table and write a new query, then see it in the datasheet mode from the query designer. The 2003 file shows the "owner" as KNJK (rather than Admin), but when I open the data base in 2007 the "owner" is unknown. Is there any way to disable the Admin so that the database can't be opened from Access 2003 and 2007?

thanks.
 
same experinece like me.

the default account Admin belongs to 2 groups, Users & Admins. so if you want to revoke the permission for user account Admin, you need remove the permisson for groups Users & Admins as well.

CAUTION, before you revoke all the permission from admin, admins, users, please ensure you had created a new account for yourself with the full permission. otherwise you will be not able open it even yourself!


best regards
ACMAIN
 
Thanks for responding so quickly. I have setup a couple of super users with both Admin and User Groups assigned. I had deleted Admin Group from the Admin user, but I couldn't delete the User Group ... Access 2003 won't let me. But the User Group has no permissions. So, I figured that was ok. I even took the extra precaution of creating an obsecure password for the Admin User, so that nothing could happen. This seems to work when I test by rejoining the default system.mdw profile ... I can't get in without the password. However, if I load the data base on a different computer with access 2007 and open it after launching Access, I can get in. It's as if Access 2007 copies the database and deletes all the user security.
 
was finally able to secure the data base. I used a combination of the paper referenced above regarding creating a new owner and using the security wizard as Microsoft had suggested. I tried one solution then the other, but neither one alone locked out a user accessing the db directly (not going through the shortcut). The only thing that finally worked was doing both. My approach is described below. Their may be a step or two that really don't impact security, but it was such a guessing game (even for the Microsoft support person I spoke with) with lots of trial and error that I would recommend follow it closely. I was so frustrated that if one of the steps had me jumping up and down three times facing North I would have gladly done it.

1) Open access and create a new workgroup file (something.mdw) with a name other than system.mdw. Don't create or open a database yet - just stay in the main Access window. Use Tools>Security>Workgroup Administrator to create the new workgroup file (something.mdw) and put this file in the network folder where you'll be placing your database.

2) Check to see if you're joined to the new workgroup by going to Tools>Security>Workgroup Adminisistrator. If you are not joined to the new workgroup file (mdw), then close Access and launch again. Join the new workgroup you just created by going to Tools>Security>Workgroup Administrator and press the "join" button and navigate the folder the new one is in and select it.

3) Then add a new user ... something like "SuperAdmin" and give them Admin group and User Group rights. Leave the Admin user with both Admin group and User group rights for now.

4) Logout as Admin and login as "SuperAdmin." This was difficult to make happen. There's not an intuitive way to make Access prompt you for a login. It seemed to occur for me when I closed out of Access, came back in, and then went to Tools>Security>User and Group Accounts. At the prompt enter user id "SuperAdmin" with password of blank. Once in change your password by going to Tools>Security>User and Group Accounts and select the Change Login Tab. Leave "old password" blank and enter a password in the "new password" and "verify" fields. Hit OK, then close Access.

5) Open Access and go to Tools>Security>User and Groups. It should prompt you to login. Enter "SuperAdmin" and the new password you entered before. You should be at Tools>Security>User and Groups. Select the user "Admin" and revoke their Admin group rights. They should only have User group rights.

6) Now create a new database while you're still logged in as SuperAdmin pointing to the new workgroup file (something.mdw). By doing this you are defining the SuperAdmin as the orginal owner of the data base instead of the generic, default "Admin" user. Put it in the same network folder. The next step will create a "2nd" new workgroup file in this same folder. And this is the mdw file you will want to use.

7) While in the new data base, logged in as "SuperAdmin" and pointing to the 1st, new workgroup file (something.mdw) launch the security wizard by going to Tools>Security>User-level Security Wizard. Select the "Create new Workgroup Information File" radio button. On the next page make sure the "file name" field at top is pointing to the network folder where the final data base will go (use the browse button). Eventhough it's greyed out, you can scroll to see that the path is pointing to your "something.mdw" file. Make sure you either use the WID provided or enter your own. Either way it's a good idea to copy it down somewhere (you won't need it for this, though). Finally, on this page make sure the "I want to create a shortcut to open my security enhanced database" radio button is selected. Select next, and you'll see all the tables, forms, etc. They will be blank because you don't have anything in this data base yet. Select next again and you'll see a list of pre-formatted user groups. You can add these later. Select next again and select "no, the Users group should not have any permissions." Select next again ...

8) This next step is important. You'll see a listbox with just one userid below a function to "Add New User". Access considers the one User as you and you can't delete it here. You want to add the "SuperAdmin" user here and give them a password. You'll need this new user id and password to get back in to this database. You now have two users. Select next ...

9) The page will default to that original User ID on the previous page with "Admin" group rights check. Uncheck this. Then select the "SuperAdmin" from the dropdown list and give them the "Admin" rights. Select next.

10) The next step is going to back up the database as unsecured in the same folder location you placed the new data base in. It will also put a shortcut on your desktop with the appropriate configuration that contains 3 pieces of information: 1st the location of the Access.exe, 2nd the location of the database, and 3rd the location of the mdw file. This process will create the 2nd workgroup file and call it Security.mdw. It will be located in the same folder as your data base. Finally, it will provide a report of the configuration for you to print, but it will force you to create a png file and store it in the same folder with your data base.

11) now you'll need to import all the objects from the old database with the lousy security. First you need to go into that database and grant the User Group with all the rights. Open the lousy data base and go to Tools>Security>User Group and Permissions. Select the "Groups" radio button and then select the User Group. Then using the drop down to the right go through all the objects (data base, table, forms, etc.) and highlight every thing and check every permission checkbox bellow and apply. Do this for every single object in the data base. By doing this you are opening this data base wide open so that you can copy it into the new data base you created. If you don't you won't be able to import it into the new data base.

12) Close Access, then using your desktop icon, launch the new data base and go to File>Get External Data>Import. And import everything. I noticed that some of my forms didn't work when I didn a mass import. I had to try to try to open all of them in design mode to see if they opened. If they didn't I just re-imported them one by one. I made sure I imported any child forms before the parent forms (not sure if this made a difference or not, but I didn't want to take any chances).

13) Once everything is imported you'll have to go back into Tools>Startup to point to the correct form to launch and the other configurations selections you may have made in your old data base.

14) You will need to recreate your user groups and user id's now. I used the manual method to create them at this point rather than use the wizard again. Go to Tools>Security>User and Group Accounts to create your custome user groups and user ids. Go to Tools>Security>Use and Group Permissions to create your permissions.

15) Finally, I deleted that original user ID I saw when I went through the Security Wizard in step 8. You also want to make sure your Admin user (that you can't delete in Access 2003 now) only has the User Group rights.

I was able to rename the Security.mdw file created by the wizard without it impacting security. But you need to update the file name on the shortcut, too.

That's it. Good luck.

Kurt
 
This is a real PITA... trust me, I've just recently been through it. Check out this PDF file. The instructions are long, but they work if you're careful and smart. :)

http://www.geocities.com/jacksonmacd/AJMAccessSecurity.pdf

That said, it MIGHT NOT BE WORTH YOUR EFFORT! User-level security support has apparently been dropped. I've been looking into other solutions, such as NTFS file security and/or using a MySQL or SQL Server back end as the security.

Perhaps this is sacrilegious to say here, but maybe the application has outgrown Access....

-Sparky
 

Users who are viewing this thread

Back
Top Bottom