In Access 2003, is there a way to get a list of table privileges (read, update, insert, delete) for a given group as defined in the security wizard.
All I can find around is security relationships between users and groups. That's the easy part.
Well, this is what I quickly hacked as a starting point.
All I can find around is security relationships between users and groups. That's the easy part.
Well, this is what I quickly hacked as a starting point.
Code:
Option Compare Database
Option Explicit
Function GetTablePrivilage()
Dim cat As ADOX.Catalog
Dim perm As Long
Dim Ws As Workspace
Dim i As Integer
Dim t As Integer
Dim db As DAO.Database
Dim mytbl As TableDef
Set db = CurrentDb
Open "C:\privileges.html" For Output As #1
Print #1, "<html><style>"
Print #1, "body{margin:0; padding:0;}"
Print #1, "table{border:1px solid black; border-collapse:collapse;}"
Print #1, "td {border:1px solid black; padding:0 4px 0 4px;}"
Print #1, "</style>"
Print #1, "<table>"
Set Ws = DBEngine.Workspaces(0)
Set cat = New ADOX.Catalog
With cat
For i = 0 To Ws.Groups.Count - 1
Print #1, "<tr><th colspan='3'>"
Print #1, "Group " & Ws.Groups(i).Name
Print #1, "</tr>"
For Each mytbl In db.TableDefs
If Left(mytbl.Name, 4) <> "MSys" Then
.ActiveConnection = CurrentProject.Connection
perm = .Groups(Ws.Groups(i).Name).GetPermissions(mytbl.Name, adPermObjTable)
If perm <> 0 Then
Print #1, "<tr><td>" & mytbl.Name & "</td><td>" & perm & "</td><td>" & privilegeToText(perm) & "</td></tr>"
End If
End If
Next mytbl
Next i
End With
Print #1, "<html><table>"
Close #1
End Function
Function privilegeToText(privilege As Long) As String
Select Case privilege
Case -2147482624
privilegeToText = "read data"
Case -1073740800
privilegeToText = "update data"
Case -2147449856
privilegeToText = "insert data"
Case -2147417088
privilegeToText = "delete data"
Case 1024
privilegeToText = "read design"
Case -1073672960
privilegeToText = "modify design"
Case -1072853760
privilegeToText = "administer"
Case -1073675264
privilegeToText = "delete & update data"
Case -1073642496
privilegeToText = "read, update, insert, delete"
Case -2147384320
privilegeToText = "read, insert, delete data"
Case -1072706304
privilegeToText = "Administer"
Case -1073708032
privilegeToText = "read, update, insert"
Case 147456
privilegeToText = "No privilege"
Case Else
privilegeToText = "NOT DEFINED"
End Select
End Function
Comment