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:

Users Group and Permissions

Introduction

Making a Database Truly Secure

When you create a new Workgroup Information File, as part of the Microsoft Access Security implementation, it is important that the following two steps you follow immediately after creating the new Workgroup Information File (mdw) file on a priority basis:

  1. Create a new Administrator User ID (say myAdmin or whatever name you like) for you and join yourself as a member of the Admins Group Account.

  2. Remove the default Admin User Account from the Admins Group. If this is not done then your database is not secure and anybody can open and use it when MS Office Software is re-installed.

From now on, you must use the new User ID myAdmin to log in as Administrator of the Databases that use this new common Workgroup Information File.

One more thing, the databases that you create after login with the new User Name myAdmin, this User Account will be the Owner of that Database and other Objects within it.

Remember the Owner of the Database has full authority on all Objects including Administrative Authority (like a member of the Admins Group) to assign permissions to Database Objects and to transfer Ownership of Objects to other Users as well. When the ownership of a particular Object is given to an ordinary User, he has full authority to that object to Run, Modify, or Delete that object.

Assign Object-wise Access Rights to User-Group.

You must implement Object-wise access rights to User Groups before you install your Project for public use. This is done manually, from Tools - ->Security - ->User and Group Permission.

The procedure also has the first priority step, like the above two steps mentioned in the Workgroup Information File. That is to remove All Permissions of all Objects for the Users Group Account. Without this step, whatever permission settings you assign to the Objects (Forms, Report, and so on) will have no effect and Users will have full authority for Design Changes or whatever they want to do with them.

This is done manually and takes some time to go through all groups of Objects to remove all permissions of the Users Group Account. Since this step is required for all new Projects I thought it was better to automate it and here it is for you to try it out. This program is written purely on my own experience with the VBA-based procedures and you may use it at your own risk

Automated Removal of Object-level Permissions.

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

  2. Remember this Program is run for an external Database that you would like to remove All Permissions of all Objects for the Users Group Account.

  3. Before you run this program, open the Database that you are targeting and check the current permission settings of all Objects for the Users Group Account. Try the program on a copy of the original Database you are targeting.

  4. Open the Database with the above Code, if you have closed it.

  5. Press Alt+F11 to display the VBA Editing Window and press Ctrl+G to display the Debug Window.

  6. Type the following and press Enter Key:

    Default UsersGrp "C:\My Documents\myTest.mdb"

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

  8. Open your Test Database and check the current status of the permissions settings of the Users Group Account. The check marks from all the permission options you will find disappeared.

Earlier Post Link References:

Share:

Create Security Group Account with VBA

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 are appearing in the Control.

In the User Name control, SMITHJOHN is selected. The Group Account name Users are appearing under the label Member indicating that he belongs 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 to 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 part 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:

  1. How to create a Microsoft Access Security Group Account MANAGER with VBA.
  2. 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
  1. Copy and Paste the above program into a Standard Module.
  2. Click somewhere in the middle of the Code and press the F5 Key to Run the Code and create the MANAGER Group Account.
  3. You may select Tools - -> Security - -> User and Group Accounts (from the Database Window).
  4. Select SMITHJOHN in the User Name List.
  5. Check for the name MANAGER in the Available Groups List and you will find it there.

Adding the User Account to 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.

Share:

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