Wednesday, 26 November 2014

Logging in without user intervention

For this, I set up an authentication plugin and behind the scenes it's using a web service to check if the client is valid or not.

To automate the login process, you need to make use of the sentry function that gets called every time you access a page in the application. This is what the item help says:

Enter the name of the PL/SQL function the plug-in can use to perform the session sentry verification. It can reference a function of the anonymous PL/SQL code block, a package function or a stand alone function in the database. For example:


When referencing a database PL/SQL package or stand alone function, you can use the #OWNER# substitution string to reference the parsing schema of the current application. For example:


There is however one caveat with this. It doesn't run on whatever you have defined as the login page (User interface attributes --> Desktop --> Login URL) - which is reasonable enough.

So I left that attribute alone, and created 2 pages that will aid in the process:
  1. Logout page
  2. Invalid session page
The idea being, when the user logs out, they will be taken to the logout page. I got this idea from the Apex Builder actually - with a button to return to the system. I think the best page to return to is the page specified in HOME_LINK. Using the substitution &HOME_LINK. doesn't seem to work, since by default it includes a substitution string for &APP_ID. which doesn't seem to be evaluated. I think a good workaround is to create a new substitution string: &HOME_PAGE. and use that in a redirect to page button action (then update your home link to reference that substitution string).

When the user attempt to access a page and authentication/sentry fails, they will be taken to the invalid login page.

We have 2 associated attributes in the authentication plugin:

  1. Post logout function
  2. Invalid session function
These are really just to determine which page to go to next - which line up with the 2 pages I created earlier on. 

When we logout, we set the page to go to, but because the session is also invalid, it will get fired as well. Since we don't want to get redirected to the invalid session page when we log out, we just need one additional check in that function to make sure the current page isn't the logout page. Here is how I implemented that:

function invalid_session (
    p_authentication in apex_plugin.t_authentication,
    p_plugin         in apex_plugin.t_plugin )
    return apex_plugin.t_authentication_inval_result
    l_invalid_Result apex_plugin.t_authentication_inval_result;
    if apex_application.g_flow_step_id != LOGOUT_PAGE
        l_invalid_result.redirect_url := 
            || apex_application.g_Flow_id
            || ':'
            || INVALID_SESSION_PAGE ;        
    end if;

    return l_invalid_Result;
END invalid_session;

Nothing fancy about the logout function:

function logout (
    p_authentication in apex_plugin.t_authentication,
    p_plugin         in apex_plugin.t_plugin )
    return apex_plugin.t_authentication_logout_result
    l_logout_result apex_plugin.t_authentication_logout_result;

    l_logout_result.redirect_url := 
            || apex_application.g_Flow_id
            || ':'
            || LOGOUT_PAGE;         

    return l_logout_Result;

END logout;

Then for the actual sentry function. I return true if it's one of the pages created above (Logout or Invalid Session page). Then, where necessary, I call the authentication function to get the appropriate result. Since the parameter p_authentication doesn't contain the username attribute on invalid sessions, I declare a local variable of type apex_plugin.t_authentication then copy all the values from p_parameter except p_username - which I'm retrieving in my own function.                     :=;                   :=;
l_auth_attrs.invalid_session_url    := p_authentication.invalid_session_url;
l_auth_attrs.logout_url             := p_authentication.logout_url;
l_auth_attrs.plsql_code             := p_authentication.plsql_code;
l_auth_attrs.session_id             := p_authentication.session_id;
l_auth_attrs.username               := upper(get_user_name());
l_auth_attrs.attribute_01           := p_authentication.attribute_01;
l_auth_attrs.attribute_02           := p_authentication.attribute_02;
l_auth_attrs.attribute_03           := p_authentication.attribute_03;
l_auth_attrs.attribute_04           := p_authentication.attribute_04;
l_auth_attrs.attribute_05           := p_authentication.attribute_05;
l_auth_attrs.attribute_06           := p_authentication.attribute_06;
l_auth_attrs.attribute_07           := p_authentication.attribute_07;
l_auth_attrs.attribute_08           := p_authentication.attribute_08;
l_auth_attrs.attribute_09           := p_authentication.attribute_09;
l_auth_attrs.attribute_10           := p_authentication.attribute_10;
l_auth_attrs.attribute_11           := p_authentication.attribute_11;
l_auth_attrs.attribute_12           := p_authentication.attribute_12;
l_auth_attrs.attribute_13           := p_authentication.attribute_13;
l_auth_attrs.attribute_14           := p_authentication.attribute_14;
l_auth_attrs.attribute_15           := p_authentication.attribute_15;

l_auth_result := 
        p_authentication => l_auth_attrs
      , p_plugin => p_plugin
      , p_password => NULL);
if l_auth_result.is_authenticated

        p_user => l_auth_attrs.username
      , p_session_id => apex_custom_auth.get_next_session_id  

    l_sentry_result.is_valid := l_auth_result.is_authenticated;

    l_sentry_result.is_valid := false;
end if;

Well that about covers it! Good luck with your authentication adventures!

Tuesday, 11 November 2014

Exposing procedures for URL access with ORDS

I had just been looking at exposing some procedure for URL access, using ORDS.

ORDS offers the following configuration properties:
  • security.inclusionList
  • security.exclusionList
  • security.disableDefaultExclusionList
  • security.validationFunctionType
  • security.requestValidationFunction

If you look at the administrator guide: it suggests pointing the validation function to: wwv_flow_epg_include_modules.authorize, and modifying the function: wwv_flow_epg_include_mod_local, within the APEX schema to return true or false depending if you want your procedure to be accessible or not. This unwrapped function is effectively called at the end of wwv_flow_epg_include_modules.authorize if none of the apex procedures matched.

If you leave the value of security.requestValidationFunction empty, all procedures will be accessible.

In addition to requestValidationFunction, or in place of, you can make use of the properties security.inclusionList and security.exclusionList. These allow you to specify a comma separated list of procedures that should be included or excluded respectively. Keep in mind, for the inclusionList, if it contains a value, all supported procedures must be specified. So it might make the validation function a little redundant if you need to go specifying the same procedures twice.

The benefit of the inclusion list is it supports wild cards, so removing the validation function, you could have a value such as: 'f, p, z, ws, apex*, htmldb*, apex*, wwv_flow*' which should support most apex related URL procedures. Then you just need to append any schema qualified procedures you want to expose.


The other option is making use of RESTful services in SQL Workshop. This has a couple of benefits:
  1. You won't have to modify the configuration everytime you want to expose a new procedure
  2. You don't need to apply grants to public on your procedures/packages
As an example, I create the following procedure:

create or replace procedure output_name(p_name in varchar2)

  htp.p('Hello ' || p_name || '. You have successfully exposed your procedure using RESTful services.' );

end output_named;

Then, through SQL Workshop, I go into RESTful services and create a new service named ''. I specify a URI prefix of messages/ - this is a completely optional part of the service, and just adds another portion to the request URL after the workspace name.

In the 'Add a Resource Template' section, I specify welcome/{name}. The bit in curly braces just creates a bind variable to be used in the handler source.

Finally, in the 'Add a Resource Handler' section, I specify the method as GET, source type as PL/SQL and specify the source as:


As per the following screenshot:

If you then navigate to the resource handler and use the Test button, you should be able to see it in action.

Basically the format of the URL will be:


Which in my case, is:


These services also have the benefit of being able to associate authentication, to lock them down a bit more. For more information, I suggest checking out the ORDS Developers Guide: