Security
KF: Can you describe the security measures you mentioned? Is all security at the data level, or are you using layers of security at each tier (application, Reporting Services, Analysis Services, Database Services, etc.)?
Frank: We have security at the data level, and we have an intermediate security application that we wrote. The security is at the data level on the server, and we have two sets of roles: function roles and geography roles. The function role describes the organizational function in the company. Basically, the company's business divisions are the function slice. The geography role is the person's location (Europe, Asia, Latin America, North America). So a person is in one or both roles, or security groups. Say a user is a comptroller in the Windows Client Division and he works in Europe. Membership in the Client Division function and Europe geography roles means he will only see the data that applies to that function and that geography, and it works up and down the hierarchy.
Because of the construction of the security schema in Analysis Services, the hierarchies will make sure that a person only sees the data that appears as an intersection of function and geography appropriate to that person's assigned role. This would be impossible in the spreadsheet world. Here's why: Let's say we have 10 or 12 functions and 10 or 12 geographies.With Excel, you'd have to create hundreds of roles to make this all work. You'd need a spreadsheet for each set of intersections.With Analysis Services, you just create the major roles and assign people in each grouping.
That's the base security level.The intermediate security is an application we wrote to limit people to certain perspectives. Perspectives are slices of data not connected with function or geography. For example, there's a marketing perspective. People want to look at marketing costs as opposed to general P&L data. So we have perspectives for just marketing data. A much wider group of people can look at that data than can look at the core revenue data,which is the most restricted data.
Before a user even gets to make the query against the database on the server, we're saying you can or cannot look at this perspective, and that's done at the Reporting Services Level. This is a prefilter to the data security. The data security still applies, but you can only see certain pictures of the data to begin with. Let's say we have 250 attributes of data and the marketing perspective only contains half of those. Before you can even make the query, you're limited by the perspective. Then further, you're restricted by the security group you're in.
KF: Based on your experience, what are your security recommendations?
Frank: We believe security should be at the data level for two reasons. First, even if you have middle-tier reporting, a person's credentials are passed to the query so the system knows exactly who that person is. A person is defined in a role, the roles are stored on the server, and the domain controllers know that each person is in a particular role. Second, other tools besides Reporting Services may be available to query the Analysis Services data, and if the security is at the data level, then no matter how users query (let's say they query through Excel), they still only get the data they should see. You don't have to enforce the security on the middle tier or through some third level of security because you have it at the data level.
We've been looking for this as the way to absolutely secure the data. Remember, this is the financial data, so we have to be very careful about who sees it. We believe that there's no way that anybody could get at this data unless they had specifically been granted permission into these roles.
Ron: Think about compliance and governance requirements. The ability to lock security at a more granular level is very important to us.
Frank: Under the procedures for maintaining financial data, you have to scrub this data on a quarterly basis, I believe, so anybody that has access to this data is reviewed. But we know that the individual user is a member of these roles and therefore cannot see anything beyond what she should see. Even a database administrator or owner of the server would still have to be in the correct role to see this data.
Also, you now have two ways to do logging: You have logging at the source, and you have logging at the Reporting Services level. Analysis Services will log the queries. Remember, if a person is directly connecting to SQL Server with, let's say, an Excel spreadsheet, in previous versions of SQL Server you'd have to write intricate code to get the query to be logged as the user was querying. You could easily get logging to make updates, but you couldn't get logging just to select. However, with Analysis Services 2005, you can log every access. Not only is the data secure, but you know what the person asked for. And Reporting Services as the reporting vehicle is also logging. It guarantees you know who was making queries.
KF: How are you managing data security in your 2005 reporting environment?
Frank: The security is in an XML schema, as is the schema for the actual database design, so security is relatively easy to manage. We don't have a separate tool for it yet, but to manage the security we use a script that we can readily apply to the source.
You don't need a lot of manual effort. If you wanted to go into the raw front-end tool that we used in SQL Server 2000 Management Studio, it would take a long time to set up every value. We have upwards of 600 possible reporting users in the cube, and 12 function and 12 geography roles, so managing that number of roles would be difficult if you wanted to do it one click at a time in SQL Server Management Studio. So using a script is good. Remember, the person is not in the script; only the role is. The person is assigned into the role through all the normal AD tools, so when a new person comes on board, the administrator adds him or her to a security group and they're in the role. The users themselves are not put in the server, only the role is, and the membership of the person in that role determines his or her actions.
Prev. page
1
[2]
3
next page 
