Oracle's row-level security gives users their own virtual private databases.
Ensuring appropriate information privacy is a pressing concern for many businesses today, given privacy legislation such as the United States' HIPAA (Health Insurance Portability and Accountability Act), Gramm-Leach-Bliley Act, Sarbanes-Oxley Act, and the EU's Safe Harbour Law. Other privacy mandates, such as Visa's Cardholder Information Security Program (CISP), also require businesses to ensure that access to information is tightly controlled.
Oracle has always included the ability to grant (or deny) users access to database objects, but these privileges are defined at the object level—for an entire table, not for specific rows in that table. Although that approach is sufficient for many applications, any application touching on financial, health, or other kinds of personal information usually requires more-discrete controls over access and authorization.
Oracle's row-level security (RLS) feature, introduced in Oracle8i, provides fine-grained access control—fine-grained means at the individual row level. Rather than opening up an entire table to any individual user who has any privileges on the table, row-level security restricts access to specific rows in a table. The result is that any individual user sees a completely different set of data—only the data that person is authorized to see—so the overall capabilities are sometimes referred to as Oracle's virtual private database, or VPD, feature.
Using Oracle's VPD capabilities not only ensures that companies can build secure databases to adhere to privacy policies but also provides a more manageable approach to application development, because although the VPD-based policies restrict access to the database tables, they can be easily changed when necessary, without requiring modifications to application code.
For example, say a bank's account managers (AMs) provide personal customer support to high-net-worth account holders. AMs use a custom banking application to help them check their customers' balances, deposit or withdraw funds, and decide on loan requirements, for example. At one time, the bank's policy was to allow all AMs to access all account holder information, but that policy was recently changed. Now, AMs are assigned to a particular set of customers, and they need to be able to access information pertaining only to those customers. The policy change needs to be reflected in the application, which currently shows all customer information to each AM, not just information on the customers to whom each particular AM is assigned.
To make the application comply with the new privacy policy, the bank has three choices:
- Modify the application code to include a predicate (a WHERE clause) for all SQL statements. This option doesn't ensure privacy policy enforcement outside the application, however, and if there are other changes in the future, the code will once again have to be modified, so this is not a good approach in the long term.
- Leave the application intact, creating views with the necessary predicates and creating synonyms with the same name as the table names for these views. This option is better from the perspective of application changes and security, but it can be difficult to administer, because of the potentially large number of views to track and manage.
- Create a VPD for each of the AMs by creating policy functions that generate dynamic predicates, which can then be applied across all objects, regardless of how they are accessed, by setting up policies with the row-level-security built-in package (DBMS_RLS).
This last option offers the best security without administrative overhead and ensures complete privacy of information—all the account managers see a different view of the table, according to their own credentials.
This article shows you how to set up a VPD security model. It goes through the process by using the foregoing bank scenario to create policy functions, define policies, and then test results. (Note that the tables are not completely defined, to keep the example simple.)
Basic Setup for the Example Application
Briefly, here are the basic assumptions about the example bank application:
- A BANK schema owns two key tables that make up the application—a CUSTOMERS table:
Name Null? Type
--------- -------- -----------
CUST_ID NOT NULL NUMBER
CUST_NAME NOT NULL VARCHAR2(20)and an ACCOUNTS table:
Name Null? Type
------- -------- -----------
ACCT_NO NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
BALANCE NUMBER(15,2)Listing 1 comprises the SQL script for creating and populating these two basic example tables.
- User SECMAN (security manager) owns an ACCESS_POLICY table that identifies the AMs and their respective customer accounts:
Name Null? Type
----------- --------- -----------
AM_NAME NOT NULL VARCHAR2(20)
CUST_ID NOT NULL NUMBER
ACCESS_TYPE NOT NULL CHAR(1)The AM_NAME column stores the user ID of the account manager; CUST_ID identifies the customer; and ACCESS_TYPE defines the specific access entitlement—S (SELECT), I (INSERT), D (DELETE), or U (UPDATE). Some example records from the ACCESS_POLICY table follow:
AM_NAME CUST_ID ACCESS_TYPE
------- ------- -----------
SCOTT 123 S
SCOTT 123 I
SCOTT 123 D
SCOTT 123 U
SCOTT 456 S
SCOTT 789 S
LARA 456 I
LARA 456 D
LARA 456 U
LARA 456 SAs you can see, SCOTT, the AM for customer 123, has all privileges—S, I, D, and U—as does LARA for customer 456. Also, because SCOTT can confirm account balances for one customer of LARA, he has S privileges on customer 456.
Step 1. Create a Policy Function
The bank's access rules (contained in the ACCESS_POLICY table) must be applied somehow, dynamically, whenever an AM tries to look into customer account information. The first step is to create a policy function that returns the appropriate predicate to be applied to the table. As with the ACCESS_POLICY table, the policy functions are created and controlled by user SECMAN, for security purposes. It's best to keep privacy rules separate from the tables to which they apply.
Let's examine the policy function, get_sel_cust_id—shown in Listing 2— in detail:
- Accepts exactly two parameters: the schema name (p_schema in varchar2) and the table name (p_table in varchar2)
- Returns one value only, a string—return varchar2 as l_retstr varchar2(2000);—comprising the predicate that will be appended to every query on the table
- Uses a cursor routine—for cust_ rec in—to get the list of values, because each user may have several cust_ids listed in the table
- Returns a constructed string—l_retstr—to be used as a predicate whenever any user attempts to access the underlying table
The function returns the predicate where cust_id in with the appended list of customer accounts (cust_id), separated by commas, that the user (am_name = USER) is allowed to see.
Note that before entering the loop that builds the list of cust_ids for the user, the code in Listing 2 checks to see if the user is the table owner BANK, in which case the function returns a NULL predicate, as follows:
if (p_schema = user) then
1_retstr := null;
After building this function, you want to make sure it returns the appropriate predicate, by testing some sample data. Connect to the database as SECMAN, and insert some records into the ACCESS_POLICY table, giving SECMAN read privileges on a few sample accounts, as follows:
insert into access_policy values ('SECMAN',123,'S');
insert into access_policy values ('SECMAN',456,'S');
insert into access_policy values ('SECMAN',789,'S');
Now execute the function:
select get_sel_cust_id
('BANK','CUSTOMERS') from dual;
The function returns a string that will be applied as a predicate, as shown in the following sample output:
GET_SEL_CUST_ID('BANK','CUSTOMERS')
------------------------
CUST_ID IN (123,456,789)
You need to create similar functions for the other types of access. For simplicity's sake, create a single function for all the other access types—UPDATE, DELETE, INSERT—as shown in Listing 3. However, note that for a real-world application, each type of access should have its own individual function defined, to ensure appropriate privacy.
The policy function in Listing 3 is nearly identical to the policy function in Listing 2, except that the predicate is further qualified by use of the information from the ACCESS_CONTROL table:
and access_type in ('I', 'U', 'D')
Creating a policy function is just the first step. You now need to ensure that the function will be used, by defining the policy that should control its use in your system.
Step 2. Define a Policy
Policies are defined with the DBMS_RLS package, which is Oracle-supplied. Be aware that the policies themselves are not database objects owned by any user (schema); they are logical constructs. Any user who has the execute privilege on the DBMS_RLS package can modify or drop a policy created by another user. Privileges to DBMS_RLS should be judiciously controlled and granted with caution.
In the following example, user SECMAN is granted execute privileges (by SYS) on the DBMS_RLS package:
grant execute on dbms_rls to secman;
Listing 4 creates a policy named CUST_SEL_POLICY on the table CUSTOMERS of schema BANK. This policy applies the predicate returned by the function GET_SEL_CUST_ID (which is shown in Listing 2) owned by schema SECMAN to all SELECT statements on the table.
Similarly, you place another policy on the table for other access types, as shown in Listing 5. This policy applies to inserts, updates, and deletes in the CUSTOMERS table.
It is almost identical to the SELECT policy, except that this policy includes a check that ensures that the policy will remain compliant even after an update:
update_check => TRUE
Data cannot be added to the table unless it adheres to the policy.
Step 3. Test the Setup
Now that the building blocks are in place, let's see how they work. Connecting as user BANK and issuing a simple select * from customers; query displays the following:
CUST_ID CUST_NAME
------- ------------
123 Jay Kulkarni
456 Wim Patel
These two records are the full contents of the CUSTOMERS table, and both records are shown because BANK owns the table, so the predicate clause is NULL—that is, no predicate is applied. However, when user LARA makes the same query, she sees the following:
select * from customers;
CUST_ID CUST_NAME
------- ---------
456 WIM PATEL
LARA sees only CUST_ID 456, not 123, because that is the row she is authorized to see, as determined by the ACCESS_ POLICY table. Note that the query has no WHERE clause but that the selection from the table is automatically filtered to show only the authorized rows.
If user SCOTT makes the same query, his results are different from the results for LARA: select * from customers;
CUST_ID CUST_NAME
------- ------------
123 Jay Kulkarni
456 Wim Patel
User SCOTT sees both rows, because he is authorized to do so, as shown in the ACCESS_POLICY table. When user LARA issues the query, the policy function get_sel_cust_id returns the predicate where cust_id in (456). Lara's original query select * from customers is rewritten as
select * from
(select * from customers)
where cust_id in (456)
The predicate is automatically appended to the user's original query. The same thing happens when the user updates the table:
SQL> update bank.customers
2 set cust_name = 'PAT TERRY';
1 row updated.
Note that in this example, only one row is updated, even though there are actually two rows in the underlying table. The policy (CUST_IUD_POLICY) appends the predicate where cust_id in (456) to the update statement. Similarly, while the table is being deleted, only the rows for which the user is authorized are deleted.
Attempting to insert a row containing data for which the user is not authorized results in an error message. For example, in this query, LARA is attempting to add a record to the CUSTOMER table for an account not under her purview:SQL> insert into bank.customers
2 values (789,'KIM PARK');
insert into bank.customers
*
ERROR at line 1:
ORA-28115: policy with check option
violation
According to the ACCESS_POLICY table, Scott has SELECT privileges on account 789—no other privileges for any other AM are listed in the table.
Using policies in conjunction with functions ensures authorized access to specific records of a table. The rules are applied regardless of how the table is accessed, whether through an application or directly through an ad hoc query tool, such as SQL*Plus. Users see only rows for which they have been authorized.
Policies can be applied to multiple tables, and a single policy function can be used by any number of policies. Listing 6 shows a policy on an ACCOUNTS table that uses the get_sel_cust_id function initially created for use with the CUSTOMERS table.
---------------------------Arup Nanda (arup@proligence.com) is the chief database architect at Proligence Solutions (www.proligence.com), a Norwalk, Connecticut-based company providing specialized Oracle security and disaster recovery solutions. He is the coauthor of the book Oracle Privacy Security Auditing, (Rampant TechPress, 2003).
March/April 2004
