Setting up User Level Security on Access

[Maturi]

Does anyone have an Dummy's guide to setting up User level security on MS Access

I've tried. I've created a Workgroup informaiton file. But when I close the Access app and reopen it I get a messge
"You don't have permission to open test.mdb"

After this I can't get into the App.

Am I doing anything wrong in the way I set up the Workgroup? How do I give other users access to the new workgroup?

Basically The app sits on the network and multiple user will have access to it. All users log into the netwrok when the boot up. Can I use that initial login in anyway?

Sorry to be vague - I new to the User Level Security stuff

Thanks

 

[PHV]

Have a look at the Security section of this FAQs page:

http://www.tek-tips.com/faq.cfm?pid=181

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dabruins]

Hi Maturi.

I'm going through a bit of learning curve on this as well and the docs are not easy to follow so I started making some notes of my own and thought they might be of use to you. I'm trying to set a front end access datbase that will be used by many user to access a MySQL server. I need to create a separate security group file that can be accessed by many users across the network and i don't want to have the database owned by the default "Admin" account. Hopefully this provides some clarity on how access security works. This might contain some logic gaps or outright errors but its what I understand of the process. BTW PHV the link to the Microsoft site is no longer valid.

Access uses a default system.mdw workgroup file to store user and group account information for any users that wish to access any mdb files using the local system the user is working from. This mdw file is located in the current user’s profile directory on the local system (ex. C:\Documents and Settings\joeblow\Application Data\Microsoft\Access\system.mdw). The default user account is Admin which has no password set on it. If the Admin password is changed to a valid string, subsequent attempts to open an mdb file will prompt the user for a password before granting access to the mdb file. Make sure you write down the password you provide for the Admin account!

If a DBA decides to add new users and/or groups Tools – Security – User and Group Accounts) and assign new permissions (Tools – Security – User and Group Permissions) on database object(s) then the changes that are made to the system.mdw file located on the machine that the DBA is working at must be shared out to all users of that database. In this scenario the system.mdw must be copied onto each local machine that will be used to access the database now containing the new user/group information. We can avoid this by creating a new workgroup file in a network accessible location and nominate it as the workgroup file for a specific database(s).

All new databases are by default owned by the account that is currently logged in when the mdb file is created and cannot be changed. Initially by default that account is usually “Admin”. To get around having the mdb file owned to Admin (which compromises security) we can create a new account and add it to the Admins group giving it administrative privileges. Provide a password (Write it down!) for the account. Change the Admin account password (Write it down!) so that the next time you attempt to open a database you will be prompted to login. After changing the password close and reopen your database. At the login prompt enter the user name of the administrative account you just created with the password and open the database. Select File – New to create a new database. After creating the new database close it down, reopen and login as the new account user and you will see that the database is now owned to the new administrative account (Tools – Security – User and Group Permissions – Change Owner tab – Select Object Type: Database).

Reopen the new database you created. Now you should create a new mdw file to store account information for the new database design so that the changes made in it will not affect any other mdb files. Use the Tools - User-Level Security Wizard option to create a new workgroup security file. This avoids updating your local copy of the system.mdw file and affecting your normal access to other mdb files that do not use a “restricted” security file. Using the wizard, you can select the default groups provided as required and add the user accounts and passwords you will require for the database. Further configuration/additions/deletions of the group and user accounts can be made using the Tools – Security menu options after the mdw file has been created. After making any changes to the workgroup file you should immediately print out the latest changes from a snapshot file of the mdw file. Initially you will be prompted to do so before closing it.

The wizard creates a backup copy of your database and updates the existing file to a secured version of it. You can create the mdw file at a location of your choosing on your network. Close the database and create a shortcut to your now secure mdb file. Update the ‘Target’ value of the shortcut to include the path to the workgroup file (ex. "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "T:\MySqlData\FRONTEND\USIDB_FE.mdb" /WRKGRP "T:\MySqlData\FRONTEND\Security.mdw". Close the shortcut properties window for the shortcut and double click on it. You should be prompted to login to the new database. Provide a username and password for one of the accounts you added when using the wizard earlier. You should be able to login to the database. * Remove all privileges to the Admin account to prevent access to anyone attempting to enter the db via that account.

Earlier you added a password for the admin account in the default system.mdw. If you don’t want to be prompted for a login for any other databases that use the default system.mdw re-open another database that does not use the mdw file you just created and select Tools – Security – User and Group Accounts. Select the Admin account and Clear Password. Subsequent attempts to open a database using the default system.mdw will bypass the login prompt and you will be logged in as “Admin” by default.