Datasecurity within Business Intelligence

Hi!

My name is Daniel and i am graduating on the subject datasecurity within Business Intelligence.
My main objective is "how to manage confidential information in is business intelligence (data warehouse and reporting)". I am searching for technical measures on the market to get a safe solution.

Incomplete list of measures:
- Encryption (data masking etc.)
- Logic access security (authentication / authorization)
- Partition. by department, etc


My question is, are there people familiar with this subject? And can you give me Pro's and Con's about using specific technics?
And does someone have experience using a multi-level security data warehouses?

Regards,

Daniel

One technique particularly useful is to de-identify sensitive data. In finance and medicine (industries I've been *ehem* lucky enough to work in), de-identifying information such as account number, medical record number, insurance information, name, etc. is quite useful.

Loosely:

(1) Load your sensitive data into the data warehouse as normal and lock it down.
(2) Create de-identification routines that can mask sensitive columns.
(3) Use the routines when you create your data marts or reports.


Remember that a data warehouse is most useful if a broad range of people can actually use it! De-identification allows you to give complete access without the worry that sensitive information can be traced back to a person, bank account, etc.

-Tod

Correct me if I am wrong, but I've never found specific Business Intelligence security technics. You'd probably rather look for security in databases in general. I am not sure though....

There are some good reporting software that can help with this. The software can enable you grant or deny access to users for specific tables, columns, and even rows of data. We set up groups, define the access for each group, and then assign users to individual groups. Tables and columns are farily easy. Setting up security for specific rows is a little trickier but can be done. We have one "user" defined for the database. The reporting software connects to the database as the one user. The reporting software handles all of the security.

Jeff Smith wrote:There are some good reporting software that can help with this. The software can enable you grant or deny access to users for specific tables, columns, and even rows of data. We set up groups, define the access for each group, and then assign users to individual groups. Tables and columns are farily easy. Setting up security for specific rows is a little trickier but can be done. We have one "user" defined for the database. The reporting software connects to the database as the one user. The reporting software handles all of the security.

Thanks for comment! It's nice to talk about datasecurity.

I've a few questions about your solution:
What's the name of the reporting software and is it suitable for server-client (like webserver,webclient) to show the reports.
In other terms, how do the user connect to the database, with an ODBC-connection? If so, i think the solution is not fully secured.

DePaul wrote:Correct me if I am wrong, but I've never found specific Business Intelligence security technics. You'd probably rather look for security in databases in general. I am not sure though....

Thanks for comment! It's nice to get some feedback.

Basically the security can be done in the data warehouse. But business intelligence is a philosophy how to implement the hole solution. Integrity is also a part of security and in my opinion when you're using information for reports, is it not necessary to have write access.

That's one reason for a wider perspective.

I think Tod's advice is spot on with presentation layer de-identification. Most good end-user tools allow this and it fits the rule of keeping the data useful for everyone while protecting identities and in some cases forcefully breaking the chain of legal record (if you can't ID the individual on a report found in a filing cabinet by a rather dogged lawyer 15 years after it was printed, it's not going to help him in court, right?).

But definitely don't do this in ETL. Ever.

Jeff is also right with record and column level securities applied at the reporting level. Just about all reporting tools can handle "hiding" data, be it columns or rows or by field. I would avoid hard coded department silos wherever possible.

I also do not think placing security in the DB is worth the time and effort. Depending on your client or company security structure, maintenance can be a huge hassle. Besides, the flexibility of applying security based on the end user login and data visibility in the tool that can be allowed/hidden with a simple click of a mouse is just too appealing.

Think of it this way: the kitchen needs all ingredients and all outgoing dishes, but depending on if you're at the bar, lounge or dining room you get a different menu picked from the overall offerings. Much better then 3 kitchens (silos) or 3 cooks (DBA/ETL/BI). It's all about conformed, consistent singular stores of data serving all departments.

Also, to address your ODBC connection question, users would typically connect to the tool which would have its own ODBC connection. The tool would have read-only access, and the tool would pull only those records or columns that the user is allowed to see. The user has no ODBC connection to abuse. It is secure.

Personally I prefer to implement the security through the reporting layer if possible. The drawback is that if another application other than your reporting tool is going to access the data then that application has to re-engineer the same security model-and keep them in sync. Done at the database level you avoid this problem. In the past we've gotten the business to agree to only use one method for viewing the data but this isn't always possible. The same is true for aggregate navigation done in the reporting tool (like BO) rather than the database.

Personally I'd recommend using the reporting tool to implement security and make the business aware of the trade-offs.

I haven't done much with de-identification of data. Just a little bit of Social Security Number hashing. Sorry.

BrianJarrett wrote:Personally I prefer to implement the security through the reporting layer if possible. The drawback is that if another application other than your reporting tool is going to access the data then that application has to re-engineer the same security model-and keep them in sync. Done at the database level you avoid this problem. In the past we've gotten the business to agree to only use one method for viewing the data but this isn't always possible. The same is true for aggregate navigation done in the reporting tool (like BO) rather than the database.

Personally I'd recommend using the reporting tool to implement security and make the business aware of the trade-offs.

I haven't done much with de-identification of data. Just a little bit of Social Security Number hashing. Sorry.

Do you suppose this solution is the most prevalent (and most "recommended" by IT folks) because typically DBMS's just don't have a good user authorization strategy? While all RDBMS have the ability to grant specific rights to specific columns/rows, it isn't always a easy/flexible option and oftentimes requires a lot of manually administration from a DBA. As you mentioned, it is pretty rare that the only access to a data warehouse is done from a single BI tool; most often there are multiple tools as well as some rogue ODBC access. Security *should* be done at the database; but since there isn't yet a good DBMS solution, we can stuck re-implementing the same security solution across all access methods.

You know, I can't really say if it's the most prevalent across the board. It has been the most prevalent where I've worked in the past. The thing is, security implementation isn't a trivial thing to build, at least not anything robust. When you buy a tool that has it already built-in the argument becomes whether or not it's faster and/or cheaper to implement multiple security measures through different apps or to build one at the database level. In my experience decision-makers have tended to lean toward using each app's built-in security model. In the end I'd imagine that each situation is a case-by-case basis.

Hope this helps.

TStahr wrote:Security *should* be done at the database; but since there isn't yet a good DBMS solution, we can stuck re-implementing the same security solution across all access methods.

TStahr wrote:While all RDBMS have the ability to grant specific rights to specific columns/rows, it isn't always a easy/flexible option and oftentimes requires a lot of manually administration from a DBA.

It's hard to find a good solution for all cases. Nice conclusion that security had to be done in the DBMS. I think a pro for managing security in a DBMS is: central administration. And TStahr suggest a con: not easy and flexible and a lot manually administration from a DBA.

I disagree your statement: there isn't yet a good DBMS security solutions. Are you familiar with Dimension Security for authorization? This is a fine-grained security measure on row-level. Do people know similar solutions for Oracle and other DBMS?

Do you have other pros and cons?

Another question about ODBC-connections. What determine the user for authentication? Is it a main user ReportingToolUser? Or the logonuser of domain?

BrianJarrett wrote: In my experience decision-makers have tended to lean toward using each app's built-in security model. In the end I'd imagine that each situation is a case-by-case basis.

I agree the best security is multiple security. More layers is harder to break through.

@Peeg: In Oracle you implement row level security and fine grained security via a feature called Virtual Private Database.

In my opinion security needs to be implemented at RDBMS level. You don't want to reimplement your security if you throw out your BI tool or have multiple of them.

Yeah, At least on the RDBMS

I can understand why some people would want to put security directly on the database as opposed to the reporting layer. But, if you put security on the database, you still have to put security on the reporting layer. You still need to set up security on Individual Reports. You also need to set up security on the ad hoc query interface unless you want users to see columns or aspects of the database that they don;t have access to.

Now you have 2 levels of security that have to be coordinated, which can be very complex. You run the risk of having reports that you want users to see but don't have read rights to the supporting tables (the table level information is sensitive but the report contains summed up information which is not sensitive).

I think that you need to have some security at the database level, but most of the detailed security should be through the reporting environment. The downside is that you will have to rebuild the security if you replace the reporting software. But, if you are replacing your reporting software, rebuilding resecurity will be the least of your worries.

Nice vision, I totally agree with you!

Hi,

BI is the latest management catchphrase for next-generation data warehousing. Where data warehousing focuses on data integration, BI is concerned with data governance -- the practice of using integrated data to make strategic business decisions about expenditures, workflow and product quality.

The most rudimentary security technique to secure BI is to apply access controls to the data.

Users should only be granted access to data on an as-needed basis. Having access to the wrong data not only means potential security vulnerabilities, but can result in erroneous analysis results and wasted time – what happens if you analyze the results for someone else’s sales territory by mistake? Proper access controls would prevent this kind of scenario.

This is a topic where there can be endless discussions. If you have any queries let me know.