The explosion of viruses and hacker attacks in recent years has pushed security
concerns to the forefront of development and application design. Responding
to the need for security tools, SQL Server 2005 Analysis Services (SSAS) offers
a robust role-based security model for restricting access to Unified Dimensional
Model (UDM) objects and data.
You can leverage UDM dimension data security to protect dimension members and
the data associated with them. First, you need to know the fundamentals of dimension
data security, which I explain here. In a future article, I'll discuss two practical
approaches for implementing dimension data security: a factless fact table and
integrating with an external security service.
Setting Up Basic Dimension Data Security
Similar to other Microsoft and home-grown solutions, the UDM security model
leverages Windows security. The user is authenticated based on her Windows account
and authorized according to the security policies the administrator has set
up. To simplify security management, the UDM administrator can group Windows
users and groups into database roles. Next, the administrator assigns role permissions
to restrict the cube space the user is authorized to access.
While UDM allows you to control access all the way down to the cube cells,
most real-life security requirements are less granular. Typically, you'll need
to secure access to dimension members and data associated with these members.
Dimension data security allows you to do just that.
My sample Dimension Security project demonstrates how you
can set up basic dimension data security. You can obtain the Dimension Security project by going to http://www.sqlmag.com, entering
InstantDoc ID 95998, and clicking the Download the Code link. You'll
need the AdventureWorksDW database to process the Dimension
Security project's Adventure Works cube. You can install the AdventureWorksDW database from the SQL Server 2005 setup program.
In the Feature Selection step of the Setup wizard, click Advanced.
Expand the Sample Databases folder and select the AdventureWorksDW database. Alternatively, you can obtain the database by
downloading and installing it from SQL Server 2005 Samples and
Sample Databases (listed in the Related Resources box).
The simplest approach to securing dimension data is to explicitly select which
dimension members a given role is permitted to see. For example, the Adventure
Works cube has Reseller and Geography dimensions. Let's create a role whose
members will have access to Australian resellers only. Although this example
might not have any practical application, it demonstrates several important
aspects of how dimension data security works.
1. Open the Dimension Security project in Business Intelligence Development
Studio (BIDS) or in Visual Studio 2005. Right-click the Roles folder and choose
New Role to open the Role Designer. For the purposes of this demo, we won't
assign members to this role. In real life, use the Membership tab to assign
Windows users and groups to the role.
2. By default, UDM prevents the members of the role from accessing cubes in
the containing SSAS project (database). Switch to the Cubes tab, click the Access
column drop-down arrow, and select Read to grant the role access to the Adventure
Works cube.
3. By default, UDM grants the new role access to all dimensions in the database.
Verify this by going to the Dimension tab. You can control dimension access
at the cube or database level (recall that a dimension can be shared among cubes)
by using the Select Dimension Set drop-down box.
4. Switch to the Dimension Data tab, which is where you'll set up dimension
data security. As on the Dimension tab, you can set rights at the database or
cube level. Let's scope the dimension data security at the cube level.
5. Click the Dimension drop-down arrow and select the Reseller dimension under
the Adventure Works cube, as Figure 1 shows.
6. In UDM, a dimension is a container of attribute hierarchies. For example,
the Reseller dimension contains many attribute hierarchies, including the Country-Region
hierarchy. The Basics tab allows you to secure dimension members explicitly
by using one of two approaches—pessimistic or optimistic. With the pessimistic
approach, you deny everything except a set of allowed members called an allowed
set. The optimistic approach is the opposite—you use it to allow all
members except a set of denied members (a denied set). For more information
about allowed and denied sets, see "Introduction to Dimension Security in Analysis
Services 2005" (listed in the Related Resources box).