TSQL Tuesday #63 – How do you manage security

This month’s T-SQL Tuesday is hosted by Kenneth Fisher (blog | twitter) and the topic is security.

Security is one of those subjects that most DBAs have to deal with regardless of specialty. So as something we all have to work with at some point or another what are some tips you’d like to share? What’s the best security design? You’ve picked up a legacy system and the security is awful, how do you fix it? Any great tools out there you’d like to share? Hate it or love it I’m betting we all have something to say.

As others in the community contribute in their own way, in their field of expertise, I’d like to chip in with some of my own observations and lessons learned through my years of working with Analysis Services (SSAS). Here goes my first #TSQL2SDAY blog post.

Having worked with the security model in SSAS and having presented on the topic a couple of times, this topic is really something I have had my hands on. I have previously posted several blog posts on the topic and this blog post will introduce a Microsoft Security Management Tool named Forefront Identity Manager (FIM) which is a  Tool that enables self-service identity management for business users. Yeah, you heard that right. So how does that fit in with cubes and SSAS?

In every SSAS database there is a Collection of roles. Each role can be assigned specific permissions in terms of cube access (r/w), drill-through, dimension and cell access. The magic link between the SSAS roles and FIM are Active Directory (AD) Groups. Each role can have one-to-many members, which can be specific (local) Users or Security Groups. Through FIM this allows the users provisioning and de-provisioning access through an semi-automated approach.

Personally, I haven’t even scratched the surface of FIM, but for the purpose of letting the business decide who gets access to what, and who doesn’t, it was well worth raising this flag. In the end, the business is happy to feel in control, and you are happy that the business is locked down to what options you expose through the cube. In the end, the mechanism of creating and managing the roles within the cubes, still reside on developer/administrative side, e.g. IT (and not Business).

If you don’t already know about Forefront Identity Manager I urge you to take a look at the capabilities. I bet you’ll be pleasantly surprised. 🙂