Yesterday my network admin asked me if I could write a simple tool that could provide him with with a spreadsheet of what users had access to a certain database, and through what groups and roles. A couple of hours later I had created an agent that analyze the ACL and identify the users who can access it. The result is presented as a CSV file.
I am sharing the code below. It is pretty straight forward. As you can see, I am using lists to hold the data for easy export later to CSV. Run the code with the Lotusscript debugger turned on, and put a breakpoint before the CSV export starts, and you can see how the data is stored in the lists.
The function ExpandGroups() is called recursively to drill down, if the group contains additional groups. This function also use a lookup into a custom view, (LookupPeople), that we have in our corporate NAB, I am sure you can modify this code with something that works for you.
Enjoy! As always, use the code on your own risk, no warranties, etc.
%REM
Agent Export ACL Info to CSV
Created Nov 14, 2011 by Karl-Henry Martinsson/Deep-South
Description: Read ACl for specified database and create a
CSV file with info about each user's access (roles, groups,
delete access, access level).
%END REM
Option Public
Option Declare
Dim nab As NotesDatabase
Type RowData
role As String
group As String
username As String
deletedoc As String
level As String
levelno As Integer
End Type
Class GroupData
Public roles List As String
Public Sub New()
End Sub
End Class
Class PersonData
Public accesslevel As Integer
Public roles List As String
Public deletedoc As boolean
Public accessthrough List As String
Public Sub New()
me.deletedoc = False
End Sub
Public Sub SetAccessLevel(level As Integer)
If me.Accesslevel<level Then
me.AccessLevel = level
End If
End Sub
Public Function GetAccessLevelText()
Select Case me.AccessLevel
Case 0 : GetAccessLevelText = "No Access"
Case 1 : GetAccessLevelText = "Depositor"
Case 2 : GetAccessLevelText = "Reader"
Case 3 : GetAccessLevelText = "Author"
Case 4 : GetAccessLevelText = "Editor"
Case 5 : GetAccessLevelText = "Designer"
Case 6 : GetAccessLevelText = "Manager"
End Select
End Function
End Class
Class RoleData
Public groups List As String
Public Sub New()
End Sub
End Class
Sub Initialize
Dim ws As New NotesUIWorkspace
Dim session As New NotesSession
Dim db As NotesDatabase
Dim pview As NotesView
Dim pdoc As NotesDocument
Dim acl As NotesACL
Dim entry As NotesACLEntry
Dim person List As PersonData
Dim group List As GroupData
Dim role List As RoleData
Dim users As Variant
Dim row List As RowData
Dim cnt As Long
Dim groupname As String
Dim filename As String
Dim rowstr As String
Dim dbname As String
Dim servername As String
servername = InputBox$("Enter server for database:","Select Server")
If servername = "" Then
Exit Sub
End If
dbname = InputBox$("Enter full path of database:","Select Database")
If dbname = "" Then
Exit Sub
End If
set nab = New NotesDatabase(servername,"names.nsf")
Set db = New NotesDatabase(servername,dbname)
Set acl = db.ACL
Set entry = acl.GetFirstEntry()
Do While Not entry Is Nothing
If entry.Isgroup Then
If IsElement(group(entry.Name))=False Then
Set group(entry.Name) = New GroupData()
ForAll r In entry.Roles
group(entry.Name).roles(r) = r
End ForAll
End If
users = ExpandGroup(entry.Name)
If IsList(users) then
ForAll u In users
If IsElement(person(u))=False Then
Set person(u) = New PersonData()
End If
Call person(u).SetAccessLevel(entry.level)
If entry.Candeletedocuments Then
person(u).deletedoc = True
End If
person(u).accessthrough(entry.Name) = entry.Name
ForAll r In entry.Roles
If FullTrim(r)<>"" then
person(u).roles(r) = r
End if
End ForAll
End ForAll
End If
ElseIf entry.IsPerson Then
If IsElement(person(entry.Name))=False Then
Set person(entry.Name) = New PersonData()
End If
Call person(entry.Name).SetAccessLevel(entry.level)
If entry.Candeletedocuments Then
person(entry.Name).deletedoc = True
End If
person(entry.Name).accessthrough("ACL") = "ACL"
ForAll r In entry.Roles
If FullTrim(r)<>"" Then
person(entry.Name).roles(r) = r
End if
End ForAll
End If
Set entry = acl.GetNextEntry(entry)
Loop
ForAll g In group
ForAll rr In g.roles
If IsElement(role(rr)) = False Then
Set role(rr) = New RoleData
End If
role(rr).groups(CStr(ListTag(g))) = ListTag(g)
End Forall
End ForAll
' *** Time to export the data
cnt = 0
Set pview = nab.GetView("(LookupPeople)")
ForAll p In person
ForAll gg In p.accessthrough
groupname = gg
If IsElement(group(groupname)) And groupname<>"ACL" Then
ForAll r2 In group(groupname).roles
cnt = cnt + 1
row(cnt).username = ListTag(p)
row(cnt).group = groupname
If p.deletedoc then
row(cnt).deletedoc = "Y"
Else
row(cnt).deletedoc = "N"
End If
row(cnt).levelno = p.accesslevel
row(cnt).level = p.GetAccessLevelText()
row(cnt).role = ListTag(r2)
End ForAll
End If
End ForAll
End ForAll
filename = "c:\ACL_"
filename = filename & Replace(Replace(dbname,"/","-"),"\","-")
filename = filename & ".csv"
Open filename For Output As #1
rowstr = |"UserRole","Group","User Name","Del","Level","Access"|
Print #1, rowstr
ForAll x In row
rowstr = |"| & x.role & |",|
rowstr = rowstr & |"| & x.group & |",|
rowstr = rowstr & |"| & x.username & |",|
rowstr = rowstr & |"| & x.deletedoc & |",|
rowstr = rowstr & || & x.levelno & |,|
rowstr = rowstr & |"| & x.level & |"|
Print #1, rowstr
End ForAll
Close #1
MsgBox "ACL exported to " & filename,,"Finished"
End Sub
%REM
Function ExpandGroup
Created Nov 14, 2011 by Karl-Henry Martinsson/Deep-South
Description: Returns a list of users in specified group in NAB
%END REM
Function ExpandGroup(entryName As string) As Variant
Dim nabview As NotesView
Dim nabdoc As NotesDocument
Dim pview As NotesView
Dim pdoc As NotesDocument
Dim uname As NotesName
Dim tmplist As variant
Dim userlist List As String
If FullTrim(entryName) = "" Then
ExpandGroup = ""
Exit Function
End If
Set nabview = nab.GetView("Groups")
Set nabdoc = nabview.GetDocumentByKey(entryname)
If nabdoc Is Nothing Then
ExpandGroup = ""
Exit function
End If
ForAll n In nabdoc.GetItemValue("Members")
If Left$(n,3)= "CN=" Then
Set uname = New NotesName(n)
userlist(uname.Common) = uname.Common
Else
Set pview = nab.GetView("(LookupPeople)")
Set pdoc = pview.GetDocumentByKey(CStr(n))
If Not pdoc Is Nothing Then
userlist(CStr(n)) = CStr(n)
else
tmplist = ExpandGroup(CStr(n))
If IsList(tmplist) Then
ForAll t In tmplist
userlist(t) = t
End ForAll
End If
End If
End If
End ForAll
ExpandGroup = userlist
End Function
Open the database and go to any view. This is important, if you are not in a view, you don't have the correct menu items. Then hold down Ctrl-Shift and select "View" and "Go To…". This will open a dialog box that will display all views, including the hidden ones (in parenthesis). Now you can release the Ctrl-Shift keys and select the view.
