March 2010 ~ LEARN MS-ACCESS TIPS AND TRICKS

Group Account Permissions with VBA

a.p.r. pillaiMarch 17, 2010MS-Access Security, security advanced 1 comment

Introduction

We have learned how to remove all permissions from every object for the Users Group Account. Since all users belong to this default group, they inherit any permissions assigned to it, in addition to those from other group accounts they are members of.

If you haven’t reviewed the earlier articles on Microsoft Access Security using VBA, it’s recommended to go through the following links before continuing.

Nature of Permission Assignment

Last week, we discussed how to remove all permissions from a specific group account. This week, we’ll learn how to assign different sets of permissions for each type of object—Tables, Queries, Forms, and others—to a particular group account. Once this setup is complete, all users belonging to that group will have restricted access to the designated objects.

For Tables and Queries, Users cannot make design changes, but they can view the Table Structure or Query Design. Users can add, edit, or Delete Records in Tables.

Users can open Run Forms and Reports, but cannot view or make Design Changes.

Macros can be run, but cannot be viewed or made Design changes.

Automating Object-Level Permission Settings

The VBA Routine given below is run for an external Database and sets permissions for the given User-Group Account in the active Workgroup Information File.

Remember, usernames, passwords, and user-group accounts are stored in the Workgroup Information File (.mdw), while permission settings are saved within the individual databases. In that sense, Microsoft Access security works like a two-part combination lock and key system.

Copy and paste the following VBA Code into a Standard Module of your Database.

Public Function SetPermission2Grp(ByVal DatabaseName As String, ByVal GroupName As String) As Boolean
'-------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : March-2010
'Purpose: To Assign Security Permissions to Objects
'       : for a particular Security Group Account
'       : in an external Database.
'Note   : The Security Group Account must be present
'       : in the active Workgroup Information File
'       :'Remarks: All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------------
Dim wsp As Workspace, db As Database, ctr As Container, doc As Document
Dim ctrName As String, docName As String
Dim L4 As String
Const OBJSFULL = &HD01FE
Const dbOpenRun = 2
Const FrmRptOpenRun = 256
Const MacOpenRun = 8
Const TblQryExcludingModifyAdmin = 244 'All permissions Exluding Modify & Administr
'Assign Permissions to Group Account
On Error GoTo SetPermission2Grp_Err
Set wsp = DBEngine.Workspaces(0)
Set db = wsp.OpenDatabase(DatabaseName)
wsp.Groups.Refresh
For Each ctr In db.Containers
ctrName = ctr.Name
ctr.UserName = GroupName
Select Case ctrName
Case "Databases"
For Each doc In ctr.Documents
         docName = doc.Name
         doc.UserName = GroupName
         Select Case docName
           Case "MSysDb"
             'Set Open/Run Permissions to Database Object
              doc.Permissions = doc.Permissions Or dbOpenRun
         End Select
      Next doc

   Case "Forms"
    'Set Open/Run Permissions to Forms Container
      ctr.Permissions = ctr.Permissions Or FrmRptOpenRun
      ctr.Inherit = True
      For Each doc In ctr.Documents
         docName = doc.Name
         doc.UserName = GroupName
         'Set Open/Run Permissions to Each Form
        doc.Permissions = doc.Permissions Or FrmRptOpenRun
      Next doc

   Case "Reports"
    'Set Open/Run Permissions to Reports Container
      ctr.Permissions = ctr.Permissions Or FrmRptOpenRun
      ctr.Inherit = True
      For Each doc In ctr.Documents
         docName = doc.Name
         doc.UserName = GroupName
         'Set Open/Run Permissions to Each Report
         doc.Permissions = doc.Permissions Or FrmRptOpenRun
      Next doc

   Case "Scripts"
     'Set Open/Run Permissions to Macros Container
      ctr.Permissions = ctr.Permissions Or MacOpenRun
      ctr.Inherit = True
      For Each doc In ctr.Documents
         docName = doc.Name
        doc.UserName = GroupName
         'Set Open/Run Permissions to Each Macro
         doc.Permissions = doc.Permissions Or MacOpenRun
      Next doc

   Case "Tables"
      '1. Assigns Full Permissions to Tables & Queries
      ctr.Permissions = ctr.Permissions Or OBJSFULL
      ctr.Inherit = True
      For Each doc In ctr.Documents
        docName = doc.Name
        doc.UserName = GroupName
         L4 = Left$(docName, 4)
       'Exclude System Objects
        If L4 = "MSys" Or L4 = "~sq_" Then
          GoTo nextloop
        End If
        '2. Remove Modify and Administrator permissions
        doc.Permissions = doc.Permissions Or TblQryExcludingModifyAdmin
nextloop:
      Next doc
  End Select
Next
 ctrSetPermission2Grp = false

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

SetPermission2Grp_Err:
MsgBox Err & ": " & Err.Description, , "SetPermission2Grp"
SetPermission2Grp = True
Resume SetPermission2Grp_Exit
End Function

To test the Program, create a copy of any Database and find a Group Account that is not assigned, with permissions for the target database.
Press Alt+F11 to open the VBA Editing Window if you have already closed it.
Press Ctrl+G to open the Debug Window.
Type the following Statement in the Debug Window and press the Enter Key to run the Code and assign permissions to the selected Group Account in the Test Database:
SetPermission2Grp "C:\My Documents\TestData.mdb", "FAGRP"
Replace the Pathname of the Database and the Security Group Account Name with your own.
After running the Code, open the Test Database and check the Permission Settings of the Group Account you have specified as the parameter.

Note: You may run the Program from a Command Button Click after assigning the Database path name and Security Group Account Name in text boxes.

