security on record level 1

[robertsquestion]

Hi,

I'm using an Access 2003-database that contains table T_Orders. Some fields in this table:
Order_Date
Article_number
Article_group
Quantity

I've created an administrator-user in the .mdw-file who can administer the database. Besides that I've also created a user-group in the mdw-file that can read all records in the database and one usergroup that can read& write all records in the database. That's all working fine.

But now I've got another user-group that should be able to:
- read the records from T_orders where Article_group = A and B
- read & write the records from T_orders where Article_group = C and D

All users are using the same forms/reports, but should be able to use it according to authorization as mentioned above.

I hope that anyone knows if there is a possiblity to set it up like this. Thanks in advance for your help!

Regards,
Robert
The Netherlands

 

[FancyPrairie]

First, create a function that returns true/false if the user belongs to a specific group. Then create a query that only pulls records belonging to the specific group(s). Then set your recordsource of the form/report to the query. Something like this:

Select * from T_Orders Where (MemberOfGroup("YourNewGroup")=true) and ((Article_group="A") Or (Article_group="B") or (Article_group="C") or (Article_group="D"))

In the OnCurrent method of the form, if Article_Group = "A" or "B", set the AllowAdditions, AllowDeletions, and AllowEdits properties of the form to false, else set them to true.

Here's some code that will tell you whether or not a user is a member of a specific group or not.

Option Compare Database
Option Explicit

Function MemberOfGroup(strGroupName As String, _
              Optional varUserName As Variant, _
              Optional varDbName As Variant) As Boolean
                         
    Dim rst As New ADODB.Recordset
    Dim bolMOG As Boolean
    Dim strSQL As String
    Dim strUserName As String
    Dim strDbName As String
    
    On Error GoTo ErrHandler
    
    If (IsMissing(varUserName)) Then strUserName = CurrentUser Else strUserName = varUserName
    If (IsMissing(varDbName)) Then strDbName = SysCmd(acSysCmdGetWorkgroupFile) Else strDbName = varDbName
    

    If (strDbName = SysCmd(acSysCmdGetWorkgroupFile)) Then
        strSQL = "SELECT Count(*) AS IsMemberOfGroup FROM (MSysAccounts AS MSA INNER JOIN MSysGroups AS MSG ON MSA.SID = MSG.GroupSID) INNER JOIN MSysAccounts AS MSA1 ON MSG.UserSID = MSA1.SID IN '" & strDbName & "' "
        strSQL = strSQL & "WHERE (((MSA1.Name)='" & strUserName & "') AND ((MSA.Name)='" & strGroupName & "') AND ((MSA.FGroup)<>0) AND ((MSA1.FGroup)=0));"  '));"
    Else
        strSQL = "SELECT Count(*) AS IsMemberOfGroup FROM (MSysAccounts AS MSA INNER JOIN MSysGroups AS MSG ON MSA.SID = MSG.GroupSID) INNER JOIN MSysAccounts AS MSA1 ON MSG.UserSID = MSA1.SID IN '" & SysCmd(acSysCmdGetWorkgroupFile) & "' "
        strSQL = strSQL & "WHERE (((MSA1.Name)='" & strUserName & "') AND ((MSA.Name)='" & strGroupName & "') AND ((MSA.FGroup)<>0) AND ((MSA1.FGroup)=0) AND (([MSA1].[Name] & MSA1.SID)=(SELECT MSA1.Name & MSA1.SID as UniqueID "
        strSQL = strSQL & "FROM (MSysAccounts AS MSA INNER JOIN MSysGroups AS MSG ON MSA.SID = MSG.GroupSID) INNER JOIN MSysAccounts AS MSA1 ON MSG.UserSID = MSA1.SID IN '" & strDbName & "' "
        strSQL = strSQL & "WHERE (((MSA1.Name)='" & strUserName & "') AND ((MSA.Name)='" & strGroupName & "') AND ((MSA.FGroup)<>0) AND ((MSA1.FGroup)=0));"  ' )));"
    End If
    
    On Error Resume Next
    rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    If (Err.number <> 0) Then
        Err.Clear
        bolMOG = False
    Else
        bolMOG = rst.Fields("IsMemberOfGroup").Value
        rst.Close
        Set rst = Nothing
    End If
    
    MemberOfGroup= bolMOG
    
ExitProcedure:

    Exit Function
    
ErrHandler:

    Err.Raise Err.number, "MemberOfGroup", Err.Description
    Resume ExitProcedure

End Function

 

[robertsquestion]

Thanks Fancy Prairie, I'll give it a try!