A Better SQL Security Approach
This is not only an SQL's problem, I am going talk about, this is a pretty general problem of all complex systems dealing with user permissions, however SQL constitutes the best possible illustration to the issue.The principal source of all evil is the generalized security policies, policies trying to cover the entire space of user actions by being formulated in basic general terms.
Normally we are mislead into details of particular implementations of policies and/or access control systems, while the biggest issue we are facing is created by the fundamental principles of our access control system.
In order to create a secure system you must thoroughly examine ALL possible user scenarios and separate valid from invalid ones. Then you must create data-access rules system which allows valid scenarios and prohibits invalid ones. With the classical SQL approach to the access control, the problem begins with the set of all invalid scenarios — it is infinite, and potentially very complex. Using only the basic access control rules applied to the basic elements of the data structure you must formulate a general rule that rules out an infinite (therefore unexplored) multitude of access attempts. Sooner or later someone finds an unforeseen sequence of actions that slips through the barriers you have built… you know the rest. But the demand for you to foresee something is not the major disadvantage this approach. The biggest flaw of the classical SQL access control language is that the very border between «valid» and «invalid» is defined indirectly. The limit of what is allowed is a result of interference of the multitude of small simple rules (sometimes many thousands of those) always scattered across the vastness of sourcecode. Each individual rule is written clearly, but the superposition of all these rules is not written anywhere — the very result of the job is completely invisible, even for the author, there is no clean analytical way to compare the result with the intent. At the same time you can not simply test each and every «invalid» access attempt, because there are infinitely many of them!
Moreover, the traditional SQL access control system simply lacks of expressive power! It is already too vast, too complex, still it is bloating every day. However complex your traditional SQL access control system is it always fails to answer to the real world challenges. There is no human who can even memorize all Oracle or MySQL permission classes, and these enormous multitude of classes is not quite enough. Per table, per column… per trigger… Per TYPE! Not enough! There are few plug-ins for Postgresql implementing sort of «per-row» permission formalism. Still not enough! In some (quite common) applications data objects exist in several certain states and access permissions change upon state transition. This simple fact puts the traditional user permission system out of the job.
Fortunately, SQL allows us to define a brand new (ideologically opposite) access control system on top of the present one (utilizing a very small subset of the present system in a very clean and straightforward way). Here, I am about to show you the idea and the implementation in SQL, and later I will explain you why it will be better to replace the SQL interface completely (oh! it is going to interfere with the error reporting, and data types issues!).
Basically, all we need is to define the set of «valid» operations DIRECTLY — we simply need to do the opposite to what has caused us a trouble. And if we expose to the client only these «valid» operations then we have implemented the purest «deny by-default» policy ever — what is not allowed is not even defined. Here is the big picture of the solution:
And this is very easy to implement using only standard SQL capabilities. At first, examine ALL possible user scenarios and separate valid from invalid ones. Then describe all «valid» user actions by a set of stored functions, and assign the «SECURITY DEFINER» attribute to all these functions.
CREATE USER reader;
REVOKE all ON [all_tables] FROM reader;
GRANT execute ON [all_functions] TO reader;
CREATE USER doer;
GRANT all ON [all_tables] TO doer;
REVOKE connect FROM doer; — optionally, forget the doer's password.
ALTER [all_functions] SET OWNER doer;
As a result you will have the following system:
A client connects as a powerless user reader, executes ONLY THE PREDEFINED SET OF FUNCTIONS, these functions authenticate and authorize the client, elevate the priviledge if needed, manipulate data if allowed, and thus encapsulate all the access control tasks.
This approach guarantees you the following advantages:
— all data access rules are localized in one place and organized naturally from the «behaviour» perspective;
— the definition is STRAIGHTFORWARD — what is written is what it does;
— what is not written is not possible — nothing is allowed «by-default» nor as a result of any superposition of the rules;
— SQL injections are rendered completely useless altogether — the reader user can perform NOTHING, and the doer user can not connect to the DB.
As for disadvantages, many may say: «We can not codify each and every allowed action!» — and it would be utterly foolish. What is not deliberately codified does not exist at all in the realm of IT. People tend to forget that there is no objective reality in the realm of IT. The entire realm is purely artificial — everything you are taking for granted, every environment, every expectation, every bug is always CODIFIED by someone, by a person. Every program is a description of exactly «each and every allowed action». It is the only job of a programmer to codify «each and every allowed action» in a form of a program.
There are, however, different ways to codify this complex object, and I did my best to explain why and how my straightforward approach is better than the classical obscurant one.
Normally we are mislead into details of particular implementations of policies and/or access control systems, while the biggest issue we are facing is created by the fundamental principles of our access control system.
In order to create a secure system you must thoroughly examine ALL possible user scenarios and separate valid from invalid ones. Then you must create data-access rules system which allows valid scenarios and prohibits invalid ones. With the classical SQL approach to the access control, the problem begins with the set of all invalid scenarios — it is infinite, and potentially very complex. Using only the basic access control rules applied to the basic elements of the data structure you must formulate a general rule that rules out an infinite (therefore unexplored) multitude of access attempts. Sooner or later someone finds an unforeseen sequence of actions that slips through the barriers you have built… you know the rest. But the demand for you to foresee something is not the major disadvantage this approach. The biggest flaw of the classical SQL access control language is that the very border between «valid» and «invalid» is defined indirectly. The limit of what is allowed is a result of interference of the multitude of small simple rules (sometimes many thousands of those) always scattered across the vastness of sourcecode. Each individual rule is written clearly, but the superposition of all these rules is not written anywhere — the very result of the job is completely invisible, even for the author, there is no clean analytical way to compare the result with the intent. At the same time you can not simply test each and every «invalid» access attempt, because there are infinitely many of them!
Moreover, the traditional SQL access control system simply lacks of expressive power! It is already too vast, too complex, still it is bloating every day. However complex your traditional SQL access control system is it always fails to answer to the real world challenges. There is no human who can even memorize all Oracle or MySQL permission classes, and these enormous multitude of classes is not quite enough. Per table, per column… per trigger… Per TYPE! Not enough! There are few plug-ins for Postgresql implementing sort of «per-row» permission formalism. Still not enough! In some (quite common) applications data objects exist in several certain states and access permissions change upon state transition. This simple fact puts the traditional user permission system out of the job.
Fortunately, SQL allows us to define a brand new (ideologically opposite) access control system on top of the present one (utilizing a very small subset of the present system in a very clean and straightforward way). Here, I am about to show you the idea and the implementation in SQL, and later I will explain you why it will be better to replace the SQL interface completely (oh! it is going to interfere with the error reporting, and data types issues!).
Basically, all we need is to define the set of «valid» operations DIRECTLY — we simply need to do the opposite to what has caused us a trouble. And if we expose to the client only these «valid» operations then we have implemented the purest «deny by-default» policy ever — what is not allowed is not even defined. Here is the big picture of the solution:
And this is very easy to implement using only standard SQL capabilities. At first, examine ALL possible user scenarios and separate valid from invalid ones. Then describe all «valid» user actions by a set of stored functions, and assign the «SECURITY DEFINER» attribute to all these functions.
CREATE USER reader;
REVOKE all ON [all_tables] FROM reader;
GRANT execute ON [all_functions] TO reader;
CREATE USER doer;
GRANT all ON [all_tables] TO doer;
REVOKE connect FROM doer; — optionally, forget the doer's password.
ALTER [all_functions] SET OWNER doer;
As a result you will have the following system:
A client connects as a powerless user reader, executes ONLY THE PREDEFINED SET OF FUNCTIONS, these functions authenticate and authorize the client, elevate the priviledge if needed, manipulate data if allowed, and thus encapsulate all the access control tasks.
This approach guarantees you the following advantages:
— all data access rules are localized in one place and organized naturally from the «behaviour» perspective;
— the definition is STRAIGHTFORWARD — what is written is what it does;
— what is not written is not possible — nothing is allowed «by-default» nor as a result of any superposition of the rules;
— SQL injections are rendered completely useless altogether — the reader user can perform NOTHING, and the doer user can not connect to the DB.
As for disadvantages, many may say: «We can not codify each and every allowed action!» — and it would be utterly foolish. What is not deliberately codified does not exist at all in the realm of IT. People tend to forget that there is no objective reality in the realm of IT. The entire realm is purely artificial — everything you are taking for granted, every environment, every expectation, every bug is always CODIFIED by someone, by a person. Every program is a description of exactly «each and every allowed action». It is the only job of a programmer to codify «each and every allowed action» in a form of a program.
There are, however, different ways to codify this complex object, and I did my best to explain why and how my straightforward approach is better than the classical obscurant one.
0 comments