Securing Your Data in Analysis Services - Professional SQL Server Analysis Services 2005 with MDX - page 998

members in the dimension for which security has been updated. Once the se-
curity information is cached on Analysis Services, you do not have a depend-
ency on the relational data source.
The External Function Approach
The member property approach and the secure measure group approach re-
quire appropriate dimensions and measure group processing to keep abreast
of changes. You can certainly setup proactive caching on the dimension and
measure group so that changes to security are immediately reflected.
However, processing does involve some cost. The external function approach
alleviates the problems of processing and ensures that only the most up-to-
date security restrictions are applied to the users.
In the external function approach you write a UDF or a .NET stored procedure
that will retrieve the list of locations the current user is authorized to access.
For example, the stored procedure can return the list of cities or states or
area that a specific user can access as an MDX set. This set is then defined
in the Allowed member set as the dimension security restrictions for the cur-
rent user. Analysis Services exposes the security permissions for .NET stored
procedures which restricts the stored procedures access on specific re-
sources such as accessing a network or creating a new file. The security per-
mission provides an extra level of code security so that your Analysis Ser-
vices is more reliable. There are no such security permissions that can be
defined for COM UDFs and you need to trust the programmer has written
good quality code. In addition to that you have the option of using the
ADOMD server object model in your .NET stored procedure to perform cus-
tom business logic which is not available if you code a COM UDF. You still
need to maintain a relational table that provides information on a user's ac-
cess to locations via a column. In this example you will use the security table
that was used in the measure group approach. Since the security table only
contains ids of employees and location you will need to make joins to employ-
ee and location tables in the relational database to retrieve the right location
members. However you can create a new table that will have the list of loca-
tions for employees based on the login information. Such a table will probably
have the columns login name, city, state, country, location id and access
where the column access has values 1 or 0 that indicate if the user has ac-
cess or not to the specified location.