Pages

Wednesday, July 28, 2010

Validation Messages

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.

Monday, July 26, 2010

ODTUG: Enhanced APEX Security

At ODTUG Kaleidoscope I promised that I would post my slides from my presentation. Here is a copy of the Enhanced APEX Security presentation.

The two demo's can be found here:
http://www.talkapex.com/2009/05/enhancing-apex-security.html
http://www.talkapex.com/2010/07/poor-mans-vpd-in-apex-and-oracle-xe.html

I hope to post the slides from the other presentation, How to be Creative, sometime next week.

Poor Man's VPD in APEX and Oracle XE

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:

CREATE OR REPLACE VIEW vemp AS
SELECT * FROM emp
WHERE deptno = some_value_from_a_context


The Code

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 point
CREATE OR REPLACE CONTEXT ctx_vpd USING pkg_vpd ACCESSED GLOBALLY;

Create pkg_vpd spec

-- VPD Demo Package from www.talkapex.com
-- @author Martin Giffy D'Souza - martin@talkapex.com
CREATE OR REPLACE PACKAGE pkg_vpd
AS
--
-- Login function for APEX to login users
-- Spec must be as is, as required by APEX for custom authentication schemes
-- For demo purposes we'll use ENAME = ENAME from EMP
-- @param p_username username
-- @param p_password password
-- @return TRUE or FALSE
--
FUNCTION f_login (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN;

--
-- 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 value
-- i.e. Sets a value for the name/value pair
--
PROCEDURE sp_set_context_value (p_name IN VARCHAR2, p_value IN VARCHAR2);

--
-- Get Context Value
-- i.e. Get value for name/value pair
--
FUNCTION f_get_context_value (p_name IN VARCHAR2)
RETURN VARCHAR2;

--
-- To be run in Post Authentication Process in APEX
-- Sets some of the name/value pairs required for VPD
--
PROCEDURE sp_apex_post_auth;

END pkg_vpd;
/

Create pkg_vpd body

-- VPD Demo Package from www.talkapex.com
-- @author Martin Giffy D'Souza - martin@talkapex.com

CREATE OR REPLACE PACKAGE BODY pkg_vpd
AS
--
-- Login function for APEX to login users
-- Spec must be as is, as required by APEX for custom authentication schemes
-- For demo purposes we'll use ENAME = ENAME from EMP
-- @param p_username username
-- @param p_password password
-- @return TRUE or FALSE
--
FUNCTION f_login (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN
AS
v_count PLS_INTEGER;
BEGIN
SELECT COUNT (e.ename)
INTO v_count
FROM emp e -- Notice how I'm not reference the view (vemp) it would return no rows at this point
WHERE LOWER (e.ename) = LOWER (p_username)
AND LOWER (p_username) = LOWER (p_password);

IF v_count = 1 THEN
RETURN TRUE;
END IF;

RETURN FALSE;
END f_login;

--
-- 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;

--
-- Set context value
-- i.e. Sets a value for the name/value pair
--
PROCEDURE sp_set_context_value (p_name IN VARCHAR2, p_value IN VARCHAR2)
AS
BEGIN
dbms_session.set_context ('CTX_VPD',
p_name,
p_value,
USER,
SYS_CONTEXT ('USERENV', 'CLIENT_IDENTIFIER'));
END;

--
-- Get Context Value
-- i.e. Get value for name/value pair
--
FUNCTION f_get_context_value (p_name IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN SYS_CONTEXT ('CTX_VPD', p_name);
END f_get_context_value;

--
-- To be run in Post Authentication Process in APEX
-- Sets some of the name/value pairs required for VPD
--
PROCEDURE sp_apex_post_auth
AS
v_empno emp.empno%TYPE;
v_deptno emp.deptno%TYPE;
BEGIN
-- Get User Information
SELECT empno, deptno
INTO v_empno, v_deptno
FROM emp
WHERE LOWER (ename) = LOWER (v ('app_user'));

-- Set Context Identifier
pkg_vpd.sp_set_context_identifier (p_session_key => v ('app_user') || ':' || v ('app_session'));
-- Set Name/Value pairs
pkg_vpd.sp_set_context_value (p_name => 'EMPNO', p_value => v_empno);
pkg_vpd.sp_set_context_value (p_name => 'DEPTNO', p_value => v_deptno);
-- This demo won't highlight the Last Access date, but can be very useful to kill sessions in the back end that have not been access for a given period of time.
pkg_vpd.sp_set_context_value (p_name => 'LAST_ACCESS', p_value => TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
END sp_apex_post_auth;
END pkg_vpd;
/

Create view which leverages the context

CREATE OR REPLACE FORCE VIEW vemp
AS
SELECT e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno
FROM emp e
WHERE e.deptno = pkg_vpd.f_get_context_value ('DEPTNO'); -- Can only view employees in same department
/
Create Custom Authentication Scheme

Shared Components / Authentication Schemes
Create
From Scratch
Name: VPD Demo
Page Sentry Function:
Session Verification Function:
Invalid Session Target: Page in This Application - 101 Login
Pre-Authentication Process:
Credentials Verification Method: Use my custom function to authenticate: return pkg_vpd.f_login
Post-Authentication Process: pkg_vpd.sp_apex_post_auth;
Cookies:
Logout URL: wwv_flow_custom_auth_std.logout?p_this_flow=&APP_ID.&p_next_flow_page_sess=&APP_ID.:1

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.

Friday, July 16, 2010

APEX Plugin: Syntax Highlighter

I finally got around to developing my first APEX Plugin!

To summarize this plugin is a code syntax highlighter based on http://alexgorbatchev.com/SyntaxHighlighter/.
. This is the same syntax highlighter for code samples on this site. You can download it here: http://www.apex-plugin.com/oracle-apex-plugins/item-plugin/syntax-highlighter.html.


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

How do they do it?

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

Missing Views from the APEX_DICTIONARY

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


SELECT SYN.SYNONYM_NAME apex_view_name
FROM all_synonyms syn, all_views av, apex_dictionary ad
WHERE syn.synonym_name LIKE 'APEX\_%' ESCAPE '\'
AND syn.owner = 'PUBLIC'
AND SYN.TABLE_NAME = AV.VIEW_NAME
AND SYN.SYNONYM_NAME = AD.APEX_VIEW_NAME(+)
AND ad.column_id IS NULL
ORDER BY syn.synonym_name

Tuesday, July 6, 2010

APEX Report Download Logger


This solution was demoed at the ODTUG APEX Open Mic night

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:

SELECT *
FROM apex_workspace_activity_log
WHERE application_id = :app_id
AND page_id = :app_page_id

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.

I still needed a way to track report downloads. Here's how I did it. A working demo can be found here: http://apex.oracle.com/pls/apex/f?p=20195:3120

- Create Download Log table:

CREATE TABLE TAPEX_WORKSPLACE_REPORT_DL_LOG
(
WORKSPACE VARCHAR2 (255 BYTE) NOT NULL,
WORKSPACE_ID NUMBER NOT NULL,
APPLICATION_ID NUMBER NOT NULL,
APPLICATION_NAME VARCHAR2 (255 BYTE) NOT NULL,
APPLICATION_SCHEMA_OWNER VARCHAR2 (30 BYTE) NOT NULL,
PAGE_ID NUMBER NOT NULL,
PAGE_NAME VARCHAR2 (255 BYTE) NOT NULL,
REGION_ID NUMBER NOT NULL,
REGION_NAME VARCHAR2 (255 BYTE) NOT NULL,
VIEW_DATE DATE NOT NULL,
APEX_USER VARCHAR2 (255 BYTE) NOT NULL,
APEX_SESSION_ID NUMBER NOT NULL
);

- Create procedure to store APEX download record

/**
* Insert Download Report Record
* @param p_region_id Report Region ID
*/

CREATE OR REPLACE PROCEDURE sp_log_apex_dl_report (
p_region_id IN apex_application_page_regions.region_id%TYPE)
AS
BEGIN
-- Insert into report download log table
INSERT INTO tapex_worksplace_report_dl_log (WORKSPACE,
WORKSPACE_ID,
APPLICATION_ID,
APPLICATION_NAME,
APPLICATION_SCHEMA_OWNER,
PAGE_ID,
PAGE_NAME,
REGION_ID,
REGION_NAME,
VIEW_DATE,
APEX_USER,
APEX_SESSION_ID)
SELECT aapr.workspace,
AA.WORKSPACE_ID,
aapr.application_id,
aapr.application_name,
aa.owner,
aapr.page_id,
AAPr.PAGE_NAME,
aapr.region_id,
aapr.region_name,
SYSDATE,
v ('APP_USER'),
v ('APP_SESSION')
FROM APEX_APPLICATION_PAGE_regionS aapr, APEX_APPLICATIONS aa
WHERE aapr.application_id = v ('APP_ID')
AND AAPr.PAGE_ID = v ('APP_PAGE_ID')
AND aapr.region_id = p_region_id
AND aapr.application_id = aa.application_id;
END sp_log_apex_dl_report;

- Trigger the above procedure every time a report is downloaded:

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

SELECT AR.APPLICATION_ID,
ar.application_name,
ar.page_id,
ar.page_name,
ar.region_id,
ar.region_name,
tdl.downloads,
al.page_views
FROM APEX_APPLICATION_PAGE_REGIONS ar,
(SELECT tdl.application_id,
tdl.page_id,
tdl.region_id,
COUNT (tdl.application_id) downloads
FROM TAPEX_WORKSPLACE_REPORT_DL_LOG tdl
GROUP BY tdl.application_id, tdl.page_id, tdl.region_id) tdl,
(SELECT al.application_id, al.page_id, COUNT (al.application_id) page_views
FROM apex_workspace_activity_log al
GROUP BY al.application_id, al.page_id) al
WHERE AR.APPLICATION_ID = :app_id
AND ar.source_type_code IN ('SQL_QUERY', 'DYNAMIC_QUERY')
-- Downloads
AND ar.region_id = tdl.region_id(+)
-- Page Views
AND ar.application_id = al.application_id(+)
AND ar.page_id = al.page_id(+)