Data-driven Security in Microsoft SQL Server 7.0 OLAP Services

Mosha Pasumansky (moshap@microsoft.com)

Aug 1999

 

Introduction

The first version of Microsoft OLAP Services offers role-based security. However, this degree of security is applied only on the cube level. With the Service Pack 1 release, a finer cell-level security is implemented that now controls access to the individual cells. This technical article describes different cell-level security scenarios and the implementations that can be applied using this cell-level security enhancement.

Contents

·         Introduction       

·         Management of users    

·         Sales rep scenario        

·         Data driven security solution    

·         Handling hierarchies      

·         Conclusion

·         Finding more information

Management of users

OLAP Services doesn’t manage users on its own. Instead, OLAP Services leverages Windows NT® to define users and groups in NT domain(s). OLAP Services defines role objects as the mechanism for access control. All users belonging to the same role share the same access restrictions. Role objects are created and managed programmatically through DSO (Decision Support Objects) or through the user interface in OLAP Manager. However, cell security statements can only be defined programmatically through DSO.

Sales Rep Scenario

An organization sells its products through its force of sales representatives. Every sale, therefore, is associated with a sales rep ( for example, each sales transaction will contain the identity of the sales rep who was responsible for the transaction.) Therefore, the Sales cube contains the dimension SalesReps. A typical security restriction in this scenario is that every sales rep should be allowed to see only sales figures of transactions for which s/he was responsible. The standard approach is to define a role for every sales rep and to set a cell security restriction for every one of these roles. In an organization with a large and dynamic sales force, it might be difficult to manage these roles. There is a need for a data-driven security solution  — creating security restrictions that are part of the data rather than applied as a special mechanism.

Data driven security solution

The data driven security solution requires two steps. First, the dimension table of sales reps needs to be extended to contain another column for the domain login name (if it doesn't exist already). For example:

A new member property, DomainName, can be defined for appropriate levels in the dimension SalesReps.

The DBA needs to define only one generic role that covers all sales reps automatically. To restrict the access of specific sales rep to their specific sales trnsactions, the read cell security statement should be defined in this role as shown below:

UserName = SalesReps.CurrentMember.Properties( “DomainName” )

UserName is an MDX function supported by  OLAP Services. This function returns the string consisting of the domain and user name of the NT user currently logged in — for example, “LOSANG\moshap”, where “LOSANG” is the domain name and “moshap” is the user name.

The expression evaluates to TRUE if, and only if, the current member in the SalesReps dimension is the member that corresponds to the user currently logged in.

Handling hierarchies

The content of the SalesReps dimension is likely more complex than simply a list of sales reps. This dimension may contain the hierarchy of local sales managers, regional sales managers, etc. To allow a manager to view the sales figures of her subordinates, the cell read security statement can be modified as shown:

UserName = SalesReps.CurrentMember.Properties( “DomainName” )

OR

UserName=Ancestor(SalesReps.CurrentMember, SalesReps.[Local Manager]).Properties(“DomainName”)

OR

UserName = Ancestor(SalesReps.CurrentMember, SalesReps.[Regional Manager]).Properties(“DomainName”)

OR

UserName=Ancestor(SalesReps.CurrentMember, SalesReps.[Big Boss]).Properties(“DomainName”)

This expression assumes that there are four levels in the SalesReps dimension.

Sometimes the SalesReps dimension is organized by geography rather than reflecting the organization chart. In this design, the parent of sales rep is region rather than the sale rep's manager. In this situation, sales figures of each sales rep must still be protected from each other. To allow a sales rep to view comparative sales figures from other reps of all the hierarchies of regions the rep belongs to, the cell read security statement can be modified as shown:

Count( Filter( Descendands( SalesReps.CurrentMember, SalesReps.[Sales Rep Name] ), 

UserName = SalesReps.CurrentMember.Properties( “DomainName” ) ) ) > 0

This expression assumes that [Sales Rep Name] is the level in the SalesReps dimension.

In another scenario, the SalesReps dimension may a combination of geographical organization through a certain level with all other levels organized in a managerial structure. In this case, the two approaches demonstrated above can be combined.

Finding More Information

For more information about MDX, see Microsoft SQL Server OLAP Services Books Online.

For more information about cell-level security, see the OLAP Services Cell-level Security white paper.