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.
- Exit Microsoft Access (Access2000 or earlier versions)
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:
Select the Create option to create a new Workgroup Information File.
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:
Start Microsoft Access 2003
Select the Tools menu, point the mouse on Security, click the Workgroup Administrator option.
In the Workgroup Administrator dialog box, click Create.
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.
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.
- Follow steps 1 & 2 as explained above, depending on the Access Version (Access2000 and earlier or Access2003).
- 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.
- Start Microsoft Access.
- On the Tools menu, point to Security, and then click User and Group Accounts.
- Click the Users tab, and make sure that the predefined Admin user account is highlighted in the Name box.
- 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.
- 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:
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.
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.
Create an Administrator account for yourself. Click show how.
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.
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.
- Create a security user account
- Create a security group account
- Add users to security groups
- Remove users from security groups
- Delete a security user account
- Delete a security group account
- Create or change a security account password
- Clear a security account password
- Assign or remove permissions
- Assign default permissions for new tables, queries, forms, reports, and macros.
- View or transfer ownership of Objects
- Transfer ownership of an entire database to another administrator
- Permit others to view or run my query but not change data or query design.
- Change default permissions for all new queries.
- RunPermissions Property
- Convert Microsoft Access 95 or 97 secured databases.
- Convert a workgroup information file from a previous version of Microsoft Access.
- Share a previous-version secured database across several versions of Microsoft Access
With user-level security how do I prevent a user (not an admin) from joining the database to another workgroup?
ReplyDeleteA particular User is identified by her UserID and PersonalID in a Workgroup Information File. If the User have the same UserID and PersonalID in Both Workgroup Information Files she can join either Workgroup, open and work with the Database that has been implemented with User-level permissions. If UserIDs are same in both Workgroup Files, but PersonalIDs are different then she cannot open the database after joining the other Workgroup.
ReplyDeletePermission settings remains with the Database. UserID, PersonalID and Passwords are stored in Workgroup Information File.
a.p.r.
ReplyDeleteThanks for the response but I am still a bit confused. I must be missing something that should be obvious.
I created a small test mdb file (2003), created a user with admin privileges and a user in the "users" group with restricted privileges.
All works well. But, when signed on as the restricted user I can go to Tools/Security and join the default "system.mdb" workgroup and open the database without a userid/password prompt and everything is wide open.
I see no way to prevent this. I thank you for any help.
Don't use default System.mdw file. I suggest you create a separate Workgroup Information File with a different name, say MyWorkgroup.mdw. It is already explained on this page how to do it. When you do that remember to note down: NameOrganization WorkGroupID Values that you specify and keep it in a safe place. If your Workgroup file is deleted by mistake then you need this information to re-create it and open the Databases that you have Secured with this Workgroup Information File. After creating this File you are automatically joined in this Workgroup File. Now open Microsoft Access (password prompt will not come till you set a password for the Admin User) go to Tools --> Security --> User and Group Accounts --> Change Logon Password and set a password for the Admin User (you are now, when you opened MS-Access).
ReplyDeleteNext steps are very important and follow carefully. 1. Create a new User Account for you with the name you prefer and join yourself to the Admins Group. After that close MS-Access and open it again. At this point the UserID, Password prompt will popup. Login with your new User Account that you have created (no password yet) and set a password as we did for the Admin User Account. 2. Remove Admin User from the Admins Group. Now the only User Account that has Administrative Privileges is the new Account that you have created.
Now securing your database part: If you have created the Database in question when the System.mdw Workgroup File was active then the Owner of that Database is the Admin User. Owner of the Database has full authority to open and assign permissions to others, like Administrative privileges. This we have to remove. 3. Create a new Database after Log-in with your new Admins User Account. Import all the Objects from the old database into the New Database. 4. Remove all Permissions of all Objects from the Admin User Account and Users Group. Always do this step whenever you create a secure database. 5. Create a New User Account for your Application User.
6. Assign permissions to Individual Objects for the User. Or better create a new Group Account and assign permissions to this Group Account, then create one or more User Accounts and join them into this Group. The Group Account privileges are inherited by the individual Users of that Group. Note: Create your new Databases always with your Admins User Account. I hope it is more clear now. Try it out and let me know any further help is required.
How do you get this same functionality with .ACCDB databases in Access 2007? Is there code available to do something like ASP.NET Membership?
ReplyDeleteThis is 4 times now that ive happened upon on your website in the last three days when searching Yahoo for absolutely unrelated things. Kinda funny. Keep up the good blogging!
ReplyDelete[...] Permission settings are stored directly on the Database. Use the following link for more details: Microsoft Access Security PS: Don't forget to Read the Comment Section also. __________________ [...]
ReplyDelete[...] Security, if your version of Microsoft Access is earlier than 2007. Take a look at the link:Microsoft Access Security and other related links. __________________ http://www.msaccesstips.com (Learn MS-Access Tips [...]
ReplyDelete[...] machine have no effect on them. To know more about Access Security Settings read the Article : Microsoft Access Security and other related topics. Don't forget to read the comment section where I have replied several [...]
ReplyDelete[...] have several Users. To read more about Microsoft Access Security refer the following blog post: Microsoft Access Security __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn Advanced [...]
ReplyDelete[...] See an overview of Microsoft Access Security and to find out who is currently online with a database, by visiting the following Links: Microsoft Access Security [...]
ReplyDelete[...] all advisable. If you need some guidance with Microsoft Access Security then refer the Blog Post: Microsoft Access Security. If you have atleast read permissions to all objects in the database then do the following, while [...]
ReplyDelete[...] the objects from the new database. Microsoft Access Security __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn Advanced [...]
ReplyDelete[...] security implementation you need to know about Microsoft Access Security. Visit the link: Microsoft Access Security for more details. __________________ http://www.msaccesstips.com (Learn MS-Access Tips and [...]
ReplyDeleteHey Sir there,
ReplyDeleteI am using ACCESS 2007. can you advise me, how to activate the "WORKGROUP" in this as i cant see any where those options.
thanks
zee
Check the following Link:
ReplyDeleteRunning Workgroup Admin in Access2007
[...] you need any help on setting up Microsoft Access Security, visit this link: Microsoft Access Security http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn Advanced MS-Access [...]
ReplyDelete[...] and it's individual objects, otherwise you will not. Read more on Microsoft Access Security from here. http://www.MsAccessTips.com (Learn MS-Access Tips and Tricks) Learn Advanced MS-Access [...]
ReplyDelete[…] the following Links for details: Running Workgroup Admin in Access2007 Microsoft Access Security __________________ www.MsAccessTips.com (Learn MS-Access Tips & Tricks) Learn Advanced […]
ReplyDelete