Changing *.mdw permissions in MS-Access using SQL?

Question

Is it possible to query, and or change permissions that are stored in a Microsoft Access *.mdw security file?

I'm getting really tired of using the UI to list the permissions for each user and write them all down to see what they are...I looked at the titles of the MSys* tables and I don't see anything in there that looks like a user or a permissions table (well at least in the database I'm connected to with the file...my guess would be that it's in the *.mdw file, somewhere but that I can't get to it through the database I'm connected to. I have admin rights, so that isn't it.

Answer 1

You can certainly write code to change user permissions.

It is 100% your choice to limit user permissions to membership in security groups. If you by sheer discipline follow this rule then the user’s permissions are NEVER stored in the database but only in the workgroup file. This thus makes management VERY easy. (hopefully this was done in your case).

You of course can choose to NOT follow the above rule and the result is now user permissions will exist in both the workgroup file and that of the database. This of course creates a real mess since then you cannot develop on a copy of the system off site for example. And management of security then becomes a real zoo.

I suggest a UI like this to select a user:

Then next screen is this to edit/assign that user to a security group.

The result is a VERY simple UI. So to give the user permissions such as being allowed to back date invoices etc. is now a simple mouse click to “select” the given group. That click thus gives the user in question “membership” in that security group.

The code to add a user to security group is this:

Public Function AddToSecGroup(strUserName As String, strGroupName As String)

 ' adds a user to a group
 Dim uUser      As user
 Dim ws         As Workspace

 Set ws = DBEngine.Workspaces(0)

 Set uUser = ws.Groups(strGroupName).CreateUser(strUserName)
 ws.Groups(strGroupName).Users.Append uUser
 ws.Groups(strGroupName).Users.Refresh
 ws.Groups.Refresh
 ws.Users.Refresh


End Function

The code to remove a user from a security group is this:

  Public Sub RemoveFromSecGroup(strUserName As String, strGroupName As String)

     ' remove user from a group
     Dim uUser      As user
     Dim gGroup     As Group

     Set uUser = DBEngine.Workspaces(0).Users(strUserName)

     uUser.Groups.Delete strGroupName
     uUser.Groups.Refresh


  End Sub

The code to change a user password is this:

 Function ChangePassword(ByVal strUser As String, _
                          ByVal strPwd As String) As Integer

     Dim ws As Workspace
     Dim usr As user

     Set ws = DBEngine.Workspaces(0)
     Set usr = ws.Users(strUser)
     usr.NewPassword "", strPwd

 End Function

And for group membership I use this:

  Public Function IsInGroup(UsrName As String, GrpName As String) As Boolean
  'Determines whether UsrName is a member of GrpName

      Dim grp As Group
      Dim IIG As Boolean
      Dim usr As user

      IIG = False

      For Each usr In DBEngine.Workspaces(0).Users
          If usr.Name = UsrName Then GoTo FoundUser
      Next

      GoTo IIG_Exit

 FoundUser:
      For Each grp In usr.Groups
          If grp.Name = GrpName Then IIG = True
      Next

 IIG_Exit:
      IsInGroup = IIG


  End Function

So I much suggest you organize these applications by security groups. The result is then it becomes one simple operation to give the sales group people use of some new feature in place of having to 25 sales people one by one to some form. By eliminating this “repetitive” process then you not need to wholesale update users security, but only assign users to security groups.

Using some logical security groups can often eliminate much of the need to update “many” users since you now just adding one thing to a given security group in which “many” users have membership. This “suggestion” applies to windows domain systems when managing desktops, or to Oracle or in this case a simple desktop system like Access.