enabling security on an MDB file, and unhiding

[galorin]

I have a use-case here that requires three groups of access restrictions. One is unfettered, one has access to all except sensitive data, and the third has access to only address-related items. I have various forms populated with buttons that open up the relevant parts of the database.

As security is an issue, I want to allow access for specific users (As a part of a group) to these areas, either by disabling buttons based on groupID, or using Access's built-in security. How can I go about doing this?

Also, I want to disable the window > unhide.. dialog, to prevent direct access to tables. How do I go about this?

 

[galorin]

As an update, this bit of code is giving me a headache. It is essential given the environment we are working in, where a connection to the server may not be present, so needs to use a locally-running database. Basically, it changes the properties of linked tables, so that the DSN is either the DSN for the local server, or the remote one.

Private Sub refreshtables(dbname As String)
    Dim Dbs As Database
    Dim Tdf As TableDef
    Dim Tdfs As TableDefs
    Dim strConn As String
    
    Set Dbs = CurrentDb
    Set Tdfs = Dbs.TableDefs
    
    For Each Tdf In Tdfs
    If Tdf.SourceTableName <> "" Then
    strConn = "ODBC;DATABASE=contacts;SERVER=localhost;DSN=" & dbname & ";TABLE=" & Tdf.SourceTableName
    Tdf.Connect = strConn
    Tdf.RefreshLink
    End If
    Next

End Sub

 

[JerryKlmns]

Run this

Sub Initiation()
Dim prp As Object

    With CurrentDb
        Set prp = .CreateProperty("AppTitle", 10, "Initiation Of All Properties", True)
        .Properties.Append prp
        Set prp = .CreateProperty("StartupForm", 10, "[b]YourStartUpFormNameHere[/b]", True)
        .Properties.Append prp
        Set prp = .CreateProperty("AllowBypassKey", 1, True, True)
        .Properties.Append prp
        Set prp = .CreateProperty("AllowBreakIntoCode", 1, True, True)
        .Properties.Append prp
        .Properties("AllowFullMenus") = True
        .Properties("AllowShortcutMenus") = True
        .Properties("StartupShowDBWindow") = True
        .Properties("StartupShowStatusBar") = True
        .Properties("AllowBuiltInToolbars") = True
        .Properties("AllowToolbarChanges") = True
        .Properties("AllowSpecialKeys") = True
        Application.RefreshTitleBar
    End With
    Set prp = Nothing

End Sub

to create the necessary properties

and use this

Sub EnforceDBProperties()

Dim prp As Object
If CurrentUser = "[b]YourUserNameHere[/b]" Then
    With CurrentDb
        If .Properties("AllowByPassKey") = True Then
            .Properties("AppTitle") = "[b]YourTitleHere[/b]"
            Set prp = CurrentDb.CreateProperty("StartupForm", 10, "[b]YourStartUpFormNameHere[/b]", True)
            .Properties.Append prp
            .Properties("AllowFullMenus") = False
            .Properties("AllowShortcutMenus") = True
            .Properties("StartupShowDBWindow") = False
            .Properties("StartupShowStatusBar") = True
            .Properties("AllowBuiltInToolbars") = True
            .Properties("AllowToolbarChanges") = False
            .Properties("AllowSpecialKeys") = False
            .Properties("AllowBypassKey") = False
            .Properties("AllowBreakIntoCode") = True
            Application.RefreshTitleBar
        Else
            .Properties("AppTitle") = "Hello Master"
            .Properties.Delete "StartUpForm"
            .Properties("AllowFullMenus") = True
            .Properties("AllowShortcutMenus") = True
            .Properties("StartupShowDBWindow") = True
            .Properties("StartupShowStatusBar") = True
            .Properties("AllowBuiltInToolbars") = True
            .Properties("AllowToolbarChanges") = True
            .Properties("AllowSpecialKeys") = True
            .Properties("AllowBypassKey") = True
            .Properties("AllowBreakIntoCode") = True
            Application.RefreshTitleBar
        End If
        Set prp = Nothing
        DoCmd.Quit
    End With
End If
End Sub

To let you in or shut the door. Call EnforceDBProperties when a hidden form (loaded from the StartUp form) unloads, terminating the application

About the security
a] You could apply security on those forms and let MSAccess give the ugly "You do not have the necessary permissions",

b] When startup form loads query the mdw file to see what the MSysUserMemberships returns for the CurrentUser() and enable/disable objects

c] Build a table where all users have the allowed permissions and enable/disable objects.

For b]+c]: When a new form opens you do the checking and then enable/disable accordingly