Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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.
  3. 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
Share:

19 comments:

  1. With user-level security how do I prevent a user (not an admin) from joining the database to another workgroup?

    ReplyDelete
  2. A 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.

    Permission settings remains with the Database. UserID, PersonalID and Passwords are stored in Workgroup Information File.

    ReplyDelete
  3. a.p.r.
    Thanks 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.

    ReplyDelete
  4. 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).

    Next 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.

    ReplyDelete
  5. How do you get this same functionality with .ACCDB databases in Access 2007? Is there code available to do something like ASP.NET Membership?

    ReplyDelete
  6. This is 4 times now that i’ve 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
  7. [...] 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
  8. [...] 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
  9. [...] 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
  10. [...] 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
  11. [...] 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
  12. [...] 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
  13. [...] the objects from the new database. Microsoft Access Security __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn Advanced [...]

    ReplyDelete
  14. [...] 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 [...]

    ReplyDelete
  15. Hey Sir there,

    I 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

    ReplyDelete
  16. [...] 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
  17. [...] 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
  18. […] the following Links for details: Running Workgroup Admin in Access2007 Microsoft Access Security __________________ www.MsAccessTips.com (Learn MS-Access Tips & Tricks) Learn Advanced […]

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code