Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Group Account Permissions with VBA

Introduction

We have learned how to remove all permissions from all Objects for Users Group Account as all Users belong to this default Group Account and inherit 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:

Nature of Permission Assignment

Last week we were talking about removing all permissions for a particular Group Account. Now, we will learn how to assign different sets of permissions for each set of Objects: Tables, Queries, Forms, and other Objects for a particular Group Account. When done, all Users belonging 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 Records to Tables.

Users can Open Run Forms and Reportsbut cannot view or make Design Changes.

Macros can be run but cannot view or make changes to the Design.

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, the Usernames, Passwords, and User-Group Accounts are maintained in the Workgroup Information File but permission settings stay with the Databases. So, you can say Microsoft Access Security is a two-part combination Lock/Key.

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

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

  3. Press Alt+F11 to open the VBA Editing Window if you have already closed it.

  4. Press Ctrl+G to open the Debug Window.

  5. 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"
  6. Replace the Pathname of the Database and the Security Group Account Name, with your own.

  7. 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 Pathname and Security Group Account Name in TextBoxes.

Earlier Post Link References:

Share:

1 comment:

  1. My cousin recommended this blog and she was totally right keep up the fantastic work!

    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