Group Account Permissions with VBA
We have learned how to remove all permissions from all Objects for Users Group Account as all Users belongs to this default Group Account and inherits permissions, if available, besides other Group Accounts each user belongs to. If you have not gone through the earlier Articles on Microsoft Access Security with VBA, you may take a look at the following links before continuing:
Last week we were talking about removing all permissions for a particular Group Account. Now, we will learn how to assign different set of permissions for each set of Objects: Tables, Queries, Formsand other Objects for a particular Group Account. When done, all Users belongs to this particular Group Account will be allowed restricted access to all Objects.
For Tables/Queries the Users cannot make design changes but they can view the Table Structure or Query Design. Users can Add/Edit/Delete Recordsto Tables.
Users can Open Run Forms and Reportsbut cannot view or make Design Changes.
Macroscan be run but cannot view or make change to the Design.
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, the User-Names, Passwords and User-Group Accounts are maintained in the Workgroup Information File but permission settings stays with the Databases. So, you can say Microsoft Access Security is a two part combination Lock/Key.
- 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 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 parameter.
Note: You may Run the Program from a Command Button Click after assigning the Database Pathname and Security Group Account Name in Text Boxes.