Fine Grained Access Control

FGAC 精细化访问控制

Fine Grained Access Control

Arup Nanda, Proligence, Inc.

Introduction

Just as a picture is worth thousand words, it’s my belief that even the most complex of concepts or features can be easily explained through an example, where the reader has the ability to test out the concepts while reading them online. Following my own convictions, I will explain the concepts and uses of this powerful feature of the database using an example of a hospital database.

The application that the hospital uses must adhere to the HIPAA regulations and one of the mandates is to allow viewing of patient data only to the people who need them and have authorization for. This means the doctors can access the data on just those patients they are treating, not all. In the old days, this could have been done using a simple filtering predicate in the application queries. This is not practical for a variety of reasons – the primary of which is the modification of a large number of SQL statements inside the application. In case of third party canned applications, this is even more difficult, with the source code controlled in a different place outside the control of the hospital.

The other problem is security – what if the user just bypasses the applications and queries the data directly from the database? This sidesteps the filtering predicate inside the code, allowing full access to the data. Obviously the solution calls for the filtering predicate to be applied automatically, regardless of how the access is made.

One of the approaches is to use a view on the table with a filtering predicate built in, and allowing users to access the view instead of the base table. This accomplishes the security requirement, but with the proliferation of the views, it might be impractical to maintain this setup. In addition, if the need ever comes to restrict the access by the owner itself, this

solution will not work, since the owner can select from the base table. Using views also forces us to use a predicate that is static, not generated at the runtime.

The perfect solution comes from Oracle’s implementation of Fine Grained Access Control (FGAC). It is also known as Row Level Security (RLS) or Virtual Private Database (VPD). In this article we will explore the use of this powerful feature in the form of an example setup, to bolster the understanding. We will also learn how to use another advanced feature – application contexts in a database setup.

Setup

You can download the scripts to create all the objects used in this article and populate the data from my website, http://wendang.chazidian.com/pubsupp.

In our example, we have used the case of an overly simplified hospital database. Typically hospitals have several doctors and each patient is assigned a doctor. The tables are owned by the schema HOSPITAL. The table DOCTORS holds the information on doctors as follows.

Name Null? Type ----------------- -------- ------------ DOCTOR_ID NOT NULL NUMBER DOCTOR_NAME VARCHAR2(20) GROUP_ID NUMBER

FGAC 精细化访问控制

The primary key of the table is DOCTOR_ID. It’s assumed that the column DOCTOR_NAME contains the doctor’s login id to the database. A group may consist of several doctors. The column GROUP_ID specifies the group the doctor belongs to.

The other important table holds information on patients visiting the hospital, named PATIENTS, shown below.

Name Null? Type ----------------- -------- ------------ PATIENT_ID NOT NULL NUMBER DOCTOR_ID NUMBER PATIENT_NAME VARCHAR2(20) DISEASE VARCHAR2(20)

The column PATIENT_ID is the primary key of the table. The column DOCTOR_ID is a foreign key to the table DOCTORS shown above. For the purpose of simplicity, let’s assume that the relationship between DOCTORS to PATIENTS is one-to-many, whereas in the real life, it is probably many-to-many.

Here is how the data looks like in the table DOCTORS.

DOCTOR_ID DOCTOR_NAME GROUP_ID ---------- -------------------- ---------- 1 DRADAM 1 2 DRBARB 2 3 DRCHARLIE 2

And here is the data in table PATIENTS.

PATIENT_ID DOCTOR_ID PATIENT_NAME DISEASE ---------- ---------- -------------------- ----------- 1 1 LARRY EGO 2 1 BILL CONTROL 3 2 SCOTT FICKLENESS 4 3 CRAIG LOWVISION 5 3 LOU GREED

Corresponding to all the names of the doctors, we need to have the userids created in Oracle in the same name. Therefore, we have the users named DRADAMS, DRBARB and DRCHARLIE, all with SELECT, INSERT, UPDATE and DELETE privileges on the tables DOCTORS and PATIENTS.

Building the FGAC Setup

In the existing application, the following statement is a call made to the database to see the patient data.

SELECT * FROM PATIENTS;

With the new requirements in mind, the call must be changed to

SELECT * FROM PATIENTS WHERE DOCTOR_ID = <id of the doctor logged in>

We somehow have to make a system where the application need not be changed, and the first call will automatically select only the records related to the doctor currently logged in, not all. In other words, we have to generate a filtering predicate,

FGAC 精细化访问控制

i.e. a WHERE clause to be appended to he query automatically. Building this predicate is the first step. The following function returns a string that can be applied to the query as a WHERE clause.

create or replace function get_doctor_id ( p_schema_name in varchar2, p_table_name in varchar2 ) return varchar2 is l_doctor_id number; begin select doctor_id into l_doctor_id from doctors where doctor_name = USER; return 'doctor_id = '||l_doctor_id; end;

Note how the function returns the string DOCTOR_ID = <id> where <id> is the numerical ID of the doctor who is logged in now, as returned by the function call USER above.

