Security, Policies and Tips - Part 10

Dear Readers,

My name is Franz Devantier, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g) Security DBA.  I will be sharing with you the basic duties of an Oracle DBA, and also some of the undocumented, and not so well known tasks. 

Security, Policies and Tips  -  Part 10

Use Application Context and Fine-Grained Access Control

Application Context

Application context helps you apply fine-grained access control because you can link function-based security policies with applications.

Oracle has a predefined application context namespace, USERENV, which provides access to many predefined attributes.  These attributes are automatically captured about a specific user session.  The username, proxy user, ip address, machine name, and many more attributes are available.

In addition an application also has its own application-specific application context, such as department, country, salary scale, and etc.  Such attributes are available to the functions implementing security functions, but not to application users, or users of an ad-hoc query tool, like SQL*Plus.

To define an application context:

·         Create a PL/SQL package with functions that validate and set the context for the application.  You can user trigger event to set the initial context for users when they log into the application.

·         Use the CREATE CONTEXT keywords to specify a unique context name, and associate it with the PL/SQL package that you created

·         You can do perform either of these options

o   Reference the application context from the policy function that implements your fine-grained access control

o   Create an event trigger on logon to set the initial context for a user.  This may involve looking up account details, and setting the values retrieved as context values 

·         Reference the application context, to limit users to seeing their own records only.  You can use fine grained access control to dynamically modify the users query from for example.  “Select * from orders;” to “Select * from orders where custno= SYS_CONTEXT(‘order_entry’,’cust_num’);” 

Fine-grained access control

This enables you to use functions to implement security policies and to associate those security policies with tables, views, or synonyms.

The database server automatically enforces your security policies, no matter how the data is accessed, including, for example, through an application by ad hoc queries.

Fine-grained access control enables you to make use of the following capabilities:

·         Limit access to data at the row level, by using different policies for SELECT, INSERT, UPDATE, and DELETE

·         Use the security policies where you need them, for example on account information

·         You can invoke a policy on a table, only if a specific column is referenced

·         You can restrict access to data, using a combination of row-level and column-level controls, by applying a VPD policy to a view

·         Static policies are always applied, whereas dynamic policies can change during execution

·         You can use multiple policies on one table

·         You can define policies for a specific application, by packaging them together in policy groups.  Each policy group is a set of policies that belongs to one specific application

·         Control the use of “INDEX” in row level security policies

·         Specify a special application context, called a driving context, to indicate the policy group in effect, when tables views or synonyms are accessed.  The fine-grained access control engine looks up the driving context to determine which policy group is in effect and enforces all the associated policies that belong to that specific group.

The PL/SQL package DBMS_RLS is for administering your security policies.  With DBMS_RLS you can add, drop, enable, disable, and refresh all of the policies and policy groups that you create.

Franz Devantier,

Need a database health check, or a security audit?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report:  The report will give you all the information you need to start making a nice long-term residual income stream for yourself.