MICROSOFT ACCESS SECURITY

Securing Your Database

Microsoft Access allows you to implement security on your database objects as you need it. By default, security is completely invisible to both the designers and the users of an Access database. As per your requirements, you can secure individual objects, so that most users can't modify a particular object. If you are extremely concerned about security, you can use Access to remove all but a few ways to retrieve data from your tables. In networked applications, a well-designed security system can help make the application more maintainable by eliminating many sources of potential disaster.

Security Concepts

To understand Access security, you'll need to grasp four basic security concepts: users and groups have permissions on objects.

• An Access user represents a single person who uses an Access application. Users are distinguished by their user name, password, and a unique secret identifier called the Personal Identifier (PID). To use a secured Access application, a user has to type in her user name and password to be able to get to any objects.
• An Access group is a collection of users. You can use groups to represent parts of your organization, such as Development and Accounting, or simply security levels, such as High and Low. Often you'll find that assigning users to groups, and permissions directly to those groups, makes a security system more maintainable.
• Access permission is the right to perform a single operation on an object. For example, a user can be granted read data permission on a table, allowing that user to retrieve data from that table. Both users and groups can be assigned permissions.
• An access security object is any one of the main database Container objects (Table, Query, Form, Report, Macro, or Module) or a database itself.

Because both users and groups can have permission, you may have to check several places to determine a user's actual permissions. The user's actual permissions are the least restrictive combination of the user's own permissions (called explicit permissions) and the permissions of all groups the user belongs to (called implicit permissions). So if Mary does not have permission to open the Accounting form, but she's a member of the Supervisors group that does have permission to open that form, she will be able to open the form.

Creating a Microsoft Access Workgroup Information File

When you install Microsoft Access, the Setup program automatically creates a Microsoft Access workgroup information file System.mdw that is identified by the name and organization information you specify. Because this information is often easy to determine, it's possible for unauthorized users to create another version of this workgroup information file and consequently assume the irrevocable permissions of an administrator account (a member of the Admins group) in the workgroup defined by that workgroup information file. To prevent this, create a new workgroup information file, and specify a workgroup ID (WID). Only someone who knows the WID will be able to create a copy of the workgroup information file.

The Procedures explained in this document assume that you have Microsoft Access 2000 installed on your machine. Other versions of Microsoft Access also use the same procedures, but the location of Workgroup-Administrator (Wrkgadm.exe) and default workgroup information file (system.mdw) may differ.

1. Exit Microsoft Access (Access2000 or earlier versions)

2. To start the Workgroup Administrator, open the language folder (C:\Program Files\Microsoft Office\Office\1033 is for US English), then double-click Wrkgadm.exe. Workgroup-Administrator image is given below:

   

3. Select the Create option to create a new Workgroup Information File.

4. Select the Join... Option to join a Workgroup Information File you have created earlier.

Alternatively, you can use the Microsoft Access Workgroup Administrator shortcut in the \Program Files\Microsoft Office\Office folder.

To run Workgroup Administrator in Microsoft Office 2003:

1. Start Microsoft Access 2003

2. Select the Tools menu, point the mouse on Security, click the Workgroup Administrator option.

3. In the Workgroup Administrator dialog box, click Create.

   

4. In the Workgroup Owner Information dialog box, type your name and organization, and then type any combination of up to 20 characters for the workgroup ID.

   Caution: Be sure to write down the exact name, organization, and workgroup ID, including whether letters are uppercase or lowercase (for all three entries), and keep them in a secure place. If you have to re-create the workgroup information file (if your existing workgroup information file is corrupted or lost by accidentally deleting it), you must supply exactly the same name, organization, and workgroup ID. If you forget or lose these entries, you can't recover them and might lose access to your databases.

5. Type a new name for the new workgroup information file, and then click OK.

By default, the workgroup information file is saved in the language folder (C:\Program Files\Microsoft Office\Office\1033, for U.S. English). To save in a different location, type a new path, or click Browse to specify the new path). The new workgroup information file is used the next time you start Microsoft Access. Any user and group accounts or passwords that you create are saved in the new workgroup information file.

To have others join the workgroup defined by your new Workgroup Information File, copy the file to a shared folder and then have each user run the Workgroup Administrator (wrkgadm.exe) as explained above on their own PC to join the common workgroup information file.

To Join a Microsoft Access Workgroup using Workgroup Administrator.

1. Follow steps 1 & 2 as explained above, depending on the Access Version (Access2000 and earlier or Access2003).
2. In the Workgroup Administrator dialog box, click Join.
li>Type the path and name of the Workgroup Information File that defines the Microsoft Access workgroup you want to join and click OK, or click the Browse button to find the Workgroup Information File on disk, click Open then click OK to close the dialog control.

Next time you start Microsoft Access, it uses the User and Group Accounts and Passwords stored in the workgroup information file for the workgroup you have joined.

Log on to a Microsoft Access workgroup

Activate the Logon dialog box

Until you activate the login procedure for a workgroup, Microsoft Access automatically logs on all users at startup using the predefined Admin user account, and then log on the dialog box is not displayed.

You need to set a password for the default Admin user account to activate the logon dialog box so that your users can enter their user name and password to open and work with your secured databases.

1. Start Microsoft Access.
2. On the Tools menu, point to Security, and then click User and Group Accounts.
3. Click the Users tab, and make sure that the predefined Admin user account is highlighted in the Name box.
4. Click the Change Logon Password tab click the New Password box, and type the new password. Don't type anything in the Old Password box.

   To maintain the security of your password, Microsoft Access displays asterisks (*) as you type. Passwords can be from 1 to 20 characters and can include any characters except ASCII character 0 (null). Passwords are case-sensitive.

5. Verify the password by typing it again in the Verify box, and then click OK.

The Logon dialog box is displayed the next time any member of the workgroup that you joined starts Microsoft Access and opens a database. If no user accounts are currently defined for that workgroup, the Admin user is the only valid account at this point.

Note: When you secure a database, you create User Accounts in a Microsoft Access workgroup, and then assign permissions for Databases, Tables, Queries, Forms, Reports, and Macros to those Accounts and to any Group Accounts to which they belong. Users log on to Microsoft Access by typing a User-name and password in the Logon Dialog Box. When Users log on to Microsoft Access by using their Accounts, they have only the permissions associated with those accounts.

Keep the following points in mind while implementing MS-Access Security:

1. The Members of the Admins Group have full Permissions to all objects of the Database and have full authority to give or take away permissions to other Users or User Groups.

2. The Owner of the Database (the User who created the database) has full authority (like members of the Admins Group) to give permissions or to give ownership of objects to other Users or Groups.

3. Create an Administrator account for yourself. Click show how.

4. Remove the default user Admin from the Admins Group.

   Caution: Before going through the procedure in step 4 above you must create a new Administrator account(as a member of the Admins group) for yourself otherwise you will be shutting yourself out of your workgroup information file.

5. Remove all permissions on all objects for the User group.

By default, all users are the members of the Users group. Even if you implement Security at User Account or and other Group Account level it will have no effect if the Users group carries full permissions and the Users will inherit the permissions from the Users group.

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert  Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access