Thursday, February 17, 2011
How Fine Grained Access Control Works in Oracle 10g?
Let us take an example to understand the concept behind the Fine Grained Access Control,
Our example is based on a Hospital database with two basic tables for simplicity. Typically hospitals have several doctors and each patient is assigned a doctor. The tables are owned by the schema HOSPITAL.
Tables involved and their structure:
Doctors
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.
Patients
The column PATIENT_ID is the primary key of the table. The column DOCTOR_ID is a foreign key to the table DOCTORS shown before. For the purpose of simplicity, let’s assume that the relationship between DOCTORS to PATIENT is one-to-many, whereas in the real life, it is probably many-to-many.
Let us have few datum like below,
Doctors:
Patients:
Corresponding to all the names of the doctors, we need to have the user ids 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:
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 =
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, i.e. a WHERE clause to be appended to the query automatically. Building this predicate is the first step.
I will take this opportunity to explain about the important term named “POLICY FUNTION” and it is nothing but the function used to generate the necessary predicate. This special function 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.
Now lets comeback to our example, the following function returns a string that can be applied to the query as a WHERE clause.
Note how the function returns the string DOCTOR_ID = where is the numerical ID of the doctor who is logged in now, as returned by the function call USER above. 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.
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.
Once the policy is in place, logon to the database as the user DRADAM and select from the table.
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
was rewritten to
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,
Another user, DRBARB is allowed to see only patient 3. If she logs in and uses the same query:
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 like 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.
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.
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
1 Responses to “How Fine Grained Access Control Works in Oracle 10g?”
February 17, 2012 at 11:40 AM
Clear and Simple Explanation of this concept. Very Nice Article!
Post a Comment