Who, What, Where and How: Why You Want to Know

Eric Kavanagh: Ladies and gentlemen, hello and welcome back, once again, to Hot Technologies! Yes indeed, of 2016. We’re in year three of this show, it’s very exciting stuff. We’ve been rocking and rolling this year. This is Eric Kavanagh, your host. The topic for today – this is a great topic, it has lots of applications all across a number of industries, quite frankly – “Who, What, Where and How: Why You Want to Know.” Yes indeed, we’re going to talk about all that fun stuff. There’s a slide about yours truly, hit me up on Twitter @eric_kavanagh. I try to re-tweet all mentions and re-tweet anything someone sends to me. Otherwise, so be it.

It’s hot, yes indeed! The whole show here is designed to help organizations and individuals understand particular kinds of technology. We designed the whole program here, Hot Technologies, as a way of defining a particular kind of software, or a particular trend, or a particular kind of technology. The reason is because frankly, in the software world, you’ll often get these marketing terms that get bandied about and sometimes they can frankly bastardize the concepts they were intended to describe.

In this show we’re really trying to help you understand what a particular kind of technology is, how it works, when you can use it, when you should not use it perhaps, and give you as much detail as we possibly can. We’ll have three presenters today: our very own Robin Bloor, chief analyst here at the Bloor Group; our data scientist calling in from Sydney, Australia on the other side of the planet, Dez Blanchfield, and one of our favorite guests Bullett Manale, director of sales engineering at IDERA.

I’ll just say a couple of things here, understanding who is doing what with which piece of data, well that’s kind of like governance, right? If you think about all of the regulations around industries, like health care and financial services, on those domains, that stuff is incredibly important. You need to know who touched the information, who changed something, who accessed it, who uploaded it, for example. What’s the lineage, what’s the providence of this data? You can rest assured all of these issues are going to remain prominent in the years to come for all kinds of reasons. Not just for compliance, though HIPAA, and Sarbanes-Oxley, and Dodd-Frank, and all these regulations are very significant, but also just so you understand in your business who’s doing what, where, when, why and how. This is good stuff, we’re going to be paying attention.

Go ahead, take it away, Robin Bloor.

Robin Bloor: Okay, well thanks for that introduction, Eric. This area of governance is, I mean, governance in IT wasn’t a word that you heard until a little after the year 2000, I think. It came about primarily because, I think anyway, it came about primarily because there’s compliance legislation that was going on. Particularly HIPAA and Sarbanes-Oxley. There’s actually a lot of it. Therefore, organizations realized that they had to have a set of rules and a set of procedures because it was necessary under the law to do that. Long before that, particularly in the banking sector, there were various initiatives that you had to obey depending upon what kind of bank you were, and particularly the international bankers. The whole of the Basel compliant started way, way before that particular set of initiatives after the year 2000. It all really comes down on the governance. I thought I’d talk around the subject of governance as an introduction to the focus of keeping an eye on who’s getting the data.

Data governance, I used to look around I think about five or six years ago, look around for definitions and it wasn’t well defined at all. It’s become clearer and clearer as to what it actually means. The reality of the situation was that within certain limits, all data was actually previously governed, but there were no formal rules for it. There were special rules that were made particularly in the banking industry for doing stuff like that, but again that was more about compliance. In one way or another proving that you were actually a – it’s kind of associated with risk, so it’s proving that you were a viable bank was the deal.

If you look at the governance challenge now, it starts with a fact of the big data movement. We’ve got an increasing number of data sources. Data volume of course is an issue with that. In particular, we started to do much, much, more with unstructured data. It started to become something that is part of the whole of the analytics game. And because of analytics, data provenance and lineages are important. Really from the point of view of using data analytics in any way related to any kind of compliance, you really do have to have knowledge of where the data came from and how it got to be what it is.

Data encryption started to become an issue, become a bigger issue as soon as we went to Hadoop because the idea of a data lake in which we store a lot of data, suddenly means that you have a huge area of vulnerability of people who can get at it. The encryption of data became much more prominent. Authentication was always an issue. In the older environment, strictly mainframe environment, they had such wonderful perimeter security protection; authentication was never really much of an issue. Later on it became a bigger issue and it’s much more of an issue now because we’ve got such hugely distributed environments. Data access monitoring, that became an issue. I seem to remember various tools coming into existence about ten years ago. I think most of those were driven by compliance initiatives. Therefore we’ve also got all of the compliance rules, compliance reporting.

The thing that’s come to mind is that even back in the 1990s, when you were doing clinical trials in the pharmaceuticals industry, you not only had to be able to prove where the data came from – obviously it’s very important, if you’re trying out a drugs in various contexts, to know who is being tried on and what the contextual data around it – you had to be able to provide an audit of the software that actually created the data. It’s the most severe piece of compliance I’ve ever seen anywhere, in terms of proving you aren’t actually messing things up deliberately or accidentally. In recent times, particularly data life cycle management has become an issue. All of these are in a way challenges because a lot of these have not been done well. In a lot of circumstances it’s necessary to do them.

This is what I call the data pyramid. I’ve kind of talked through this before. I find it a very interesting way of looking at things. You can think of data as having layers. Raw data, if you like, are really just signals or measurements, recordings, events, single records mostly. Possibly transactions, calculations and aggregations of course create new data. They can be thought of at the level of data. Above that, once you actually connect data together, it becomes information. It becomes more useful, but of course it becomes more vulnerable to people hacking it or abusing it. I define that as being created, really, through the structuring of data, being able to visualize the data having glossaries, schemas, ontologies on the information. Those two lower layers are what we process in one way or another. Above that is what I call the layer of knowledge consisting of rules, policies, guidelines, procedure. Some of which may actually be created by insights discovered in analytics. A lot of them are actually policies that you have to abide by. This is the layer, if you like, of governance. This is where, in one way or another, if this layer isn’t properly populated, then the two layers below are not being managed. The final point about this is, understanding in something that resides only in human beings. Computers haven’t managed to do that yet, thankfully. Otherwise, I’d be out of a job.

