APEX Authentication with Remember Me Cookie

Purpose

As part of the ODTUG KSCOPE14 conference, I gave a talk: Cookie Monster – Understanding how and when to use browser cookies with APEX. In that talk I described the technical features of cookies and how the APEX framework uses them.

As the talk progressed, I used the example of how Amazon always seems to know who you are, and how APEX will always throw you out if you close your browser. Even though Amazon knows who you are, any time you try to touch something sensitive such as account details or transactions, Amazon will challenge for authentication credentials. I continued by showing a method in which you could use the Amazon style authentication for APEX applications. It requires a little setup, but it is very possible to do within a single application.

The example application included here operates with APEX in the same way. You can authenticate once and choose to save a “Remember Me” cookie. This cookie will allow you to come back to the application and it will remember who was last authenticated (although not the same sesison). Using an additional check, we can determine if the user has actively authenticated for the current session, and if not, require them to re-confirm their credentials.

** UPDATED ** Download Presentation and APEX Application V1.1  (4.2.2 required)

Demonstration Application on APEX.oracle.com

The High Level Idea


There are three authentication states.

  • Authenticated Automatic by Cookie
  • Authenticated This Session
  • Not Authenticated

An active authentication (user enters password) will set an application attribute that can be used to identify the current APEX session as authenticated. If the user on login chooses the “Remember Me” option, a cookie is set on their browser that allows them to automatically Authenticate next time they come to the application. The application attribute is used to differentiate the difference between authentication by cookie and authentication by password.

The next time the user comes to the application and needs to provide authentication, the application will use the “Remember Me” cookie to authenticate the user. APEX will see this as an authenticated user, but the attribute will show it as an “Automatic” authentication.

Automatic Authentications identify the user to APEX, so as far as APEX is concerned, the user has authenticated.

For sensitive pages, we want to re-challenge anyone using the application with automatic credentials. An authorization (Auth-Z) scheme has been configured to enforce this requirement. When a SECURE page is requested, and the user authenticated with the “Automatic” cookie, they will be re-routed to the login screen. This can either be done with a branch, or with an authorization error notification. (Other designs are also possible)

Steps to Reproduce


Public Home Page

The home page on the application (Mobile or Desktop) is public. It will show either additional pages or tabs when a user is authenticated.

Standard Login

This demonstration has been setup with a dummy Authentication (Auth-N).

AC_login

  • Click on either LOGIN or on the “Go To Page..” button.
  • Enter TEST for user name
  • Enter TEST for password
  • Check the REMEMBER ME check box (if not defaulted) to generate a Cookie.
  • Click Login
  • Navigate back to the HOME page, you should see additional tabs or pages (Mobile).

Clear the Session

Starting a new session will effectively log you out of APEX. This is the same effect as closing the browser and coming back to the application. (some browsers will remember session state, so your mileage may vary)

AC_new Session

  • Click the “Start a New Session” button.
  • The tabs or pages should be removed.

Auto Authentication

This will show the cookie authentication working

AC_goto

  • On the home page, click the “Go To Page..”

Normally, this page would default to the Login Page. With the “Remember Me” option, it will automatically authenticate the user and navigate to the destination page.
The footer of the page should identify the user as having used the cookie authentication.

Navigate to a secure page

Navigating to a secure page will identify the session as automatic – and either generate an authorization failure (Auth Z) or generate an automatic branch back to the login screen.

AC_secure_page1

  • Click the Secure – Auth-Z Tab
  • Click Back
  • Click the Secure – Branch Tab
  • Login as before
  • Secure page is shown

Deep Link Feature

A good authentication feature should automatically navigate the user to the page requested. The login page here has a feature defined that will set the post login page when needed.

Logout Feature

The feature built into the logout request (same page as login) will clear the “Remember Me” cookie as well as the current session.

  • Click the Logout Button
  • Click the “Go To Page..” button
  • Notice Auto Authentication did not occur

Mobile

This example can also be run on a mobile device (same application – using auto detection)

AC_mobile_app2

Pseudo code


Standard Login

(Page 101 / 1001 (Mobile) Called directly with no request)

  • Authenticate User (Package Code)
  • If Valid Authentication and Remember Me Requested – Write Cookie

