Database Security by Design

Network and database encryption are the real security mechanisms in place, but far too often, the simplest design principles are overlooked.  In the current environment of “buy don’t build” and agile methodologies, database design is often compromised for the sake of expediency.  None of the details presented here will be new to DBAs, or veteran designers. This is intended as a re-cap, and for those who might want designers and DBAs to bypass basic design principles.

First, do not allow any application to own or modify structure.  The schema that owns the data should only be touched by the DBA group.  Individual users may have privileges to modify data, but never structure. There are some reasonable needs for applications to have privileges on structures.  For example, an ETL application may need to drop and re-create indexes for efficiency purposes.  This can be accommodated by writing database procedures and granting execution privileges to the ETL user.  It’s a bit more work, but limits access to known exceptions to the general rule, (as opposed to allowing the ETL account to drop and create any index). The basic principle of a sound design strategy is to disallow any “back doors”, and definitely don’t leave them wide open.

Many applications require data to be logically horizontally partitioned.  For example, a parent company may acquire multiple subsidiaries.  There may be financial and/or legal reasons to keep subsidiaries from viewing and/or modifying each other’s data, while the parent may be able to view and modify all.  Some vendors and application developers may insist that this is an application security issue, but in reality, only database security can prevent unauthorized access. In such a case, introduce “subsidiary_id”, or whatever you choose to call the column.  This is a design consideration, because a column used for logical horizontal partitioning should be included in every table of reasonable size to avoid unnecessary joins.  For example, requirements may dictate that a customer belongs to a subsidiary, and no “pilfering” of customers between subsidiaries is allowed.   Multiple subsidiaries may have the same customer, so some redundancy must be introduced, (MDM considerations aside). 

Designing for performance may dictate that this “subsidiary_id” be introduced to any number of tables to avoid joining to the customer to filter out rows that the subsidiary should not see. This can create more redundancy, but the point is security.  One schema “owns” all data.  There should be an additional schema for each subsidiary that contains views limiting to a “subsidiary_id”, (select * from owner.table where subsidiary_id = <specific_subsidiary>).  Furthermore, no users (application or otherwise), should be allowed to modify any objects of these schema, while DML operations may be permitted. End-users, including application users, should only have the privileges necessary to operate on subsidiary-specific objects.  

Logical vertical partitioning works in exactly the same way.  The only difference is that the columns that can be viewed or modified by a specific user or application are limited, as opposed to the row-based limitations discussed previously. The classic example relates to departmental differences.  For example, only the HR department can view/modify salary, government ID, or other sensitive information, while a host of applications may be able to access the various attributes of the org chart without this information.

Implementation details may vary widely, particularly for DML on logically partitioned objects.  The following reference links can be used for specific information for three of the largest traditional RDBMS vendors.

References:

Oracle 12c Schema (User)

SQL Server 2014 Schema

DB2 10.5 Schema

For Additional Blog Post please check out NVISIA's Enterprise Data Management Page