Syllabus
Schedule
Reading
Discussion
Project
Test
Resource
Tip

Virtual Private Database

I am providing the following reference in order for students to gain enough understanding to implement Project 8. Please read carefully and follow the code example so that we can gain the detailed insight. In this way, you will be able to troubleshoot for some problems you encounter during implementation. Good luck and have fun.

You will see Fine Grained Access Control referred to with various names in different publications. The following are synonymous terms for this feature:

  • Fine Grained Access Control (technical name)
  • Virtual Private Database (marketing name)
  • Row Level Security (technical name based on the PL/SQL packages that implement this feature)

Fine Grained Access Control and Application Contexts

In a nutshell, the Fine Grained Access Control in Oracle is the ability for you to dynamically attach, at runtime, a predicate (where clause) to any and all queries issued against a database table or view. You now have the ability to procedurally modify the query at runtime. You may evaluate who is running the query, where they are running the query from, when they are running the query and develop a predicate given those circumstances. With the use of Application Contexts, your may securely add additional information to the environment (such as an application role the user may have) and access this in your procedure or predicate as well.

As an example of Fine Grained Access Control, you might have a security policy that determines what rows different groups of people may see. Your security policy will develop a predicate based on who is logged in and what group they are in. Fine Grained Access Control allows the query "select * from emp" when submitted by different users to be evaluated as:

User Logged In

Query dynamically rewritten to

Notes

Employee

select * from emp (where ename = USER )

Employees may see their records only

Manager

select * from emp (where mgr = ( select empno from emp where ename = USER ) or ename = USER )

Managers may see their record and the records of people that work for them.

HrRep

select * from (emp where deptno = SYS_CONTEXT( 'OurApp', 'Deptno' ) )

HrRep may see anyone in a given department. This introduces the syntax for retrieving variables from an application context, the SYS_CONTEXT() builtin function.

The content in the parenthesis "()" is the predicate that you want to apply automatically by your system. This will be implemented through a policy function.

Why use this feature?

There are many reasons to use this feature. Some of the most prevalent ones are:

  • Ease of Maintenance
    • Fine Grained Access Control allows you to have 1 table and 1 stored procedure to manage what used to take many views. This multiple view approach typically leads to a proliferation of database objects as you end up creating a separate view for each group of people. For example, in the above example with employees, managers and HR representatives, a typical system would have had 3 database views created. If you need to add another group of people, you would have to add another set of views that you manage and maintain. If you change the security policy (e.g. you want managers to see not only their direct reports but also 2 levels down), you would have to recreate the view in the database invalidating all objects that refer to it.
  • It's done in the server
    • Many times, given the complexity of managing and maintaining so many views, developers will encode the application logic into the application itself. The application will look at who is logged in and what they are requesting and submit the appropriate query. This protects the data but only when the data is accessed via the application. This hampers your ability to use ad-hoc query tools, report generation tools, and the like on your data. This increases the probability that the data will be compromised at some point since all one needs to do is log into the database with some other tool, other then your application, and query the data. By placing the security logic, the logic that determines what data the user should see, in the database--you insure the data is protected, regardless of the tool used to access it and you make it so any tool can in fact access the data.
  • It avoids the approach of using common users to log in.
    Using Fine Grained Access Control, each user should log in as themselves. This supplies complete accountability you can audit actions at the user level. In the past, many applications when faced with having different views of the data for different users, make the choice to have a database user per set of data. For example, using the employee/manager/HR rep example from above, an application would set up 3 accounts. Every employee would use the employee account. Every manager would use the manager account. Every HR Rep would use the HR Rep account. This removes the ability to audit actions at the user level.
  • It allows for easier application development.
    • Fine Grained Access Control takes the security logic out of the application logic. The application developer can concentrate on the application itself, not the logic of accessing the underlying data to keep it secure. Since Fine Grained Access Control is done entirely in the server, the applications immediately inherit this logic. In the past, the application developers had to encode the logic into the application, making the applications harder to develop initially and making them especially hard to maintain. If the application is responsible for mediating access to the data and you access the same data from many locations in the application, a simple change to your security policy may affect dozens of application modules. Using Fine Grained Access Control, a change in a security policy affects no application modules.
  • It allows for evolutionary application development.
    • In many environments, security policies are not well defined initially and change over time. As companies merge, or as health care providers tighten access to patient databases, or as privacy laws are introduced security policies will necessarily change. By placing the access control as close to the data as possible, you allow for this evolution with minimal impact on applications and tools. There is one place to go to implement the new logic and all applications and tools that access the database automatically inherit the new logic.

