Row-Level Security and Column-Level Security, Explained Without the Jargon

There is one question that tells you more about a business system’s security than every other question combined: when a user asks for data they should not have, does the screen hide it, or does the database refuse to hand it over? Those are completely different systems, and from the demo they look identical. Row-level security — along with its companion, column-level security — is the name for the second answer, and this article explains the distinction in language you can take into a vendor meeting without a database administrator beside you.

The two ways to say no

Imagine a school system with two thousand students, and a parent logging in to see their own child’s attendance.

The interface approach. The page asks the database for student records, receives them, works out which one belongs to this parent, and renders that one. The other 1,999 arrived at the browser or at the application server and were discarded before display. The parent sees exactly what they should see, and the demo is flawless.

The database approach. The database is told, as part of the connection, who is asking. It applies a rule attached to the table itself: a parent may see rows where the student is their child. The query for all students returns exactly one row, because for that user, that is all the rows that exist. There is no discarding step, because there is nothing to discard.

On screen, on a good day, these are indistinguishable. The difference appears on the days that matter.

Where the interface approach breaks

The trouble with hiding data is that the browser is not the only door into the system, and a well-behaved user is not the only kind.

The address bar. A page that shows an invoice by its number will happily show a different invoice if someone changes the number in the address. If the check was “does this user’s role include the invoices page?” rather than “may this user have this invoice?”, the answer comes back.

Report filters. Reporting is where interface-level permissions go to die. Reports are generated by a different code path than the pages, usually by a query with a filter, and filters are parameters. Widen the date range, clear the branch code, and a report built to show one branch shows all of them.

Exports. The Excel export is written by whoever built the export routine, at a different time, under deadline. It very often skips the checks the page performed, because the checks lived in the page.

Search. Global search is the classic leak: even when it returns nothing a user may open, it can confirm that a record exists, and sometimes shows a preview line containing the very number that was meant to be private.

Every new feature. This is the structural problem, not an accident. When the rule lives in the interface, the rule must be re-implemented in every screen, every report, every integration, every export, forever, by everyone who ever touches the codebase. One omission is a breach. When the rule lives in the database, new features inherit it without anyone remembering to.

Row-level security in practice: nine portals

Our school management system is the clearest illustration we have, because it has nine roles that must genuinely not see each other’s data: super admin, admin, academics, finance, HR, teacher, librarian, student and parent.

A parent logs in. Every query that user’s session makes — the fee voucher, the attendance summary, the exam result, the notice board, the search box, the printed report, the mobile view — is constrained at the database to the rows belonging to their own child. It is not that the parent portal was written to show one child. It is that for that session, one child is what the data consists of.

Now consider what this makes safe. A new module gets added — a transport routing screen, say. The developer building it does not have to remember the parent rule, because the parent rule is not theirs to remember. A report gets built in a hurry during admissions week. It cannot leak, because there is nothing in the result set to leak. The system’s security stops depending on the discipline of whoever writes the next feature, which is the only way security survives a decade of changes. The nine portals we describe in our portal guide are a user-experience story on the surface and a data-boundary story underneath.

The same architecture does the same work in a factory group: a unit’s staff work in their unit’s production and ledger data, the group’s directors read across all units, and the boundary is one rule in the database rather than a hundred conditions scattered through the application.

Column-level security: the part people forget

Rows answer “which records”, and that is only half of access. The other half is “which fields of the records they are allowed to have”.

An HR assistant maintaining employee files needs the record — name, contact, department, leave balance — and has no business with the salary column. A production supervisor checking an order’s specification needs the order and has no business with its margin. A branch accountant needs the customer’s ledger and may not need the credit limit set by head office.

Without column-level control, every one of those situations forces a bad choice: hand over the whole record, or build a duplicate screen that shows a subset — which is the interface approach again, with all its leaks. Column-level security lets the same record open differently for different people, enforced in the same place as the row rules. In practice this is what makes a single employee record, a single customer record, a single order record usable across an organisation instead of being copied into three parallel versions that immediately disagree with each other.

The same discipline has to follow the data out of the system. A printed fee voucher, a salary slip, an exported ledger — each is a projection of a record, and each should be produced under the same row and column rules as the screen that requested it. The moment an export routine runs as a privileged user for convenience, every restriction upstream of it becomes a suggestion.

What this costs, and what it does not

It is fair to ask what you give up. Database-enforced access takes longer to design, because the rules must be stated explicitly and agreed before the first screen is built — which is one reason our written specification always covers roles and data boundaries rather than only modules and features. It also demands that the data model be right: the rule “a parent sees their own child” only exists if the relationship between parent and student is a real, reliable structure in the database rather than a name typed into a field.

What it does not cost is speed. Rules evaluated in the database run where the data already is; the alternative is fetching thousands of rows across a network in order to throw most of them away, which is slower, not faster.

And it does not cost you flexibility. Roles change, branches open, someone gets promoted into a wider view. Changing a rule in one place is a smaller job than auditing every screen in the system to find out where the old assumption is written down.

There is one more cost worth naming: honesty at specification time. Writing the rules down forces the business to decide, before development, questions that are otherwise settled by accident — whether a branch accountant may see head-office adjustments, whether a teacher may see another section’s marks. Deciding them in a meeting is considerably cheaper than discovering them in an incident.

The test to run in your next demo

Ask the vendor to log in as the most restricted user their system has. Then, in front of you: change a record identifier in the address bar; widen or clear a filter on a report that is supposed to be scoped; export any list to a spreadsheet and open it. Ask a fourth question too — what happens if a developer adds a new report next year and forgets the rule? The answer to that one tells you where the security actually lives.

This is the mechanism underneath the phrase every vendor uses, which we unpack more broadly in what bank-level security should mean. If you want to see row and column rules working on data shaped like your own, ask us for that demo specifically — it is a more useful hour than a feature tour.