Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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:

No comments:

Post a Comment

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