The governance empire – I kind of put this together, I think it must have been about nine months ago, possibly a lot earlier than that. Basically, I kind of enhanced it but as soon as we started to get concerned about governance, then there was, in terms of the corporate data hub, there was not just data reservoir, data lake resources, but also general servers of various sorts, specialist data servers. All of which needed to be governed. When you actually looked at the various dimension as well – data security, data cleansing, metadata discovery and metadata management, creating a business glossary, data mapping, data lineage, data life cycle management – then, performance-monitoring management, service-level management, system management, which you might not actually associate with governance, but a certain – now that we’re going to a faster and faster world with more and more dataflows, actually being able to do something with a particular performance is actually a necessity and starts to become a rule of operation rather than anything else.

Summarizing in terms of the growth of compliance, I watched this happen over many, many years but the general data protection actually came in the 1990s in Europe. It just got more, and more sophisticated since then. Then, all of these things started to get introduced or being made more sophisticated. GRC, that’s governance risk and compliance, has been going on since the banks did Basel. ISO has been creating standards of various sorts of operations. I know for all the time that I’ve been in IT – it’s been a long time – U.S. government has been particularly active in creating various legislations: SOX, there’s Gramm-Leach-Bliley, HIPAA, FISMA, FERPA. You’ve also got the wonderful NIST organization that creates many standards, particularly security standards, very useful. The data protection laws in Europe have local variances. What you can do with personal data in Germany, for example, is different than what you can do in Slovakian republic, or Slovenia, or wherever. They introduced recently – and I thought I’d mention this because I find it amusing – Europe is introducing the idea of the right to be forgotten. That is, there ought to be a statute of limitations on data that has been public that actually is personal data. I think that’s hilarious. From an IT point of view that’s going to be very, very difficult if it starts to become effective legislation. In summary I’d say the following: Because IT data and management are evolving quickly, governance must also evolve quickly and it applies to all of the areas of governance.

Having said that I’ll pass the ball to Dez.

Eric Kavanagh: Yes indeed, so Dez Blanchfield, take it away. Robin, I’m with you, man, I’m dying to see how this right to be forgotten plays out. I think that it’s not going to be just challenging but basically impossible. It’s just a violation of waiting to be exercised upon by government agencies. Dez, take it away.

Dez Blanchfield: It is indeed and that’s a topic for another discussion. We have a very similar challenge here in Asia-Pacific, and particularly in Australia where carriers and ISPs are required to log everything related to internet and be able to record and regurgitate it in case somebody of interest does something wrong. It is a law and you have to comply with it. The challenge, just as somebody in Google in the USA might get told to delete my search history or whatever, it might be to comply with European law, particularly German privacy law. In Australia if an agency wants to look into you, a carrier has to be able to provide details of calls and search history made, which is challenging, but it is the world we live in. There is a bunch of reasons for that. Let me just jump into mine.

I deliberately made my title page hard to read. You’ve got to really look hard at that text. Compliance, conforming to a set of rules, specifications, controls, policies, standards or laws, with a silly, messy background. That’s because you’ve got to really look at it hard to get the detail and pull out information from what it’s overlaid, which is a series of tables and rows and columns, either a database, a scheme or a mock-up in Visio. That’s what compliance sort of feels like day to day. It’s quite hard to dive into the detail and pull out the relevant bits of information you need to be able to confirm that you are compliant. Report on that, monitor it and test it.

In fact, I thought a really good way to visualize this when we ask ourselves the question, “Are you compliant?” “Are you sure?” “Well, prove it!” There’s a really fun thing that is perhaps a little bit more Anglo-Celtic but I’m sure it’s made its way around the world into the U.S., so it’s: “Where’s Wally?” Wally is a little character that gets into these cartoon drawings in the form of books. Usually very large-scale pictures of A3 or bigger. So, table-size drawings. He’s a little character that wears a beanie and a red-white striped shirt. The idea of the game is you look at this picture and you look around in circles to try and find Wally. He’s in that picture there somewhere. When you think about how to discover and describe and report on compliance, in many ways it’s like playing “Where’s Wally.” If you look at that picture, it’s almost impossible to find the character. Children spend hours on that and I had a lot of fun doing that myself yesterday. When we look at it, we find a whole bunch of people in these cartoons, deliberately placed there with similar pieces of the Wally outfit of a striped beanie and a jersey, or woolen top. But they turn to be false positives.

This is a similar challenge we have with compliance. When we’re looking at things, sometimes something we think this is the case, isn’t the case at all. Somebody might have access to a database and they’re supposed to have that access to a database but the way in which they’re using it is slightly different from what we expect. We might decide that that’s something we need to look at. When we look into it we find, oh actually, that’s a very valid user. They’re just doing something quirky. Maybe it’s a PC researcher or who knows. In other cases, it might be the opposite. The reality, when I go forward again, there’s Wally. If you looked really hard in this high resolution there’s one character who’s actually wearing the proper attire. All the others are just lookalikes and feel-alikes. Compliance feels very much like that. Most people I know, they work in controls and compliance and policies areas of businesses. In whole range of areas, whether it’s technology, whether it’s finance, or operation, and risk. Often it’s very hard to see the Wally in the picture, you’ll see the trees or the wood.

The question that we ask ourselves, when we think about things like compliance, is “Big deal, what could possibly go wrong if we don’t quite meet the compliance?” In the context of today’s discussion, specifically around database and control of access to data, I’m going to give you some very real wake-up call examples on what can go wrong in very short succinct form. If we think of data breaches, and we’re all familiar with data breaches, we hear them in the media, and we kind of stop and laugh, because people think it’s markets. It’s personal things. It’s Ashley Madison and people who are looking to get dates outside their relationships and marriages. It’s fling accounts. It’s all these weird things or some random European or Russian ISP or hosting company gets hacked. When it gets to things like MySpace and these top ten, when you look at these numbers, what I want you to realize is this: 1.1 billion people’s details in these top ten breaches. And yes, there’s overlaps, there’s probably people who’ve got a MySpace account, and a Dropbox account, and a Tumblr account, but let’s just round it up to a billion people.

