Securing the Data | The MicrosoftВ Data Warehouse Toolkit: With SQL ServerВ 2005 and the MicrosoftВ Business Intelligence Toolset

Now that weve done the basics, we come to the most interesting part of the security plan: securing the data while making it available for users to query.

Providing Open Access for Internal Users

We strongly encourage you to develop a data access policy that is fairly open for corporate users. The best approach is to start from the position that all data should be available to internal users; any exceptions should be justified.

Weve worked with organizations that approach the problem from the other direction. Even internally, these folks natural reaction is to make data available only on a need to know basis. They say that the sales manager in one region cannot see sales numbers for other regions. The problem with this mindset should be obvious: A sales manager cant assess her regions performance outside the context of the rest of the company. She may think a 10 percent growth in sales is a great number, until she realizes that all other regions saw 15 percent. The more information you hide, the less valuable your DW/BI system is going to be.

UNEXPECTED VALUE OF OPEN ACCESS

One of our clients had an enlightened data access policyor perhaps, as a tech startup, they hadnt gotten around to drafting a more restrictive policy. At any rate, they experienced the power of open access. A Customer Care agenta kid out of high schoolwas poking around the data and uncovered a trend. A certain kind of trouble ticket was associated with a specific suppliers hardware, hardware which, as it turned out, was not manufactured to specification. Our client wrung several million dollars out of the supplier, and averted unsatisfactory user experiences for many customers. This for a startup, for whom several million dollars and customer satisfaction were tremendously important.

Many organizations would say a Customer Care agent has no business getting a global view of the data. It sounds reasonable to limit an agents view only to specific tickets. The upside is unknowable in advance, but weve seen it happen often. The doomsday scenarios are easier to see, but many people have a tendency to overstate both the likelihood and potential financial downside of data getting out of the hands of those who need to know.

DATA ACCESS POLICY

Depending on your organization, the data access policy doesnt have to be a long statement. A reasonable statement would say something like:

Open access to administrative information is provided to employees for the support of corporate functions. Inappropriate use of information is a violation of your employment agreement. Default access is: Open Access to all employees, except for data elements on the Restricted Access list. Restricted Access is a designation applied to certain data elements, and limits access because of legal, ethical, or privacy issues. Access to elements so designated can be obtained with the approval of the designated data trustee. Any request to restrict employee access must be documented to Data Administration by the designated data trustee. Any employee denied access may appeal the denial to Data Administration.

The ideal situation is to have very little truly sensitive information, like employee salaries or social security numbers, in the DW/BI system. Just dont bring it in. It should go without saying that any data around employee compensation is highly sensitive. In a health care environment, details about an individual patient may be equally sensitive, and access is highly regulated as well. Were not saying that you should make such sensitive information widely available. Rather, identify the information thats sensitive and document it as an exception to the general rule of data availability.

In order for an open access policy to work, you must:

Gain executive sponsorship for the approach. You cant change corporate culture on your own. Many healthcare and law enforcement organizations are hopelesslyand justifiably paranoid about data security.
Develop a system use policy statement, which users must sign before gaining access. Set up a mechanism for ensuring users sign this document before accessing the DW/BI system.
Confirm that executives are willing to carry out any sanctions against security violations implied in the policy statement.
Gain executive agreement on the list of sensitive data elements.
Review the security policy at the beginning of every training class.
Publish (on the BI portal) the detailed security policy, including the list of sensitive data elements.

Reference

You can get a good starting draft for a complete data access policy from the Internet. Many universities and government agencies post their policies online. Search for data access policy.

Itemizing Sensitive Data

Whether you approach the problem from a mindset of most data is available or most data is sensitive, you need to develop a matrix of what data is hidden (or available), and to whom. As youre developing your data sensitivity matrix, remember that the vast majority of system use is at aggregated levels, where sensitivity is usually less.

Our primary concern is read-only access to data. Any writing activities, like developing forecasts and budgets , should be securely managed by an application. Specify the level at which aggregated information becomes available. For example, most people cant see sales by salesperson, but anyone can see aggregated sales at the region or district level, and corporate-wide.

The related sidebar illustrates a sample format of a Data Sensitivity Document highlighting some of the issues that you should be concerned about.

There is a subtlety associated with allowing data access at aggregate levels, but not at the detailed level. Returning to our discussion of sales by salesperson, what if we allow aggregate reporting at the district level, by gender? That could be a really interesting question, but what if a district has only one saleswoman? Anyone in the company could infer her sales figures. Theres no easy answer to the inferred data member problem, although we discuss this issue again in the upcoming sections.

Securing Various Types of Data Access

The more restricted data you have, the more difficult it is to provide ad hoc access to the DW/BI system. Look back at the data sensitivity matrix and think about how to implement open access at aggregate levels and restricted access at detailed levels. The easiest way to do that is through an application like a report. Define aggregate-level reports that everyone can see, and limit access to reports that contain sensitive information.

If you allow ad hoc access to the information, you may need to apply complex access rules in the database. This is difficult in the relational database, as we discuss in the section Relational DW Security. This is a place where Analysis Services really shines: Its possibleand really not that difficultto meet a wide range of access scenarios by using Analysis Services permissions. In particular, the tool addresses the difficult problem of hiding detailed data but publishing aggregated data for ad hoc access.

SAMPLE DATA SENSITIVITY DOCUMENT

Weve never been able to come up with a structured format, like a spreadsheet, that captures all the nuances of what is accessible, at what level, and by whom. Write a document thats as clear and concise as possible. Target this document to the business users because you need to get business agreement about the policies. Its helpful to align the sections by fact table (or set of fact tables), to simplify the process of turning this agreement into a set of database permissions.

SALES

Sales data is captured at the transaction line item level. For each sale, we know the customer, product, salesperson, and channel.

Restricted Access: None.
Open Access: Atomic and aggregated sales data, including any base measure and any calculation involving only sales data.
External Access: None. In the future, we may modify this policy to open access at an aggregate level to reseller partners .

SALES QUOTA

Sales quotas are stored for each salesperson, product category, and quarter.

Restricted Access: Atomic data at the salesperson level, including any physical and calculated measures based on salesperson. A salespersons quota information and bonus calculations are available only to:
- His or her manager, and up the reporting chain to the VP of Sales.
- VP of Sales administrative staff, with permission of VP of Sales.

Open Access: All physical and calculated measures, at the district level and above.
External Access: None.

HUMAN RESOURCES

Data warehouse Human Resources information includes employee attributes like name , current manager, home address, and marital status. Currently, there is no HR fact data; the only employee information is descriptive. Note that the descriptive information about employees is largely Restricted. Only a few fields have Open Access:

Open Access: Employee name, manager name, current department, current job title, office location, office phone.
Restricted Access: All other attributes of the employee, including home contact info and any personal information. Restricted attribute information is available to an employees manager, up the reporting chain, and to all HR staff.
External Access: None

