University of Washington

• By David Raths
• 06/27/12

Bill Yock remembers the bad old days of DMUG meetings at the University of Washington.

DMUG stands for Data Management Users Group, and, as director of enterprise information services, Yock was the technology guy in the room during long debates about who could access certain human resources and financial information in the data warehouse.

"Someone might need access to 10 specific tables for certain job responsibilities, but not 12 other ones," Yock says. "It becomes chaos for IT to customize access that way."

Yock and other IT leaders were convinced there had to be a better way. The first step was to change from a users group to a data-management committee with an official charter from the provost. "We also changed the conversation from data ownership to data stewardship," Yock recalls.

That was 2006. Fast-forward six years and UW is in a much better place in terms of data warehouse access. It has created tools that both automate much of the access provisioning and turn over the decision-making from IT to the data custodians themselves.

The new Data Access Control (DAC) and Security Metadata Administration Tool (SMAT) create a matrix in which users are classified by roles according to their job responsibilities. Data custodians decide which roles get access to specific domains of data. "The whole process becomes transparent," explains Anja Canfield-Budde, senior manager of UW-IT's Decision Support Services group. "It no longer requires the database administrators to guess which level of access each person should have."

The technology rollout began in 2009. Because the enterprise data warehouse is based on Microsoft SQL Server, the data warehouse team brought in a Microsoft consultant who talked about the possibilities for an agile, flexible security solution. Ultimately, the team developed SMAT as a .NET web application that generates security schemas in XML format, and the DAC tool as Microsoft SQL Server T-SQL-based code. (DAC is integrated with UW's ASTRA user-authorization system, which maintains user identities and other system permission information.)

Since implementation of the SMAT and DAC access controls, the number of users with access to the data warehouse has increased gradually from approximately 50 to more than 5,000. The data warehouse has generated more than 200 new enterprise reports and report execution has grown to more than 25,000 per quarter. "The paradox is that, by applying more security-access controls, we are able to provide broader access," notes Canfield-Budde.

The data warehouse team members are not resting on their laurels. First, they want to make the tools more user-friendly for the stewards applying security schemes to their data. They also see a need to fine-tune the access-request process, which currently involves e-mail. "The e-mail chains are cumbersome," Yock says. "We are working on automating that."

The tools also will be applied to other business intelligence analytical tools such as multi-dimensional cubes, and could be used to provide access to other systems and repositories of data in the UW system. The code has already been shared with the UW Physicians data warehouse team.

UW has also presented the tools at national conferences. According to Canfield-Budde, the audience response has been so positive that the Decision Support Services team decided to apply for a patent. "We think any higher education organization using SQL Server would be very interested," Yock says. "And if we get more funding, we could port it to other platforms."