Earlier Post Link References:

Users Group and Permissions

a.p.r. pillaiMarch 08, 2010MS-Access Security, security advanced No comments

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.

Earlier Post Link References:

Create Security Group Account with VBA

a.p.r. pillaiMarch 01, 2010MS-Access Security, security advanced 1 comment

Introduction.

Last week, we learned how to create a Microsoft Access Security User Account(SMITHJOHN) with VBA and learned how to join him as a member of the default Group Account Users. The Users and Admins Group Accounts are already present in the default Workgroup Information File (Sytem.mdw ) or in the new Workgroup Information File that you create separately.

If we need to group Users under different categories (like Managers, Supervisors, Data Entry Operators, Editors, and so on) so that their access rights to different objects can be defined differently at the group level, and any number of Users can be joined into these groups. Once we clearly define group-level access rights, all Users joined to that group will inherit the privileges assigned to that Group.

One User can be joined to more than one Group as well. A sample image of the User and Group Accounts control is given below, and we will examine how the User SMITHJOHN's Security credentials appear in the Control.

Right-click to open Large Image in New Tab/Window.

In the User Name control, SMITHJOHN is selected. The Group Account name Users are appearing under the label Member, indicating that they belong to that Group Account only. If you look under the Available Group List, you can see that the Users Group Account is appearing there also. Unlike the Users Group Account, other Group Accounts shown there are created manually for different Groups of Users.

If we want to make the User SMITHJOHN a member of the VEHGRP, then all we have to do is select the VEHGRP under the Available Groups List and click on the Add>> Command Button to copy that name into the Member List. Whatever Access Privileges we have assigned to the VEHGRP will be automatically available to the selected user in the User Name control above.

At the end of last week's main program, CreateUser, the following code segment is doing exactly what we did manually in the above paragraph:

. . .
   With wsp
     Set tempGrp = .Users(UsrName).CreateGroup("Users")
         .Users(UsrName).Groups.Append tempGrp
         .Users.Refresh
   End With

Creating the Group Account.

But, to do that, first we must create the Group Account VEHGRP by going through the same procedure as creating a new User Account from the Groups Tab on the Control above. So, the VEHGRP Account was created at some point in time earlier.

Here, we will learn:

How to create a Microsoft Access Security Group Account MANAGER with VBA.
How to join the User SMITHJOHN to the MANAGER Group Account with VBA.

NB: User and Group Accounts are not case sensitive. Here they are given in Upper Case for legibility only.

The following Program creates the MANAGER Group Account in the Available Groups List:

Public Function CreateUserGrp()
'---------------------------------------------------------------------
'Creating a Security Group Account
'Author : a.p.r. pillai
'Date   : March-2010
'All Rights Reserved by www.msaccesstips.com
'---------------------------------------------------------------------
Dim newGrp As Group, wsp As Workspace
Dim tempGrp As Group
Dim grpName As String, grpID As String

On Error Resume Next

Set wsp = DBEngine.Workspaces(0)

    grpName = "MANAGER"
    grpID = "MGR13579"

With wsp
    Set newGrp = .CreateGroup(grpName, grpID)
               .Groups.Append newGrp

    If Err = 3390 Then
      MsgBox "Group Name : " & grpName & vbCr & vbCr & "Group PID     : " & grpID & vbCr & vbCr & "Account Name already Exists..! "
      Err.Clear
    End If

      .Groups.Refresh
End With

End Function

Copy and paste the above program into a Standard Module.
Click somewhere in the middle of the Code and press the F5 Key to run the Code and create the MANAGER Group Account.
You may select Tools -> Security -> User and Group Accounts (from the Database Window).
Select SMITHJOHN in the User Name List.
Check for the name MANAGER in the Available Groups List, and you will find it there.

Right-click to open Large Image in New Tab/Window.

Adding the User Account to a Group.

But the User SMITHJOHN is not yet added to the MANAGER Group Account, and this is where we need to add the code segment, given at the top of this page, to the main program.

The revised VBA Code is given below to create the MANAGER Group Account and to add the User SMITHJOHN to this Group Account.

Public Function CreateUserGrp()
'---------------------------------------------------------------------
'Creating a Security Group Account
'Author : a.p.r. pillai
'Date   : March-2010
'All Rights Reserved by www.msaccesstips.com
'---------------------------------------------------------------------
Dim newGrp As Group, wsp As Workspace
Dim tempGrp As Group
Dim grpName As String, grpID As String

On Error Resume Next

Set wsp = DBEngine.Workspaces(0)

    grpName = "MANAGER"
    grpID = "MGR13579"

With wsp
    Set newGrp = .CreateGroup(grpName, grpID)
               .Groups.Append newGrp
    If Err = 3390 Then
      MsgBox "Group Name : " & grpName & vbCr & vbCr & "Group PID     : " & grpID & vbCr & vbCr & "Account Name already Exists..! "
      Err.Clear
    End If

      .Groups.Refresh
End With

'Add the User SMITHJOHN to the MANAGER Group Account
usrName = "SMITHJOHN"

   With wsp
     Set tempGrp = .Users(usrName).CreateGroup(grpName)
         .Users(usrName).Groups.Append tempGrp
         .Users.Refresh
   End With
End Function

Running the Code a Second Time.

If you run the revised Code again, it will show an Error Message saying that the MANAGER Group Account already exists, because you have already run this code once. But the remaining part of the code will run.

If you open the User and Group Accounts control now and select SMITHJOHN in the User Name Control, you can see that the MANAGER group name is now appearing under the Member List, indicating that the User is a member of the MANAGER Group Account.

See the sample image given below.