SecureTransport - Admin to Business Unit Mapping

SecureTransport (ST) users can be allocated to Business Units. A business unit allows you to define a settings that are common to a group of users. Deleting a business unit can be convoluted because they cannot be deleted  if there are accounts, applications or administrators associated with the business unit.

Unfortunately, the GUI does not provide a means to easily identify which administrative users have been assigned which business units. So you have to manually check each administrative account.

However if you made a note of the MySQL password set during the installation of ST you can figure the mapping between administrative accounts and business units using a SQL query.

1. CD C:\Program Files (x86)\Tumbelweed\SecureTransport\  
2. CD STServer\mysql\bin  
3. mysql –P 33606 –u root –p <mysql admin password>  
4. use st;  

Then enter the following SQL query

1. SELECT T12.name AS AdminAccount, T3.name AS BusinessUnit FROM (SELECT * FROM administrator AS T1 JOIN administrator_businessunit AS T2 ON T1.id=T2.administratorId) AS T12 JOIN businessunit AS T3 ON T12.businessunitId=T3.id;  

You can also use the script below if typing the above SQL query is a too much work ;-). The script requires that the MySQL Connector for .NET is installed on the server. You will also need to change the MySQL password to match the one you set when installing ST. The default port used by the MySQL database that comes with ST is 33060, if you changed this to something else during installation you'll need to modify the script accordingly.

The source code is also available on GitHub https://github.com/vijayjt/SecureTransportScripts