These top ten breaches for the last decade or so – not even a decade, in most cases – sum up approximately one-seventh of the world’s population of human beings, but more realistically, about 50 percent of the number of people are connected to the internet, over a billion individuals. These come about because compliance hasn’t been met in some cases. In most cases, were controls of access to database, control of access to particular data sets, and systems, and networks. This is a scary reality check. If it doesn’t frighten you, when you look at the top ten and you can see that this is a – or can see this is one billion individuals, real human beings just like us, on this call right now. If you have a LinkedIn account, if you had a Dropbox account, or a Tumblr account or if you bought from Adobe products or even registered download free Adobe viewer. It’s entirely likely, not possible, it’s entirely likely that your details, your first name, your last name, your email address, potentially even your work company address, or your home address or your credit card, are actually out there because of a breach that took place because of the controls, that weren’t necessarily managed well in the form of data management, data governance.

Let’s have a look at it when we look at it in real detail. There’s one screen of them, there’s about 50-something there. There’s another 15. There’s about another 25. These are data breaches that are listed on a website called haveibeenpwned.com. This is what could possibly go wrong if something simple as controlling who has had access to data in databases in different fields and rows and columns and different applications in your business, aren’t managed properly. These organizations are now data driven. Most data lives in a database in some form. When you think about that, that list of breaches that we just looked at, and hopefully it’s given you a bit of a cold shower in a sense, in that you thought “Hmm, that’s very real,” and it potentially has impacted you. In 2012, that breach of LinkedIn for example, most professionals have a LinkedIn account these days and it’s likely that your details are lost. They’ve been out on the internet since 2012. We’ve only just been told about it in 2016. What happened with you information in those four years? Well it’s interesting and we can talk about that separately.

Database and systems management – I often talk about what I consider the top five challenges in managing these things. At the very, very top and I’m ranking these in order of preference from myself, but also order of impact, number one is security and compliance. The controls and mechanisms, and policies around controlling who has what access to what system, for what reason and purpose. Reporting on that and monitoring it, looking into the systems, looking into the databases, and seeing who can actually access records, individual fields and records.

Think about this in a very simple form. Let’s talk about banking and wealth management as one example. When you sign up for a bank account, let’s just say a normal cash account for an EFTPOS card, or a cash account or a check account. You fill in a form and there’s lots of very private information in that piece of paper that you fill in or you do it online and that goes into a computer system. Now, if somebody in marketing wants to contact you and send you a brochure, they should be allowed to see your first name, and last name, and your personal address, for example and potentially your phone number if they want to cold call you and sell you something. They probably shouldn’t see the total amount of money you’ve got in the bank for a bunch of reasons. If somebody is looking at you from a risk point of view, or trying to help you do something like get better interest rates on your account, that particular person probably wants to see how much money you’ve got in the bank, so they can offer you the appropriate levels of interest return on your money. Those two individuals have very different roles and very different reasons for those roles, and purposes for those roles. As a result, need to see different information in your record, but not all of the record.

These controls around the different report of usual screens or form that they have in the applications that are used to manage your account. The development for those, the maintenance of those, the administration of those, the reporting around those, and the governance and compliance wrapped around those like bubble wrap, all are a very, very large challenge. That’s just the number one challenge in managing data and systems. When we go deeper down that stack into performance and monitoring, and incidence detection and response, management and administration of the system, and compliance around them, the design and development of the systems from the compliance, it gets much much harder.

Managing the whole issue of reducing risks and improving security. My top five challenges in this space – and I like the imagery that goes with a customs desk when you’re entering a country – they present your passport, and they check you out, and they look at their computer system to see whether you should pass or not. If you shouldn’t, they put you on the next plane back home. Otherwise, they let you back in and they ask you questions like, “Are you coming on a holiday? Are you here a tourist? Are you here for work? What kind of work are you going to see? Where are you going to stay? How long are you coming for? Have you got enough money to cover your expenses and costs? Or are you going to become a risk to the country that you’re in and they might have to look after you and feed you?”

There are some issues around this space of data, managing data protection. For example in the database space, we need to think about mitigating database bypasses. If the data is in the database, in a normal environment and there’s controls and mechanisms around that in the system. What happens if a dump of the data is made in more SQL and backed up to tape? The databases are dumped in raw form and backed up sometimes. Sometimes it’s done for technical reasons, development reasons. Let’s just say a DB dump was taken and it’s backed up to tape. What happens if I happen to get my hands on that tape and restore it? And I’ve got a raw copy of the database in SQL. It’s an MP file, it’s text, I can read it. All the passwords that are stored in that dump have no control over me because I’m now getting access to the actual content of the database without the database engine protecting it. So I can technically bypass the security of the database platform that’s being built in the engine with compliance, and risk management to stop me looking at the data. Because potentially the developer, system administrator, I’ve got my hands on a full dump of the database that should be used for backups.

Misuse of the data – potentially getting somebody to log in as their elevated account and letting me sit at the screen, looking for information, or similar things. Proprietary auditing, of the access and use of the data, and viewing of the data or changes to the data. Then the reporting around that control and the compliance required. Monitoring the traffic and access and so forth, blocking threats that come from external locations and servers. For example if the data is presented via a form on a webpage on the internet, have their SQL injections been protected through firewalls and concept controls? There’s a long detailed story that goes behind this. You can see here that just some of these absolutely fundamental things that we think about in mitigating and managing risk around data inside databases. It’s actually relatively easy to get round some of these if you’re at different levels of stacks of the technologies. The challenge gets harder and harder as you get more and more data, and more databases. More, and more challenging with people having to manage the systems, and monitor use of them, track the relevant details that specifically pertain to things that Robin spoke about, around things like personal compliance. Individuals have controls and mechanisms around them that comply – if you do something wrong, you’re potentially fired. If I log in as my account let you see it, that should be a fireable offense. Now I’ve given you access to data that you shouldn’t have seen normally.

There’s personal compliance, there’s corporate compliance, companies have policies and rules, and controls that they’ve set upon themselves just so the company runs well and provides a return on profit and a good return to investors and shareholders. Then there’s often citywide or statewide or national, federal as you said U.S. controls and laws. Then there’s global ones. Some of the bigger incidents in the world, where the likes of Sarbanes-Oxley, two individuals who are asked to come up with ways on how to protect data and systems. There’s Basel in Europe and there’s all range of controls in Australia, particularly around stock exchange and credential platforms, and then privacy at individual or company level. When each of these is stacked up as you saw in one of the sites that Robin had, they become almost a near-impossible mountain to climb. The costs get high and we’re at the point where original traditional approach you know, like human beings measuring control, is no longer an appropriate approach because the scale is too big.

