New QlikView Governance

[#1]

Hopefully this isn't so open a question that it begs for "tough love" responses.

My company has just ended the selection process and we are starting with QlikView. Actually we started about 4 months ago on trial license and Personal Editions while the procurement group caught up. We anticipate this growing out like wild fire and have just installed the QV server hardware something like 40 cores and 256GB RAM. I'm not directly responsible for the program, but have a lot of enterprise IT experience and am worried that this may get out of hand like I've seen with SharePoint (sites not consistent, too many administrators, no controls...). I think that this is an awesome product and don't want to see a mistake early on cause it to get a black eye.

I've read through a number of posts and documents including many by John Callan. I have been through training (Dev and Designer). I setup QV Publisher and have several documents like the MetaScanner/Monitor, QlikView Ops Monitor, QVSystemMonitor_v3.2, and a couple of others reloading on schedule. That seems like a good start on the server monitoring outside of what our IT server admins do. Now I really want to get the development lifecycle documented.

One of my biggest concerns is controlling the data presented by dashboards. I have concerns that QVWs will be proxies to sensitive information like payroll data. If I have access to the whole general ledger and went through our existing process to gain that access, I could then publish a QVW that effectively bypasses that intial controled access procedure. On top of that, I could chain QVWs (and QVDs) together in such a way that it would require some effort to trace back the data to the source to determine the allowable audience. Currently, I will be proposing a series of documentation to be completed by the developer (and probably included on a standardized script tab) that explains the interfaces, data sources, data source owners, and intended audience. This is not fleshed out yet, but is primarily in my head. I am going to propose that a consultant be brought in to help get things started, but I don't know if we will get buy off on that considering the amount of money we just spent (I know, seems penny wise/pound foolish).

So, without asking for details (unless you are willing to share of course), generally, what are people doing to control this data proxy issue?

Thanks

[rwunderlich · Accepted solution]

You have correctly and wisely anticipated that you have some security perimeter issues to deal with. You have to establish procedures to ensure your security policies flow through to QV and audit compliance with those procedures. Here's some practical tips.

1. Document the security policies and responsibilities. Include statements like "payroll extract will not include salary or SSN". Document policies like QVW/QVD data should not be removed from the designated shared directories. Document (as you've proposed) the information that must be provided to get a new application approved and installed.

2. Limit access to the interface(s) that can extract data from sensitive  source systems. For example, grant read to the Payroll database to one individual (or limited group) who is responsible for creating the Extract QVD. This individual is responsible for understanding and complying with the policy and knows to raise the flag if new requirements require the policy to be revised. There may then be a larger group who is authorized to read those extracted QVDs.

3. Don't imbed userid/password in connection strings. That allows anyone with the script statement to have access to the source. Instead use trusted connections / pass through authentication.

4. Segregate sensitive QVDs and QVWs into thier own folders (in development and prod) with limited access rights. Beware of folder inheritance! Best to establish groups like "QV Developers - Payroll". 

5. If you use live data in development, use the scrambling feature on sensitive fields.

6. Create AD groups for document distribution and document for each group who in the business authorizes group membership.

7. Limit file system access to production -- only for Administrators.

8. Audit!

9. Audit NTFS or DMS permissions against the groups defined.

10. Audit actual QVW usage.

11. Audit data usage. You can, with a bit of creativity, determine that data from source table Payroll.Employees is used in QVWs A.qvw & B.qvw, and that users D,E,F have access to those dashboards.

There is a new book -- "Qlikview for Enterprises" by A. Rajendran -- due out soon. I was fortunate to read a review copy and it has an excellent chapter on security. I recommend checking it out when it's available.

-Rob

http://robwunderlich.com

[kaushiknsolanki]

Dear Rick,

        QlikView has ways to handle this kind of data security issues.

        QlikView uses Section Access for hiding data of an application from the people who are not authorized to see it.

        I would recommend you to hire a consultant who has good experience in QlikView and discuss all the possibilities of security.

        Its better to spent some more money instead of wasting your investment which you had made.

Regards,

Kaushik Solanki

[#4]

Thanks for the response Kaushik. I have data reduction and section access up and running no problem. The problem is not how to handle the issue when it is identified, but making sure that data access controls are defined during development and placing the appropriate gates in the change control process to make sure that they are not bypassed.

I am working on a document and process (10 years of process automation and improvement does that to you) that defines the steps to go from development to QA/UA to Production. I think that in itself is a problem in that I really want a pre-prod environment but again don't control the purse strings and don't think I could free up the money even if I did.

Rick

[#5]

Thanks Rob. I found that book and am looking forward to it. It is interesting that in my last job doing EAI and SOA, I frequently intentionally proxied data as a service endpoint but now that I know the problems with that, it really concerns me.

Again, thanks for the post.

[#6]

The book referred (QlikView for Enterprises - A Practitioner's Reference) is available for purchase in Createspace eStore, Amazon US, Amazon UK, Amazon France, Amazon Italy, Amazon Germany, Amazon Spain, Pothi.com India and Flipkart.  Foreword by Johan Averstedt of QlikTech Sweden who helped QlikTech expand in the international markets. This is similar to Ralph Kimball's Datawarehousing book - focus more on approach, principles and design for QlikView. Beginners to Experts everyone can benefit from this book. You can read the Foreword and Preface on this page: [http://www.qv4ent.com/excerpt]. Amazon has the "Look Inside" to view pages inside the book.

[datanibbler]

Hi Rob,

´sorry for "linking in" to this discussion: What is that scrambling feature you mention?

I am supposed to build some KPI displays of HR data and I want to do all that is possible to be on the safe side myself as well as avoid any further issues.

I am just drafting up a document that will be reviewed and signed by a number of persons, incl. the shop commitee and top_mgmt.

Scrambling of fields like personell_number in the GUI would simplify getting it approved at all. For my QV app to make any sense, the personell_number has to be there in plain text - not being able to identify employees through that app would in a way render it useless - but I could then implement another security layer before those are displayed in plain text.

Thanks a lot!

Best regards,

DataNibbler