Running queries on the HMRC database fiasco

If I had a pound for every time...

Shit! If I got paid that much for chopping up a CSV file...

*goes misty-eyed*

HMRC IT is outsourced, the outsourcer has to be profitable!

There is no point in thinking that you could offer do the work for £500 unless you offer to do it for CapGemeni ; see: http://www.channelregister.co.uk/2007/11/13/capgemini_job_cuts/

I think that there might be a markup put on your work as well as some overhead of writing the requirements, formulating the demand for extra IT work when costs are being reduced, 5 layers of management to get the request approved after choosing the appropriate budget line, possibly a committee or two to pass through, then the costed proposal from the outsourcer, the quality plan for the work, fully detailed PERT chart, test specifications, approval process, proposed modifications to service level agreements, approval of tea breaks (oops, only kidding) etc. etc. etc.

Techies like you make things sound EASY. ;-))

So it appears

So it appears that the way the child benefit department has a massive database, and they don’t know how to use it.

They have outsourced the management of the database to a company, which is probably owned by some relative of a government minister.

They and only they know how to query the database, which will keep them in a job for a very long time.

They decide how much to charge for one of their employees to do any sort of work on the database. And because said lazy bastard can’t be bothered to do the work they are asked to do, they quote some ridiculous sum.

When asked how they can do it cheaper, for half the price, they will click on export as CFV. Copy said file on a couple of blank CD’s in a password protected zip file……

Excuse my lack of faith in yet another government department.

I think I'll start keeping my cash under the matress, and when i have enough money I'll be off somewhere safe.....

select count(idiot) from HMRC

I'd suggest that the reason that NAO was being sent the data in this format was that it was a format that HMRC already had and was already using internally in some integration or other. Given that the extract contained *all* the data, I'd suspect that this was a copy of the feed to an HMRC data warehouse.

The most likely formats therefore might be an XML file, a CSV file (either of which really isn't going to trouble a black-hat much to figure out the format, given they've probably saved it with a file extension of .xml or .csv) or a database extract. You have to remember the thinking of the numpties might be, "well here's the data, no we don't know how to get it onto your machine, but it is on the disk; oh you'll incur an expense getting it? Sorry, not our problem". But as you say, let's notr discuss it... pointless anyway seeing as I'm sure we can all guess where the "password" protection comes in.