Most client access tools (other than Microsoft Excel) contain some security features. Reporting Services and most third-party tools like Business Objects, Cognos, and Analysis Services-specific tools like Panorama and Proclarity all contain security features. You should carefully examine the security features of any client tool before you decide to rely on those features. If a user has login privileges to the underlying database (Analysis Services or relational), the security must be applied to the database objects. Otherwise , users could simply use Excel or any other client tool to create an ad hoc connection, log on, and browse restricted data.

Many front-end tools, including Reporting Services, canor even mustbe configured to use a shared report execution service account for access to the database server. If your front-end tool manages user security this way, you typically dont even grant database login access to the users. This is very secure, assuming youre exceptionally careful not to compromise the password to the report execution service account.

Note

If a user is not allowed to see certain data, then he must either be:

Denied logon privileges to the database containing that data, or
Denied read privileges to the data table, columns , or rows.

Client-based security is questionable security. Security through obfuscation is not security.

Most implementations have a variety of security requirements. Some reports are available for anyone in the company, some have limited access to the complete report, and some reports return a different result depending on who runs the report. Beyond reporting, you need to support some ad hoc access as well. You need to define some security in Reporting Services as well as the relational database and Analysis Services, to cover all these contingencies. Each component has several kinds of security options, which means you can probably figure out a way to do anything you want. But it also means that its hard to know where to begin. We try to simplify the problem by first discussing the different types of secure access. Next , we look at how you use SQL Servers components together to deliver the different levels of access.

Unrestricted Reports

An unrestricted report is one thats accessible to everyone in the company, or at least to everyone who can access the reporting portal. In general, the only security on an unrestricted report is whether the person trying to access the report is authenticated. This is handled by the operating system. Theres no SQL Server security involved. We like unrestricted reports because organizations benefit from broadly shared information. Unrestricted reports are easy to manage.

The Reporting Services report definition for an unrestricted report should be configured for everyone to use a shared connection. This is the default behavior for Reporting Serviceswhen you define a report, its normal for everyone who executes the report to use the same database credentials. Windows uses a users credentials to authorize access to the reporting portal.

Reporting Services reports can query either a relational database or an Analysis Services database (or both, but lets keep it simple). If the report accesses the relational data warehouse, the connection should use a service account that has read-only access. By service account we mean a Windows login (or database account if youre using SQL database security) that is not tied to a person. Instead, the account would be named something like ReportUser. Perhaps the report was designed using a DBAs personal account. Before the report is moved into production, modify it to use a service account with readonly privileges to unrestricted information.

Tip

Always use an account with the lowest possible level of permissions. Executing reports on a highly privileged account should be safe because the connection is encrypted, but why ask for trouble? Encourage or require your development team to use a shared data source for the report connection when they design a report. Many reports can share a data source, which contains the connection information including server, database, user name, and password. The credential information for a data source definition is encrypted for you.

Users who access the report do not need login privileges into the underlying database. The reports query runs under the service account, not under the users account.

Restricted Reports

A restricted report, like a public report, is predefined. Not everyone in the organization can access the report, but the report looks the same to everyone who does access it. For example, in a small company anyone in Human Resources can access employees personal information. Anyone at the executive level can access all corporate financial data.

Restricted reports are a good security model for all but the most sensitive data, other than data found in unrestricted reports. Its easy to implement in the reporting portal, and doesnt require a security layer within the underlying databases.

Restricted reports should use a shared connection, the same way that unrestricted reports do. In fact, it can be the same shared connection. Your security administrator can use Report Manager or Management Studio to define which users and groups have access to which reports and report folders. The security is not at the database level, but instead at the reporting system level.

As with unrestricted reports, business users dont need login privileges into the underlying database to run restricted reports. The reports query is executed by the ReportUser service account. Later in this chapter we describe how to restrict access to reports in Reporting Services.

Filtered Reports

A filtered report is a predefined report that returns a different result set depending on who runs the report. Filtered reports usually are also restricted, and are appropriate for the most sensitive data in your organization. The classic example is salary and job performance information, which is usually available only to an employees supervisor, or possibly to anyone up the management chain.

In order to implement a filtered report, you need some way to connect the users name to the list of people, organizations, accounts, and other data elements that theyre allowed to see. If the report is run on demand by the user, it must be executed dynamically with the users credentials; the report cant be cached. In this case, the query underlying the report needs to return the filtered result set. Alternatively, filtered reports can be pushed to users on a daily or weekly basis by emailing individualized reports.

Filtered reports are expensive to develop and maintain. You need to develop a security infrastructure, and maintain the list of what elements each user is privileged to access. You may need to write a simple application to help manage these processes.

Some organizations are dead set against allowing business users to log in to any relational database, including the data warehouse. This feels like superstition to us, as you can secure the database objects to different users. But lets not quibble. If youre committed to barring direct user login to the relational database, but need to deliver filtered reports, you have several options:

Define the report to use a stored procedure as its source query. Pass the users identification as an input parameter to the stored procedure. The stored procedure must perform the filtering, typically by joining to a table that contains a list of accessible rows for each user ID.
Use a data-driven subscription. This feature of Reporting Services Enterprise Edition is a cost-effective way to distribute individualized reports to many people, for example a monthly budget variance report for each managers area of responsibility. If the filtered report doesnt need to be run on demand, the data-driven subscription is a neat solution that can use system resources very effectively.

Reference
See the Books Online tutorial topic Creating a Data-Driven Subscription for detailed instructions on creating a filtered data-driven subscription.
If you have only a handful of filtered reports that are limited to only a few people, you can fake filtered reports by creating restricted reports. In other words, create different reports for each department, and grant access only to the appropriate version. This approach has obvious scalability and management problems, and isnt recommended except as a stopgap measure.

If you allow business users to log in to the underlying databasesAnalysis Services or the relational data warehouseyou must set up those databases to support ad hoc access. If a user can log in to a database, the database itself must protect access to its objects. Otherwise, users could attach any query tool to the system, and look at any data they want.

Ad Hoc Access

Ad hoc access means the user is allowed to leave the boundaries of predefined reports to create new queries, reports, and analyses. Up to this point, its been possible to avoid giving database login privileges to users. But a user who needs ad hoc access really needs login privileges.

The best place to perform ad hoc analysis is within Analysis Services. And Analysis Services contains a rich security model that lets you define very fine-grained security profiles, while still delivering reasonable query performance. Analysis Services security has several very nice features. You can:

Use dimension-based security to cut down the apparent size of a dimension. In other words, users wont even see dimension members like product names whose data theyre not allowed to view. Think of dimension security as hiding full rows and columns of a report.
Use cell-based security to limit the data that users see within a reports data grid. Think of cell security as leaving a reports row and column headers in place, but hiding pieces of the reports numeric contents.
Mix and match dimension and cell security.
Secure detailed data, like sales by salesperson, and still leave broad access to aggregated data, like sales by region.
Use dimension attribute security to hide textual data about dimension members, like employees Social Security numbers.
Define a component of a role as starting from empty (access is denied unless specific permission is granted), or from full (access to all elements is granted, then specific permissions revoked ).
Use role impersonation to easily test security definitions while administering security roles.

