While APEX offers a number of controls to limit user access to its components, it offers few components to protect the data itself. That may seem like a shortcoming on the surface, but it is not. Protecting data at the application layer can be potentially short-sighted because it protects only that single avenue to the data. If developers spent all of their time building in controls to their APEX application to restrict what users could see, then users could circumvent those controls by accessing the data via another application or directly.

Thus, data is best secured at the database layer, not within the application. Oracle provides a couple of features to assist with this task: Oracle Virtual Private Database and Oracle Label Security. Both of these tools require the Enterprise Edition of the Oracle Database, and Oracle Label Security is a for-cost option. Fortunately, if you’re running Oracle Standard Edition One or Standard Edition, you can still secure your data at the database layer using nothing more than a smartly designed view.

This chapter will outline the benefits of using such views as a layer of security in your APEX and other Oracle applications. It will illustrate some techniques for where and how to use secured views and their associated components. It concludes with a summary of the benefits and potential drawbacks to using this approach.

The View

In general, most Oracle developers are quite familiar with views. Views are very powerful and are often an underappreciated and underused construct in any database. They allow developers to mask the complexity of a sophisticated SQL statement in a much easier-to-use format. Additionally, they can obfuscate columns or data that should not be seen by specific applications. If the definition of the view needs to change, it can do so without any application code changes in many cases, thus making applications easier to manage over the course of their lifetime.

All of the traditional benefits of views are good for nothing if they don’t get used in APEX applications. And chances are, most APEX applications are built to interact directly with tables and make little, if any, use of views at all.

The use of views in your application should be something that is considered during the design phase. Designing an application based on views from the ground up is a lot faster than trying to retrofit an existing application with views. Like most facets of application development, a little bit of planning will go a long way.

Using a view in APEX is quite simple. Simply create the view in the same schema that the APEX application parses as and then reference that view in a report, form, chart, and so on, just as if it was a table. That’s all there is to it! Take, for example, the view in Listing 11-1.

Listing 11-1.  SQL to Create a simple Simple view View on the EMP tableTable

CREATE OR REPLACE VIEW emp_v
AS
SELECT
  empno,
  ename,
  job,
  deptno
FROM
  emp
/

This simple view will return the EMPNO, ENAME, JOB, and DEPTNO columns from all the rows from the EMP table. To use this view when creating a component in APEX, simply select from EMP_V instead of EMP, and your component will be based on the rows and columns referenced in the view. If business conditions dictated a change in how this view were constructed, the definition of the view could be rewritten, and little to no code changes in the application would be required.

If a simple APEX application were created using the EMP_V view, the results would look similar to Figure 11-1.

Secure View Components

A secure view is not an actual database object or APEX component type but rather a term used to describe the techniques of using a view to provide data security for an application. Secure views provide both the traditional benefits of views combined with an added layer of both data security and functionality that can be used in any application – APEX or otherwise.

In the following example, you will create a secure view based on the EMP table that restricts users from seeing records from other departments. When you build the APEX report on top of this view, you will pay little to no attention to data security there because it will all be addressed via the secure view. Not only does this decrease the time it takes to develop applications, but it also makes them a lot easier to manage over time because of their minimalistic design and sparse use of sophisticated SQL queries.

Because secure views are not an actual object type, there are a few components that need to be in place before they will actually work. Secure views rely on application contexts to act as the filter in their predicate. Application contexts are the most efficient and effective way to create a secure view, because their values are stored in memory and take almost no measurable time to reference.

The values of application contexts can be set only by a specific PL/SQL procedure, so you will have to create one of them. And since application contexts are based on database sessions, not APEX sessions, you need to augment your APEX application with some PL/SQL code to call both before and after a page is rendered.

CREATE CONTEXT emp_ctx USING SCOTT.emp_ctx_pkg;

CREATE OR REPLACE PACKAGE emp_ctx_pkg
AS
PROCEDURE set_ctx
  (
  p_user_name                IN VARCHAR2
  );
PROCEDURE unset_ctx;
END emp_ctx_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_ctx_pkg
AS
PROCEDURE set_ctx
  (
  p_user_name                IN VARCHAR2
  )