Automatic Login

(Page 101 / 1001 (Mobile) called with request “AUTO”)

  • Cookie is read and validated against table store (Package Code)
  • If cookie is valid…
    • User is set
    • Application Item set to AUTO
    • If Post Login Page is given – redirect
    • If no Post Login Page is given – default apex behavior
  • If cookie is not valid…
    • Default to login page with no request
    • Application Item is set to NULL

Authorization Scheme (Auth-Z)

  • Validate Application Item = PASSED indicating a valid session login
  • Provide link in the message to default login page – No request in the link.

Secure Page Auth-Z

  • Authorization is tested at the page level

Secure Page Branch

  • A “before header” branch is configured to call the LOGIN URL (no request) and set the Post Login page to the current page.
  • This branch uses the Auth-Z as the security scheme.

Logout

  • Page 101 / 1001 (Mobile) called with request “LOGOUT”
  • Package code that clears the cookie and logs out the session using standard apex features

Cookie

  • The cookie is written as a HTTP only cookie
  • The cookie key is in a custom table that was created on install
  • The “user agent” is used with the key as an added level of security
    (this is just an example of how the cookie structure can be modified to your needs)
  • There is an expiration date (30 days) for the cookie both in the browser and the table

Is it Secure?


That depends on who you are and what you are doing.
Here are some considerations:

  • Are you using HTTPS? – This will increase the security of cookies and cookie transmission.
  • Are you displaying sensitive information if they cookie authentication is used?
  • Are you working on an internal corporate network only?
  • Do you review your code for security compliance?

Your Design

There is more than one way to address the protection of sensitive data. This example there is simply one of many suggestions.
Applications which contain sensitive information should always be evaluated extensively to assure security and effective design.

Notations

My history on this topic goes back some time, but never in a way that was presentable to others. There were always too many specifics that made the application only applicable in my situation. This here is an attempt to take what I have done in the past and make it understandable by others, and maybe even useful.

Credit to those who have done similar work is implicit into all of my posts, if not explicit. In this case, Christian Rokitta did an excelent post on APEX and Remember Me options. His approache similar, and an excelent learning platform.

APEX BLOB CLOB Sample Application and Mobile Issue

I have done a presentation on how attachments work in Oracle APEX.  Most recently at the RMOUG 2014 training days conference in Denver.  The presentation walks through several of the options for uploading, storing and presenting files to the user.

The packaged up the sample application and the presentation are now ready to share.  The Desktop application is installable on APEX version 4.1.1 and later.

BLOB CLOB Sample Application 1.1

On the same vein, an attachment related issue was presented to me recently with regards to the APEX Mobile themes.

When using Mobile reports, if you want to show the contents of the attachment, it may be a challenge.  There is an issue with attachments links generated by APEX that prevent them from being displayed when using the jQuery Mobile theme.  The issue lies with the AJAX loading of the page before it is displayed.  This will generate an page with the error message “undefined”.

BLOB CLOB - Mobile - Not working
Shows the error seen when using the download link for attachments.

This link was generated using a standard report BLOB column format.

Standard Report - Link definition.
Standard Report – Link definition.

There is a fix for this issue.  The link needs to be generated with an attribute of either:

rel="external"

or

data-ajax="false"

It is possible to generate the links another way, or to write your own download routine, but I think this is a good opportunity for jQuery to save us some time.

On my report page, I have included a small snippet of jQuery code to locate report links and add the rel=”external” attribute to the anchor tags inside reports.

<script type="text/javascript">
  $(document).bind('pageinit',function() {
    $("[id^=report] .list-view-cell .ui-link").attr( "rel", "external" );
    //alert(" I am done");
  }
);
</script>

Note: Template changes my require adjustment to this example. Review the classes assigned to rows in the report on the theme that you are using.

Reviewing the resulting link for the download should snow the attribute as part of the anchor tag.  This should result in a successful attachment retrieval process on using the mobile theme.

Shows a working example of the Mobile Theme attachment.
Shows a working example of the Mobile Theme attachment.

 

Note: These examples were developed with APEX 4.2.5.  Future versions may vary.

Scripts for Setting Oracle ACL