We provide more how-to details later in this chapter, in the section on Analysis Services security.

Within the relational database, its easy to define permissions to tables, views, and columns within a table or view. Theres no user interface for defining row-level security. The general approach is to create a separate table that has at least two columns: the business users ID and a list of keys, like EmployeeKey or SalesPersonKey, that the user is permitted to see. Then you create a view that joins the fact table to the security table on the key column, filtering on the user ID. Each business user who queries this view sees only rows for the keys listed for them in the security table. We provide more details on setting up this view definition later in this chapter, in the section on Relational DW security.

In addition, SQL Server provides column-level encryption. You can encrypt columns with sensitive identifying information, so only users who have the decryption key can ever see the data.

External Reports

An external report is a report from your DW/BI system thats available to people outside your organization. For example, you may let your suppliers see your inventory of their products, or see how well their products are selling.

The easiest way to meet the security requirements for standard external reporting is to use a push model: Email reports to your partners. The data-driven subscription feature of Reporting Services (Enterprise Edition) should meet the majority of external reporting requirements. This is a preferred approach because you dont need to provide any access into your system, and youre completely controlling when the report is run and to whom its delivered.

Data-driven subscriptions dont meet all external access requirements. You probably dont want to send a daily email to millions of customers about their account status. Were tempted to call this operational reporting, and wash our hands of the problem. Indeed, you should think seriously about whether you want external users accessing the same system that your employees are using to run the business. Most often youll decide to spin out a data mart, and associated reporting portal, dedicated to this application. Youll need to figure out how to authenticate those external users to allow them into the portal. These users will typically access only filtered reports, using the same techniques weve already discussed.

Its unusual for a company to provide ad hoc access to external people. Those who do are generally in the Information Provider business. You could use SQL Server and other Microsoft technologies to develop a robust Information Provision system, but this topic is beyond the scope of this book.

What Should You Do?

There are so many security features, and combinations of security features, that it seems overwhelming. The first items to knock off the list are unrestricted and restricted predefined reports. Reporting Services handles these very well and easily, whether the report is sourced from the relational database or Analysis Services.

Its harder to decide how to handle filtered predefined reports because there are several options. Your choice is going to be driven by whether you need to also provide ad hoc access to the relational database, or whether the information to be filtered is included in an Analysis Services database.

If you can deliver filtered reports from Analysis Services, thats certainly easiest. Analysis Services user security model is flexible enough to define any user security plan weve seen.

If the filtered reports must be sourced from the relational database, you still have a bunch of options. Table 12.1 summarizes the main approaches. Weve characterized the basic approaches by whether they can support filtering, ad hoc access with and without row filtering, and whether they require user login privileges to the database. Weve also specified which approaches we think are easiest to implement.

Table 12.1: Summary of Approaches for Implementing Data Access Security
APPROACH	FILTERED	AD HOC (NO ROW FILTERING)	AD HOC (WITH FILTERING)	USER LOGIN TO DATABASE NOT REQUIRED	EASY
Predefine Reporting Services (SSRS) reports that execute under service account. Use SSRS security to limit the list of reports to those that each user can access.
Predefine SSRS reports that execute under service account. Maintain a user permissions table in the relational database. Use a stored procedure to execute the source query, or data-driven subscription.
Predefine SSRS reports that execute under users account. Maintain a user permissions table in the relational database, and define a row-based security view between it and the fact table.
Analysis Services Security
Report Builder Security
User login access to relational, with row-level security

Next, we turn our attention to describing how to implement security in the various components of the DW/BI system. These components include the operating system, Analysis Services, the relational database, Reporting Services, and even Integration Services.

Windows Integrated Security

Microsoft SQL Server uses Windows Integrated Security as its primary security mechanism. Set up Windows users and groups in the Windows environmentthis is usually a task for system administrators rather than someone on the DW team. Then in Management Studio, create a set of roles, like Executives or Marketing, and grant or deny permissions to database objects for each role. Then assign the Windows users and groups to the appropriate roles. In a large enterprise, users will be assigned to several groups and roles.

In a Microsoft-centric enterprise, the users and some or all of the groups will be defined on the domain in Active Directory. In a heterogeneous environment, someone should integrate Active Directory into your environment like LDAP (Lightweight Directory Access Protocol). This is a job for a system administrator rather than a DW/BI expert, so we wont go into any details here. Buy a book or hire a consultant to help you with this project.

Its possible to connect to Reporting Services, Analysis Services, and the database engine through mechanisms other than Windows Integrated Security. These options are less secure than truly Integrated Security, so you should consider this approach only if you cant possibly make Integrated Security work in your environment. Even if you use these alternative authentication mechanisms, youll still need to create Windows users and groups on the database servers, and grant privileges to those groups.

Tip

The SQL Server database engine supports database security, where you define users and groups within the SQL Server relational database. Those of us who are older find this security model familiar and comfortable, but its inherently less secure than Integrated Security and should be avoided.

In this chapter, when we talk about users and groups, we mean Windows Integrated Security users and groups unless we explicitly say otherwise.

Analysis Services Security

When you install Analysis Services 2005, all members of the Administrators local group are granted access to the server and all databases and data. Until you set up roles and explicitly grant access, no other users can even connect to the Analysis Services instance, much less browse data.

Administrative Roles for Analysis Services

First, set up an administrative role for each Analysis Services database. A database administrator has full control over the database, including processing, aggregation design, and security. Analysis Services database administrator is a highly privileged role, but it doesnt require system administrative privileges on the server. If your Analysis Services server contains multiple databases, create a database administrator role for each.

Reference

See the Books Online topic Granting Administrative Access for details on the database administrator and server administrator roles.

The easiest way to create a role, including an administrative role, is within Management Studio. While youre logged in with server administration privileges, use the Object Browser to navigate down to a database, then to Roles within that database. Right-click to create a new role. Youre presented with the Create Role dialog box, illustrated in Figure 12.1. This is a complicated wizard. There are eight pages to work through, which you can see listed on the left-hand side.

Figure 12.1: Analysis Services database administration role

The person who administers security needs unrestricted access to the cubes data. Thats because the security administrator will need to test the roles and permissions. With Analysis Services 2005, the security administrator must have Full Control (Administrator) permissions to the database. This is more permission than a security administrator needsa security administrator shouldnt be able to process the database or modify aggregationsbut its as fine grained as you can get.

You should take some time to think about how the security administration process will work on the production system. As we describe in the next section of this chapter, the administrator needs to work through three major steps in managing users access to data:

Creating and managing operating system users and groups
Creating Analysis Services roles and granting data permissions to those roles
Assigning operating system users and groups to Analysis Services roles