How to use this feature

Please refer your textbook for more detailed description. The following is an example that you could follow in order to work at Project 8 Virtual Private Database (VPD) by Context . The code will be presented in the format and syntax that work for a typical SQL script. Your textbook used more interactive SQL command, which is not necessarily the most efficient practice.

 

Assume your user name and password are RLS/rls, and you have CREATE TABLE privilege. If you want to test the following codes, please create a user RLS.

CONNECT rls/rls;

Please note that a different font was used for the actual code. This will help you write your own SQL script file. Now, you are creating a table my_table as follows:

CREATE TABLE my_table
(SAMPLE_DATA varchar2(30),
OWNER varchar2(30) default USER);

You will grant all privileges on my_table to public:

GRANT all on my_table to public;

You may insert some testing data into the my_table:

INSERT into my_table ( SAMPLE_DATA) values ( 'Some Data' );
INSERT into my_table ( SAMPLE_DATA, OWNER )
values ( 'Some Data Owned by SCOTT', 'SCOTT' );

COMMIT;

To see the data in the my_table:

SELECT * from my_table;

SAMPLE_DATA OWNER
Some Data RLS
Some Data Owned by SCOTT SCOTT

If you wanted a security policy that would allow the user to see only rows they 'owned' unless you were the RLS_ADMIN user, you might code:

CREATE OR REPLACE function my_security_function( p_schema in varchar2,
p_object in varchar2 )
return varchar2
as
begin
if ( user = 'RLS_ADMIN' ) then
return '';
else
return 'owner = USER';
end if;
end;
/

The above is a typical PL/SQL code segment. The format should work if you insert it into your SQL script file. Here is the logic for the above policy: If the login user is RLS_ADMIN, the function will return a blank. In other words, there will be no predicate attached to your statement in the future. If the login user is not RLS_ADMIN, the predicate will be "owner = USER," where USER is a special key word from Oracle. You may use Oracle context, "sys_context('userenv','session_user')", to replace USER to achieve the same result.

You would attach the policy "My_Security_Policy" with the following PL/SQL code:

begin
dbms_rls.add_policy
( object_schema => 'RLS',
object_name => 'MY_TABLE',
policy_name => 'MY_POLICY',
function_schema => 'RLS',
policy_function => 'My_Security_Function',
statement_types => 'select, insert, update, delete' ,
update_check => TRUE );
end;
/

If the above code has problems, please pay attention on the spelling and cases within single quote. I have spent several hours on my trial due to the fact I missed one "s" in "statement_types." The policy will apply to SELECT, INSERT, UPDATE and DELETE actions against my_table. For example, if the user is not RLS_ADMIN and the user issued the following SELECT SQL command:

SELECT * FROM my_table;

The database system will convert the above SQL command, by attaching a predicate as follows:

SELECT * FROM my_table
WHEER owner = USER;

For example, if you login as RLS, the "SELECT * FROM my_table;" query will now give you only one row of data, instead of two. The row owned by SCOTT will no longer be accessible by RLS.

Please compare the above code with the ones in the textbook. The above format can be directly used in a SQL script file.

You have used the simplest form of system context to implement VPD, which is similar to the view implemented on your previous project. Once you understand how to implement VPD, there are many more types of access controls. See your textbook for other scenarios.

 

 

Note: The above was based upon an article published at: http://asktom.oracle.com/~tkyte/article2/index.html.