It was yet another afternoon of record-setting heat. I loosened my tie, rolled up my sleeves, and plugged in the fan, hoping to get some of the heavy, humid air moving, then sat in my creaky office chair and contemplated a city wrapped in a cloud of smog.Time for a vacation, I thought. I needed to get out of the city and away from my cramped office, the dusty keyboard, and my usual order at the diner down the street. I wanted clean, cool mountain air and wilderness as far as the eye could see.
The phone jolted me out of my reverie. "B.I. Powers," I said as the phone banged against my ear.
"Randal Reynolds here, director of the Organization for the Welfare of Injured Extremities—OWIE for short. We're a government bureau responsible for investigating employee injuries at local businesses. We have a case that needs to be addressed immediately. Can we meet somewhere to go over the details?" Reynolds asked.
"How about the Mini Diner on East Lake Street in 30 minutes?" I replied.
Over lunch, Reynolds asked me to investigate Wry and Wry Resin Rendering, which had recently experienced a rash of wrist injuries. He suspected that the upsurge in injuries might be covering up some wrongdoing. He further explained that the injuries came from a single department, the Business Intelligence (BI) group. My role was to determine the cause of those wrecked wrists.
As I raced down the road in my '37 Packard, I anticipated a workplace of tiny cubicles containing misplaced monitors and keyboards. My wrath rose at the thought of BI workers sustaining painful injuries because of their employer's indifference.
Ryan Wry, president of Wry and Wry, greeted me on my arrival. While we toured the facility, I noticed that the offices were downright roomy. Ryan was eager to point out monitors on robot-like arms and keyboards with wrist rests. "As you can see," he remonstrated, "our office environment meets or exceeds all OWIE rules and regulations."
"So you know of no reason why wrist injuries have taken such a big jump?" I queried.
"Well, there might be one thing..." Ryan Wry responded, before being interrupted by a wail from across the room. I rushed toward the sound, rounded a corner, and saw a young researcher rubbing his right wrist.
"What happened?" I asked.
"I accidentally roamed into an area of the Analysis Services cube that I don't have rights to," the worker said, "and the wrist rapper whacked me."
"Wrist rapper?" I said. "Really?"
Ryan reappeared. "Yes; our security department recently implemented the wrist rapper program. If employees venture into restricted regions of the database, they get rapped."
"I see," I replied as I approached the researcher's keyboard. I noticed a thin metal band protruding from the keyboard where the Pause key had been. The band reminded me of a slap bracelet, an item my niece Melissa had introduced me to during her recent visit.
Ryan nervously responded, "The program has been very successful and has reduced the occurrence of employees viewing sensitive data by 85 percent."
"Yes, I see. Can you demonstrate the wrist rapper for me, Mr. Ryan?" I asked.
The researcher leapt out of his seat. Ryan sat down and pressed a few keys, and the wrist rapper quickly snapped down on his right wrist and returned to its upright position. "See, that's not so bad," said Ryan. "I don't know why employees are complaining."
"But I imagine that if an employee receives multiple raps each day, it can become quite painful, "I said. "I'd like to recommend another approach." I explained that Analysis Services databases can be configured to give users access to only the areas they need to complete their daily tasks. "For example, you can create a role that gives the manufacturing group permissions to see all data that relates to products, such as parts lists, vendor lists, item costs, and product development times."
Using Roles and Permissions
I explained to Ryan that you can define security roles within an Analysis Services database to grant and restrict the rights of users who hold those roles to dimensions, attributes, and measures within cubes. Roles also control who can perform administrative tasks, such as processing cubes or reading cube definitions. You can assign Windows logins and groups to roles to facilitate user access to a database and its cubes.
To create and manage roles, you use Business Intelligence Development Studio or SQL Server Management Studio (SSMS). Both applications offer the following tabs for configuring and managing roles:
- General—Enter a role name and description and an authorization for administrative tasks.
- Membership—Specify the Windows logins and groups that are members of the role.
- Data Sources—Define the role's rights to read data from each data source in the database.
- Cubes—Specify the role's rights to access each cube in a database.
- Cell Data—Define the role's rights to access data within each cube.
- Dimensions—Specify the role's rights to access and process each dimension in the database.
- Dimension Data—Define the role's rights to access members within each dimension.
- Mining Structures—Specify the role's rights to access data mining structures in the database.
The Cell Data and Dimension Data tabs provide the most complex and powerful features, letting you specify with a fine level of granularity the data that members of a role can and can't see. For example, I told Ryan, he could create a security role—Wdlnd Crtr Prod Mgr—for the manager of the Woodland Creatures product type, then use the Cell Data and Dimension Data tabs to let her see only products of that type when she browses the manufacturing cube. In addition, if he wanted her to be able to see only the Accepted Products and Rejected Products measures, he could use the Data Sources and Cubes tabs to grant the Wdlnd Crtr Prod Mgr role read access to the data in the Wry and Wry cube, then use the Cell Data tab to restrict access to the measures in that cube, as Figure 1 shows. The Cell Data tab uses an MDX script expression to restrict the measures the manager can see.
You can use the Dimension Data tab to select the dimensions the Wdlnd Crtr Prod Mgr role can view. I showed Ryan that there are two tabs: Basic and Advanced, as Figure 2 shows. I instructed him to select the Basic tab and click Select all members, then clear the check boxes for the dimension members that the Wdlnd Crtr Prod Mgr role shouldn't have the right to view. Alternatively, he could accomplish the same thing by using the Advanced tab to create appropriate MDX script expressions.
After deploying the role to the Analysis Services database, select the Browser tab to test the new security role. When you browse the cube without using the role, you can see all products and measures. Click the Change User toolbar button to invoke the new role for testing. When the role is in place, you'll see product members from only the Woodland Creatures product type and data for only the Accepted Products and Rejected Products measures, as Figure 3 shows.
Running Right
Within two days of my visit, Ryan Wry had eliminated the wrist rappers and implemented restrictions according to roles. A follow-up inspection revealed no further wrist injuries.
In this case, I tracked down and identified a repulsive security method. In the next case, I suggest a way to track down and identify Reporting Services reports.