Understanding and configuring the ACL (Access Control List) in an Oracle database can be a challenge. It has functionality to control network access from the database which is important to understand to prevent abuse and security leaks, but can be a little un-clear.  Because of this, people often set their ACL to be too “loose” so that they don’t have to mess with it again.

If you have ever run across the ORA-24247 error, you can understand why.

ORA-24247: network access denied by access control list (ACL)

While security is good, too much security prevents functionality. Oracle APEX requires access in order to communicate with e-Mail services, LDAP authentication, BI Publisher servers, and any other service type architecture.  It is better to understand your needs and set the security accordingly.

I have constructed a series of scripts to make seeing and changing ACL settings easier.  Each script has a purpose and is designed to accept user inputs.  Built in checks try to reduce input errors.

To run the scripts and make changes to the ACL setup of your database,  you will need to be logged into your database with a user that has the appropriate credentials (SYSDBA and the like).

 SQL> @acl_menu
================================================================================
============= Display Only =====================================================
== @acl_menu     = This file
== @show_hosts   = Server and port range assignemtns in ACL files
== @show_users   = User assignemtns to ACL files
== @show_valid   = Test for invalid ACL files
== @show_descrip = Show developer descriptions of ACL files
== @show_all     = Show above information about all the ACL files
== @show_test    = Quick port 80 test to google
================================================================================
============== Make Changes ====================================================
== @create_new_acl = Create an ACL file
== @drop_acl       = Remove an ACL file
== @add_user       = Prompt for User to assign to an ACL
== @remove_user    = Prompt for User to remove from an ACL
== @add_host       = Add a host definition to an ACL file
== @remove_host    = Remove a host definition from an ACL file
================================================================================

Download ACL Management Scripts

Let me know if you find these scripts useful, or if you have suggestions for improving them.

Additional References

APEX 4.2 ACL Post Installation Steps
Joel Kallman Application Express Network ACL
11.2 Managing Fine-Grained Access in PL/SQL Packages and Types

Sublime Text for Oracle Apex Developers

The following is information related to using Sublime Text for developing Oracle APEX Applications.  Well, actually it is more for any PL/SQL development, but it just sounds cooler.

I use Sublime Text every day and have found it to be a great productivity tool.  As it is cross platform, I use it on MAC, Windows, and Linux operating system (often simultaneously). The pictures may have steps or details specific to one OS or the other. Regardless, the concepts apply to Linux, Mac or Windows. As with many things, there is more than one way to accomplish a goal. This is especially true with Sublime Text. My intention not to re-do Sublime documentation or duplicate what others have already done, but to show you what has worked for me.

Initial Setup

Sublime Text is a commercial software that has a fully functional trial. To the best of my knowledge the trial does not expire, but I like the idea of being able to exercise the software fully before purchasing. (I am a licensed user to support their good work)

Sublime Text

Install Package Control

One of the exciting parts of Sublime Text is not just the multi cursors, but the ability for people to Extend the platform. The folks at Wbond have a package manager that makes extensions very simple.

Package Control Installation

package_install

After the Package Control is installed, open the use Package Control Command List and install the following packages:

  • Abacus
  • Oracle PL SQL

These two extension packages are what I like to use most to complement the built in functionality of Sublime Text to develop Oracle packages.

Tutorials and References

Some of my favorite tutorial and document sites:
Video Tuts
Theme Editor (Chrome Only)
Tip Sheet
Docs and Keybard Shortcuts

Abacus

Abacus is useful for aligning multiple values that follow a similar syntax such as a := ‘value’;
The issue for oracle developers is that the := and the => are not supported by default. This is easily fixed.

Navigate to Preferences -> Package Settings -> Abacus -> Settings – Default
As a good practice, don’t change the default settings file.  Instead, do a “save as” into the “User Directory” with the same file name.

abacus_settings_1

abacus_save_as

Add additional “token” values to the USER settings for Abacus.  Here are the ones that I use.

        {
            "token":                ":=",
            "gravity":              "right",
            "preserve_indentation": true
        },
        {
            "token":                "=>",
            "gravity":              "right",
            "preserve_indentation": true
        },
        {
            "token":                " OUT",
            "gravity":              "right",
            "preserve_indentation": true
        },
        {
            "token":                " IN",
            "gravity":              "right",
            "preserve_indentation": true
        },
        {
            "token":                "||",
            "gravity":              "right",
            "preserve_indentation": true
        },
        // end my additions

