Fine-grained access control (FGAC) is a new feature in Oracle8i that allows you to implement security policies with functions and then use those security policies to implement row-level security on tables and views. The database server automatically enforces these security policies, no matter how the data is accessed -- through SQL*Plus or the Internet, as an ad hoc query, or as an update processed through an Oracle Forms application.
What, you might ask, is a security policy ? Consider the following very simple scenario (I'll expand upon this scenario in the full example at the end of this chapter). Suppose that I have tables of hospital patients and their doctors defined as follows:
CREATE TABLE patient ( patient_id NUMBER, name VARCHAR2(100), dob DATE, doctor_id INTEGER ); CREATE TABLE doctor ( doctor_id NUMBER, name VARCHAR2(100) );
Now suppose that I want to let a doctor see only her own patients when she issues a query against the table. More than that, I don't want to let a doctor modify patient records unless those records belong to that doctor's patients.
You could achieve much of what is needed through the creation of a set of views, and many organizations have been doing just that for years. The view-based approach can become quite complex, especially if you want to make it foolproof. Wouldn't it be so much more elegant if you could just let any doctor connect to her schema in Oracle, issue the following query:
SELECT * FROM patient;
and then make certain that the doctor sees information only about her patients? With this approach, you embed all the rules needed to enforce the appropriate privacy and security rules into the database itself as a security policy so that it is transparent to users of the data structures. Oracle uses that policy to modify the WHERE clause of any SQL statement executed against the table,thereby restricting access to data. This process is illustrated in Figure 8.1.
With Oracle8i 's fine-grained access control, you can apply different policies to SELECT, INSERT, UPDATE, and DELETE operations and use security policies only where you need them (for example, on salary information). You can also design and enforce more than one policy for a table, and can even construct layers of policies (one policy building on top of an existing policy) to handle complex situations.
A variety of programs you can use to define security policies and to associate those policies with specific PL/SQL functions that will generate WHERE clause predicates for use in fine-grained access queries. See Chapter 7, New and Enhanced Built-in Packages in Oracle8i.
TIP: The default database installation does not grant the EXECUTE privilege on the DBMS_RLS package to PUBLIC. Access is granted only to EXECUTE_CATALOG_ROLE, so schemas calling the package must have that role assigned to them.
Oracle discusses each of these topics in a different area of its documentation, making it difficult to pull them all together into a sensible, easy-to-deploy feature. This chapter takes a different approach. I will explain each area of functionality and the standalone steps needed to use them, but then immediately move to an extended example that will show you exactly how to implement FGAC in your own environment.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.