Group Account Permissions with VBA ~ LEARN MS-ACCESS TIPS AND TRICKS

Wednesday, March 17, 2010

Group Account Permissions with VBA

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.