Users Group and Permissions ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

Making a Database Truly Secure

When you create a new Workgroup Information File (.mdw) As part of implementing Microsoft Access security, there are two essential steps you must complete immediately:

Create a new Administrator User ID (for example, myAdmin, or any name you prefer) and add this user as a member of the Admins Group.
Remove the default Admin user account from the Admins Group.

If you skip this step, your database remains insecure—anyone can open and use it if Microsoft Office is reinstalled.

From this point forward, you must use your new user ID (myAdmin) to log in as the database administrator for any databases associated with this new Workgroup Information File.

Additionally, any databases you create while logged in as myAdmin will have that account automatically designated as the owner of the database and all its objects.

Remember:

The database owner has full authority over all objects, including administrative rights to assign permissions and transfer ownership.
When ownership of a specific object is transferred to another user, that user gains complete control over it—they can run, modify, or delete the object.

Next, let’s look at how to assign object-level access rights to a user group.

You must set object-level access rights for user groups before deploying your project for public use. This process is performed manually through the menu path:
Tools → Security → User and Group Permissions.

This procedure also includes an essential first step, similar to the two priority steps mentioned earlier for the Workgroup Information File. The step is to remove all permissions for all objects assigned to the Users Group Account.

Without completing this step, any permission settings you later assign to objects—such as Forms, Reports, or Tables—will have no effect, and users will retain full access, including the ability to make design changes or modify the objects freely.

This process can be time-consuming when done manually, as it requires reviewing and clearing permissions for every object group. Since this step is necessary for all new projects, I decided to automate it with a VBA-based procedure, which I’m sharing here for you to try.

Automated Removal of Object-level Permissions.

Copy and paste the following Program into a Standard Module and save it.

Public Function DefaultUsersGrp(ByVal DatabasePathName As String) As Boolean
'----------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date     : March-2010
'Purpose: Remove All Permissions from Users Group Security Account
'All Rights Reserved by www.msaccesstips.com
'----------------------------------------------------------------------------
Dim wsp As Workspace, db As Database, ctr As Container
Dim GroupName As String, doc As Document
Dim L4 As String

Const DB_FULLNO = &H60000
Const OBJS_FULLNO = &H2FE01

'Remove All Permissions on Containers & documents
'for USERS Group

On Error GoTo DefaultUsersGrp_Err

Set wsp = DBEngine.Workspaces(0)
Set db = wsp.OpenDatabase(DatabasePathName)

wsp.Groups.Refresh
GroupName = "Users"

Set ctr = db.Containers("Databases")
Set doc = ctr.Documents("MSysDb")
doc.UserName = GroupNamedoc.Permissions = DB_FULLNO

Set ctr = db.Containers("Tables")
GoSub SetPermission

Set ctr = db.Containers("Forms")
GoSub SetPermission

Set ctr = db.Containers("Reports")
GoSub SetPermission

Set ctr = db.Containers("Scripts")
GoSub SetPermission

Set ctr = db.Containers("Modules")
GoSub SetPermission
DefaultUsers_Grp = False

DefaultUsers_Grp_Exit:
   Set db = Nothing
   Set wsp = Nothing
Exit Function

SetPermission:
For Each doc In ctr.Documents
    doc.UserName = GroupName
    If ctr.Name = "Tables" Then
        L4 = Left$(docName, 4)
        If L4 = "MSys" Or L4 = "~sq_" Then
          GoTo nextloopxxx
        End If
    End If
    doc.Permissions = OBJS_FULLNO
nextloopxxx:
NextReturn

DefaultUsersGrp_Err:
MsgBox Err & ": " & Err.Description
DefaultUsersGrp = True
Resume DefaultUsersGrp_Exit
End Function

Points to Note.

Remember, this program is designed to be run on an external database—specifically, the one from which you want to remove all permissions for all objects assigned to the Users Group Account.
Before running the program, open the target database and review the current permission settings of all objects for the Users Group Account. It’s strongly recommended that you test the program on a copy of the original database first, to ensure that your data and structure remain safe.
Open the Database with the above Code if you have closed it.
Press Alt+F11 to display the VBA Editing Window and press Ctrl+G to display the Debug Window.
Type the following and press the Enter Key:

Default UsersGrp "C:\My Documents\myTest.mdb"
Replace the database Pathname in quotes with your own database name.
You may run the Program from a Command Button Click Event Procedure from a Form.
Open your test database and review the current permission settings for the Users Group Account. You’ll notice that all the check marks from the permission options have now disappeared, confirming that the permissions have been successfully removed.