How to produce a security audit of roles and access

Hi

We want to produce a report, on who has what access to what project. What would be the best way of doing this?
SQL query, api, restapi?

thanks - peter

Hi Peter,

Thanks for getting in touch.

If you’re needing a quick one-off report, then a SQL query would work. You can query your users and the teams/roles they belong to by having a look at the following tables:

select * from [User]
select * from [UserRole]
select * from [Team]

If some teams are restricted to certain projects, those tables (especially the Team table) will help you identify them.

If you want something you can re-run without having to jump into raw-SQL, the API would be the way to go.

Our users and teams documentation should help in understanding the different roles of Octopus users. Our REST API documentation may also help if you’d prefer an API-based approach.

Hope this helps!

Cheers
Mark

This db is pretty weird. You’ve got arrays in the fields. Makes it very hard to do joins.

e.g.
|Users-60|Users-68|Users-62|

Hi Peter,

Octopus was designed to avoid the need for joins as much as possible. We have a blog post with some insights as to why we use SQL Server as a document store in this way.

Depending on the structure of the report you’re trying to generate, the API may be more suited to what you’re trying to do. Each TeamResource object returned by the API includes a collection of user ids and project ids. Eg. http://[your-octopus-server]/api/teams. You could use this, combined with results from the /api/projects or /api/users api to then filter those collections in-memory via PowerShell.

Or, avoiding the API, you could use multiple SQL queries and the LIKE operator to find results within those piped arrays. Eg. select * from [Team] where [ProjectIds] LIKE '%|Projects-123|%'

Hope this helps

Cheers
Mark

Wrote this, thought i’ll post it here in case someone else finds it usefull

Creates a report like so
USER = some@somewhere.co.uk
Team = Development_team : Groups = {“ExternalSecurityGroups”:[],“UserRoleIds”:[“userroles-projectlead”,“userroles-projectdeployer”]}
Projects = ProjectName
Team = Everyone : Groups = {“ExternalSecurityGroups”:[],“UserRoleIds”:[]}

package main.java;

import java.sql.*;
import java.util.Spliterators;
import java.util.stream.StreamSupport;

import org.apache.commons.dbutils.ResultSetIterator;

public class GetListOfOctopusDeployUsers {

	static final String userName = "sa";
	static String passwords = password.password3;
	static final String url = "jdbc:sqlserver://servername;databaseName=Octopus;user=" +userName +";password=" + passwords;
	
	public static void main (String args[]){
        GetListOfOctopusDeployUsers OD = new GetListOfOctopusDeployUsers();
        	
        String SQL = 	"SELECT u.Id, u.UserName from [Octopus].[dbo].[User] u;"; 
	    /*
	     * The various foreach here, in effect creates our own Join using java. 
	     * The reason for this is because the database has array of values, which means we can't use joins, 
	     * or we need to create a function to split the values first before we can do a join. 
	     */
        
        StreamSupport.stream(Spliterators.spliteratorUnknownSize(
              new ResultSetIterator(OD.runSql(url, SQL)), 0), false).forEach(s -> {
            	  System.out.println("USER = " + s[1]); 
            	  String SQL2 = "SELECT m.Name, m.JSON, m.ProjectIds from [Octopus].[dbo].[Team] m Where m.MemberUserIds like \'%" + s[0].toString() + "%\';"; 
            	  StreamSupport.stream(Spliterators.spliteratorUnknownSize(
            			  new ResultSetIterator(OD.runSql(url, SQL2)), 0), false).forEach(d -> {
            				  System.out.println("\tTeam = " + d[0] + " \t: Groups = "+ d[1]); 
            				  String[] pete = d[2].toString().split("\\|",  -1);
            				  
            				  for(String peter : pete){
            					  if (!peter.isEmpty()){
		            				  String SQL3 = "SELECT p.Name from [Octopus].[dbo].[Project] p Where p.Id like \'%" + peter + "%\';"; 
		                        	  StreamSupport.stream(Spliterators.spliteratorUnknownSize(
		                        			  new ResultSetIterator(OD.runSql(url, SQL3)), 0), false).forEach(e -> {
		                        				  System.out.println("\t\tProjects = " + e[0]);
		                        			  });
            					  }
	                          }		  
            			  });
              });
       	}
	
	private ResultSet runSql(String url, String SQL){
		 try {
	         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");   
	         ResultSet rs = DriverManager.getConnection(url).prepareStatement(SQL, ResultSet.TYPE_SCROLL_INSENSITIVE, 
	        		  ResultSet.CONCUR_READ_ONLY).executeQuery();
	         
	         return rs;
		 }
		 catch (Exception e) {  
			 e.printStackTrace();  
		 }
		return null;  
	}
}