This special function is called a policy function and is the building block of a FGAC setup. Note that this has exactly two input arguments, for the schema and the table on which it will be applied and has exactly one return value, the string that will be used as a WHERE clause. The structure of the policy function must be exactly this and is not flexible. The logic can be changed inside, though.

Next step is to build a policy to be placed on a table. This policy is the one that restricts the rows accessible to the users. It does so by applying the output of the policy function. The following code segment sets up a policy on the table PATIENTS.

begin dbms_rls.add_policy ( object_schema => 'HOSPITAL', object_name => 'PATIENTS', policy_name => 'PATIENT_VIEW_POLICY', policy_function => 'GET_DOCTOR_ID', function_schema => 'HOSPITAL', statement_types => 'SELECT, INSERT, UPDATE, DELETE', update_check => true, enable => true ); end;

Here we have defined a policy named PATIENT_VIEW_POLICY on the table PATIENTS in schema HOSPITAL. The policy calls the function GET_DOCTOR_ID as shown in the parameter policy_function. This policy is applied to all types of DML statements on the table – SELECT, INSERT, UPDATE and DELETE. The other options will be explained later.

Once the policy is in place, logon to the database as the user DRADAM and select from the table

SQL> select * from hospital.patients

FGAC 精细化访问控制

PATIENT_ID DOCTOR_ID PATIENT_NAME DISEASE ---------- ---------- -------------------- -------- 1 1 LARRY EGO 2 1 BILL CONTROL

Well what happened? There are only two rows selected from the table; but we know for a fact that the table has five rows. However, only patients 1 and 2 are supposed to be seen by the user DRADAM, and only those were displayed. The most important point to note here is that the user did not specify any where clause. Yet, the filtering predicate was applied automatically, by the policy using the output from the policy function.

The user’s original query

SELECT * FROM PATIENTS

was rewritten to

SELECT * FROM

(SELECT * FROM PATIENTS)

WHERE DOCTOR_ID = 1

What if the user deletes the table, as seen below?

SQL> delete hospital.patients; 2 rows deleted.

Note, only 2 rows are deleted, not all the five. The same principle hold true – the filtering predicate is applied

automatically to the query. If the user updates the table,

SQL> update hospital.patients set disease = null; 2 rows updated.

Again, only 2 rows are updated, not all five.

Another user, DRBARB is allowed to see only patient 2. If she logs in and uses the same query:

SQL> select * from hospital.patients; PATIENT_ID DOCTOR_ID PATIENT_NAME DISEASE ---------- ---------- -------------------- ------------ 3 2 SCOTT FICKLENESS

Notice how only one row was returned, even though the table has 5. Dr Barb was authorized to see patient 3 only and that is what she saw.

The policy is applied regardless of how the table is accessed – through a trigger, a procedure, an application, anything. It is as if the table contains only two rows for the user DRADAMS, not the five which are actually there. This facility

creates a window where the user always sees the rows he is supposed to see, not all, something akin to a private view of the table. By applying the same policy to all the tables in a database, the users can see the data which they are authorized to see only, as if they have a private database inside a real database. Hence the Fine Grained Access Control is also known as Virtual Private Database feature.

FGAC 精细化访问控制

Note how the content of the table changes depending upon the user logged in, eliminating the need for application changes. This powerful feature makes applications developed only once; the policy dictates the filtering predicate.

In addition to example given above, FGAC has other uses in hosting companies, where multiple users share the same database and in some cases the same table. FGAC allows the creation of several virtual databases, not physically different ones – making the setup simpler and less expensive to maintain.

Bypassing the Restrictions

Sometimes it might be required to bypass the restrictions. For instance, we may decide to remove restrictions from the owner of the tables. To do this, we will pass a NULL as the return value to be used as a predicate. If the value is NULL, the policy allows all rows to be visible without restrictions. The policy function can then be rewritten as follows.

create or replace function get_doctor_id ( p_schema_name in varchar2, p_table_name in varchar2 ) return varchar2 is l_doctor_id number; begin if (p_schema_name = USER) then return null; end if;

select doctor_id into l_doctor_id from doctors where doctor_name = USER; return 'doctor_id = '||l_doctor_id; end;

Note the newly added segment shown in bold. If the current user is the schema owner of the table, then the policy function returns NULL, i.e. no filtering predicate is applied to the query.

There is a special type of system privilege called EXEMPT ACCESS POLICY used to exempt a specific user from being subject to any kind of restriction on any of the tables. The DBA role is granted this system privilege by default; hence a user with DBA role accessing data will not be restricted by any policy – a very important point to keep in mind while designing applications involving FGAC.

Other Dependent Tables

So far we have talked about only one table. Suppose there is another table in the database to hold information on

treatments given to patients. Since a patient may be given treatment more than once, the table TREATMENTS, shown below, has column to record the dates, too.

Name Null? Type ----------------- -------- ------------ PATIENT_ID NUMBER TREATMENT_DT DATE TREATMENT VARCHAR2(30)

Here are the sample data in this table.