Now when using Abacus, it will align the :=, =>, OUT, IN and || tokens in a line.  To use Abacus, you select the lines you would like to align using your mouse or keyboard, then select abacus from the Selection menu or use the shortcut key assigned.

abacus_run

Align_1
Before Abacus Alignment
After Alignment
After Alignment
Before Alignment
Before Alignment
After Alignment
After Alignment

Reference
Abacus Package Manager
Abacus Git Hub

Oracle PL SQL Package

The Oracle PL SQL extension provides the addition of PL/SQL syntax highlighting in Sublime Text beyond the standard included SQL highlighter.
This allows for easier to read code and syntax identification.

For our projects, we use the extensions of .pks and .pkb to identify PL/SQL code. It is easy enough to default the Syntax for the file type using built in Sublime Text features.
Use the menu View -> Syntax -> Open All with Current Extension As… to select the default when you have one of the files open.

Default Extension
Default Extension

Reference
Oracle PL SQL Package at Wbond
Oracle PL SQL Package at Git Hub

Building Code

I subscribe to the adage that Application Express code should be in packages and complex queries exposed via Views. When using Sublime to write that code, I wanted an easy way to compile that code into the database. I researched available packages and did find one, but it was more complex than I desired. (Probably because I don’t know Python that well) I decided to write my own build process that was easier for me to understand and debug. The basis of my Oracle Compilation approach is the same as an installation script. The code I write these days need to be installed via command prompt SQL*PLUS scripting, so this lines up with my goals nicely.

The build process consists of two parts
1 – Sublime Text build option that calls a script command to do the build
2 – A wrapper script that does any work to connect to the database with SQL*Plus and run the desired file

It is necessary to customize as OS environments will handle terminal prompts differently. This is a known behavior. (Windows is different than Mac… so it is said)

SQL*Plus Script

My direction is to have a “script” directory that is in my path for my little tools. This allows the “build shell script” to be called from any directory on that machine. This would apply to any operating system.
I place the build script into the accessible path location. Now Sublime Text can open a “shell” and utilize that script.

Build Option

Sublime allows you to write any type of build command you desire. There are substitutions available for passing values to a command. In my case, the command is a “build script”.   From there, I can do any additional work that I may require inside the script with the single command.

To make a new build system available, you simply need to put a *.sublime-build file into your User directory. (see Browse Packages)
Once a valid build file has been placed in the directory, it should be immediately available using the name of the file as the Build option.

sublime_build_options

Selecting a build option will now allow the quick key of Ctrl+B to automatically send the active file to the build script. There is an option to “Save All on Build” that can be turned off if desired.

Note: The build file does have the user name and password embedded in it for convenience and speed. This is a known and intended for development only.

I have included here the scripts that I use for both MAC and Windows. They have different syntax as the environments are different.
Example: %1 vs $1 for variables. Place the respective Mac or Windows .sublime_build file in your Sublime Text Packages/Users directory. Place the respective Shell script into a directory in your path statement.

Duplicate the .sublime_build file for each connection making updates to the title and the contents appropriately to reflect it’s connection details.

Sublime Text Oracle Build Scripts

The build process produces a results window that has the output of the script with a simple keystroke. (Ctrl+B)

build_results

Any errors that are encountered are identified by the standard SQL*Plus error reporting process.

build_results_error

Sublime Text can work very well for building DDL scripts.  I do not recommend it for query building or testing as the formatting is SQL*Plus text based.  SQL Developer is a much better choice for developing queries and testing results.  Improved productivity can come about by knowing what the right tool for the job is, and how to use it.

Side Note – linking SQL Developer to Sublime Text

If you are interested in using Sublime Text with SQL Developer – Take a look at the post by Jeff Smith that outlines now to setup external editors inside of the SQL Developer tool.

LOB, CLOB, BLOB – Dealing with Attachments in APEX

Included here is the presentation slides that I gave during the RMOUG 2014 conference.

