Voice of the DBA

Author: way0utwest

The Weakest Link

I noticed another data breach recently. This breach was from PageUp, a firm that helps companies find employees. This means that they have lots of information, potentially PII data, and some of it might be out there. Since they provide job sites for many other companies, you might have used them to apply for a job and not realized it. Certainly if you have used them, you might keep an eye out.

The company notes that no personal information was lost, though encrypted usernames and passwords were disclosed. These were salted values encrypted wtih bcrypt, which is secure, but all encryption can be broken given time and effort. Some people see bcrypt as secure, but others disagree. However, the strength of bcrypt depends on how the hashing was set up, and I wouldn’t depend on this to be foolproof. If you used a password to apply for a job that you use on other sites, change it.

The bigger issue for me is near the end of the BBC piece. A bank notes that a third party supplier had a security issue, so that means they need to check their systems. To me this means one thing.

Their security depends on the security of their business partners.

Depending on the level of access and integration, this might mean that your security is compromised by a link much weaker than the weakest link in your internal environment. Or that your security depends on the weakest human link not only inside your organization, but also within your partners. Despite all the work you’ve done to increase the security of your systems, you might have other holes out there.

It doesn’t appear this breach is as bad as originally thought, but the point is still valid. The more interconnected you are with partners, especially with shared access, the larger your attack surface area. I take away the need from this that I need to ensure a limited API and protected access with minimal privileges for internal systems that are connected to any other networks. Production level security is important not only to public facing systems, but also those that are semi-private with business partner access.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

August 23, 2018
Remember the Default Window
I ran across a question recently from a user about why they had strange results from a windowing query. This is better explained with an example, so let’s look at one.

I have some data in a table. This is NFL data, and a sample of it looks like this:

What I want to do is compare the passing yards each year with the most current value for that player, showing the plus or minus. This means that for Aaron Rodgers, who threw for 1675 yards in 2017, I’d want to show this for the first few years of his career:
```
  PlayerName  NFLYear PassYards Most Recent Yards Difference

------------- ------- --------- ----------------- -----------

Aaron Rodgers 2005    65        1675              -1610

Aaron Rodgers 2006    46        1675              -1629

Aaron Rodgers 2007    218       1675              -1457

Aaron Rodgers 2008    4038      1675              2363
```
This shows
me an easy view of the years where he was better in his career than he is now. Last year was likely a down year because of injury, but we’ll see this year.

In any case, if I run this query using LAST_VALUE() for the final year of his career, I don’t get the right results.

It seems as though in every row, I’m getting the current row as the last value, not the last value of the partition. My partition is by player, so I should only have a window for each player. In this case, I should have the years 2005-2017 for Aaron Rodgers. My ordering is by year, so the last value should be 1675.

Why isn’t it?

The reason has to do with the framing. As the window is consumed, the default values for the framing are between
- start – unbounded preceding
- end – current row
That means the first row for 2005 has the range of 2005-2005. The preceding rows are this row, and the current row is this row. For 2006, we have the first row as 2005 and the current row as 2006. The last value in this case is 46.

What we need to do is specify the entire window if we want that. In this case, we could use the current row as the start, but we certainly need the unbounded following rows.

This is a common mistake when writing window queries. I’d recommend you always include the partition and the framing to avoid any issues.
August 23, 2018
Super Nerds

I enjoy the game of baseball, playing as a kid and then for a decade as a 40 year old adult. I gave it up a few years ago, worried about the wear and tear of sporadic play. I’m happy now coaching kids and participating in individual sports for exercise, but I still love the game.

A few years back I read the book, Moneyball. The book is about the use of metrics and analysis in addition to the human scouting evaluation in the Oakland As organization. There was a movie made as well, and both are worth consuming. Since that time, other baseball teams have adopted some of the ideas, and I’ve been hearing that both basketball, NFL football, and other sports in the US are using more metrics. There’s even a Revisionist History episode that talks about football/soccer and the way that makes the most sense to improve your team.

Recently I ran across an article with a great headline: Jayson Werth rails against ‘super nerds’ that are ‘killing the game’. In the article, a player rants about the way in which data and statistics are being used to make decisions. Instead of allowing players to just play, analytics become a part of the decision to play a certain way. For example, bunting and stealing have dramatically declined, mainly do to the analytics that show these are lower percentage actions compared to other choices.

It’s interesting to hear players rant against the user of statistics. I completely get the annoyance at losing control of your choices, and certainly appreciate that the game becomes less exciting at times. However, I also know that players, and even coaches, may make emotional decisions, or base decisions on poor information. Most of us humans can’t remember all the tendencies and likelihoods. In a modern world where skill levels have dramatically increased in many ways, there are often better ways to build a strategy.

Data is valuable, and certainly can help in sports. It isn’t the end-all be-all, and it can be misleading when applied to individual humans. At the professional level, where more data is available, I think it makes sense to use data more as a significant part of your decisions, though not the only factors. For me, as a coach of younger kids where I have relatively little data, I still use the eye-test for most things, relying on data to double check my thoughts. The world moves fast, and it can be easy to forget how individual players have performed across an event, especially when I’m trying to manage the game as well. Data helps me remember how the day is going.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 5.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

August 21, 2018
Destroying Data

For many of the data professionals out there, our job is to ensure that data is protected. We work to protect the information in our databases in a variety of ways that allow our organization to continue to work in adverse situations. We often schedule backup schemes to ensure we can recover data from losses. We perform maintenance to ensure efficient access to information, and we move data from one system to another so that a wide variety of applications and clients can use it. We spend most of our workday trying to ensure that data is where it needs to be.

It’s worth considering that there may be times when we are asked to destroy data as well. If hardware is being upgraded, then it might be a part of our job description to ensure that no traces of data exist in old systems. Even if it’s not our job, we might want to ensure that hardware is destroyed or permanently wiped. It’s unlikely to be an issue, but there are numerous stories of someone buying used equipment with old data on it. This is such a low bar of something to check that there isn’t an excuse to avoid removing old data.

If you aren’t sure what to do, don’t have time, or want independent proof, maybe you want to use a service. There are numerous ones, which makes sense with the growth in legislation requiring stronger data protections as well as the embarrassment from management that wants to outsource their risk to another entity. This might be especially handy for larger organizations that could have multiple types of devices, some of which aren’t as accessible as a hard drive in a server chassis. There are services to ensure wiping of phones, tablets, and more.

It’s a little funny to me to think of destroying data when most of my career has revolved around protecting data. What I mostly think about here is that too often many organizations have thought about data as an asset of the company to be used as needed, but not necessarily protected throughout its life cycle, and certainly not classified according to it’s sensitivity.

I hope that with the growing value of data, and regular misuse of information, and the security issues that appear in the news that most companies would start to actively manage their data in the future, from capture to destruction.

Steve Jones

August 20, 2018