Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

You'll regret using natural keys

blog.ploeh.dk

Post reply on HN
Print view View original

81–90 of 568 posts

Re: You'll regret using natural keys

#81

Post by kubanczyk »

selcuka wrote: post #58
eddd-ddde wrote: post #47

Earlier quoted context omitted.

Why is knowing someone's ID an issue? They are meant for identification, if anything it should be a benefit that they are easily guessable.

> Why is knowing someone's ID an issue? It increases the attack surface as an authorization vulnerability will allow an attacker to enumerate and access all records. Yes, it is security through obscurity, but a random (e.g. UUID) scheme makes it harder.

~128 bits worth of obscurity is considered real security for the time being. Assuming a cryptographically secure PRNG.

Thats like guessing a password 18 ASCII chars long.

Re: You'll regret using natural keys

#82

Post by MikePlacid »

> I already predicted that changing the number would prove to be either impossible, or have all sorts of cascading effects, ultimately terminating in official records no longer recognizing that the car is mine.

Oh my, I can feel that pain. Here’s what happened last week, caused by a change in… SSN? no - in our home address.

My wife earned some unemployment benefits three years ago, which were put on a plastic card issued by The Bank. She finally found time to access the funds (she’s a middle school teacher), but when she went to The Bank, they said they didn’t have the money anymore—they’d sent it back to California. So, she called California. They were like, “No problem, we’ll send you a check. Oh, you have a new address? Let’s change it. Wait, what is happening… oh, now your account is locked, it says: potential fraud”… They needed a supervisor to unlock it, which took 20 minutes. The supervisor unlocked the account, but because it was marked as potential fraud, they couldn’t mail a check anymore. Instead, they linked the account back to The Bank (20 more minutes), and she had to go there in person with her ID to get it checked.

So, she went to The Bank. But you can’t just walk in The Bank and show your ID to get it checked; you need an appointment. And to get an appointment, you need an account with The Bank. But her California benefits account? Oh, it is marked as potential fraud - it didn’t count. So, they spent 20 minutes to open a new account for her. She got an appointment for later that day, in 4 hours, went back to The Bank, and had her ID checked (yes, the second time in one day - they need to check your ID to open an account too).

Did she get the money then? Of course not—the account is still flagged as potential fraud. No cash possible. Call California again. California agreed to mail the check to the new address, probably by some oversight.

So my question is: do these “potential fraud” flags in databases ever die a natural death?

With some hope, sincerely, a Husband of a Potential Fraudster.

Re: You'll regret using natural keys

#83

Post by jtriangle »

jklowden wrote: post #27

You think your surrogate key will save you? It will not. The world has an external reality that needs to be reflected in your database. If the unique identifier for your object — VIN, CUSIP, whatever — if it changes, the world will henceforth refer to it by both. You will need to track both. Adding a synthetic key only means you have to track all three. Plus you have to generate a meaningless number, which is actuall…

>Model your data on the real world. Do not depend on spherical horses.

This took me years to realize, and once I did things became much, much simpler.

Re: You'll regret using natural keys

#84

Post by CrimsonRain »

jklowden wrote: post #27

You think your surrogate key will save you? It will not. The world has an external reality that needs to be reflected in your database. If the unique identifier for your object — VIN, CUSIP, whatever — if it changes, the world will henceforth refer to it by both. You will need to track both. Adding a synthetic key only means you have to track all three. Plus you have to generate a meaningless number, which is actuall…

Sorry. This is a very bad advice. I just had to fight tooth and nail to make my lead turn around from this disastrous decision. Using a lot of external IDs as our own row primary keys and then they get propagated to all other tables as foreign keys and what not. One day the foreign key chances or God forbid, the formatting changes in external systems, now we need to fix our whole database and all codes instead of a small isolated place.

Generate your own unique keys for everything; add a few more unique constraints if needed. A bit more work but never a regret.

Re: You'll regret using natural keys

#85

Post by kubanczyk »

roenxi wrote: post #26
projektfu wrote: post #19

The devil's advocate cases: 1. When duplication occurs and goes unnoticed because the natural key isn't being used, and then perhaps the problem is "corrected" by an administrator in a way that doesn't make sense. For example, someone sets up an account with an street address A, then forgets they had it and sets up an account with street address B. They call and complain that they can't find an old order, or whatever…

