A few decades back, I can recall needing to have security within the database be a bit more sophisticated than what was available. On specific tables, there was a need to limit access to a subset of rows, or a subset of columns to specific users. Yes indeed, views have always existed, and yes indeed, views can be established limiting rows or columns displayed. However, views only can go so far. For example, given a base ORDER table, a view may be defined for the finance area called ORDER_4_FIN, and a view may be defined for the sales area called ORDER_4_SLS, and a view may be defined for external vendors delivering orders called ORDER_4_SHIP.
In this fashion, each grouping has a properly defined and scoped view they may use. Nevertheless, each user or tool must know exactly which view each specific user must use. In the circumstance years ago, an application was designed to do a lookup on a reference table giving the right name for a user to invoke, and then the application dynamically wrote the SQL to be executed. Here, decades later, with few poor performance exceptions, the same kind of solution is still required. For any circumstance where users may be accessing data from multiple fronts, this option is effectively useless. Controls such as this can only be an effective solution when the world is ruled by a single application access, or API. Beyond that scope, a DBMS solution is still needed.
Why can’t an internal metadata table be created within the DBMS linking ORDER_4_FIN and ORDER_4_SLS and ORDER_4_SHIP to ORDER and linking DBMS users to which specific view they must use whenever they invoke the term “ORDER”? Since most optimizers do query rewrite, this kind of substitution should be able to be performed within the optimizer pre-processing without crazy and excessive performance impacts. If such an approach existed within the DBMS, doors would be opened on many fronts to ease the fashion in which security must be managed. It could be done, but vendors would first need to decide if such a feature is wanted and desired by users.
Certainly, one contributing factor to the lack of interest in finessing security in this way is likely our reporting and analytics tools. These tools, which shall remain nameless—but we all know many of them, since it is virtually all of them—bypass DBMS-level security entirely. All queries issued by the tool generally have a generic service account that has access to all data. The tool itself must establish its own rules for securing the data so that the right user may see only what they are allowed. Since DBMS-level security is virtually ignored, who needs enhancements at the DBMS-level?
Why exactly is duplicating security a good thing? Why is opening the door to another separate effort that may create new and unthought of security holes desired? Does the world need more data auditors? Inside every DBMS the security rules are defined. Many metadata tables exist to support the DBMS’ ability to decide who can do what kinds of things with what data. Most reporting and analysis tools are only interested in read/select authorities. It should not be impossible to import the necessary data security rules from the DBMS and then leverage them inside the analytics tool.
Admittedly, sometimes these tools store new and altered versions of the data. But all data and derivations come from the DBMS. Absorbing rules, and massaging them as data is maintained within the tool, should be a task that can be accomplished. Most of these tools are more than old enough to drink, so they have had time to mature these functions and tasks. If we did have DBMSs that supported a poly-view to object security approach, or reporting and analytics tools that effectively leveraged existing DBMS-level security, wouldn’t those be really cool features?