I’m in Chicago at the SQL in the City Summit. Therefore, republishing Can Auditing Fail? as I won’t be online.
Tag: auditing
-
The Uninteresting But Necessary Work
There has been a rising tide of data legislation in the last few years that asks for organizations, especially private companies, to better protect their data. The GDPR is one of the most well known, taking effect with regards to enforcement earlier this year, and I’ve been doing quite a bit of work in relation to this law. There are plenty of other laws, such as California’s CCPA, Australia’s NBD, Japan’s APPI, and more that we ought to be aware of as data professionals. These laws affect personal data about people in a variety of ways, and they can affect how we process and use portions of the data we store.
It’s not as simple as it might sound to change our data handling practices. In fact, it might not be that easy for many of us to do this now unless we’ve actually done something in advance: we need to have classified our data. We need to understand the impact of the various columns in our tables, the exports of flat files or reports, and even the development processes that make copies of our production databases.
I’ll be honest, classification work is mind-numbingly boring and uninteresting. This almost feels like busy work to me, especially once we get past the obvious tax IDs and birthdays of people. When we start examining other data, the task feels like it ought to be delegated to junior staff, but many of them lack the experience to make the decisions. What can be more frustrating is that most of them lack the status to get others in the organization to respond to questions, which means the task ultimately falls on more senior people. This also means they often do it once and then forget it, leading to out of date information.
We don’t like doing classification, but we need to do it. Without having some mechanism that allows us to determine if data can be moved or used in another system/database/report/etc., we end up just ping-ponging around. We assume all data is sensitive and try to lock it all down. That leads to complaints, as well as staff working to circumvent the rules until they appear meaningless. At this point we might give up on controlling data and just trust people. That leads to audit problems, potential data loss from security incidents, and plenty of embarrassment about why we didn’t implement some simple controls.
Then the cycle starts again.
Classifying data is simple in some ways, but not easy to ensure the data is available, up to date, and easy to find for any size team. I’ve seen simple solutions that rely on spreadsheets. I’ve seen complex software packages that are expensive and cumbersome to implement with other applications. Microsoft has started to help with a few changes in SSMS, but this doesn’t seem like a long term solution, though SQL Server 2019 might help. Redgate has spent some time on this as well, thinking about the issue and we have an early access program now. All of these are partial solutions that might work for some organizations, but not all.
Ultimately, this is something like security, that we ought to be building into our systems from day one. Every proof-of-concept or prototype ought to be classifying data from the beginning. We won’t be perfect, and won’t get every label correct, but if we’re always thinking about the data, we can always correct our label and more tightly or loosely decide to handle data. I’d also like to think that if we conservatively label the data early, we’re unlikely to get into positions where we are mishandling data in a way that makes it more likely that we accidentally lose data.
Steve Jones
The Voice of the DBA Podcast
Listen to the MP3 Audio ( 6.0MB) podcast or subscribe to the feed at iTunes and Libsyn.
-
Finding the Right Data
For a couple years, Big Data was heavily hyped, and Hadoop became incredibly popular. In fact, so popular that Microsoft build HDInsight and Polybase to allow us to take advantage of these technologies and integrate them into our own systems. While the year has seen less hype on “big data” specifically, more and more of us are dealing with large amounts of data every day. There isn’t a good definition of Big Data I’ve seen, but whatever you thought it meant five years ago has surely changed to mean larger volumes today.
One of the important things that many organizations are learning is that they don’t necessarily need more bits and bytes of all their data. They’re increasingly learning that they need more of the right data, which is the data that is useful to them. Often this is the data that lets them make decisions that improve their revenue, profits, efficiency, etc. As we move to GDPR this spring, it might also me more auditing data that prevents problems or satisfies regulators.
I ran across an interesting article that talks about companies needing the right data, which can often mean unstructured data outside of their traditional OLTP databases when dealing with customers. The article focuses on NLP (natural language processing) and social media data, but it could just as easily mean audio/video data from customer calls (or emails) or even sensor data from systems that are managed and track the ability of customers to use your product or service.
As the world of computing advances, many of us know that we need to find new and better ways to provide value to our employers. This might be with managing and gathering or tracking a wider variety of data, perhaps meaning that some of us need to keep some of those tweets or posts inside our systems. It could be that we need to provide new ways of analyzing data, maybe with some sort of ML (Machine Learning) or AI (Artificial Intelligence) processing. Perhaps it’s that we need to collate and collect detailed auditing information we can produce on demand to ensure our organization complies with legal requirements.
Perhaps there’s some other way that our work as data profressionals will change, but I’m sure it will continue to change and evolve across the next decade. I’m also sure this means lots of new and different data opportunities for us, if we’re willing to grab them with the right data.
Steve Jones
The Voice of the DBA Podcast
Listen to the MP3 Audio ( 3.9MB) podcast or subscribe to the feed at iTunes and Libsyn.
-
The Problems with Gods
When I was learning how to work with computers early in life, I ran across various documentation and writings that would liken the root account to being a god on the system. Over the years, I’ve seen other articles that note will describe “God mode” in various software systems. There have even been science fiction books describing the god-like abilities of a person that obtains a privileged account on a system.
To me, this is one of those places where our industry is immature. Having an account that can perform any task on a system, with no limits, is indeed like a god. This account can do anything, which is a double edged sword. Someone can reconfigure, fix, patch, update anything to ensure the system runs well. Or they can “rm /rf” the system.
Recently Verelox had their entire system wiped out by a former administrator. There are a variety of problems with this story, not the least of which is leaving old credentials active. Mike Walsh wrote about some of the issues from a backup perspective. I would add from a security perspective that this is why an individual’s credentials need to be disabled immediately, and any well known, long time passwords need to be changed. We do this in the physical world by changing locks. We need to do this in the digital work as well.
However, I see a overreaching account with unlimited privileges as fundamentally a bad idea. Sure, this makes installing software or reconfiguring our system easier, but perhaps we should be required to use separate accounts for all sorts of options. This is especially true when we build a distributed system across multiple machines. As the number of services and systems increases, the value from having one account able to accomplish every task outweighs the potential issues.
Humans make mistakes. We make inadvertent ones when we’re tired or distracted. We make malicious mistakes we regret; we make emotional mistakes by overreacting to a situation. We make mistakes based on incorrect information. If we have all the power over a shared system, then we may easily make mistakes that could cause an extraordinary amount of damage.
Our modern systems should include the ability for a separation of all duties and more default accounts that we configure. At the very least we should separate administration from auditing, and perhaps security as well. A slight inconvenience during setup is worth accidental issues in the future. Having separate accounts for different functions will also help to slow down the potential problems in the future by ensuring no one user account can be used to perform every function on a platform if it’s compromised.
Steve Jones
The Voice of the DBA Podcast
Listen to the MP3 Audio ( 3.7MB) podcast or subscribe to the feed at iTunes and Libsyn.
Voice of the DBA 