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:

check_ldap_session_sentry

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:

#OWNER#.check_ldap_session_sentry


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
AS
    l_invalid_Result apex_plugin.t_authentication_inval_result;
BEGIN
    
    if apex_application.g_flow_step_id != LOGOUT_PAGE
    then
        l_invalid_result.redirect_url := 
            'f?p=' 
            || 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
AS
    l_logout_result apex_plugin.t_authentication_logout_result;
BEGIN

    l_logout_result.redirect_url := 
        'f?p=' 
            || 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.id                     := p_authentication.id;
l_auth_attrs.name                   := p_authentication.name;
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 := 
    authenticate_user(
        p_authentication => l_auth_attrs
      , p_plugin => p_plugin
      , p_password => NULL);
      
if l_auth_result.is_authenticated
then

    apex_custom_auth.define_user_session(
        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;
else

    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: https://docs.oracle.com/cd/E37097_01/doc.42/e35129/adm_mg_service_set.htm#AEADM209 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)
as
begin

  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 'example.com'. 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:

begin
    output_name(:name);
end;

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:

/workspace_name/URI_prefix/uri_template/bind_variable

Which in my case, is:

/test1/messages/welcome/bob

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: http://www.oracle.com/technetwork/developer-tools/rest-data-services/documentation/listener-dev-guide-1979546.html

Thursday, 30 October 2014

Debugging parameterised views outside of apex

Recently I've been working on a project that had some views that needed to reference some session state information, which uses the ever too familiar v function:

select *
from some_table
where some_item = v('P1_SOME_ITEM')

Since I work extensively in SQL Developer, when I'm debugging, it becomes a bit more difficult, because we are outside the context of your apex session, our views data comes back empty.

One solution I've come up with to help with this is using some un-documented procedures to create an apex session outside the apex scope. Actually, I can't take the credit, I stole the code from @martindsouza's blog with a few minor adjustments.

create or replace package apex_session_utl
as

    procedure re_init_session(
        p_session_id in apex_workspace_sessions.apex_session_id%type);
    
    function get_session_username(
        p_session_id in apex_workspace_sessions.apex_session_id%type) 
    return apex_workspace_sessions.user_name%type;
    
    function get_session_application(
       p_session_id in apex_workspace_sessions.apex_session_id%type) 
    return apex_workspace_activity_log.application_id%type;   
    

end apex_session_utl;
/

create or replace PACKAGE BODY apex_session_utl AS

    /*
    
        Example used from: http://www.talkapex.com/2012/08/how-to-create-apex-session-in-plsql.html
    
    */
    procedure re_init_session(
        p_session_id in apex_workspace_sessions.apex_session_id%type)
    as
        l_workspace_id apex_applications.workspace_id%type;
        l_cgivar_name owa.vc_arr;
        l_cgivar_val owa.vc_arr;
        
        l_app_id NUMBER;
    begin
        htp.init;
        
        l_app_id := get_session_application(p_session_id);
        
        l_cgivar_name(1) := 'REQUEST_PROTOCOL';
        l_cgivar_val(1) := 'HTTP';
        
        owa.init_cgi_env(
            num_params => 1
          , param_name => l_cgivar_name
          , param_val => l_cgivar_val);
        
        select workspace_id
        into l_workspace_id
        from apex_applications
        where application_id = l_app_id;
        
        wwv_flow_api.set_security_group_id (l_workspace_id);
        
        apex_application.g_instance := 1;
        apex_application.g_flow_id := l_app_id;
        apex_application.g_flow_step_id := 1;
        
        apex_custom_auth.post_login(
            p_uname => get_session_username(p_session_id)
          , p_session_id => NULL
          , p_app_page => apex_application.g_flow_id || ':' || 1);
          
        apex_custom_auth.set_session_id(
            p_session_id => p_session_id);
          
    end re_init_session;
    
    function get_session_username(
        p_session_id in apex_workspace_sessions.apex_session_id%type) return apex_workspace_sessions.user_name%type
    as
        l_user_name apex_workspace_sessions.user_name%type;
    begin
    
        select user_name
        into l_user_name
        from apex_workspace_sessions
        where apex_session_id = p_session_id;
        
        return l_user_name;
    
    end get_session_username;
    
    function get_session_application(
        p_session_id in apex_workspace_sessions.apex_session_id%type) return apex_workspace_activity_log.application_id%type
    as
        l_application_id apex_workspace_activity_log.application_id%type;
    begin
    
      select 
        distinct application_id into l_application_id
      from (
        select 
          application_id
        , dense_Rank() over (order by view_date desc) ranked
        from apex_workspace_activity_log
        where apex_session_id = p_session_id
        and application_schema_owner not like 'APEX_%'
      )
      where ranked=1;
        
      return l_application_id;
    
    end get_session_application;    

END apex_session_utl;
/

I've also place a copy of this code in a github gist for better readability: https://gist.github.com/trent-/1d02da19be85f46030ab

So, with this package in place, you can grab your session identifier from your apex URL and issue that following command:

begin

  apex_session_utl.re_init_session('xxxxxxxxxxx');
  
end;

This will give you immediate access to items like APP_USER and APP_SESSION. You should be able to set other items with apex_util.set_session_state.

begin

  apex_util.set_session_State('P1_SOME_ITEM', 2);
  
end;

Any views that reference this item should now return data! One gotcha I just noticed, setting an item value in SQL Developer will reflect back into your web session, but setting an item value in your web session doesn't reflect back in SQL Developer.

There may be a better way to handle this, but hope this helps!

Thursday, 14 August 2014

Getting the instance URL

To get the instance URL of your APEX environment, it is not too uncommon to use OWA_UTIL.GET_CGI_ENV function calls, similar to the below block:

declare
    l_protocol varchar2(5);
    l_host varchar2(150);
    l_script varchar2(15);
    
    l_instance_url varchar2(200);
begin


    l_protocol      := owa_util.get_cgi_env('REQUEST_PROTOCOL');
    l_host          := owa_util.get_cgi_env('HTTP_HOST');
    l_script        := owa_util.get_cgi_env('SCRIPT_NAME');
    
    l_instance_url := l_protocol;
    l_instance_url := l_instance_url || '://';
    l_instance_url := l_instance_url || l_host;
    l_instance_url := l_instance_url || l_script;
    l_instance_url := l_instance_url || '/';
    
    dbms_output.put_line(l_instance_url);
  end;  

Something you might not know, is that there are a couple of helper functions in the APEX API that allow you to do exactly that.

Option 1: APEX_UTIL.HOST_URL('SCRIPT')

Reference: http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_util.htm#AEAPI2312

Option 2: APEX_MAIL.GET_INSTANCE_URL()

Reference: http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_mail.htm#AEAPI29399

Option 2 seems to work even without being in the context of a HTTP session, but as per the docs: "This function requires that the instance setting Application Express Instance URL for emails is set."

Wednesday, 6 August 2014

APEX5 EA APEX_ZIP usage

I recently saw a link to a presentation that made mention of APEX_ZIP for the upcoming release of APEX (Version 5). Currently in early adopter, you can have a play around. So just thought I'd go and have a bit more of a play to see the possiblities.

On the presentation, and specifically for extracting files the sample code is:

declare
    l_zip_file  BLOB;
    l_unzipped_file     BLOB;
    l_files             apex_zip.t_files;
begin

    select file_content
    into l_zip_file
    from my_zip_files
    where file_name = :P13_file_name;
    
    l_files := apex_zip.get_files(p_zipped_blob => l_zip_file);
    
    for i in 1..l_files.COUNT
    LOOP
        l_unzipped_file := apex_zip.get_file_content(p_zipped_blob      => l_zip_file,
                                                     p_file_name        => l_files(i));
                                                     
        insert into my_files (file_name, file_content)
        values (l_files(i), l_unzipped_file);
    END LOOP;

end;


With a bit of looking around, we can soon find that APEX_ZIP refers to WWV_FLOW_ZIP, and we can grab the package specification by querying all_source. It seems that the package is derived from that code originally shared on the OTN forums here: https://community.oracle.com/message/4509996

Side note: If the zip file contains folders, you may need to get familiar with string functions to weed out only the folders you are interested in.

The other common usage would of course be creating a zip:

declare
    l_output_zip BLOB;
    l_filename varchar2(200) := 'newzip.zip';
begin

    for file in (
        select filename, contents
        from unzipped_files
    )
    LOOP
    
        apex_zip.add_file(
            p_zipped_blob       => l_output_zip
          , p_file_name         => file.filename
          , p_content           => file.contents
        );
    
    END LOOP;
    
    apex_zip.finish(
        p_zipped_blob           => l_output_zip
    );
    
    insert into zipped_files (mime_type, filename, contents) 
        values ('application/zip', l_filename, l_output_zip);


end;

If you wanted to put files in a specific folder, you just need to prepend the file name with the folder name and a forward slash: 'folder/' || file.filename

nb: it is essential to call apex_zip.finish once all files have been added


Thursday, 3 July 2014

Oracle vim config

This page gives a pretty good overview of tweaks you can do, so I just want to re-gurgitate what I have done (from that site).

Syntax Highlighting

Grab the file: http://www.datacraft.com/plnet/files/vim/syntax/plsql.vim, and save this in: ~/.vim/syntax/

Then edit your .vimrc file and add the following line (to auto detect the filetype based on the extension):

au BufNewFile,BufRead *.fun,*.pks,*.pkb,*.sql,*.pls,*.plsql    set filetype=plsql

Auto Indentation

Grab the file: http://www.datacraft.com/plnet/files/vim/indent/plsql.vim, and save this in ~/.vim/indent/

Then edit your .vimrc file and add the following line

runtime! indent.vim

..

Though, I prefer just to use auto indent - there are some special situations where it wont calculate the indentation correctly using the indentation file above.

So this gives me the following in my .vimrc file:
 
au BufNewFile,BufRead *.fun,*.pks,*.pkb,*.sql,*.pls,*.plsql    set filetype=plsql
au BufNewFile,BufRead *.fun,*.pks,*.pkb,*.sql,*.pls,*.plsql    set nosmartindent




Wednesday, 2 July 2014

Accessing Google data, part 2

Intro

Back in Novemeber 2012, I blogged about accessing Google data from APEX - and provided some sample code to getting started. You can find this post here: http://apextips.blogspot.com.au/2012/11/accessing-google-data.html.

Over the past little while, I have been (slowly) working on a more modularised API, that can be easily built upon to add support for additional Google services. I have called it PL/GAPI (Google API). It can be found on github: https://github.com/trent-/pl-gapi.

I hope the wiki on github is straightforward enough to follow in setting this up in your local environment: https://github.com/trent-/pl-gapi/wiki

Services

I've started with the following services:
  • Drive
  • Calendar
Usage

Without going into too much detail (since it's all on the wiki linked earlier), you link to an authorization URL with: 

&OWNER..GAPI_AUTH.BEGIN_AUTH?p_scope=&P0_SCOPE.&p_return_app=&APP_ID.&p_return_page=&APP_PAGE_ID.&p_session=&APP_SESSION.&p_item_for_refresh_token=GOOGLE_REFRESH_TOKEN

This tells the system where to return to and P0_SCOPE defines what scope you are requesting authorization for. Current scopes defined:
  • GAPI_DRIVE.SCOPE_FULL
  • GAPI_CAL.SCOPE_FULL
  • GAPI_CAL.SCOPE_READ_ONLY

Once the user accepts, they are returned to the application in the parameters specified in the above redirect (p_return_app, p_return_page) along with the item to store the refresh token in.

Refresh token is a means to be able to get the access token without continually prompting the user for access. All requests require a valid access token to complete successfully. Because the returned refresh token contains the '/' character, it is escaped by APEX. Before storing it, you need to un-escape the string. To do this, I used the function: UTL_I18N.UNESCAPE_REFERENCE. 

Once we have the refresh token, we can easily get the access token with:

gapi_auth.get_access_token(:GOOGLE_REFRESH_TOKEN);

Read more about the implementation here: https://github.com/trent-/pl-gapi/wiki/Authorization

Then for example, we can add a new folder to our drive:

    :P2_RETURN_FOLDER_ID :=
        gapi_drive_file.create_folder(
            p_folder_name       => :P2_NEW_FOLDER_NAME
          , p_access_token      => :GOOGLE_ACCESS_TOKEN);


I've set up a few examples on the wiki, but really, at this stage, the sample application in the samples folder is the best tool to refer to for sample usage

Feedback

Any feedback, criticisms, suggestions about the implementation are more than welcomed.

note: It's a long way from being complete, but just thought it was time to share.