IS
  l_deptno                   NUMBER;
BEGIN

-- Fetch the DEPTNO based on the currently signed on APP_USER
SELECT deptno INTO l_deptno FROM emp
  WHERE ename = p_user_name;

-- Set the Context
dbms_session.set_context(
  namespace => 'EMP_CTX',
  attribute => 'DEPTNO',
  value     => l_deptno);

EXCEPTION
WHEN no_data_found THEN
  -- If no data is found, then clear the context
  dbms_session.clear_context('EMP_CTX'),
END set_ctx;

PROCEDURE unset_ctx
IS
BEGIN
-- Clear the context
dbms_session.clear_context('EMP_CTX'),
END unset_ctx;

END emp_ctx_pkg;
/

CREATE OR REPLACE VIEW emp_v AS
SELECT
  empno,
  ename,
  job,
  deptno
FROM
  emp
WHERE
  deptno = SYS_CONTEXT('EMP_CTX', 'DEPTNO')
/

emp_ctx_pkg.set_ctx(p_username => :APP_USER);

emp_ctx_pkg.unset_ctx;

Benefits and Drawbacks

One of the biggest benefits of using secure views in an APEX environment is that they will neutralize most types of URL tampering attacks. Since the view itself is filtering the data at the data layer, it matters little if a user passes a different ID via the URL to an APEX page. In fact, if a malicious user does pass an ID that he does not have access to see, a “no data found” message will be displayed, which may lead users to believe that the record they are trying to access simply does not exists.

It is important to keep in mind that any Automated Row Fetch and Get Next/Previous Primary Key processes also reference the secure views, not the base tables. Failure to reference the secure view will allow a user to see records that they are not supposed to see by manipulating the URL or simply clicking the Next/Previous Record buttons, if they exist on a form.

Another benefit of using secure views is that the total time to develop an application will decrease. If all the data security is taken care of at the data level, then developers don’t need to be concerned with ensuring that all queries are secured with the proper predicates. This will also simplify the SQL used in reports, thus making it easier to understand and maintain over the life cycle of the application.

Centralizing the data security also makes it a lot easier to change the business rules, should the need arise. Making a change to the application context procedure will impact any view that references that application context instantly. If the rule for determining which department a user is associated with changes, a single change to the procedure would instantly change how the entire application worked without making a single change to the application itself. This obviously saves a great deal of time and money and makes the application much more flexible.

Lastly, secure views also carry with them all of the benefits of standard views. Complex joins between multiple tables can easily be masked by a view. Columns that are not necessary for an application can be omitted, providing the developer with just those columns that are needed for a specific application. And if core business rules change, the definition of the view itself can be changed without impacting the application in many cases.

Nothing comes without a cost, and secure views are no exception. While they are quite robust and can be configured in a number of ways, they do have their shortcomings and potential drawbacks.

Secure views protect data only from a SELECT. They do nothing when it comes to INSERTs, UPDATEs, and/or DELETEs. In some scenarios, this drawback does not make much of a difference because the requirement is to simply filter data in reports and forms. However, if additional controls are required to also prevent users from updating or deleting specific records, then secure views may not offer enough functionality, and you should consider Virtual Private Database, which provides protection for all DML transactions.

If a secure view is created with the READ ONLY option enabled, either a separate set of PL/SQL APIs or INSTEAD OF triggers will have to be developed to facilitate any DML transaction. While there is no additional risk with using either of these approaches, it will take extra time to design and develop the extra code required versus using the APEX built-in DML processes.

Summary

Using secure views, you can easily and affordably secure the data in your APEX application, no matter which version of the Oracle Database you are running on. The techniques described in this chapter use both core APEX and database functionality and can be set up and configured quickly and easily. In addition to the benefits of the secure views, using views in general will result in a more manageable and easier-to-understand application.

Although secure views provide an adequate layer of security in many cases, they may not provide enough. Carefully consider your security requirements before starting any development to ensure that your application won’t soon outgrow what secure views can provide.