Security

TSQL Tuesday #63 – How do you manage security

Posted by Jens Vestergaard on in Community, Programming, tsql2sday 3 Comments

 

TSQL Tuesday

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. 🙂

 

Loading

SSMS Quick Tip

Posted by Jens Vestergaard on in Programming 0 Comment

If you ever need to test Analysis Services roles, you can easily do so either by browsing the cube or by means of a query.

When browsing a cube through SQL Server Management Studio (SSMS) or Bisuness Intelligence Developer Studio (BIDS), you can select one or more roles to be enforced on data, by clicking the little “user” icon in the top left corner of the cube browser:

You will then be able to select on or more of the roles available on the database via a dialog:

This way of browsing roles has the following pros and cons:
Pros: Visual confirmation of PC Hierarchies
Cons: A lot of clicking top verify permission integrity

The other way of testing roles is through SSMS is by creating an MDX query. The trick is to click the options button when the connection dialog is active. The last tab allows you to specify additional connection parameters. The Roles property is described in detail at technet. Basically it’s a comma seperated list of roles (case sensitive). The trick is showed in the screendump below:

Pros: Custom queries
Cons: No visual confirmation of integrity, can be hard to visually confirm permissions on a PC hierarchy as an example

Loading

Analysis Services Dimension Security – via AMO

Posted by Jens Vestergaard on in Programming 1 Comment

As part of a portal our team is currently developing, I needed a way to push permissions set in the portal onto the Analysis Services instances present in the system. A big part of the solution is what we call meta-data driven BI, which means the portal acts as a toolbox of common data warehouse best practices driven by meta-data. Hence cubes are represented by the name of the server, database and cube, as three columns in a table called Cubes. All this meta-data is marshalled by up to several types of user profiles. The data steward has his domain, the BI consultant another, the end-user a third and so on. The portal tries to embraces every step of the process, from raw text files to flashy end-user reports.
We’re not quite there yet, but well on our way. In short, we’ve got ourselves a data staging area (DSA), an enterprise data warehouse (EDW), traditional portal administration functions, data marts (DM) on the back of that and now finally we can push permissions onto Analysis Services. It’s not the first time I’ve crossed blades with Analysis services Management Objects (AMO), and that saved my butt more than once. The trick is that from the portal administrators perspective, a user is denied any access by default, in Analysis Services it’s the other way around. If nothings defined, your free to browse your eyelids off.

Maybe it’s appropriate to adress the dynamic external assembly solution that exists. See an example by Chris Webb (blog|twitter) and read the main reason why we did not choose this path.

As an extension to the way permissions work in Analysis Services, the design and implementation should be able to handle what is refered to as Down-Permissions, Up-Permissions and UpDown-Permissions.

Down-Permissions are best described as a member in the allowed set, with Visual Totals checked. See the detailed description of Visual Totals here.

Example (from a modified AdventureWorks database/cube):

Up-Permissions is a type of permission the is constructed in such a way, that the role has access to the member in question and all that members ancestors, and ancestors only not their siblings. Visual Totals is not checked. So data is aggregated on the ancestors, so at top level the role will see the grand total.

Example:

UpDown-Permissions is a combination of the two former types. This means deined access to parent siblings, but full path to root, and Visual Totals not checked.

Example:

All this is set through the portal, by clicking a mouse and selecting the permission you want to grant. Something even some super-users could be trusted. No need for knowing the nitty-gritty details of MDX or anything like that. See actual screendump, sorry for the language in the screen shot, but I think you get the point all the while:

 

Loading

How to setup basic dimension security in SSAS

Posted by Jens Vestergaard on in Programming 1 Comment

SQL Server Analysis Services offers the possibility to create roles, whereby you can manage the access certain users will have to data in the cube(s) on the database. The following is a basic tutorial on how to create a role Limited Access, used to limit the access to the Product dimension of the Adventure Works cube.

First we right-click the Roles collection on the database and select New Role:

Then we give the role a meaningfull name and perhaps a description to distinguish it even more:

Next we add the members of the role, eg. the users:

We can add as many users as we like, even groups to let he IT Administration handle the permissions:

We then need to decide which cube(s) the role is granted access to:

We jump a few steps here in order to get to the basic part of setting dimension security in SSAS 20008.
Select the Dimension Data tab, and select which dimension(s) the role is to be restricted on:

This particullar role is limited to seeing only members Accessories and Bikes, hence Clothing and Components are prohibited:

In order for us to test this newly created role, we can select to browse the cube using the credentials of the role.
This is done in the browse cube dialog:

We then select our new role:

And voilà, the permissions show up as expected:

For more advanced stuff on security in cubes, please refer to: http://www.ssas-info.com/analysis-services-articles/51-security

Loading