Pages

Wednesday, July 28, 2010

When you have an apostrophe (') in your last name you soon realize that a lot of sites don't handle them properly when entering your information. Normally I get a simple error or my last name will be converted to "D" or "Souza". Today I tried to register for a demo for a fairly popular application and received the following error message:

The error message says "Last Name contains errors". Someone clearly didn't do their job when creating this registration form. Besides the fact that I couldn't register with my last name, stating that my last name "contains errors" doesn't really make sense.

I know this post isn't specific to APEX but this is just a reminder when defining validations to use meaningful error messages.

At this year's ODTUG Kaleidoscope I gave a presentation called "Enhancing APEX Security". A copy of the presentation can be downloaded here.

As part of the presentation I discussed how to create a "Poor Man's" VPD using Oracle XE. The main concept was to simulate basic VPD on a non Enterprise Edition (EE) (VPD is only available on EE). This post will cover how to do this. Please note, since this is for demonstration purposes I have kept things very simple and it is by no means a complete solution.

Before you can review the code, we need to discuss some of the basic architecture and technology that will be used. I strongly encourage you to do some additional research on these topics if you plan to use this method in production.Schema Setup

Assuming you don't have Oracle EE, you'll need a way to secure your existing schema. Lets say you had a schema called "DEMO". You'll need to create a new schema called "DEMO_PUB". The DEMO_PUB schema will not contain any objects. Instead, it will have synonyms which point to views and packages in the DEMO schema. Note, the DEMO_PUB schema will not have any access to the DEMO tables. All DML statements will be made via packages and procedures. The views from the DEMO schema will be "secure views" which will restrict access to the data. On the flip-side the DEMO schema will only grant SELECT and EXECUTE to views and packages respectively, to the DEMO_PUB schema.

You APEX applications should use the DEMO_PUB so that security logic is stored in the database rather than the front-end. This should help prevent developers from displaying data that end users don't have access to.

The following diagram, taken from my presentation, highlights the overall schema structure.

Contexts

For people unfamiliar with Contexts, the easiest way to describe them is a globally accessible container of name/value pairs. The container is only accessible if you have the correct key. Oracle has some great reference material on this (search for VPD or FGAC) so I won't cover this any further Here's a diagram to illustrate Contexts.

The key to "Poor Man's" VPD is to leverage context values in your views to restrict the data. To demonstrate this in pseudo code, if you wanted to restrict access on the EMP table to only employees in your department you'd write a view like this:

Hopefully you understood the small bit of background information I wrote before. Here's how to implement a very simple "VPD" enabled application

-- Create Context-- ctx_vpd is our context name-- pkg_vpd is the only place where we can modify values in this context-- This provides a lot of security since single access pointCREATE OR REPLACE CONTEXT ctx_vpd USING pkg_vpd ACCESSED GLOBALLY;

-- -- Set context identifier -- This will register our "key" which will be required each time we want to access a Context (name/value) pair -- @param p_session_key in APEX use :APP_USER || ':' || :APP_SESSION -- PROCEDURE sp_set_context_identifier (p_session_key IN VARCHAR2);

-- -- Set context identifier -- This will register our "key" which will be required each time we want to access a Context (name/value) pair -- @param p_session_key in APEX use :APP_USER || ':' || :APP_SESSION -- PROCEDURE sp_set_context_identifier (p_session_key IN VARCHAR2) AS BEGIN dbms_session.set_identifier (client_id => p_session_key); END sp_set_context_identifier;

Change current Authentication Scheme to "VPD Demo". This will vary between APEX 3.x and APEX 4.0

Create a Region Report with:

SELECT *FROM vemp

Now login to your application with "KING/KING". Notice how you only see 3 rows in the report? Now logout and login with "MARTIN/MARTIN". You should now see 6 rows returned. As you can see, none of security was handled in the front end.

Since this was my first APEX plugin that I created I thought I'd give some pointers to help others writing their first plugin:

- Decide on a feature you want to add. This can be harder said than done. If you're new to JavaScript etc, then you may want to create an example in a html file so that you can reference it later on.

- Keep it simple. Chose a simple feature to add. If you decide to add something very complex you may just get lost.

- Put your PL/SQL code in a package/function so that you can develop using a 3rd party tool such as Toad or SQL Developer. This will allow you to quickly debug your code. Once it's working you can extract it and put it inline with your plugin

- Look at other examples. View other plugins and see how they were built etc.

Wednesday, July 14, 2010

Have you ever seen something in the APEX Developer application and wonder how the APEX team built it? Since APEX is built in APEX you can actually see the code for the development environment. The way to do this is to install APEX in APEX. When you download APEX you also get all the development/builder application files:

Simply install each of the applications into a workspace:

Note: Since some of the builder files are extremely large it does take a while to install and you may get a timeout error.

Tuesday, July 13, 2010

A long time ago I wrote about how you can list all the apex views by querying APEX_DICTIONARY. They're some views that are still public but are not listed in the Apex Dictionary. The following query lists these views

Tuesday, July 6, 2010

I developed a reporting application in APEX. The goal of the application is to help turn "data into information". Users should be able to make business decisions based on the reports and charts rather than exporting the report data and using spreadsheets to analyze it.

I've been a bit concerned that the application isn't achieving it's initial goal and that the reports are just used as data dumps. To validate this hypothesis I needed a way to track how many times a report was downloaded and compare it to the number of times it was viewed.

I can find out how many times a report/page was displayed by looking at the APEX logs:

Note: this approach is not 100% accurate since it only logs the page and not the individual regions, however for this purpose it should do. I have tinkered with "Region Logging" and may write about it soon.

Since Standard Reports (STD) and Interactive Reports (IR) download functionalities are handled differently I can't use an Applicatiobn Process to trigger the logging function. Instead, I'm going to leverage the VPD section in APEX. For those of you new to APEX, the Virtual Private Database (VPD) section in APEX is a section in APEX where you can put a block of PL/SQL code. This code gets run right at the beginning of the page, after the APP_USER is defined. The label of VPD is a bit misleading since the code doesn't have to do any VPD tasks. Initially I had planned to handle IRs and STD Reports using a different method but thanks to the guys at Purdue Pharma for reminding me that I can leverage the VPD section in APEX.

- Shared Components - Security Attributes - Virtual Private Database

DECLARE v_region_id APEX_APPLICATION_PAGE_REGIONS.REGION_ID%TYPE;BEGIN -- If CSV/HTMLD is for IR (does not factor in email IRs). FLOW_EXCEL_OUTPUT is for Standard Report IF :request IN ('CSV', 'HTMLD') OR :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN -- Check for Standard Report IF :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN v_region_id := REGEXP_SUBSTR (:request, '[[:digit:]]+'); ELSE -- Interactive Report SELECT region_id INTO v_region_id FROM APEX_APPLICATION_PAGE_ir WHERE application_id = :app_id AND page_id = :app_page_id; END IF;

sp_log_apex_dl_report (p_region_id => v_region_id); END IF;END;

- Comparison report:

Here's the query that I used to compare the downloads to the report views