1. <#     
2.     .SYNOPSIS     
3.         Queries the SecureTransport MySQL database to produce a report on the mapping between administrative users and the business units which they can manage.   
4.           
5.     .DESCRIPTION     
6.         Queries the SecureTransport MySQL database to produce a report on the mapping between administrative users and the business units which they can manage. This information can be used to when deleting a business unit which can only be achieved if you   
7.             - Delete accounts within the business unit.  
8.             - Remove the business unit from applications. Since at this stage there are no plans to create applications this does not apply.  
9.             - Administrative accounts can be assigned business units which allow the administrator to modify settings for users in the business unit; you must un-assign the business unit from all administrators first.   
10.           
11.     .PARAMETER    
12.           
13.           
14.     .EXAMPLE     
15.           
16.           
17.     .NOTES    
18.         File Name  : get-admin-to-bu-mapping.ps1   
19.         Author     : Vijay Thakorlal  
20.         Requires   : PowerShell V2  
21.         To Do      :   
22.           
23.         IMPORTANT NOTES  
24.             1. You must change the password to the mysql database password set during installation of SecureTransport  
25.           
26. #>     
27.   
28. [CmdletBinding()]  
29. param()  
30.   
31. ############################  
32. # BEGIN VARIABLE DEFINITIONS  
33. ############################  
34.   
35. # Get the hostname  
36. $servername = $env:COMPUTERNAME  
37.   
38. # MySQL Connection String  
39. $MySQLServer = "localhost"  
40. $MySQLPort = "33060"  
41. $MySQLUser = "root"  
42. $MySQLPassword = "tumbleweed"  
43. $MySQLDatbase = "st"  
44. $connString = "Server=$MySQLServer;port=$MySQLPort;Uid=$MySQLUser;Pwd=$MySQLPassword;database=$MySQLDatbase;"  
45.   
46. # HTML Report File Location  
47. $ReportPath = "C:\scripts\" 
48. $ReportFile = Join-Path $ReportPath  "securetransport-admin-to-bu-report.html" 
49.  
50. ########################### 
51. # END VARIABLE DEFINITIONS 
52. ########################### 
53.  
54.  
55. Function Run-MySQLQuery  
56. { 
57.  
58.     Param( 
59.         [Parameter( 
60.             Mandatory = $true, 
61.             ParameterSetName = '', 
62.             ValueFromPipeline = $true)] 
63.             [string]$query,    
64.         [Parameter( 
65.             Mandatory = $true, 
66.             ParameterSetName = '', 
67.             ValueFromPipeline = $true)] 
68.             [string]$connectionString 
69.         ) 
70.     Begin  
71.     { 
72.         Write-Debug "Starting Begin Section"      
73.     } 
74.     Process  
75.     { 
76.         Write-Debug "Starting Process Section" 
77.         try  
78.         { 
79.             # load MySQL driver and create connection 
80.             Write-Debug "Create Database Connection" 
81.             # You could also could use a direct Link to the DLL File the path assumes x86 system and the system has  
82.             # a version of .NET framework lower than 4.0 if the system is running .NET Framework 4.0 repalce v2.0 with v4.0 in the path 
83.             # $mySQLDataDLL = "C:\Program Files\MySQL\MySQL Connector Net 6.5.4\Assemblies\v2.0\MySql.Data.dll" 
84.             # For x64 use 
85.             # $mySQLDataDLL = "C:\Program Files (x86)\MySQL\MySQL Connector Net 6.5.4\Assemblies\v2.0\MySql.Data.dll" 
86.             # [void][system.reflection.Assembly]::LoadFrom($mySQLDataDLL) 
87.             [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data") 
88.             $connection = New-Object MySql.Data.MySqlClient.MySqlConnection 
89.             $connection.ConnectionString = $ConnectionString 
90.             Write-Debug "Open Database Connection" 
91.             $connection.Open() 
92.               
93.             # Run MySQL Query 
94.             Write-Debug "Run MySQL Query $query" 
95.             $command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $connection) 
96.             $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command) 
97.             $dataSet = New-Object System.Data.DataSet 
98.             $dataAdapter.Fill($DataSet) | Out-Null 
99.             return $DataSet.Tables[0] 
100.             #$recordCount = $dataAdapter.Fill($dataSet, "data") | Out-Null 
101.             #return $dataSet.Tables["data"] | Format-Table 
102.  
103.         }        
104.         catch  
105.         { 
106.             #Write-Output "Could not run MySQL Query ( $query )" $Error[0] 
107.             Write-Host "Could not run MySQL Query" $Error[0]     
108.         }    
109.         Finally  
110.         { 
111.             Write-Debug "Close Connection" 
112.             $connection.Close() 
113.         } 
114.     } 
115.     End  
116.     { 
117.         Write-Debug "Starting End Section" 
118.     } 
119. } # END FUNCTION Run-MySQLQuery 
120.  
121.  
122. function is-null($value) 
123. { 
124.   return  [System.DBNull]::Value.Equals($value) 
125. } # END FUNCTION is-null 
126.  
127.  
128. #  
129. function get-Mapping() 
130. { 
131.     $Query = "SELECT T12.name AS AdminAccount, T3.name AS BusinessUnit FROM (SELECT * FROM administrator AS T1 JOIN administrator_businessunit AS T2 ON T1.id=T2.administratorId) AS T12 JOIN businessunit AS T3 ON T12.businessunitId=T3.id" 
132.     $QueryResults = run-MySQLQuery -connectionString $connString -query $Query 
133.      
134.     $TempHash = @() 
135.     $ObjProps = @() 
136.      
137.     foreach ( $row in $QueryResults ) 
138.     { 
139.         $ObjProps = @{'Admin Account'=$row.AdminAccount; 
140.             'Business Unit'=$row.BusinessUnit;} 
141.         $object = New-Object –TypeName PSObject –Prop $ObjProps 
142.         $TempHash += $object 
143.     } 
144.      
145.     return $TempHash 
146. } # END FUNCTION get-Mapping 
147.  
148.  
149.  
150.  
151. $head = @' 
152. <title>SecureTransport Report</title> 
153. <style> 
154. body { background-color:#dddddd; 
155.        font-family:Verdana; 
156.        font-size:12pt; } 
157. td, th { border:1px solid black; 
158.          border-collapse:collapse; } 
159. th { color:white; 
160.      background-color:black; } 
161. table, tr, td, th { padding: 2px; margin: 0px } 
162. table { margin-left:50px; } 
163. a:link, a:visited { color: #6600ff; text-decoration:none;} 
164. a:hover  { color: #ff4b33; text-decoration:underline;} 
165. table {  
166.     font-family: Verdana;  
167.     border-style: dashed;  
168.     border-width: 1px;  
169.     border-color: #FF6600;  
170.     padding: 5px;  
171.     background-color: #FFFFCC;  
172.     table-layout: auto;  
173.     text-align: center;  
174.     font-size: 10pt;  
175. }  
176. table th {  
177.     border-bottom-style: solid;  
178.     border-bottom-width: 1px;  
179. }  
180. table td {  
181.     border-top-style: solid;  
182.     border-top-width: 1px;  
183. }  
184. </style> 
185. '@ 
186.  
187. $rundate = Get-Date 
188.  
189. $precontent = @"  
190. <h1>SecureTransport Admin Accounts Assigned to Business Units</h1>  
191. <br />  
192. <table>  
193. <tr><th>Computername</th><td>$($servername)</td></tr>  
194. <tr><th style="text-align:left">Run Date</th><td>$($rundate)</td></tr>  
195. </table>  
196. <br />  
197. "@ 
198.  
199. ########################## 
200. # BEGIN MAIN SCRIPT LOGIC 
201. ########################## 
202.  
203. Write-Output "" 
204. Write-Output "Starting user account audit script run at: $(Get-Date)"  
205. Write-Output "" 
206. Write-Output "Report will be written to $ReportFile" 
207. Write-Output "" 
208.  
209. Write-Output " " 
210. Write-Output "Generating HTML report" 
211. Write-Output " " 
212.  
213. $MappingHash = get-Mapping 
214. $HTMLFragment = $MappingHash | ConvertTo-Html -As Table -Fragment | Out-String 
215.  
216. ConvertTo-HTML -head $head -PostContent $HTMLFragment -PreContent $precontent > $ReportFile 
217.  
218. Write-Output " " 
219. Write-Output "Script finished and report produced at $(Get-Date)" 
220. Write-Output " "  
221.   
222. $HTMLFragment = $null  
223. $MappingHash = $null  
224.   
225. [GC]::Collect()  
226.   
227.   
228. ##########################  
229. # END MAIN SCRIPT LOGIC  
230. ##########################