Database Row Level Security - Part 1

Hi

A friend of mine sent me an e-mail to know why I have became lazy to update my weblog. I’m just so busy at the moment with a new coming project and don’t have enough time to make a post more than once a week. So I guess I have to apologize to those are really following my posts.
Anyway, in this series I’m going to present how we can implement access controls in databases at row level, Row Level Security or RLS.

Access control of information based on user permissions is a fundamental part of most computer software. Microsoft SQL Server, for example, enforces access controls on the server login, databases, and on objects within a database (such as tables). So does Oracle DBMS using users in databases.
In both cases, 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 and diagnoses, for example, may be stored in a single table. Any one doctor, however, may only be permitted to review information related to their own patients. In such a case, merely issuing a GRANT/DENY SELECT command on the table will not meet the business requirements. One solution to this problem is to implement this requirement in the application level - in Business Logic Layer if application is using 3-Tier architecture - to filter data based on username.
More realistic example can be those applications that implement security in application level. That is, the application uses a table of username/password couples in the same database or separate one to authenticate users and another table(s) to authorize users and control their access to the application’s subsystems.
But what if someone breaks the code and finds out how to get access to the data straightly? Trust me, it’s possible. I’ve done it at least twice. Not to break someone’s code, to test my own application.
Or maybe someone has the super user’s password (SA or SYS).
In both cases, the data is not actually secured. The way that we present it, it looks secured.
What is needed is a way to present the actual tables (or views) to user accounts with the filtering logic applied automatically, based on the user. In the case of doctors and Patient table, all users might have access to the Patient table but, for each user, SELECT * FROM Patient returns only the data that user should see.

In the next part, I’ll show you how to use Oracle 10g’s auditing features to implement RLS.

By the way, I don’t hear much from you about the weblog. I just get comments about individual posts. Let me know if there is any topic that you like me to write about or you have any idea how to make the posts better.

Love to see your comments.