Security: Separation of Privilege

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

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

Security principle: Separation of Privilege

The Principle of Separation of Privilege, aka Privilege separation demands that a given single control component is not sufficient to complete a task. A different, more generic description is that multiple conditions need to be met in order to gain access to a given process or object. A control could be a permission, for example.
Privilege separation is sometimes (but not necessarily) implemented with a form of dual control and requires a certain level of compartmentalization of a process or program to facilitate multiple access checks.

This approach together with the Principle of Least Privilege reduces

Folks with an affinity for history may like to use “Divide and conquer” or even more original, “divide et impera” as a memory hook :) .

Incidentally, OpenSSH has a security option called UsePrivilegeSeparation (https://linux.die.net/man/5/sshd_config), turned on by default, that has the effect that an unprivileged child process without root privileges to deal with incoming network traffic. However, this is more a case of classical PoLP and Privilege bracketing, as discussed here (The Principle of Least Privilege (POLP)) and here (Delegation of Authority). As you can see its easy to get confused when doing research. And I am not saying that is wrong. What matters is that you know what you want to reach and why.

More classical examples would be scenarios where 2 keys are required like certain types of safes. They may or may not be held by different people.

Privilege Separation with 2 keys

If we look at the authentication process, Azure AD Multifactor authentication (MFA) could be considered an example of multiple controls involved: Even if an attacker gains knowledge of the password, he would still need to gain access to an additional piece, like the (unlocked) phone.

Note on Separation of Privilege vs Separation of Duties
As some will notice, there is a great overlap with Separation of Duties (SoD): Depending on the exact implementation, Privilege Separation can directly enable SoD.
For example, the same way “Dual control”-mechanisms can be used to implement Privilege Separation, such a mechanism can also be used to implement SoD.

Depending on which sources you consult (I will even include such in the references below), you may read that Separation of Privileges is equivalent to SoD. But I find it important to distinguish and keep in mind the fine line between those two principles. And that is that Separation of Duties requires separate persona.

This is why in my view dual control does not necessarily solve Separation of Duties. Dual control could be solved involving but not necessarily SoD.
: Therefore, if you want to express the SoD-requirement to involve different persona, using the terms “Two-person control” or “4 eyes principle” is less prone to confusion than the more generic term “dual control”.

Separation of Privilege in the SQL realm

In SQL Server, privilege separation is not commonly built-in by design, but there are some examples that perfectly fit the criteria.

Example 1, Object-creation

One example is that to create tables, a User needs to have at least both the ALTER-Permission on the schema and the CREATE TABLE-Permission on the database. Other than that, this is a rare case within the SQL engine.

2PermissionsToCreateObjects

Example 2, querying across objects

There is another way one can implement privilege separation in SQL Server and Azure SQL: normally, when multiple objects are accessed within one query, the SQL Server engine honors the so-called “ownership-chain”. This is a concept unique to SQL Server and has the effect that so long as any referenced object within a query is owned by the same principal as the first one in the chain, no further permission checks occur. This means a single SELECT (or INSERT-, UPDATE-, DELETE) -permission is required to access, for example, a View “AggregatedSales” if that view accesses a table “Orders” and the view and the table have the same owner. It is not required to grant SELECT on the table if the intention is to solely grant access to the accumulated data from the view. This is a built-in behavior.

However, one can intentionally break this ownership-chain and change the owner of the table or the view (for example, by placing them into different schemas and with different schema-owners, a recommended practice over changing owners at object-level), which then would require the calling user to have the SELECT-permissions on both the view and the table to use the view. In other words, the user would need two permissions. So, there you have another scenario of technically privilege separation, as one permission is not sufficient alone any more to access the view. But to be fair, this applies only to accessing the view: to query the table alone, one still only requires one SELECT-permission.

This is how this looks like in code:

BrokenOwnershipChain

We can see that the table is owned by a “dbo” (principal_id =1 ) whereas the view is still owned by the overall Schema owner “SchemaOwner”.

Hence the SELECT-permission on the view alone is not sufficient for User Jiao to query it, as is accesses the table.

BrokenOwnershipChainMissingPermission

After granting the SELECT on the table as well, the User can use the View:

BrokenOwnershipChainPermissionsComplete

In many if not most cases, it makes sense to have ownership-chains set up. But there are cases where you will want to explicitly break them. In general, it is advisable to always make conscious decisions around this and use a different owner than the built-in dbo.

Ownership-chaining by itself is a topic that surely deserves its own articles, but this is where it connects with Separation of Privilege.

Divide and be more secure :)

Andreas

Thank you to my Reviewers:

Rohit Nayak, Senior Program Manager in SQL Security

Raul Garcia, Principal Security Program Manager