Step 1 occurs at the operating system level, using Windows security. It must occur on the production server, an Active Directory server, or both. In most companies, the Windows security issues are handled by your Information Services organization when new employees are hired . You can simply tap into the existing user directory.

Step 2 is more of a development task than an operations task. Role development and testing should take place on the test server, and then be migrated to production using a script, as we describe in Chapter 14. The tools dont prevent you from developing roles on production, but its unwise.

Step 3 is an operational task that occurs on the production server. The roles, users, and groups are already set up, and the security administrator simply matches them up. The security administrator can use Management Studio to modify the membership in existing roles, as illustrated in Figure 12.2. This screenshot is from the Membership page of the same Create Role Wizard (or the Edit Role Wizard, which looks identical) as in Figure 12.1.

Figure 12.2: Assigning users and groups to roles

Because the server JOYSRV-01 in this illustration is not on a Windows domain, the only users and groups available are defined locally. Your domain users and groups would also be accessible within this dialog box.

Note	By default, you can add users and built-in security principles to a role, but not groups. To add a Windows group, select Object Types from the Select Users and Groups dialog box, and add a check to the Groups option.

You may choose to create a second administrative role, for processing cubes and dimensions. This role is useful for operations staff who should not have an account privileged to view data; they can only execute cube processing. A Processing role must have the Process database permission (see Figure 12.1) checked. If you want the operator to be able to use Management Studio instead of simply executing scripts, youll also need to check the Read definition box. Note that the processing role does not allow the operator to create new partitions, modify aggregations, backup, or restore the database. These operations, like security administration, require Full Control privileges.

User Roles for Analysis Services

Now that you understand how to administer security, its time for the more interesting topic of how to define users permissions.

Use the same Create (or Edit) Role Wizard pictured in Figures 12.1 and 12.2 to define user roles. Users, of course, dont need any of the administrative privileges pictured in Figure 12.1; leave those checkboxes unchecked.

Define and test Analysis Services roles on your test server. When you first set up a role, you dont need to assign any users or groups to that role. You can define the role and perform initial tests by impersonating the role while browsing the cube within Management Studio. Figure 12.3 illustrates cube browsing with a different roles credentials. Note that you can switch users (or roles) by clicking on the Change Users icon in the upper left of the browser window, highlighted by the tooltip in Figure 12.3. You can test multiple roles overlaid on top of each other. When youre working on security, youll quickly get in the habit of looking at the message near the top of the window, informing you of which role or roles youre impersonating.

Figure 12.3: Test role definitions by impersonating credentials

Although you might think that the first step in defining a user role is to assign users to the role, that assignment is actually the last step. The next page of the Role Wizard, Data Sources, is also something to skip over most of the time. The Data Source is the source of the Analysis Services databases data, usually a SQL Server relational database. The database and cube definition place a layer between the user and that source; very few users need access to the Data Source. The exception is if the Analysis Services database includes a data mining model, users may need access to a data source in order to perform a predictive query.

Reference

See the Books Online topic Granting Access to Data Sources for an excellent discussion of the Data Source page of the Roles Wizard

The next page of the Roles Wizard, called Cubes, is where you grant user access to the cubes within the database. You must grant access to a cube; users are not automatically granted cube access. If you simply grant access to a cube and immediately save the role, youve created a role with full access to all the cubes data. If thats not what you want to do, continue through the pages of the wizard to set the appropriate cell and dimension limits for the role.

Well skip the Cell Data page of the Roles Wizard for a moment, to speak briefly about the Dimensions page. Most implementations wont use the Dimensions page very often. Here, you can specify whether a role can read the metadata for a dimension: exactly how the hierarchies and dimension attributes are set up, and what their properties are. Most users dont need to see this information, but power users and technical folks might be interested. The database administrator will use the Dimensions page to let some users browse the definitions of one or more dimensions, without granting edit or processing privileges.

Finally we come to the interesting part of the Roles Wizard: the Cell Data and Dimension Data pages. Use these pages of the wizard to define cell-level security and dimension-level security in your database role. But before you do that, map out the strategy for this role. Remember that dimension security makes a report grid smaller: It limits the display of dimension attributes. Cell security doesnt change the row or column headers of a report; instead, it replaces some numbers inside the body of the report with #N/A (or a value of your choice). Its easiest to envision dimension versus cell security in the context of a tabular report, but of course the security holds no matter how youre viewing the results of a query.

If you want to hide descriptive information about an employee, like his Social Security number, you use the Dimension Data page. If you want to hide quantitative information, like a salespersons sales quota, you use the Cell Data page. Think of cell security as security on facts.

Remember that a user or group can have multiple roles: A user can be in both the Marketing role and the Executive role. When Analysis Services combines multiple roles, it does so additively, with a union operation. If a user belongs to two roles, one forbidding access to a data element but the other allowing it, the user will have access to that data element.

Theres one quasi-violation of this union rule. If a user has roles with both cell security and dimension security, the dimension security trumps the cell security. In other words, even if a role is permitted to see every fact cell in the cube, that data wont show up if the role cant see the dimension or dimension member. This is just what youd expect to happen because the dimension security would forbid dimension members from showing up as row and column headers.

When youre testing your role definitions, you will really come to appreciate the role impersonation feature of the cube browser in Management Studio.

Dimension Security

There are two basic approaches to defining dimension security on a dimension attribute: Specify the members that are allowed (all others are excluded), or specify the members that are denied (all others are accessible). In most cases, your choice of whether to specify the allowed set or denied set depends on the relative size of the included and excluded sets of members. Theres a second order problem that can be really important: if a new member is added to the dimension, should it be accessible or excluded by default? The safest thing is to specify the members the role is allowed to see. Then when new members are added, they wont be visible to restricted roles until the role definition is explicitly changed.

Figure 12.4 illustrates a simple definition of dimension security on a dimension attribute. In this case, were using the Deselect all members option to explicitly define the allowed set. Any new members will be excluded.

Figure 12.4: Defining basic dimension security

Weve introduced the problem of new dimension members joining the dimension. If this happens rarely, its not too big a burden to redefine the roles to account for the new member. If the dimension changes rapidly for example a customer dimensionyoull want to take a different approach.

One of the neatest solutions is to use MDX to define the included (or excluded) set of members. Under the covers youre always using MDX: The pick list illustrated in Figure 12.4 is simply a user interface that generates an MDX expression. In this case, the expression is a list of members. You can see this expression by switching over to the Advanced tab on the Dimension Data page.

A common MDX security expression will include (or exclude) all the children of a parent. For example, imagine you want to create a role that can see all the Product Categories (Bikes, Clothing, Accessories, and Components), cannot see any of the Subcategories under Bikes, but can see all the Subcategories for the other three Categories. Instead of listing the products that are in the brand today, define the MDX expression for the Subcategory attribute of the Product dimension as illustrated in Figure 12.5.

Figure 12.5: Using MDX expressions to define dimension security