We have a scenario where compliance is what I call now an always-on issue. And that is that we used to potentially have a point in time, either monthly or quarterly or annually, where we would review our state of the nation and help to compliance and control. Making sure that certain people had certain access and didn’t have certain access depending on what their permissions were. Now it’s a case of the speed of things with which things move, the pace at which things change, the scale at which we’re operating on. Compliance is an always-on issue and the global financial crisis was just one example where the relevant controls, and measures in security and compliance could have potentially avoided a scenario where we had a runaway freight train of certain behavior. Just creating a situation with the entire world effectively knowing it would go broke and bankrupt. To do that, we need the right tools. Throwing human beings at the train, throwing bodies is no longer a valid approach because the scale is too big and things are moving too fast. The discussion today, I think we’re going to have, are about the types of tools to apply to this. In particular the tools that IDERA can provide to us that should to do that. And with that in mind, I’m going to hand it to Bullett to walk through his material and show us their approach and the tools that they’ve got to solve this problem that we’ve tabled now for you.

With that, Bullett, I will hand to you.

Bullett Manale: Sounds great, thank you. I want to talk about a few slides and I also want to show you a product that we use for SQL Server databases specifically to help out with compliance situations. Really, the challenge in a lot of cases – I’m going to skip through a few of these – this is just our portfolio of products, I’m going to go through that pretty quickly. In terms of really where this product’s going to address and how it relates to compliance, I always pull up this as like the first slide because it’s kind of a generic, “Hey, what is the responsibility of a DBA?” One of the things is controlling and monitoring user access and also being able to generate reports. That’s going to tie in to when you’re talking to your auditor, how difficult that process can be is going to vary depending on if you’re going to do it on your own or if you’re going to use a third-party tool to help.

Generally speaking, when I’m talking to database administrators, a lot of times they’ve never been involved in an audit. You kind of have to educate them into really what it is that you really are having to do. Related to what type of compliance that is needing to be fulfilled and being able to prove that you’re actually following the rules as it applies to that level of compliance. A lot of people don’t get it at first. They think, “Oh, I can just buy a tool that will make me compliant.” The reality is, that’s not the case. I wish I could say that our product magically, by you know, hitting the easy button, gave you the ability to make sure that you’re in compliance. The reality is that you have to have your environment set up in terms of the controls, in terms of how people are accessing the data, that all has to be worked out with the application that you have. Where that sensitive the data is being stored, what type of regulatory requirement it is. Then, also having to work with typically an internal compliance officer as well to be able to make sure you’re following all the rules.

This sounds really complicated. If you look at all the regulatory requirements, you’d think that that would be the case, but reality is that there is a common denominator here. In our case with the tool that I’m going to show you today, the Compliance Manager product, the process in our situation would be that, we first and foremost, we need to make sure we’re collecting the audit trail data, related to where the data is in the database that’s sensitive. You can collect everything, right? I could go out and say I want to collect every transaction that happens on this database. The reality is that you probably only have a small fraction or a small percentage of transactions that are actually related to the sensitive data. If it’s PCI compliance it’s going to be around the credit card information, the owners of the credit cards, their personal information. There might be a ton of other transactions as it relates to your application, that don’t really have any bearing on regulatory requirement of PCI.

From that standpoint, the first thing when I talk to DBA is I say, “The number one challenge isn’t trying to get a tool to do these things for you. It’s just knowing where is that sensitive data and how are we locking that data down?” If you have that, if you can answer that question, then you’re halfway home in terms of being able to show that you’re in compliance, assuming you are following the right controls. Let’s say for a second that you are following the right controls and you told the auditors that that’s the case. The next part of the process is obviously being able to provide an audit trail that shows and validates those controls are actually working. Then, following that up with making sure you save that data. Usually with things like PCI and HIPAA compliance, and those types of things, you’re talking seven years’ worth of retention. You’re talking about a lot of transactions and a lot of data.

If you are keeping, collecting every transaction even though only five percent of the transactions are related to the sensitive data, you’re talking about a pretty big cost associated to having to store that data for seven years. That’s one of the biggest challenges, I think, is in getting people’s head around that to say, that’s a really unnecessary cost, obviously. It also is a lot easier if we can just focus granularly on the sensitive areas within the database. In addition to that you’re also going to want controls around some of the sensitive information as well. Not just to show in terms of an audit trail, but to also be able to tie things back to actions that are happening and be able to get notified in real time, so that you can be made aware of that.

The example I always use, and it may not be necessarily related to any type of regulatory requirement but just being able to track, for example, somebody were to drop the table associated to the payroll. If that happens, the way you find out about it, if you’re not tracking that, is nobody gets paid. That’s too late. You want to know when that table gets dropped, right when it gets dropped, to avoid any bad things that happen as a result of some disgruntled employee going and deleting the table that’s tied directly to payroll.

With that said, the trick is finding the common denominator or using that common denominator to map what the level of compliance is. That’s kind of what we try to do with this tool. We basically take the approach of, we’re not going to show you a report that’s specific to PCI, specific to stocks; the common denominator is you have an application that’s using SQL Server to store the sensitive data within the database. Once you kind of get over that you say, “Yeah that’s really the main thing we need to focus on – where is that sensitive data, and how is it being accessed?” Once you have that, there’s a ton of reports that we offer that can provide that proof is, you will, within compliance.

Going back to the questions that are asked by an auditor, the first question’s going to be: Who has access to the data and how are they getting that access? Can you prove that the right people are accessing the data and the wrong people aren’t? Can you also prove that the audit trail itself is something that I can trust as an immutable source of information? If I’m giving you an audit trail that’s fabricated, it doesn’t really do me much good as an auditor to patch your audit if the information is fabricated. We need proof of that, typically from an auditing perspective.

Going through those questions, kind of a little bit more detailed. The challenge with the first question is, you have to know, like I said, where that sensitive data is in order to report on who’s accessing it. That’s usually some type of discovery and really you’ve got thousands of different applications that are out there, you’ve got tons of different regulatory requirements. In most cases you want to work with your compliance officer if you have one, or at least somebody that would have some additional insight in terms of really where my sensitive data is within the application. We have a tool that we have, it’s a free tool, it’s called a SQL Column Search. We tell our prospective customers and users that are interested in that question, they can go download it. What it’s going to do is it’s going to basically look for the information within the database that’s going to be likely sensitive in nature.