A synthetic key is adding an additional dataum - so any problem that could be solved with the original data can be solved by the new data too (worst case, ignore the primary key). That is one of the best reasons to add a proper index - there is almost literally no downside. We're talking maybe a few bytes/record and arguably a trivial amount of administrative overhead. Bearing that in mind: 1. Add a unique constraint…

In other words, don't mistake a primary key with a key or a constraint.

Re: You'll regret using natural keys

#86

Post by devjab »

jklowden wrote: post #27

You think your surrogate key will save you? It will not. The world has an external reality that needs to be reflected in your database. If the unique identifier for your object — VIN, CUSIP, whatever — if it changes, the world will henceforth refer to it by both. You will need to track both. Adding a synthetic key only means you have to track all three. Plus you have to generate a meaningless number, which is actuall…

In terms of the Danish CPR that is mentioned here, the way we actually solved the challenge in the national architecture strategy was to define your social security number(s) as what we call an address on your person. I’m not sure why it was called an address, but it’s basically a UUID and the information. Maybe it’s because it was first used to store addresses?

Anyway. Unless your system has not yet implemented the national strategies (which by now are approaching 24 years) then changing a Danish Social Security won’t actually matter because it’s just an added address to your person “object”. So basically you’ll now simply have two, and only which is marked as the active. Similar to how you’ve got an array of addresses in which you have lived but only one main address active.

It did indeed cause a lot of issues historically because it was used as a natural key… though with it being based on dates, it was never really a good candidate for a key since you couldn’t exactly make it into a number unless you had some way to deal with all the ones beginning with 0 being shorter than the rest. Anyway… it was used as a key, and it was stupid.

Anyway I both agree and disagree with you. Because we’ve successfully modelled the real world with virtual keys, but you access it by giving any number of natural keys. Like… you can find the digital “me” by giving a system my CPR number, but you can also find me by giving a system my current address. Technically you could find me by giving a name, but good luck if you’re dealing with a common name. There is a whole range of natural keys you can use to identify a “digital” person, but all of it leads from a natural key into a web of connected virtual keys.

All of it is behind some serious gatekeeping security if you’re worried. Or at least it’s supposed to be.

Re: You'll regret using natural keys

#87

Post by the_gipsy »

In Spain the ID numbers are assigned at birth, carry no information, and cannot be changed. Each police comissary that registers birth gets a unique set of IDs to assign (per year or whatever).

However. Mistakes still happen. A colleague had the same ID as someone else. He said he tried to change it, but it was impossible because it was such an impossible concept to any public servant involved. In the end he gave up and just lived with the fact.

Re: You'll regret using natural keys

#89

Post by pseudosavant »

dqv wrote: post #59
selcuka wrote: post #54

Earlier quoted context omitted.

> The US SSN is not guaranteed to be unique The cases you listed do not mean SSNs are not unique, unless there are people who share the same SSN. You can still define a unique index for the SSN column. A column can be both nullable and unique as each null is different in SQL.

> It’s not as uncommon as you might think. In fact, some 40 million SSNs are associated with multiple people, according to a 2010 study by ID Analytics. https://www.pcworld.com/article/424392/a-tale-of-two-women-s...

Funny enough, I used to work at IDA.

I’ll add that there is a huge difference between the SSN database that the Social Security Administration maintains, and the list of SSNs that have been associated with a person. Especially because it is very common to change a single digit of your SSN when performing credit fraud - because they’ve already burned their real one. Some people will have dozens of SSNs attached to them.

IDA was very good at determining who a person is through the graphs that represent identities in our world (names, DOBs, phone numbers, addresses, SSNs, etc.)

Re: You'll regret using natural keys

#90

Post by bongodongobob »

selcuka wrote: post #58
eddd-ddde wrote: post #47

Earlier quoted context omitted.

Why is knowing someone's ID an issue? They are meant for identification, if anything it should be a benefit that they are easily guessable.

> Why is knowing someone's ID an issue? It increases the attack surface as an authorization vulnerability will allow an attacker to enumerate and access all records. Yes, it is security through obscurity, but a random (e.g. UUID) scheme makes it harder.

Exactly, it's not that my systems use security through obscurity, it's the other ones mine ties into.

This was years ago and you don't see it as much anymore, but think autogenerated links to shitty CRM, ticketing, and project management software where the link is the query aka - Blahsoftware.local/info/bunchofgarbage?=userid+garbage+view+sensitiveinfo.html type stuff.

Post reply on HN