MDX expressions can be a lot more complicated than this, but the Exists function illustrated here, and its close friend the Except function, cover most cases. Books Online has more examples under the topic Granting Custom Access to Dimension Data . For more complex examples, you should look at a reference book on MDX. Chapter 7 lists several MDX references.

There are a few wrinkles associated with dimension security. The first is the behavior of related attributes; the second issue is referred to as Visual Totals.

We introduced related attributes in Chapter 7. One of the common uses of related attributes is to define a natural hierarchy, like Product Category to Subcategory to Brand. Related attributes have an implication for the way dimension security works for denied sets. If you deny access to a specific Product Category, then youre also denying access to its children (Subcategories and Brands). This behavior doesnt come from the definition of a hierarchy between these three attributes. Instead, the behavior is driven by the definition of the relationships between the attributeswhich is what makes it a natural hierarchy.

Tip

It really feels like the hierarchy definition should be the thing that drives the security relationship, not the related attribute definition that users never see. To help keep it straight, think of the hierarchy merely as a drilldown path for the user interface; its the underlying related attribute definitions that are really important. A product attribute like Color that has no related attribute to the item youre securing (Product Category in our example), will not be affected by the security definition. Which, after all, makes perfect sense.

The second complexity with dimension security is the notion of Visual Totals, which you can see as an option on the advanced tab of the Dimension Data page in Figure 12.5. Imagine a role that grants permission only to a single product and doesnt restrict the brand level. What should be the subtotal by brand, for the brand containing the one product the role can see? Should the subtotal be the real subtotal for the brand? Or should it be the subtotal for the visible product? The answer depends on your business user requirements. The default behavior is to not use Visual Totals: Subtotals reflect both visible and invisible cells .

Tip	This is a difficult concept with a mediocre name. Visual Totals refers to what you would expect the total to be if you were looking at a report, and added all the numbers youre allowed to see on the report.

We usually prefer the default behavior for several reasons. First, the server has to work harder to support Visual Totals. It makes much less use of predefined aggregates. More important, with Visual Totals turned on, youre sowing confusion among your business users. Two people running the same report would come up with different totals at the brand level. This is a situation youve built your DW/BI system to avoid.

A downside of using the default behavior is that users may be able to infer a piece of hidden information. The simplest example is if youre hiding only one product, and showing the true brand total. Any user could calculate the hidden products sales as the difference between the brand total and the sum of the accessible products sales.

SOME COMMENTS ON THE SCALABILITY OF DIMENSION SECURITY

Readers who were familiar with Analysis Services 2000 on large scale systems may be wary of dimension security. In the old days, security on large dimensions created memory problems, which could lead to a server crash. The underlying architecture has changed; you no longer need to shy away from dimension security for large dimensions.

Note that for a large dimensionor, more accurately, a dimension attribute with high cardinalityyou should define security by creating MDX expressions, rather than explicitly choosing the members from the Basic tab of the Dimension Data page. In fact, that Basic tab will show you only the first 1,000 members. Which is a really good thing, if you think about the user experience of working with an attribute that has cardinality in the millions. If you have more than 1,000 members, the Basic tab displays a Search function.

Cell Security

