Database Row Level Security - Part 1

In this 3 parts series I intend to explain what Row Level Security in database management is and how to implement it in Oracle and SQL Server.

In almost all database management systems the level at which information is controlled extends only to a certain level of granularity. In some scenarios, however, there is a requirement to control access at a more granular level. A list of patients, for example, may be stored in a single table. Any doctor, however, is only be permitted to view his own patients' records. Another example can be those applications that implement feature access control. That is, users' credentials and their access to application's features and modules are stored in database and used by application.
In such cases, merely issuing a GRANT/DENY SELECT command on the table will not meet the requirement. One solution is to implement this requirement at the application level - in Business Logic Layer if application is using 3 tier architecture - to filter rows of data. But what if someone bypasses the code and gain direct access to data? The presentation layer creates the illusion of security. What actually needs to be done is to automatically filter queries at the database level. In the doctors and patients example, all users might have access to the patient table, but SELECT * FROM Patient only returns the rows that a user should see; wherever that query is executed from.

In part 2, I’ll explain how RLS can be implemented in Oracle 10g utilizing auditing features.