And then once you do that, you also have to understand how people are accessing that data. And that’s going to be, once again, which accounts are within which Active Directory groups, which database users are involved, there’s a role memberships associated to this. And keeping in mind, of course, that all of these things that we’re talking about have to be approved by the auditor, so if you say, “This is how we’re locking down the data,” then the auditors can come back and say, “Well, you’re doing it wrong.” But let’s say that they say, “Yeah, that looks good. You’re locking down the data sufficiently.”

Moving on to the next question, which is going to be, can you prove that the right people are accessing that data? In other words, you can tell them your controls are, this is the controls you’re following, but unfortunately the auditors are not real trusting individuals. They want proof of it and they want to be able to see it within the audit trail. And this goes back to that whole common denominator thing. Whether it’s PCI, SOX, HIPAA, GLBA, Basel II, whatever, the reality is, is that the same types of questions are going to be typically asked. The object with the sensitive information, who has accessed that object within the last month? That should map up to my controls and I should be able to pass my audit eventually by showing those types of reports.

And so what we’ve done is we’ve compiled about 25 different reports that follow on those same kind of areas as that common denominator. So we don’t have a report for PCI or for HIPAA or for SOX, we have reports that, once again, they go up against that common denominator. And so it doesn’t really matter what regulatory requirement you’re trying to fulfill, in most cases you’re going to be able to answer whatever question’s posed to you by that auditor. And they’re going to tell you who, what, when and where of every transaction. You know, the user, the time the transaction occurred, the SQL statement itself, the application that it came from, all that good stuff, and then also be able to automate the delivery of this information to reports.

And then, once again, once you get past that and you’ve provided that to the auditor, then the next question’s going to be, prove it. And when I say prove it, I mean prove that the audit trail itself is something we can trust. And the way we do that in our tool is we have hash values and CRC values that tie directly back to the events themselves within the audit trail. And so then the idea is, is that if somebody goes out and deletes a record or if somebody goes out and removes or adds something to the audit trail or changes something in the audit trail itself, we can prove that that data, the integrity of the data itself, was violated. And so 99.9 percent of the time if you’ve got our audit trail database locked down, you won’t run into that problem because when we run that integrity check we’re essentially proving to the auditor that the data itself hasn’t been changed and deleted or added to since the original writing from the management service itself.

So that’s kind of a general overview of the typical kinds of questions that you’d be asked. Now, the tool that we have to address a lot of this is called SQL Compliance Manager and it does all those things in terms of tracking the transactions, the who, what, when and where of the transactions, being able to do that in a number of different areas as well. Logins, failed logins, schema changes, obviously data access, select activity, all those things that are happening within the database engine. And we’re also being able to alert the users of specific, very granular conditions, if need be. For example, somebody’s going out and actually viewing the table that contains all my credit card numbers. They’re not changing the data, they’re just looking at it. In that situation I can alert and I can let folks know that that’s happening, not six hours later when we’re scraping logs but in real time. It’s basically as long as it takes for us to process that transaction through a management service.

As I mentioned before, we’ve seen this used in a variety of different regulatory requirements and it doesn’t really – you know, any regulatory requirement, once again, as long as the common denominators, you have sensitive data in a SQL Server database, this is a tool that would help in that type of situation. To the 25 reports that are built in, now the reality is that we can make this tool good for the auditor and answers every single question that they ask, but the DBAs are the ones that have to make it work. So there’s also that thinking of, you know well, from a maintenance perspective we have to make sure that the SQL’s working the way that we want. We also have to be able to go in and look at the things that are going to be able to go out and look at other pieces of information, you know, as far as the archiving of the data, the automation of that and the overhead itself of the product. Those are things that we obviously take into account.

Which brings up the architecture itself. So on the very right side of the screen we’ve got the instances of SQL that we manage, everything from 2000 all the way up to 2014, getting ready to release a version for 2016. The biggest takeaway on this screen is that the management server itself is doing all the heavy lifting. We’re just collecting the data, using the trace API, built in with SQL Server. That information is trickling up to our management server. That management server itself is identifying and if there are any events tied to any types of transactions that we don’t want, sending out alerts, and those kinds of things, and then populating the data within a repository. From there we can run reports, we would be able to go out and actually see that information in the reports or even within the application’s console.

So what I’m going to go ahead and do is I’m going to take us through, real quickly, and I just want to point out one quick thing before we jump into the product, there’s a link on the website right now, or on the presentation, that’s going to take you to that free tool I mentioned earlier. That free tool is, like I said, it’s going to go out and look at a database and try to find the areas that look like sensitive data, social security numbers, credit card numbers, based on the namings of the columns or the tables, or based on the way that the format of the data looks, and you can customize that as well, so just to point that out.

Now, in our case let me go ahead and share out my screen, give me one second here. Alright, and so, what I wanted to take you to first is I want to take you to the Compliance Manager application itself and I’m going to go through this pretty quickly. But this is the application and you can see I’ve got a couple of databases here and I’m just going to show you how easy it is to go in and tell it what you’re looking to audit. From the standpoint of schema changes, security changes, administrative activities, DML, Select, we have all those options available to us, we can also filter that down. This goes back to the best practice of being able to say, “I really only need this table because it’s containing my credit card numbers. I don’t need the other tables that have product information, all those other things that aren’t relative to the level of compliance I’m trying to meet.”

We have also the ability to capture data and show it in terms of the values of the fields that are changing. In a lot of tools you’ll have something that will give you the ability to capture the SQL statement, show the user, show the application, time and date, all that good stuff. But in some cases the SQL statement itself isn’t going to give you enough information to be able to tell you what the value of the field was before the change occurred as well as the value of the field after the change occurred. And in some situations you need that. I might want to track, for example, a doctor’s dosage information for prescription drugs. It went from 50mg to 80mg to 120mg, I would be able to track that using the before and after.