And the Id cards argument from Darling is just hilarious, in a very black way... yes, it would have made it less likely that the NAO would have had to request the data in the first place (bearing in mind that they didn't actually request the data at all) but on the flip side once the idiot with unrestricted access to the dataset sent the whole caboodle through an insecure channel we'd be in an even worse position than we are now. And it doesn't matter how many technical safeguards you try to put into your system, at some level there will be some idiot with too much access who doesn't understand why what they are about to do should qualify them for a red hot poker up the jacksie.

I'm still trying to figure out who are the bigger set of morons here, the HMRC clowns or the policitcal muppets, it's a difficult choice.

Open Access

Unless things have changed in the past few years then this is just the tip of the iceberg, and after having done some work for them in the past I'm not surprised

Try using personal information from the system to send letters asking for Autographs from the celebs.

Looking up family tax info, and amending them for better tax breaks.

Checking out your mates info.

Causing more hassle for your ex by amending their account for "EXTRA" checks or lower tax breaks.

Chasing up people's addresses at whim.

And these are just some of what HMRC get upto during work hours.... imagine if someone actually did their work instead.

IIRC, you can also search for partial matches on ANY field. And there are lots of fields..... want to know how many people have a HSBC account sure just stick in the first couple of sortcode digits and let it run.

Posted Anonymously from a cafe of course......

In the hands of criminals

HMRC states the data hasn't fallen into the hands of criminals. Yet they admit it has gone to KPMG. Any Private Eye or Register reader knows what that means. At least they haven't admitted that EDS has got hold of it - yet!

The real costs

- about 47 coppers on the ground, doing a fine-combed search of all likely places these disks could be: tens of thousands;

- everybody in the HMRC foodchain working (paid) overtime to cover their ass: tens of thousands;

- spinmeisters employed to deal with pack of bloodhound press: tens of thousands;

- cost to banks and government to "monitor all accounts for suspicious usage": tens of thousands;

- cost for the now inevitable massive HMRC IT overhaul project coming our way, courtesy of [insert random consultancy house full of overpaid paper MCSE's]: millions

- seeing the retards in charge getting away with it all scott free: priceless.

The basic two problems - and the untold story

It's cute to go into such detail, but you're missing the basics so here goes.

(1) The UK government does not have the equivalent of the US American Encryption Standard (AES), a standard encryption technique, open, published and peer reviewed so that it's easy to embed it in anything that needs security across the whole of government. In short - there WAS no crypto that the junior could have used.

(2) The junior defaulted to the 'safe transport' assumption that used to be true for internal government post. The only problem was that it *was* no longer safe as it was outsourced, but the risk management model was never updated to take that into account. So we have an assumption of safety where none exists, aka "false sense of security" (a thing long lost with this goverment, but I digress).

So, ladies and gents, the many smart ones amongst you will thus have already deduced from the above that, despite public statements to the contrary, AFAIK NO PROCEDURES WERE BROKEN. That's right - the chap simply did business-as-usual. Cynics may observe that the results were thus also business as usual, casually endangering the lives of quite a sizable chunk of the population.

I suggest you watch the government twist and turn to avoid admitting that one basic fact because it will be one hell of an indictment for the 'leadership'.

That's all very nice

But the public don't read El Reg - if you've been smacking your gob over this go join you local No2ID branch, hassle the public on a few flyering sessions and make sure next time it's not 60 million records lost.

Passwords and encryption.

I wonder if the term "password protected" is merely spin implying that the account details by themselves are not sufficient to access bank accounts and that a password is used. That is the password is used by the banks and nothing to do wth the data.

As regards encryption. RSA is not used for bulk encryption. A symmetric algorithm such as AES or triple DES is typically used with a random transport key. It is that key which may be encrypted using RSA or derived from a password.

IQ test - 101

You can Trust your government

Your data is Safe

There has been no infraction of the data protection act

ID cards are a safe and efficient step forwards in combating crime

Outsourcing is clever

The Postal Service is reliable

One of these statements is true.

The Frightening Thing About ID Cards

Watching David Davis on TV this morning brought it home to me. ID cards are a very bad thing.

These CD's being stolen (or just lost is more likely IMHO) is bad enough because you have something of value. But what about a situation where a terrorist organisation wanted to get access to somewhere? Stealing someone's fingerprints or DNA isn't really a choice.

However, what would happen if said terrorists REPLACED your data with their own, proving that that person was actually you? From that point forward you wouldn't be able to buy a loaf of bread in Tesco's. And government wouldn't want to correct the data because you clearly aren't who you are pretending to be.

That for me is the big danger with ID cards - giving the terrorist community the facility to masquerade as someone else very easily. That simply couldn't be done so easily if they had to produce a gas bill with a driving license.

Andrew

I'll see your £500

and do it for a couple of pints over lunch whilst it runs ...

cut -d\, -f 1-2,4,6 db.csv > NAOout.csv

your delimiters may vary ...

Rational Spending

Until recently I worked at a government agency (the RPA) as a consultant. I got yelled at a few times for doing things that should have been done by the contracted "IS partner" organisation, via the nebulous ever-changing, extraordinarily complex 'change process'.

But we knew, from painful experience, that something that would take me maybe two hours would take maybe four months and cost no less that £30,000 done the 'proper' way - and in actuality would not get done at all because there were other things it was more rational to spend £30,000 on.

Mostly I feel sorry for the 'Junior Official' at HMRC who was just trying to do what he'd been told to do - trying to do the job properly and with appropriate security would have meant never getting it done at all, and probably being regarded as simultaneously useless and trying to operate outside ones grade by 'management'.

Oh, and I feel sorry for the farmers. I never, ever, EVER want to work in government again.

Unresticted access?

Little attention is being given to the fact that some civil servant has _unresticted_ access to the entire database. With access controls like that you may as well assume that multiple copies of the database (of various accuracies) are floating around government departments all the time. Now that is worrying...

As for the use of the post, I guess that both sites have network firewalls installed and the necessary procedure to establish a secure network connection between the two was just too complex :-)

Re:File format *and* protection...

Excel is limited to something stupid like 65k rows (16 bit int size) so I doubt it was in Excel. That would mean approx 385 worksheets!

MDB - maybe - but PW protection is a pain in access and easily circumvented - what with MDA files and the like.

My money is on a huge csv (we regulary load in csvs up to 11 mill records so it is done) that was then zipped and spanned across disks; with the zip providing the much vaunted "password protection".

@ Sceptical Bastard, Anonymous (pirate) Coward and James Joy

You guys - collectively - have the reasons laid out: no one did anything "wrong", but things DID fall through the cracks.

The issue behind this whole mess is that NAO needed a representative, random subset of the data for their processing.

The usual method for doing this is to use (a) a random SELECT function that grabs X rows randomly, (b) a statistically tuned SELECT function that grabs X rows randomly that match the overall demographics of the database itself, (c) a complete database extract that is then analyzed by several methods and the appropriate subset extracted.

In my past work doing this sort of thing for a variety of customers (including the US Internal Revenue Service) the key to the extract is getting an unbiased extract based on the criteria of the auditing group. This *USUALLY* involves having a tech member of the audit group visit the source group on-site and supervise (or perform) the extract to insure that the unbiased extract is obtained. The data is then remanded into the possession of the auditor and removed personally by them to their location.

Sceptical Bastard and James Joy hit the main point here: for (a) or (b) above, you send someone up to the source site and have them get the data. If you can't do that, you do (c) - exactly what caused this problem in the first place.

Anonymous (pirate) Coward got the rest of the story: the SOP for (c) is to extract the whole database and send it to the NAO for their analysis. Inter-office mail is expected to be REASONABLY secure - after all, paycheques and HR info is routinely sent through this channel with no issue.

The one piece of data that's missing in all the press stories is the loss rate of the inter-office mail system itself. I'll be that the loss of this particular package is consistent with the overall losses in the system - this one just happened to contain a "political bomb".

Back when I worked in banking, we had a robotic inter-office mail system installed at our huge processing facility out in Brea, CA, USA. This consisted of little electric boxes about .5X.3X.2M that ran on a computerized "train track" through the entire building. The track was designed so that the little boxes could transit vertically between floors and up-side-down to negotiate other building obstructions.

Soon after the installation and operation of the system, a "scandal" developed when pay cheques failed to arrive in many departments after being consigned to the robot delivery system. An investigation was started, pay cheques were delayed pending "apprehension of the criminals", and all Hell broke loose.

Finally someone decided to check the bottoms of the vertical shafts and the plenum runs that the robots ran through upside-down. Lo and behold, the bundles of cheques were found, dusty but intact, at the bottom of one of the shafts, along with a lot of other "missing" stuff.

It turned out that the lid catch on several of the robot boxes had been damaged, allowing a heavy load to fall out. Once empty, the lid would latch again - no one the wiser. Paycheque bundles were "heavy" loads, as were computer tapes, six-packs of Coca-cola and several other items that were routinely sent between departments.

Several people were fired over this, mostly those sending soft drinks through the mails...

Yes, those disks are GONE for good...probably stuck in a crack in the back of the sorter...

Security should be discussed!

> Of course, "they" won't tell us and, in fairness, they shouldn't.

This is a dangerous and outdated view of computer security. It is well understood that how systems are secured MUST be the subject of public discussion and review. The security of live systems should rely on few well understood secrets (like keys or passwords), and not ignorance of the security architecture.

This is key to the development of the fields of cryptography, and security engineering that are taught and discussed in public, as well as the security of free source software that is open for all to inspect.

The government is clearly trying to say as little as possible on the matter, with good *political*, not security, reasons. It is unclear why IT journalists should play along with this strategy instead of asking for the full requirements, specifications, and even security audits of the systems that were involved in the data leaks. Making such documents public should not make the system more vulnerable, if it is engineered with security in mind.

George Danezis

(Security Researcher)

http://homes.esat.kuleuven.be/~gdanezis/

PS The idea that ignorance of the database format, or even the encrypted archive format, would slow down even an amateur attacker from retrieving the data is particularly silly.

Root Causes

This whole fiasco is the result (imho) of a pervasive problem in modern management "theory": the idea that it doesn't matter who does the work, that workers are all just interchangeable cogs, and are totally fungible. This theory is never stated explicitly, afaik, but holders of MBA degrees demonstrate its existence (and widespread application) daily.

The net effect of this theory is the devaluation of experience, expertise, intelligence, education, and inborn ability. Among other specific results, you end up with call centers with employees whose accents are too thick to be understood, convicted criminals having access to confidential financial data, workers who are simply unqualified to do the work at hand, and the surrender of control over important data to consulting firms.

Applied widely and indiscriminately, the theory of worker fungibility has a great many other consequences -- corollaries to the theory, if you will. Identifying these corollaries and relating them to the details of the HMRC data loss disaster is left as an exercise for the reader.

Ah, yes & no.

We don't know the disks have been stolen - we do know claims have been made about loss or non-receipt and so criminal activities are still speculation.

We don't know what service was to be provided for the cost quoted - we do know that it appears to be practically extortionate given what appears to have been asked for.

Some points:

This was a blunder waiting to happen - its just the scale and consequence that are outstanding.

Electronic Data is not currently (and cannot be) "safe" in the hands of either government or its IT contractors with the current systems and procedural models and practices. It is not, systematically, possible.

Every time there is a blunder the taxpayer pays. Even if it is made by an IT contractor. We are the only people who can pay. We are the "customer".

If a regular company blundered on this scale then it's likely that they would either go out of business due to loss of custom or would end up in court and paying punitive fines. Certainly, customer sanctions and legal processes could/would be applied. What chance here?

This blunder will not deflect the government one degree from ID cards because ID cards are a panacea for security ills. This blunder is not a "security problem" it is a "data processing problem". I've just written this and I don't understand it either!

The government need not care because there is no choice. It is, like its IT contractors, a monopoly provider. It may say it cares, it may say it will change - many partner-abusers do. Evidence is all. To others who know - do they change?

Let's see what happens when/if the status of the two disks is confirmed.

It is simply no IT problem

and no problem of EDS, CG or any other outsourcer as well.

Sorry for me stepping in here rudely from germany, but after reading this, the problem was again the human factor. The HMRC guy who replied to the NAO: "You asked for all the data initially" brings it to the point: Why do you NAO guys bother me at all?

As far as i know, the NAO (or their alikes in other countries) do not have a fan club in the public sector, so pushing back on them is "natral" behaviour i guess.

This whole problem would definitely happened as well with an insourced IT department which would only have to be badly enogh aligned with the "business" .

What made me wonder though is: I have not heard about anything like this in germany, does that mean we never loose data / notebooks etc ????

Simple equation

Surely the cost of deleting the relevant data fields must be less that the cost of a train ticket to Newcastle and a day's time for a NAO junior auditor. Otherwise they would have sent someone down to do the sample selection on site. Therefore, pretty cheap. But the cost to the government? Maybe five years in power...

I've seen this set up before a few times. It is usually someone junior who's fouled up but this is invariably caused by the leader not caring about proper control systems (e.g. Chancellors who care more about results than how you got there...)

AES - American Encryption Standard?

Since when has the AES been the American Encryption Standard? Whilst the algorithm's competition was run by the US the A stands for Advanced, not American and A is not for Apple.

ID cards, Unique Identifiers.

So ID cards make data easier to tie together because they use a unique identifier, eh? What a spiffing idea. Especially as i've already got one of those. It's called a "National Insurance Number". In fact, i have another unique identifier. It's called a "National Health Number". Now i'm going to get a "National Dupe Number" too?

i'm leaving this farce of a country as soon as i have the wherewithal.