Cell security affects which numbers or facts are displayed in the grid of a report, and which are blanked out (or replaced with #N/A or some other display element). Cell security, like dimension security, uses MDX expressions to define the cells that are accessible or hidden. Unlike dimension security, the Cell Data page of the Roles Wizard doesnt have any user interface other than entering MDX expressions. Cell security is so flexible that the best UI is the MDX editor window.

Figure 12.6 illustrates the Cube Data page of the Role Wizard. Weve started by granting read privileges to one node of data in the Product dimension: those data elements that roll up to the Bikes Category. Data for other Categories will display #N/A.

Figure 12.6: Defining cell-level security

Note

Users in the United States are familiar with the use of #N/A to mean Not Available because thats the convention in Microsoft Excel. Your client application may be able to translate this abbreviation for you. Or, you can specify a different value for the Secured Cell Value property in the users connection string.

The expression in Figure 12.6 refers to the Product dimension. Data along other dimensions in the cube is currently unrestricted. You can build up an MDX expression that refers to multiple dimensions, with clauses connected by AND s and OR s. We recommend you start simply and build up the expression piece by piece, testing after each addition.

Reference

The Books Online topic Granting Custom Access to Cell Data contains a nice set of increasingly complicated cell security definitions.

Look back at Figure 12.6, and notice the option to enable read-contingent permissions. This is an advanced option that most applications will not need. Read contingency is relevant for permissions on derived cells. The definition of read-contingent permissions is the same as for read permissions, but the behavior is different. Read-contingent permission will show a derived cell only if the role has access to all the data that goes into that derived cell. For example, if you have a derived measure for Contribution to Margin, the role could see that measure only if the underlying data to calculate Contribution to Margin is also accessible to that role. The rationale for this feature is similar to the Visual Totals discussion under dimension security: A smart user may be able to infer information that you dont want him to have. We advise caution with implementing read-contingent security. No doubt there are cases where this finesse is important, but its more likely to leave everyone confused .

Return to Figure 12.6, and notice the third place to enter an MDX expression for read/write permissions. Write permissions have to do with Analysis Services databases that support data writeback. These are typically financial applications like budgeting and forecasting. This book doesnt address this kind of application, but not because its uninteresting or because its not part of business intelligence. Rather, its a huge topic worthy of a paper or book of its own. Within the context of our security discussion, assigning write permission to a portion of the cell data uses exactly the same kind of MDX expression weve already discussed. Most organizations should steer clear of developing write-enabled cubes, and instead look to purchase packaged budgeting and forecasting software that is implemented on Analysis Services.

Warning

Be careful about checking the Read Contingent or Read/Write checkboxes without adding an MDX expression to focus the permission. If you check the Read/Write checkbox but dont add an expression, youre giving the role Read/Write privileges to the entire cube. Similarly for Read Contingent. This is unlikely to be what you intended.

Data Mining Security

The final page of the Roles wizard lets you assign Read, Read/Write, Process, and Browse permissions for data mining structures and the models that use those structures. Theres nothing particularly complicated about this page, except the point we noted earlier in this section: If youre enabling data mining predictive queries, the role must also have appropriate permissions to the source database for that drillthrough .

Relational DW Security

If the only access to the relational data warehouse comes through Analysis Services cubes and Reporting Services, then the relational security model is simple. If youll allow ad hoc access into the relational data warehouse, especially if you have requirements for filtering data (also known as row-level security), the relational security model grows increasingly complex.

No matter how users access the relational data warehouse, begin by thinking about the roles necessary for administering the relational database. After securing the operations, well discuss issues around users security.

Administrative Roles for the Relational Database

The SQL Server database engine has predefined server and database roles. These roles work the same for a DW/BI system as for any other SQL Server database application. Compared to the administrative roles for Analysis Services, the database engine has fine-grained permissions.

Server roles include:

bulkadmin: Has permission to run Bulk Insert. Many experienced database administrators refuse to grant the bulkadmin role to anyone. Its very important that the service account for Integration Services has bulkadmin privileges.
dbcreator: Has permission to create, alter, drop, and restore databases. Only a few members of the DW/BI team or DBA organization should have dbcreator privileges.
diskadmin: Can manage the databases disk files. The ETL service account used to run Integration Services packages may need diskadmin privileges, especially if you automate partition management.
processadmin: Can kill processes, including users runaway queries. We hope you wont have any runaway queries, but its indisputably more likely in a data warehousing environment than a transaction system. A relatively small number of people on the DW/BI team (or DBA organization) should have processadmin privileges. But dont hold this privilege too tightly, or you may find the system bogged down because the wrong person is at lunch or out for the day.
securityadmin: Can manage server logins. If you use SQL Server logins (as opposed to the recommended Integrated Security), securityadmin privileges let you reset passwords.
serveradmin and setupadmin: Can change system-wide configurations, and are seldom important during the ongoing operations of the DW/BI system.
sysadmin: Can do anything.

Database predefined roles, as you might expect, are database-specific . In other words, a user might be highly privileged in one database, but not even allowed to access a second database. The predefined database roles are:

db_accessadmin and db_securityadmin: Used to manage logins and permissions for users and groups, and to assign users to roles.
db_datareader and db_datawriter: Can read and insert, update, or delete data from any table in the database. The ETL service account should have db_datareader and db_datawriter privileges.
db_denydatareader: Cannot read any data and db_denydatawriter cannot write any data from any table in the database. Unless you have a writeback application, any user login should have db_denydatawriter permissions.
db_backupoperator: Can, you guessed it, back up the database. The ETL service account should have this role, if youre integrating database backup into your ETL system.
db_ddladmin: Can execute any DDL (Data Definition Language) operation, including creating tables and indexes. The ETL service account should have db_ddladmin privileges.
db_owner: Can do anything to the database, including dropping the database.

Its always good practice to grant people, even people on the DW/BI team, as few privileges as they need to get their jobs done. Occasionally people are malicious, but more often theyre just careless.

The service account that runs the Integration Services packages needs to have high privileges. You should always run production operations, like the ETL system, under a service account thats not associated with a persons login.

Its common for the DW/BI team members to all have high privileges on the development system. The test system should be set up the same way as production. One of the things you need to test before moving into production is whether the permissions are set correctly. This is a very common task to overlook.

SQL Server 2005 introduces the ANSI-standard concept of a schema. Object names are fully qualified as server.database.schema.object . We usually create the data warehouse tables under a single schema, but weve seen people use schemas to segregate dimension, fact, utility, and metadata tables.

Tip	Most often, the database is installed as the default instance, and the server name referenced above is the machine name of the server. If you defined the relational database as a named instance, the server name is, unsurprisingly, that name.

User Roles for the Relational Database

There are two main kinds of accounts that issue relational queries in a DW/BI system: service accounts, like those used by Reporting Services, and business user accounts. Youre likely to have one or several service accounts. Depending on your business requirements, you may have business user accounts that can log in to the relational database.

Service Account Permissions

Depending on how users access the DW/BI system, you may have only a few user roles to worry about. If youre using Reporting Services, you should expect to create and manage a reporting service account. Like the ETL service account, the reporting service account should not be associated with a person. If youre using Analysis Services, you should create a separate Analysis Services processing account. This account may have the same privileges as the reporting account, but its foolish to assume theyll always be the same. In some configurations, one account may need more privileges than the other.

We have already said several times that all users should access the relational data warehouse through views, rather than directly querying tables. There are several reasons for this:

Views let you insulate the user experience from the physical database. Its amazing how much the view layer lets you restructure the database with minimal disruption to the user.
Views let you hide or rename columns to suit users. You can remove the prefixes (like Dim and Fact, which we use in MDWT_AdventureWorksDW) from the view names.
Views let you add row-level security seamlessly.

Create a view on every user-accessible table, even if the view simply selects all columns from the table. Grant user access to the views, not to the tables. This includes the reporting account and the Analysis Services account. Both of these service accounts should use views rather than the underlying tables.

Tip	Create all user-oriented views and stored procedures under a single schema, and have that schema own no other objects. This makes it easier for you to find these objects when it comes time to assign permissions.

The reporting service account should have read access to the appropriate views, possibly all the views. As we discuss later in this chapter, Reporting Services secures the reports, so the reporting service account will have greater read privileges than any individual user would. Some reports will use a stored procedure to deliver up the rowset. If so, the reporting account will need execution privileges on those stored procedures. The reporting account should never have write privileges on any table or view. We cant think of a technical way for the encrypted reporting account credentials to be stolen. But the most likely way for those credentials to get out is the obvious one: Someonepresumably on the DW team tells someone else. Theres no point in tempting fate. This is especially true for any custom reporting front-end or non-Microsoft query software that might use a reporting account. Microsoft is very careful about security these daysdont laugh , they really are. You should thoroughly investigate the security mechanisms of any software on your system.

The Analysis Services service account should have read access to the appropriate views, possibly all the views. The Analysis Services users should not need any direct privileges in the relational database.

Business User Roles

Business users will need login privileges into the relational data warehouse database if they need to perform ad hoc analyses and if you have row-level security requirements. In the absence of row-level security requirements, users ad hoc needs may be able to be met by the Report Builder feature of Reporting Services, which can continue to use the reporting service account. We discuss these issues later in this chapter, in the section Reporting Services Security.

If business users can log in to the relational data warehouse, you need to grant them only the appropriate permissions. This means read-only permission on the appropriate views, columns, and stored procedures. The easiest way to get started administering security is in Management Studio. In Object Explorer, navigate to the database and select Security Roles Database Roles. Create a new role for all information available to anyone with login privileges. Well call this role BIPublic.

Click Add Objects and add all the views to which everyone has access. As we pointed out earlier, its much easier to find all the views if you created them under a single schema. For each view object, you should explicitly set the permissions. In general, deny all permissions except Select, as illustrated in Figure 12.7.

Figure 12.7: Defining the BIPublic role

You have to work through the object list one view at a time, granting only SELECT privileges This is pretty tedious for a large system, but its probably faster than scripting it.

Note

What if you want to hide a few columns, like Social Security number, from some users but not others? One approach is to define two views on the table, one with all public information and the other with all public and private information.

The best approach is to encrypt the private information and distribute decryption keys only to the appropriate users.

Create new roles that grant permissions to objects for the subset of people who can access that object. For example, say you protected the Employee dimension from BIPublic, but you want everyone in Human Resources to have access to it. Create a new role for HR, granting access to the appropriate views. Roles are additive, so if you have a user whos a member of both BIPublic and HR, that user can see the Employee view.

The next step is to add users and groups to roles. You can accomplish this on the first page of the Database Role Properties dialog box. Figure 12.7 illustrates the second page of that dialog box. On the first page, you can launch a standard dialog box to add users or other roles to the role youre working on. Continuing our example, you might add all authenticated domain users to the BIPublic role, and all members of the Windows group HRUsers to the HR database role.

Row-Level or Filtering Security

You may need to build row-level or filtering security into your relational data warehouse. What we mean by row-level security is that users views of sensitive data differ not at the column level, as we discussed in the preceding text, but by rows. One person should see a different set of rows than another person.

Theres no row-level security feature of SQL Server, but the classic solution is not difficult to implement. First, create a table that lists users IDs and the identifiers for the rows theyre allowed to see. For example, secure the FactOrders table from MDWT_AdventureWorksDW to limit a users view of the data only to a specific set of salespeople (identified by SalesRepKey).

Here is some DDL for this permissions table:

Listing 12.1: Data definition for the UserPermissions_SalesRep table

   IF OBJECT_ID ('[dbo].[UserPermissions_SalesRep]', 'U') IS NOT NULL      DROP TABLE [dbo].[UserPermissions_SalesRep] CREATE TABLE [dbo].[UserPermissions_SalesRep](UserPermissions_SalesRepKey int IDENTITY NOT NULL,      [UserName] sysname NOT NULL,      [EmployeeKey] int NOT NULL CONSTRAINT [PK_UserPermisssions_SalesRep]      PRIMARY KEY CLUSTERED ([UserPermissions_SalesRepKey] ASC))

Next, insert some rows into this table. Lets say that the user Joy is allowed to see only information for employees 272 and 281.

In the case of Listing 12.2, youre using Windows Integrated Security, and Joy is a user on the KimballGroup domain.

Listing 12.2: INSERT rows that define which employees Joy can see

   INSERT UserPermissions_SalesRep VALUES ('KimballGroup\Joy', 272) INSERT UserPermissions_SalesRep VALUES ('KimballGroup\Joy', 281)

The final step is to create a view definition that joins the fact table to this new permissions table:

Listing 12.3: Define a view to provide row-level security

   IF OBJECT_ID ('OrdersSecure', 'V') IS NOT NULL DROP VIEW OrdersSecure CREATE VIEW OrdersSecure AS SELECT f.* FROM FactOrders f INNER JOIN UserPermissions_SalesRep u ON (f.SalesRepKey = u.EmployeeKey) WHERE u.UserName=SYSTEM_USER

As you can see by looking at the view definition, the trick is to limit the view to only those rows where the user name in the permissions table is the same user name as the person issuing the query. If you maintain the permissions table, many people can access this OrdersSecure table and see only the appropriate set of rows.

This solution is not as satisfactory as the dimension and cell security that you can define in Analysis Services. Youve safely protected the detailed data, but no one can use this view to see company-wide sales. You need to create a second view, simply called Orders, that drops the SalesRepKey. The BIPublic role could consume the Orders table, and use that for most queries about sales volumes . The dual views create a usability problem for ad hoc business users, but its the best solution the relational database offers.

Note that this view is useful not just for ad hoc querying. You can also set up Reporting Services filtering reports that use the view. When you define that report, you need to pass credentials from the user into the database.

If you need to support filtering reports and row-level security, you should consider building an applet to help maintain the UserPermissions tables. For performance reasons, you want the UserPermissions tables to use the warehouse surrogate keys. But your requirements are probably phrased along the lines of granting permission to all the employees in a department or sales reps in a region. A simple applet could help the security administrator be more effective than writing INSERT statements, and greatly reduce errors.

Testing Relational Security

Even though relational security isnt as complicated as Analysis Services security, its still plenty complicated. You must thoroughly test all the roles and the combinations of roles.

You can impersonate a user and then run a query. Compare the results of the queries before and after impersonation to evaluate whether the security definitions are correct. The following script shows you how. The script assumes youve created the OrdersSecure view described previously, and also that you created a role BIPublic to which you granted access to OrdersSecure. We further assume that when you run this script, you have sufficiently high privileges that you can create logins and users. Sysadmin would work just fine.

Listing 12.4: Create a temporary user to test security roles

   USE MDWT_AdventureWorksDW; GO --Create a temporary login and user CREATE LOGIN LoginBIPublic WITH PASSWORD = 'J345#$)thb'; GO CREATE USER UserBIPublic FOR LOGIN LoginBIPublic; GO --Display current execution context. This should come back as you. SELECT SUSER_NAME(), USER_NAME(); --As a simple, not very accurate test, how many rows can you see? SELECT COUNT(*) FROM OrdersSecure --Set the execution context to LoginBIPublic. EXECUTE AS USER = 'UserBIPublic'; --Verify the execution context is now 'UserBIPublic'. SELECT SUSER_NAME(), USER_NAME(); --Select from the view. You should get a permissions error SELECT COUNT(*) FROM OrdersSecure --Revert back to yourself and add the user to the BIPublic role REVERT; EXEC sp_addrolemember @rolename='BIPublic', @membername='UserBIPublic' GO --Now select from the view as UserBIPublic. We expect to see zero rows --because UserBIPublic is not in the UserPermissions table. EXECUTE AS USER='UserBIPublic'; SELECT COUNT(*) FROM OrdersSecure --Revert back to yourself REVERT; --Remove temporary login and user DROP LOGIN LoginBIPublic; DROP USER UserBIPublic; GO

SPECIAL PRIVILEGES FOR POWER USERS

Some of the organizations weve worked with have had a few power users who were not officially part of the DW/BI team, but whose analysis required a sandbox on the database server. These users are probably doing incredibly valuable stuff, and may be working with enough data that they can really benefit from the horsepower of the server. If so, they need specially privileged accounts.

Before we talk about the kind of permissions this power analyst might need, its worth commenting on an alternative approach: Let the analyst install SQL Server on a local server, and pull down chunks of the database to manipulate as she wishes. This approach has some appeal, but recognize that you will have no idea what this analyst is doing, nor how secure shes keeping her server. Everyone is better off if you provide a managed sandbox, probably on a test server but possibly on production.

If your organization has only a few analysts with these requirements, you could create a separate database for each. Alternatively, create a single sandbox database and set up each user with her own schema to avoid namespace collisions. The power users will need extended privileges in this database. Theyll need to be able to create (and drop) tables within their schema. Youll need to monitor the sandbox to ensure these people arent doing something crazy like copying an entire huge fact table. Set database size limits.

Many database professionals are horrified at the notion of users manipulating large datasets with write privileges. You may consider co-opting these users and making them part of the DW/BI team. But in the ideal world these people are closely tied to the business, and are working on the kinds of projects and analyses the DW/BI system was designed to support. Figure out what they need, and try to give it to themwithin reason.

With our security hats on, we recommend that you always run impersonation tests from a script, and bracket the script with CREATE and DROP login and user, as we illustrated here.

Reporting Services Security

Reporting Services can source reports from the relational data warehouse, other relational and even non-relational sources, and from Analysis Services. Reporting Services is a client of the databases, but a special kind of client: one that is also a server, and that contains its own security features.

Administrative Roles for Reporting Services

Reporting Services is installed with a set of predefined roles for administrative and business users. You can modify these roles, or replace them with custom roles. But most organizations will simply assign these predefined roles to various people who are performing administrative tasks .

The relational database and Analysis Services have administrative roles that are clearly the purview of the DW/BI team, and only the DW/BI team. By contrast, your configuration and use of Reporting Services may distribute the administrative workload out to the business units. You may have some business users who need an administrative role.

The predefined roles include:

System Administrator: Set server level features and security, and manage jobs. Clearly this highly privileged role should be granted to only one or two members of the DW/BI team.
System User: View basic information about the report server, including shared scheduling information.
Content Manager: Manage report folders and items within those folders, including security on those items. Content management permissions are not necessarily system-wide. In other words, you could have one person be the content manager for one set of reports like Marketing, and a different person manage Sales reports.
Publisher: Publish content, like a report, to a report server. Some organizations will tightly control publishing rights, linking this role with Content Manager. Others will let anyone publish a report to the enterprise. We recommend being careful about who has publishing rights.
My Reports: Build reports for personal use and store reports in a user-owned folder.
Browser: View the list of reports, and run the reports to which you have access.

Everyone involved with administering the reporting system should understand that securing folders is the most common way to manage user security. Security is inherited through the folder structure, so you can simplify the administrative burden by grouping reports in folders according to how confidential those reports are. When you restrict a folder, you are by default restricting all the items in the folder.

One sensible use of a highly restricted folder is a place to hold reports that are being tested . Testing report definitions and layouts straddles the line between an administrative function and a user function. Often, you want a business person to sign off on the report before its published more broadly. Using roles and a designated test folder, you can make it easy for the testers to find the reports under development, yet hide those reports from the rest of your organization.

Remember that the security assigned to a folder is the default security setting for any item, like a report, thats created inside that folder. You can change an individual reports settings, but thats a second step that you (or the distributed Content Managers) may forget to take.

You can use either Management Studio or Report Manager to manage permissions. The DW/BI team might use Management Studio; business users who are Content Managers will almost certainly use Report Manager.

User Roles for Reporting Services

When a user connects to Reporting Services, the report list shows only the reports that user is allowed to see. When a report is executed on demand, its usually executed not with the users credentials, but instead with a reporting service account used only to execute reports. In this scenario, users do not have login privileges to the underlying database.

Earlier in this chapter we discussed four kinds of security for reports:

Unrestricted reports are available to everyone who can connect to the report server.
Restricted reports are available to a subset of users.
Filtered reports return a different result set depending on who runs the report. Filtered reports use some form of row-level security to filter the results as appropriate for each user, and are usually also restricted.
Ad hoc access is best secured by user permissions in the source database, rather than in Reporting Services. Reporting Services Report Builder enable some ad hoc querying, enough to meet many users ad hoc requirements (which are usually quite modest).

Reference

You can read about Report Builders security features in the Books Online topic Securing Models.

Reporting Services security model is perfectly targeted for unrestricted and restricted reports. Its very easy to set permissions on folders and reports. You may have a Data Administration team manage this function, or you can distribute some administration out to the departments who design and develop the reports.

Our recommendation for Reporting Services security is to keep it simple. Create a few user groups and assign them different roles and permissions as needed. In the simplest case, you could create two groups and assign permissions as follows :

RSStandard: This group is the default for all users who have access to the DW/BI system. RSStandard does not have access to sensitive information.
RSFull: This group is for those people who need to access sensitive information. In this simple two-level scheme, this group has access to the entire warehouse.

Next, build your sensitive reports in separate projects, which will correspond to directories or folders in Report Manager. Use Management Studio to set the properties for those folders. For example, grant Browse permission on the Home folder to all user groups. Assign Browse permission to the RSFull group for directories that contain sensitive reports. This could even be done at the report level if there are only a few reports that need limited access. Finally, assign the My Reports role to all users so they can load their own reports into the Report Server.

This simple approach can be expanded by adding more groups and assigning them different levels of access. It may make sense to have an RSPowerUser group that has report management permissions. You might even have several of these groups so power users can manage the report directories associated with their departments. For example, the sales power user would be assigned the Content Manager role in the Sales Reports directory.

Earlier in this chapter, in the section Filtered Reports, we talked about how to deliver filtered report functionality while still prohibiting users from logging into the relational database directly. But maybe youve decided to service filtered reports by using database security, either the relational database or Analysis Services. Youd be most likely to implement filtered reports this way if youve already set up database security to support ad hoc access.

If you want to rely on database security to filter data rows, you need to set up the filtered report so that it doesnt use a shared data source. Instead, you should pass the users credentials down to the database, whether the relational database or Analysis Services. If youre using Windows Integrated Security, simply set the credentials of the reports data source to Windows NT Integrated Security.

Note

In an ideal world, you use Windows Integrated Security with trusted domains, and this works great. If youre running Reporting Services on a different computer than the relational database or Analysis Services, and you dont have trusted domains, Windows isnt going to let the credentials hop between the Reporting Services server and the database server. The easiest solution is to enable Kerberos on these servers, but there are other solutions as well.

Reference

See the Books Online topic Best Practices for Authenticating Server and Data Source Connections and the topic How to: Configure a Report-Specific Data Source.

Integration Services Security

Integration Services is a back-room operation, so its security story is simple. First, make sure the packages are secure, so no one can mess with package contents. You dont want anyone to replace the package that performs an incremental load of the data warehouse with one that deletes all the data. This is a pretty far- fetched scenario. More likely someone on the team is careless and does something wrong.

Packages can be stored in the file system as XML, or in SQL Server. You should secure the package location. Packages stored in SQL Server are stored in the msdb database, in the table called sysdtspackages90. Simply use the database engines security to grant limited permissions to msdb and sysdtspackages90, and the package contents are automatically secured. If you store the package on the file system, use Windows security to limit access.

In addition to this basic security, you can sign or encrypt packages. Digitally sign the package and set the packages CheckSignatureOnLoad property to True to prevent anyone from modifying a packages contents. More accurately, what youre doing is telling the package to check for a signature before it runs. If the package has been modified unintentionally, it wouldnt have been signed. If someone maliciously modified the package, they ought not be able to sign it.

Packages contain sensitive information within them, including the connection information to an account that, usually, has very high privileges in the data warehouse database. Integration Services automatically encrypts all connection information for you, but you can go farther and encrypt more of the package contents. There are several encryption options, but they come down to requiring a password before anyone can view the package.

The section of this chapter on Relational Data Warehouse security provides guidance on the kinds of relational database permissions youll need to provide for the connections from your Integration Services packages to the data warehouse database.