Sensitive columns is another thing that we run into a lot, for example, with PCI compliance. In the situation here you’ve got data that is so sensitive in nature that just by looking at that information, I don’t have to change it, delete it, or add to it, I can cause irreparable harm. Credit card numbers, social security numbers, all that kind of good stuff we can identify sensitive columns and tie alerts to it. If anybody goes out and looks at that information we would be able to, obviously, alert and send an email or generate an SNMP trap and those kinds of things.

Now in some cases you’re going to run into a situation where you might have an exception. And what I mean by that, you have a situation where you have a user that has a user account that might be tied to some type of ETL job that runs in the middle of the night. It’s a documented process and I just simply do not need to include that transactional information for that user account. In that case we would have a trusted user. And then in other situations we would use the feature of Privileged User Auditing which is essentially, if I’ve got, let’s say for example, an application, and that application’s already doing auditing, of the users that are going through the application, that’s great, I already have something to reference in terms of my audit. But for the things that are tied to, for example, my privileged users, the guys that can go into SQL Server management studio to look at the data within the database, that’s not going to cut it. And so this is where we could define who our privileged users are, either through Role Memberships, or through their Active Directory accounts, groups, their SQL-authenticated accounts, where we’ll be able to choose all of those different types of options and then from there make sure that for those privileged users we can specify the types of transactions we’re interested in auditing.

These are all kinds of different options that you have and I’m not going to go through all of the different kind of types of things based on the time constraints here for this presentation. But I do want to show you how we can view the data and I think you’ll like how this works because there’s two ways we can do it. I can do it interactively and so when we talk to people that are interested in this tool for maybe their own internal controls, they just want to know what’s going on in a lot of cases. They don’t necessarily have auditors coming on site. They just want to know, “Hey, I want to go after this table and see who’s touched it in the last week or last month or whatever might be.” In this case you can see how quickly we can do that.

In the case of the health care database, I’ve got a table called Patient Records. And that table, if I were to just group by object, it could very quickly start to narrow where we’re looking for. Maybe I want to group by category and then maybe by event. And when I do that, you can see how quickly that shows up, and there’s my Patient Records table right there. And as I drill in we can now see the DML activity, we can see that we’ve had a thousand inserts of DML, and when we open up one of these transactions we can see the relevant information. The who, the what, the when, the where of the transaction, the SQL statement, obviously, the actual application being used to perform the transaction, the account, the time and the date.

Now if you look at the next tab over here, the Details tab, this goes back to that third question we’re talking about, proving that the integrity of the data has not been breached. So basically every event, we have a secret calculation for our hash value, and this is going to then tie back to when we do our integrity check. For example, if I were to go out to the tool, go into the auditing menu, and I were to go out and say, let’s check the repository integrity, I could point to the database where the audit trail is, it’ll run through an integrity check matching up those hash values and CRC values to the actual events and it’s going to tell us that there have been no problems found. In other words, the data in the audit trail hasn’t been tampered with since it was originally written by the management service. That’s obviously one way to interact with the data. The other way would be through the reports themselves. And so I’m just going to give you one quick example of a report.

And once again, these reports, the way we came up with them, are not specific to any type of standard like PCI, HIPAA, SOX or anything like that. Once again, it’s the common denominator of what we’re doing, and in this case, if we’re going back to that patient records example, we would be able to go out and say, in our case here, we’re looking at the health care database and in our case we want to focus specifically on that table that we know contains private information, in our case, related to our patients. And so, let me see if I can type it here, and we’re going to go ahead and run that report. And we’re going to see then, obviously, from there all of the relevant data associated to that object. And in our case it’s showing us for a month’s period of time. But we could go back six months, a year, however long we’ve been keeping the data for.

Those are the kind of ways in which you would be able to actually prove, if you will, to the auditor that you’re following your controls. Once you’ve identified that, then obviously that’s a good thing in terms of passing your audit and being able to show that you’re following the controls and everything’s working.

The last thing to kind of talk about that I wanted to demonstrate is in the administration section. There’s also controls from the standpoint of within this tool itself being able to set controls to be able to make sure that if somebody is doing something that they’re not supposed to be doing that I can be made aware of it. And I’ll give you a couple of examples there. I’ve got a login account that’s tied to a service and that service needs elevated permissions to do what it does. What I don’t want is somebody going in and using that account in Management Studio and then, you know, using it for things that it was not intended for. We would have two pieces of criteria here that we could apply. I could say, “Look, we’re really interested in this working, say, with our PeopleSoft application,” just as an example, okay?

Now that I’ve done that, what I’m saying here is, I’m curious to know any logins that are tied to the account I’m getting ready to specify, if the application that’s being used to log in with this account is not PeopleSoft, then that’s going to be a raise for the alarm. And obviously we have to specify the account name itself, so in our case let’s just call this Priv Account, for the fact that it’s privileged. Now once we’ve done that, when we do this here, now we would be able to then specify what we would want to have happen when that occurs and for each and every type of event or, I should say, alert, you can have a separate notification to the person that’s responsible for that particular piece of data.

For example, if it’s salary information it might go to my director of HR. In this case, dealing with the PeopleSoft application, it’s going to be the administrator of that application. Whatever the case may be. I would be able to put in my email address, customize the actual alert message and all that kind of good stuff. Once again, this goes all back to being able to make sure that you can show that you’re following your controls and that those controls are working the way that they are intended. From the last perspective here, just in terms of maintenance, we do have the ability to take this data and put it offline. I can archive the data and I can schedule it out and we would be able to kind of do these things very easily in the sense that you would actually be able to, as a DBA, that’s using this tool, set it up and kind of walk away from it There’s not a lot of hand holding that will take place once you have it set up the way it should be. Like I said, the hardest part about any of this, I think, is not setting up what you want audit, it’s knowing what you want to set up to audit.

And like I said, the nature of the beast with auditing, you have to keep the data for seven years, so it only makes sense to focus in those areas that are sensitive in nature. But if you want to go the approach of collecting everything, you absolutely can, it’s just not considered the best practice. So from that standpoint I’d just like to remind folks that if this is something that is of interest you can go onto the website at IDERA.com and download a trial of this and play around with it yourself. In terms of the free tool that we talked about earlier that’s, well, it’s free, you can go download it and use it forever, regardless of whether you’re using the Compliance Manager product. And the cool thing about that column search tool is that our findings that you come up with, and I can actually show that, I think, is that you will be able to export that data out and then be able to import it into Compliance Manager as well. I don’t see it, I know it’s here, there it is. This is just an example of that. This is where it’s finding the related sensitive data.

