Security: The Need-to-know principle

This article is contributed. See the original author and article here.

(part 2 of my series of articles on security principles in Microsoft SQL Servers & Databases)

The Need-to-know security principle

This principle states that a user shall only have access to the information that their job function requires, regardless to their security clearance level or other approvals.
In other words: a User needs permissions AND a Need-to-know. And that Need-to-know is strictly bound to a real requirement for the User to fulfill its current role.
As you might be able to tell by the choice of words the Need-to-know principle is typically enforced in military or governmental environments.
Sometimes, in non-military scenarios, you will also find a slightly different description which states in weaker terms that access to data must be regularly reviewed to ensure that users only access data they strictly needed for legitimate reasons. This is enforcement by regulation or rule rather than permissions and can be sufficient in the private sector.
In information technology the Need-to-know can be implemented by using mandatory access control (MAC)* as well as discretionary access control (DAC)* in conjunction with a secondary control system.

*links to further reading below this article

Background
SQL Server uses a discretionary access control, as owners of objects (and the highest “owner” is the sa-account) can pass permissions to individuals. A MAC-based system is traditionally based on a multilevel security (MLS) operating environment working with Classifications of assets and security-clearance of Users and can be extended to require other mandated factors such as a Need-to-know.
In the Windows world a Multiple single-level (MSL) approach is often used: essentially keeping different levels of data (i.e. Secret and “Top Secret” on different servers or even within different environments.

For the secondary control system there is no specific type that needs to be used. Anything that can enforce this principle is good.
If no secondary control system is available, Auditing of access can be used to control adherence to the Need-to-Know protocols in place. Auditing does not prevent breach, but it can make sure it does not go undetected and without consequences, whichever those may be.

Note
The Need-to-know principle may require much more diligence and particularly different procedures than the often used discretionary or role-based access control – as under Windows Server, the Azure RBAC system or SQL Server. This depends on the exact implementation of the Need-to-know factor within the system. When a user changes job roles it needs to be ensured that the Need-to-know is adjusted immediately.

Generic example scenario

A database contains data of technical specifications of all products the company produces worldwide.
Data can be classified as “Public ,“Internal” or “Restricted”.
A User, Hoang, has been granted read-permission to all tables that contain data for his job function. He has clearance for anything “Internal” – including anything below, which is this case is “Public”. In effect, that he cannot read tables which are classified “Restricted”.
In addition to that, he has a Need-to-know for data only concerning Data of a certain project: “Kilimanjaro”.
So while many other project details are considered “Internal”, with the Need-to-know he can only see those belonging the project he is assigned to: “Kilimanjaro”.
Other users with the same clearance may have a different Need-to-know, according to their specific job function. Like User Ricardo in the below image.

NeedToKnow_Example1_DatabaseTables

Tip
A simple way to depict the Need-to-know principle is thinking about it adding basically a filter on a secondary axis, in addition to the existing access-control-system. The exact mechanics are subject to implementation.

This simple example assumes that individual data objects within a database are classified differently.

Note
The additional control via a Need-to-know on data which is classified “public”, or some equivalent does not make sense.

Example scenario military use case

The above example may look strange if you are familiar with for example military environments. In such, “Top Secret” Data will never be located even within the same environment even as “Secret” data, let alone “Unclassified” data.

A simplified scenario which is somewhat more realistic in such environments is depicted below. A given user Nathan may have “Top Secret” Clearance, but according to his Need-to-know would only ever get to see data concerning “Alpha”.

Or, in other words: just because Nathan has a Top Secret clearance, does not mean he can see Top Secret data. He must have a Need-to-know (and in fact additional add-on’s). Otherwise, all such data would be redacted.

Need-to-know in the SQL realm

SQL Server does not have a native security feature strictly built to enforce a Need-to-know concept. The SQL engine does not have a MAC system integrated at this time. The permission system of SQL Server is based on user identities and owners who can grant permissions and thus considered a DAC system (discretionary access control).

But this is not the end of the line. There are ways to extend this to a Need-to-know system.

Using encryption

One technique is to use Cell-Level encryption: One can encrypt values in specific cells either on columns and or row-axis (using custom logic), and the Decryption key can be made accessible for multiple personae and exclude others.

In the code-snippets below we can see that 2 doctors are assigned the same minimal privileges on a table named “Patients” – simply by putting them into the same database role (as a general best practice).

NeedToKnow_Encryption_1

In the next step we can see that each doctor will have one which is the protector of one symmetric key – one distinct key per certificate.

NeedToKnow_Encryption_2

This is what the system then contains:

NeedToKnow_Encryption_3

Now each doctor needs to have access to just “his” personal key. For this the VIEW DEFINITION permission is sufficient.

NeedToKnow_Encryption_4

As a result, each doctor can only decrypt the data that has been encrypted with his own key.

NeedToKnow_Encryption_5

Note
The NULL-values in the “Symptoms”-column are returned for those columns where the DecyptByKey-Function cannot decrypt the data with the currently loaded key(s).
This columns data essentially is only accessible on a Need-to-know basis.

It is also possible to enable multiple personae to use the same keys. This can be done by adding encryption by additional certificates to the same symmetric key as shown here.

NeedToKnow_Encryption_6

The complete example is available as a download below this article.

In theory also the Always Encrypted feature can be used to enforce a Need-to-know system, by assigning different keys for different columns. The principle is that different users can be granted the same permissions (SELECT, INSERT, UPDATE and or DELETE) on the table while accessing the tables via different Since the keys ache cached within the driver though, this separation is not straightforward to implement and would only work in scenarios where different machines and applications with separate drivers are utilized.

Security-Note
Can’t say this enough: more often than one may wish for I see examples of Credit Cards or Social Security Numbers being stored in a way that leaves the last 4 digits in clear-text. (Famous question for call-center Agents but also bad actors!) Do not do that. Those last digits are the random pieces of those important numbers. The first blocks are fairly static and not personalized.
Here you can read more on how those can be abused: How Apple and Amazon Security Flaws Led to My Epic Hacking | WIRED, What Can A Scammer Do With the Last 4 Digits of Your Social Security Number? | Consumer Boomer

Using Row level security for the Application

There is another feature within SQL that can help implementing a Need-to-know system: When access can be restricted to use certain Applications (and users cannot directly connect to the database), Row Level Security (RLS) can be used to implement such a system.
The concept here is to store information in the database that can be looked up by a special table valued function and uses information from the currents’ user context to apply a filter on the query. This is all done without requiring a rewrite of the calling statement as this function is bound to the table that requires the filtering, by the means of a security function.

Security-Note
Row Level Security is not a security feature but a programmability feature which can be used to implement security mechanisms when it can be ensured that users cannot query the database directly. It is important to enforce this. Also, it cannot be used to protect against Database Administrators or even Developers on the same database.

Here is how such a function and security policy can look like:

NeedToKnow_RLS1

When querying the table, in this case “Patients”, SQL Server will change the query plan and join the function and apply the filtering in this case based on the current users’ SID, which is stored in a table “StaffDuties”.

NeedToKnow_RLS2

This means that only staff that is working in the same wing as the patient is residing, can access the patient’s data – no matter the overall access-permissions again as depicted below.

NeedToKnow_RLS3

You can find code-examples in the online-documentation: Row-Level Security – SQL Server | Microsoft Docs.

Similar can be done by using custom stored procedures with respective logic inside. Here the same applies: it can only be seen as an application-level convenience but not as the foundation of a Need-to-know system.

It can perfectly make sense, to combine RLS and Always encrypted to enforce Need-to-know even in case of Admin-access.

Again, these are just some examples on how the Need-to-Know principle can be implemented. As I said there is no one golden rule about this.

Happy securing

Andreas

Thank you to my Reviewers:
Adrian Rupp, Senior Program Manager in SQL Security with special knowledge in military scenarios
Jakub Szymaszek, Principal Program Manager in SQL Security on the encryption parts
Rohit Nayak, Senior Program Manager in SQL Security
Dilli Dorai Minnal Arumugam, Principal Software Engineer in Data-Governance