Can staff get round permissions by uploading a spreadsheet?

You gave a clerk permission to upload spreadsheets, because filling in three hundred customers by hand is nobody’s idea of a good afternoon. You did not give her permission to create customers or products, because you want those controlled. Then you find she can create both, by uploading them.

An import built the obvious way allows exactly this, and closing that gap is what we did in ours. The upload is guarded by one permission, “may import”, and the file itself said what to create. Name customers in the file and customers appeared. Name products and products appeared. Every other part of the permission system stays intact; the one door that can write to everything was the one nobody had checked against it. The cost is a control you believed you had and did not, found out when somebody has already created records they should not have.

The second problem belongs to the same feature. Upload three hundred rows with one mistake on row forty-one, and a badly built import stops dead, leaving you a message that means nothing and no idea which rows got in.

What was actually going on

The permission hole is structural, not careless. Most permission checks are attached to the page or action in question: the screen for creating a customer knows it needs the customer permission because it is about customers. An import does not know what it is creating until it has read the file, and by then the ordinary check has already been passed. Anything that takes the kind of thing to act on from its contents, such as an import, a batch action or a generic message receiver, has this shape.

The second weakness is that an import tends to grow its own rules. The file is text from a spreadsheet, the real screen expects tidy input, so someone writes a translation layer. That layer is now a second definition of what a valid customer is, and it drifts one way: it accepts things the real screen would refuse, because it was written to forgive messy spreadsheets.

The halted upload has a technical cause, and it is easy to get wrong. In the database we use, one failed step poisons everything after it in the same batch, unless each row is protected separately. Catching the error in the program is not enough. The batch is already unusable, every later step fails, and the report reads “the upload stops after the first bad row” however carefully the loop was written.

What we changed

The import now checks two permissions, and the second is worked out from the file: the caller needs the import permission and the create permission for whatever the file names.

Each row goes through the same checks and the same creation route as the real screen, including the per-company code uniqueness rule and the home-branch rule. Nothing about validation has an import-only path. To prove it, after an import the created records are read back through the ordinary screens. If the import had written anything the real route would refuse, that is where it would show.

Each row is protected on its own. A row with a missing field or a duplicate code is recorded against that row, with the field named, and only that row is undone. The valid rows go in, and the report gives a count of each. The downloadable template is built from the same list of fields the real screen uses, so a new required field appears on the day it becomes required. A hand-kept template is a third definition, and the stalest.

Adding real file upload was a front end onto that same route, not a second route. One fault got through every other check: the web side sets a default content label on every request, which broke the file upload and made the server report that both the file and the target were missing. Typing and builds passed. It failed only when a person uploaded a real file in a real browser. The fix was to unset that label on that one call, leaving the shared setting alone.

What it did not fix

An upload cannot be proven by any check that does not actually send a file. The shared setting that caused the failure still exists, and it still applies to other requests. We worked round it on one call rather than changing the shared client, because that was outside the scope of the work.

The pattern, for anyone letting staff upload spreadsheets

Ask your supplier five things. When a file names what to create, is the permission for that thing checked, not only the permission to upload? Does the import go through the same checks as the real screen, and was that proved by reading the records back? What happens to row forty-one, and do the other two hundred and ninety-nine go in? Where does the template come from? And was the upload tried with a real file in a real browser?

Where this ends up

An import is usually added late, by whoever is nearest the deadline, which is exactly why it deserves the scrutiny given to the screens it stands in front of. This one is the import in Field, where each company’s customers and products have their own codes and a home branch, and the import respects both.