Now this case I’ve gone out and I’ve really, I’m having a look up at everything, but you have a just a ton of stuff that we can check for. Credit card numbers, addresses, names, all that kind of stuff. And we’ll identify where it is in the database and then from there you can make the decision as to whether or not you actually want to audit that information. But it’s definitely a way to make it a lot easier for you to define your scope of auditing when you’re looking at a tool like this.

I just will go ahead and close with that, and I will go ahead and pass it back to Eric.

Eric Kavanagh: That’s a fantastic presentation. I love the way you really get into the gritty details there and show us what’s going on. Because at the end of the day there is some system that’s going to access some records, that’s going to give you a report, that’s going to get you to tell your story, whether that be to a regulator or an auditor or someone on your team, so it’s good you know you’re prepared if and when, or as and when, that person comes knocking, and of course that’s the unpleasant situation that you’re trying to avoid. But if it happens, and it probably will happen these days, you want to be sure you have your I’s dotted and your T’s crossed.

There’s a good question from an audience member I want to throw out to perhaps you first, Bullett, and then if maybe a presenter wants to comment on it, feel free. And then maybe Dez ask a question and Robin. So the question is, is it fair to say that to do all those things that you have mentioned you need to start an effort of data classification at an elementary level? You need to know your data when it emerges as a valuable potential asset and do something about that. I think you’d agree, Bullett, right?

Bullett Manale: Yeah, absolutely. I mean, you’ve got to know your data. And I realize, I recognize that there’s a lot of applications that are out there and there’s a lot of different things that have got moving parts in your organization. The column search tool is very helpful in terms of going a step to the direction of understanding that data better. But yes, it is very important. I mean, you do have the option of going the firehose approach and auditing everything, but it’s just a lot more challenging that way logistically when you talk about having to store that data and report against that data. And then you still kind of need to know where that piece of data is because when you run your reports you’re going to need to show your auditors that information as well. So I think that, like I said, the biggest challenge when I talk to database administrators is knowing [inaudible], yeah.

Eric Kavanagh: Yeah, but maybe Robin we’ll bring you in real quick. It seems to me the 80/20 rule applies here, right? You’re probably not going to find every system of record that matters if you’re in some mid-sized or large organization, but if you focus on – like Bullett was suggesting here – PeopleSoft for example, or other systems of record that are predominant in the enterprise, that’s where you focus 80 percent of your effort and then 20 percent is on the other systems that may be out there somewhere, right?

Robin Bloor: Well I’m sure, yeah. I mean, you know, I think the problem with this technology, and I think it’s probably worth having a comment on it, but the problem with this technology is, how do you implement it? I mean, there is very definitely a lack of knowledge, let’s say, in most organizations as to even the number of databases that are out there. You know, there’s an awful lot of lack of inventory, let’s say. You know, the question is, let’s imagine that we’re starting in a situation where there isn’t a particularly well-managed compliance, how do you take this technology and inject it into the environment, not just in, you know, technology terms, setting stuff up, but like who manages it, who determines what? How do you start to shoehorn this into a genuine, doing-its-job kind of thing?

Bullett Manale: Well I mean, that’s a good question. The challenge in a lot of cases is that, I mean, you have to kind of start asking the questions right at the very beginning. I’ve run into a lot of companies where they, you know, maybe they’re a private company and they got acquired, there’s an initial, kind of, first, kind of, road bump, if you want to call it that. For example, if I’ve just now become a publicly traded company because of acquisition I’m going to have to go back and probably figure some stuff out.

And in some cases we talk to organizations that, you know, even though they’re private they follow SOX compliance rules, simply because in the event that they do want to get acquired they know they’ve got to be in compliance. You definitely don’t want to take the approach of just, “I don’t need to worry about this now.” Any type of regulatory compliance like PCI or SOX or whatever, you want to make the investment of doing the research or the understanding of where that sensitive information is, otherwise you might be finding yourself dealing with some significant, hefty fines. And it’s a lot better just to invest that time, you know, finding that data and being able to report against it and show the controls are working.

Yeah, in terms of setting it up, like I said, the first thing I would recommend to folks that are getting ready to face an audit, is to just go out and to do a cursory examination of the database, and figuring out, you know, in their best efforts, trying to figure out where that sensitive data is. And the other approach would be to start with maybe a larger net in terms of what the scope of auditing is, and then slowly curb your way down once you, kind of, figure out where those areas within the system are that are related to the sensitive information. But I wish I could tell you there’s an easy answer to that question. It’s going to probably vary quite a bit from one organization to the other and the type of compliance and really how, you know, how much structure they have within their applications and how many have, diverse applications they have, some can be custom written applications, so it’s really going to depend on the situation in a lot of cases.

Eric Kavanagh: Go ahead, Dez, I’m sure you’ve got a question or two.

Dez Blanchfield: I’m keen to just get some insight into your observations around the impact to the organizations from a people point of view, actually. I think one of the areas where I see the greatest value for this particular solution is that when people wake up in the morning and go to work at various levels of the organization, they wake up with a series of, or a chain of, responsibility that they’ve got to deal with. And I’m keen to get some insight into what you are seeing out there with and without the types of tools you’re talking about. And the context I’m talking about here is from the chairperson of the board level to the CEO and CIO and the C-suite. And now we’ve got chief risk officers, who are thinking more about the types of things we’re talking about here in compliance and governance, and then we’ve now got new role play chiefs, chief data officer, who’s, you know, even more concerned about it.

And on the side of each of them, around the CIO, we’ve got IT managers on one side with, sort of you know, technical leads and then database leads. And in the operational space we’ve got development managers and development leads and then individual developments, and they also loop back into the database administration layer. What are you seeing around the reaction of each of these different parts of the business to the challenge of compliance and regulatory reporting and their approach to it? Are you seeing that people are coming at this with a fervor and can see the benefit to it, or are you seeing that they’re reluctantly dragging their feet to this thing and just, you know, doing it for a tick in the box? And what are the kinds of responses you’re seeing once they see your software?

