I've been doing some R&D work on a new integrated security model for our database/analysis services lately, with one of the main requirements being a system where security changes made to the Sql server database will automatically propogate through to the SSAS environment.
To illustrate this point, I employ the use of a departmental structure. For instance, if in the database, a user gets shifted to a different department, he/she should have those changes automatically replicated when they log into the SSAS server and view cube data. Ie: they should be restricted to view departments and their children that they belong to.
The thing about SSAS is that it sits alongside Sql Server, rather than a subset; and since it's seen as a seperate entity that can be made up of a number of databases, subsequently don't inherit any of their rights. Roles can be set up within SSAS, which we can use to mirror the security structure of the Sql Server. As long as windows authentication is used in Sql Server, a flexible model can then be created to manage both servers.
Coming back to the departmental structure, how can a role be created that restricts the user to viewing his departments and it's children? This can be achieved through by creating a Department dimension within SSAS, and creating a new role that returns an allowable member set via a CLR Stored Procedure that connects to the original SQL Data source.
Creating a CLR Stored proc for SSAS is pretty simple. All you need to do is create a new class library, and within a new class do something like:
public static Set GetAllowedDepartments()
{
SetBuilder sBuilder = new SetBuilder();
using (SqlConnection conn = new SqlConnection(@"data source=<data source>;initial catalog=<orig database>;integrated security=sspi;"))
{
conn.Open();
SqlCommand comm = new SqlCommand("select departmentId from vwDepartment", conn);
SqlDataReader reader = comm.ExecuteReader();
TupleBuilder tBuilder = new TupleBuilder();
while (reader.Read())
{
int departmentId = reader.GetInt32(0);
Member m = new Expression(
string.Format(
"[Department].[Hierarchy].&[{0}]",
departmentId)
).CalculateMdxObject(null).ToMember();
tBuilder.Add(m);
}
sBuilder.Add(tBuilder.ToTuple());
reader.Close();
conn.Close();
}
return sBuilder.ToSet();
}
Two things to note:
- you MUST connect to the sql server using SSPI, as when it connects to the database, you want it connecting as the current user who's on SSAS
- the vwDepartment view filters all rows based on the current logged in user, and hence only returns departments based on those that the user has access to
Compile that into a dll, and on the SSAS side of things, go to the assemblies folder and:
- New Assembly
- File name -> browse to the dll
- Include debug info if you want to attache the debugger later on
- Permissions -> external access (as it accesses an external source)
- Impersonation -> use credentials of the current user
You should now have access to the stored proc within any of your MDX scripts. What is needed is to restrict department rows to the a certain user role. Open up the role (or create a new one) for the one you want to restrict and:
- Dimension Data
- Dimension -> Department
- Attribute -> Hierarchy
- Allowed Member Set -> <Your Assembly Name>.GetAllowedDepartments()
Save that and try browsing the cube as that role. If you drop the department dimension in, you should see that only the departments the user has access to are visible.
This model also relys on the fact that members of the Department dimension rely on a single ID attribute, named with the department name; as well as kerberos authentication being properly set up.
I'm hoping that this sort of model will deliver the flexibility and centralization needed to cut out the maintenance and effort needed by both developers and administrators of the system. So far, it's looking pretty good.
posted @ Thursday, December 20, 2007 1:12 PM