RMOUG14 St.Hilaire CLOB_BLOB_LOB

Demonstration Application Update:

I am working to make the Demonstration application installable to allow users to be able to install and see behind the slides.  This is still in progress.  If you are interested in testing the application, please leave me a comment below.

How to break SQL Developer -or- Dr. It hurts when I do this…

I have been working with SQL Developer a lot over the last few years. Being a Toad user beforehand, it was an adjustment to say the least.  With the last few releases, I have come to understand the working much better and can see where the seams are in the product.  That being said, something I still don’t understand fully (or trust) is performance tuning with SQL Developer. When dealing with troublesome tuning, I have come to trust SQL scripts (see SQLPLUS TIMING) as a much more accurate representation of performance rather than running through a GUI system.

Why?

Because there are too many variables that are included with a GUI that are intended to HELP me.
Variables that I don’t’ always understand when or why they have been put in place and hence run a risk of giving me bad results that I then make decisions on.  Decisions that I thought were based on empirical data, but turn out making me question my own understanding.

Problem “Java Heap Space error in Query Results Window”

In this story, I was working on a particularly complex and layered performance issue that was behaving differently in SQL Developer vs. the resulting system usage. In trying to duplicate the performance issue in SQL Developer. I was able to get the high level issue to duplicate, but when breaking the parts down, I was unable to get same issue with the parts of the statement.

2014-02-setting
Array Fetch Size Setting

After investigating: There is a setting called SQL Array Fetch Size (between 50 and 200)
This will greatly change the behavior of a running query when attempting to get all rows returned.

What I found was if I turn this value up, I would be able to see the performance issue more clearly.

2014-02-results_normal
Array Fetch Size used in Query Results (adjusted to 53)

If you have done performance tuning before, you can see where this little story is heading.
When the number of rows greatly affect the performance, it is a good indicator of where the problem lies.

How to fix “Java Heap Space error in Query Results Window”

2014-02-heap_error
Java Heap Size – error message in query results tab

So.. back to the main theme of this post.
If you set the SQL Array Fetch Size to something too large – it may help you for the moment find your problem, but it can also have a side affect.  What I found was that I would get a “Java heap space” error message and a gray window in the Query Results.  Adjusting the SQL Array Fetch Size to a value in the suggested range will correct this error (after a re-start of SQLDEV).

How to debug SQL Developer settings issue

Now if you try to do a web search for SQL DEVELOPER JAVA HEAP SPACE ERROR, you will get a whole listing of issues and text to read, most of which have to do with the startup of SQL Developer. Often the most successful results people have is by re-installing SQL Developer. This can be misleading. When just replacing the binaries the settings will persist.
Keep in mind – it isn’t the main code that had this issue, as much as the SETTINGS the (l)user set.

To find out where your settings are located for SQL Developer (system agnostic) – go to HELP -> About -> Properties then search for ide.system.dir

IDE Settings
IDE Settings

Purging the settings will also purge all of your CONNECTIONS. If you are like me, that is what I didn’t want to lose.
Your settings directory can be backed up (copied, zipped, whatever) until you determine the actual cause of the IDE error.
This is the best way to determine if the issue is with something you have done, or something wrong with the IDE.

This is sound methodology with more than just SQL Developer.

Disclaimer -or- Knowing where the seams are

Just because this was the issue that I found that generated the very generic “Java heap space” error, does not mean it will be the same for you.  The modular construction of SQL Developer makes me think there is more than one way to get that error.  The take away here is more determining how to debug a packaged product before going off the deep and blaming the product.

Special thanks to Jeff Smith (@thatjeffsmith) for letting me know that it was just me.

RMOUG – 2014 Comming Up!

RMOUGSpeakerbutton

I have the privilege of presenting at the Rocky Mountain Oracle Users Group (RMOUG) in February.  I will be presenting on Friday – LOBS, BLOBS, CLOBS – Dealing with Attachments Inside Oracle APEX
I look forward to the conference as it will be my first time participating in this user group.

After the presentation, I will be posting the final slides and example code on this blog, so check back after Feb 7th.

Many thanks to Enkitec  for supporting my attendance at the conference.

Tim St. Hilaire – Notes and Shares