Bullett Manale: Yeah, that’s a good question. I would say that this product, the sales of this product, are mostly driven by somebody who’s in the hot seat, if that makes sense. In most cases that’s the DBA, and from our perspective, in other words, they know that there’s an audit coming up and they are going to be responsible, because they are the DBAs, to be able to provide the information that the auditor’s going to ask. They can do that by writing their own reports and creating their own custom traces and all those kinds of things. The reality is, is that they don’t want to do that. In most cases DBAs aren’t really looking forward to having those conversations with the auditor to begin with. You know, I would rather tell you that we can go call upon a company and say, “Hey this is a great tool and you’re going to love it,” and show them all the features and they’ll buy it.

The reality is that they’re typically not going to be looking at this tool unless they’re actually going to be faced with an audit or the other side of that coin is they’ve had an audit and failed it miserably and now they are being told to get some help or they’re going to be fined. I would say that in terms of, you know, general overall, when you show this product to folks they definitely see the value of it because it does save them a ton of time in terms of having to figure out what they want to report on, those kinds of things. All those reports are already built in, the alerting mechanisms are in place, and then with the third question is also, in a lot of cases, can be a challenge. Because I can show you reports all day long but unless you can prove to me those reports are actually valid then, you know, it’s a lot tougher proposition for me as a DBA to be able to show that. But we’ve worked out the technology and the hashing technique and all those kinds of things to be able to help make sure that the data in its integrity of the audit trails is being kept.

And so those are the things that, those are my observations in terms of most of the people we talk to. You know, there’s definitely, in different organizations, you know like, you’ll hear about, you know like, Target, for example, had a data breach and, you know, I mean, when other organizations hear about the fines and those kinds of things people start, it raises an eyebrow, so, hopefully that answers the question.

Dez Blanchfield: Yeah, definitely. I can imagine some DBAs when they finally see what can be done with the tool are just realizing they’ve got their late nights and weekends back as well. Time and cost reductions and other things I’m seeing when the appropriate tools are applied to this whole problem, and that is that, three weeks I sat with a bank here in Australia. They’re a global bank, a top three bank, they’re massive. And they had a project where they had to report on their wealth management compliance and particularly risk, and they were looking at 60 weeks’ worth of work for a couple of hundred human beings. And when they were shown the likes of a tool like yourself that could just automate the process, this sense, the look on their faces when they realized they didn’t have to spend X number of weeks with hundreds of people doing a manual process was kind of like they’d found God. But the challenging thing then was how to actually put it into plan, as Dr. Robin Bloor indicated, you know, this is something that becomes a mixture of behavioral, cultural shift. At the levels that you’re dealing with, who are dealing with this directly at the application level, what kind of change do you see when they start adopting a tool to do the kind of reporting and auditing and controls that you can offer, as opposed to what they might have done manually? What does that look like when they actually put into practice?

Bullett Manale: Are you asking, what’s the difference in terms of handling this manually versus using this tool? Is that the question?

Dez Blanchfield: Well, specifically the impact of the business. So for example, if we’re trying to deliver compliance in a manual process, you know, we invariably take a long time with a lot of human beings. But I guess, to put some context around the question, like you know, are we talking about a single person running this tool replacing potentially 50 people, and being able to do the same thing in real time or in hours versus months? Is that kind of, what does that generally turn out to be?

Bullett Manale: Well I mean, it comes down to a couple of things. One is having the ability to answer those questions. Some of those things aren’t going to be done very easily. So yeah, the time it takes to do the stuff homegrown, to write the reports on your own, to set up the traces or the extended events to gather the data manually, could take a lot of time. Really, I’ll give you some, I mean, this doesn’t really relate to databases in general but like right after the Enron happened and SOX became prevalent, I was at one of the larger oil companies in Houston, and we counted for, I think it was like 25 percent of our business costs were related to SOX compliance.

Now that was right after and that was kind of the initial first step at SOX but the thing with, I’d say, you know, you get a lot of benefit by using this tool in the sense that it doesn’t require a lot of people to do this and a lot of different type of people to do it. And like I said, the DBA’s not typically the guy that’s really looking forward to having those conversations with the auditors. So in a lot of cases we’ll see that the DBA can offload this and be able to provide the report being interfaced to the auditor and they can remove themselves completely out of the equation rather than having to be involved. So, you know, that’s a tremendous savings as well in terms of resource when you can do that.

Dez Blanchfield: You’re talking about massive cost reductions, right? The organizations not only remove the risk and the overhead of it, but I mean essentially you’re talking about a significant reduction in cost, A) operationally and also B) in the fact that, you know, if they can actually provide real-time compliance reporting that there’s significantly reduced risk of a data breach or some legal fine or impact for not being compliant, right?

Bullett Manale: Yeah, absolutely. I mean, for not being compliant there’s all kinds of bad things that happen. They can use this tool and it would be great or they don’t and they’ll find out how bad it really is. So yeah, it’s not only the tool obviously, you can do your checks and everything without a tool like this. Like I said, it’s just going to take a lot more time and cost.

Dez Blanchfield: That’s great. So Eric, I’m going to pass back to you because I think the takeaway for me is that, you know, the kind of market is fantastic. But also, essentially, the thing’s worth its weight in gold on the basis that being able to avoid the commercial impact of an issue taking place or being able to reduce the time it takes to report and manage compliance just makes it, you know, the tool pays for itself immediately by the sounds of things.

Eric Kavanagh: That’s exactly right. Well, thanks so much for your time today, Bullett. Thanks to all of you out there for your time and attention, and Robin and Dez. Another great presentation today. Thanks to our friends at IDERA for letting us bring you this content free of charge. We will archive this webcast for later viewing. The archive is usually up within about a day. And let us know what you think about our new website, insideanalysis.com. A whole new design, a whole new look and feel. We’d love to hear your feedback and with that I’m going to bid you farewell, folks. You can email me [email protected]. Otherwise we’ll catch up to you next week. We’ve got seven webcasts in the next five weeks or something like that. We’re going to be busy. And we’ll be at the Strata Conference and the IBM Analyst Summit in New York later this month. So if you’re around there, stop by and say hello. Take care, folks. Bye bye.