Oracle USER vs sys_context

This post was inspired by the fact I couldn't find many good references for what I thought was 'previously discovered truth'. So what does any good science-nut do? Add their own contribution.

So here are two simple performance suggestions. The second was an added bonus I realised I could demonstrate simply.

1) Stop using USER

I'm using USER far less frequently anyway, since it has no context in Oracle APEX, but it is still a handy default value for created_by colums, and I'm sure some Forms programmers could add life with a small refactor.

Sven explores this in his excellent post regarding triggers in 12c (spoiler: there is typically no need for a trigger).
I thought Tim had a section in an article similar to this, but I couldn't find it again.
And there's a tweet. There's always a tweet.

I executed the following on a development server, and it took 47, 50, 50 seconds respectively.

declare 
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := user;
  end loop;
end;
/

50 seconds

Replacing USER with sys_context('userenv','session_user') took an order of magnitude lower at 2.5, 2.4, and 2.4 seconds.

declare 
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := sys_context('userenv','session_user');
  end loop;
end;
/

2.5 seconds

It's no longer context switching between SQL and PL/SQL.

2) Stop using NVL

Instead, consider COALESCE, or other null-related functions.

So consider the previous test, but NVLing both expressions. At 58 seconds, the time taken seems like the sum of both, plus time to evaluate. Connor has more detail on the difference.

declare
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := nvl(sys_context('userenv','session_user'),user);
  end loop;
end;
/

58 seconds

By swapping the NVL with a COALESCE, you utilise a programming concept called 'short circuit evaluation'.  Results: 2.5, 3.1, 2.5 seconds.

declare
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := coalesce(sys_context('userenv','session_user'),user);
  end loop;
end;
/

2.5 seconds

I'm sure there's the odd "it depends", but if you don't take the performance freebies, what are things going to be like when you tackle to nasty queries?

Customising APEX Session Expiry

It's nearly 8am, you're holding your favourite morning beverage, and you open yesterdays APEX tab, only to find this:

This is the current default expiry page. I'd like to tart it up.

I've used the following technique for so long, I've forgotten what it used to look like to drive me to this solution. Ultimately, you end up with a similar result, but you really can customise it to behave however you like.

Under Shared Components -> Security -> Session Management, you'll find the Session Timeout URL attribute. Here we can specify what page should be opened when the application times out.

If I use the following, it will open the login page just as it normally would have, but also include a REQUEST parameter, with the value "TIMEOUT"

f?p=LEMENU:LOGIN::TIMEOUT

We can define this value to be whatever we like, but the intention is that on the login page, we can conditionally render a region based on the declarative condition:

Request = Value
TIMEOUT

Where TIMEMOUT is the literal string we provided.

Here I've used it to conditionally display a region that uses the Alert template.

Useful feedback for the user

The only trouble is, when your original session times out, Oracle initiates a new Session ID - which will also time out. Then, when you press the login button, you'll just see this.

Your login page has timed out!

So we could extend the region I added by providing a link to re-open the login page, forcing a new session.

Your session has timed out.
<br><br>
Click <a href="f?p=&APP_ID.">here</a> to log in.

And add a condition to the original Login region to exclude when the timeout message is being displayed, forcing the user to click the link to re-open the page, thereby initiating a new session. I use this SQL expression to test the opposite, since the request value may be null.

lnnvl(:REQUEST = 'TIMEOUT')




Clean timeout message

You could pimp up the link by adding UT classes, with help from the UT button builder
class="t-Button", since buttons are easier to tap.

Or just add a declarative button to the region.

Or you could just set your Timeout URL to redirect to a completely different page, where the Authentication page attribute is 'Page is Public'.

Nothing too exciting, but I do like the options a REQUEST parameter can offer - transmitting information with no need for a defined parameter name.

This is one of a few ideas shown in this presentation, from slide 22.

Authentication - Switch in Session

It's only taken a year, but I've finally checked out the ability to switch authentication schemes at runtime with 18.x. It's mentioned in this 18.1 new features slide deck, and the new features list in the documentation.

Such a frequent request in the forums is to either share authentication between sessions, or dynamically change the authentication scheme - which hasn't been possible until APEX 18.1.

I've set up a sample application, where the home page is public, and contains a concise summary of this post.
https://apex.oracle.com/pls/apex/f?p=100567:1
Clicking 'Secured Page' in the menu will open page 2, forcing default open-door authentication, if not already authenticated.

The default authentication scheme is open-door credentials, but there is another defined for APEX accounts. I've given them a simpler name for simpler parameterisation.

Authentication Schemes available to app

The non-current scheme must have the following property set to Enabled.

Authentication Scheme - Switch in Session

However, is this opening up a security issue, offering the end-user an ability to change authentication method on the fly?

Now the following two links allow toggling between authentication schemes at runtime
https://apex.oracle.com/pls/apex/f?p=100567:2:0:APEX_AUTHENTICATION=apex
https://apex.oracle.com/pls/apex/f?p=100567:2:0:APEX_AUTHENTICATION=open

If this REQUEST parameter is present, it always appears to force fresh authentication.
However, :REQUEST returns null when attempting to use as a condition on the page.

This is an alternative to the application session sharing technique, and may be useful when integrating social sign-in.

Another use case could be for the related development environment. If you're using OAuth2 authentication, it probably won't be practical signing in as other users, so enabling switching back to an older authentication scheme could be useful.

Thanks to Morten for the heads-up.

Oracle APEX Social Sign-in Authentication Scheme

Recently I was involved in setting up a Social Sign-in Authentication Scheme, so despite the doom & gloom of this post, we are breaking some interesting rock.

I say 'involved', since I had the support of one of the nerdiest nerds in Perth, for all the server tinkering. I just had to paste in some URLs and other config settings within APEX.

He expected every error, and it always seemed just due process to the finish line. We had it all sorted within a couple of hours, and I was really doing other things while they configured SSL & wallets.

I can't really divulge all the details, since, you know, security, but I can certainly help describe the steps, to maybe help future tinkerers.

This post from Ciprian Iancu was a major reference for the team, though it wasn't entirely accurate for us. Details below.

<handwave> this is handwaving I'll use when I either don't fully understand what was done / is a security issue / just noted as a prompt. I'm the data/UX guy ;p
We also moved so quickly, I didn't have a chance to copy all the errors.

1 - SSL 

Our new Tomcat server needed to be running with SSL before Azure would accept a handshake.

<handwave> There is something they do that allows me to use https

2 - Wallet

<handwave>A wallet was configured on the database server. There is talk about ensuring the one wallet contains numerous certificates, since we now need it for a number of exchanges.

I entered the wallet's file location in the Internal workspace under Manage Instance -> Instance Settings -> Wallet.
file:/home/oracle/wallet

Without it, we get a warning regarding certificate failure.
At one point the permissions on the wallet file were wrong, which I saw by opening the login page in debug mode, then checking the debug log.

https://myhost:port/vbs/f?p=100::::YES::

You should see an entry starting with apex_authentication.callback

Debug example

3 - ACL

ACLs allow the database to communicate to certain hosts in the outside world. Ciprian's blog was helpful, but the ACLs weren't quite right. The ACL API has changed in 18c, the graph host was incorrect, and we only needed http, not connect.

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'graph.microsoft.net',
    lower_port => 443,
    ace  =>  xs$ace_type(privilege_list => xs$name_list('http'),
                        principal_name => l_user,
                        principal_type => xs_acl.ptype_db));

  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'login.microsoftonline.com',
    lower_port => 443,
    ace  =>  xs$ace_type(privilege_list => xs$name_list('http'),
                        principal_name => l_user,
                        principal_type => xs_acl.ptype_db));

4 - Web Credentials

It took a little while to know this is where we needed to define something prior to creating the authentication scheme, as noted in this bug description. Without it, we are unable to define the authentication scheme.

Web Credentials

<handwave> Mr Sysadmin set up a Client Secret somehow in server land.

I then pasted the long string under Application Builder -> Workspace Utilities -> Web Credentials.

5 - Create Authentication Scheme

I always find some strange satisfaction in setting up an authentication scheme, and to do one that integrates with Azure using contemporary methods was particularly gratifying.

First round of settings

The User Info Endpoint URL allows us to harvest more information from Azure. By default, a small set of attributes are returned in a JSON packet, but we can extend the attributes supply by extending the URL

https://graph.microsoft.com/v1.0/me/?$select=userPrincipalName,onPremisesSamAccountName,mail,officeLocation,department,displayName,givenName,jobTitle,mobilePhone,surname,id

Post-Authentication Attributes

I found an OTN post from Christian Neumueller describing exactly how to apply the apex_json package to get attributes listed in the settings - I just made some adjustments to make it look a little prettier, and add the fields we wanted to look at.

I'd really love to see examples like this in the inline help for attributes more often.
Here is what I used in the post-authentication procedural code:

:G_USER_INFO := 'Authenticated via Azure. '||chr(10)||
  '<br>Details from Graph:<br>'||
  '<table class="t-Report-report">'||
  '<tr><td class="t-Report-cell">id:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('id')||'</td></tr>'||
  '<tr><td class="t-Report-cell">userPrincipalName:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('userPrincipalName')||'</td></tr>'||
  '<tr><td class="t-Report-cell">onPremisesSamAccountName:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('onPremisesSamAccountName')||'</td></tr>'||
  '<tr><td class="t-Report-cell">mail:</td><td class="t-Report-cell"> '||apex_json.get_varchar2('mail')||'</td></tr>'||
  '<tr><td class="t-Report-cell">displayName:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('displayName')||'</td></tr>'||
  '<tr><td class="t-Report-cell">givenName: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('givenName')||'</td></tr>'    ||              
  '<tr><td class="t-Report-cell">surname: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('surname')||'</td></tr>'||
  '<tr><td class="t-Report-cell">officeLocation: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('officeLocation')||'</td></tr>'||
  '<tr><td class="t-Report-cell">department:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('department')||'</td></tr>'||
  '<tr><td class="t-Report-cell">jobTitle:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('jobTitle')||'</td></tr>'||
  '<tr><td class="t-Report-cell">mobilePhone: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('mobilePhone')||'</td></tr>'||
  '<tr><td class="t-Report-cell">Access Token: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('access_token')||'</td>'||
  '</table>';

I had a simple region on the home page to display the data.

Region on Home Page, showing G_USER_INFO

Then when we launch the application, we're redirected to login.microsoftonline.com; enter our Azure credentials, accompanied with the client's splash; then see our APEX page.

G_USER_INFO displayed at runtime

We were prompted to stay signed in, but I keep pressing No until we have a chance to experiment further.

APP_USER

APEX uses the Username Attribute to source the APP_USER variable. In our case the userPrincipalName returned an email address, but we wanted to match this up to our existing login IDs which use the WESLEYS style format.

We first tried using the onPremisesSamAccountName attribute, but that returned "wesleys"

Christian Neumueller once again provided an easy solution to ensuring our APP_USER returned the same uppercase value, by adding this to the post authentication process:

apex_custom_auth.set_user(p_user => upper(v('APP_USER')));

And in 19.1, there's already a declarative "Convert Username to Uppercase" attribute.

Logout URL

This was originally pointing to the application home page, which just re-authenticated me - which was quite handy as I refined the handling and temporary display of attributes from JSON.

We still need to experiment with authentication behaviours when it comes to remembering who you are, and logging out. For now, I know we can go to portal.office.com to log out of Azure.

No doubt there are other experiences & behaviours to refine as we move forward, but this is a nice step away from database accounts!

7 - Link Applications

This experiment was done on a fresh application, but I was able to use the principles behind sharing application authentication, to then create a button that linked to our main application - already authenticated.

I hear there is now a way to programmatically change the current authentication scheme, via a parameter. I'd like to experiment with that to see how it might change my answer when people ask if we can authenticate into the one application using two authentication schemes.

Conclusion

We had this hooked up within 2-3 hours. That can't be a normal experience, but it's still been a really good exercise in understanding how this technology works. Kind of.

It's also pretty cool that we have such declarative options for setting this up in our APEX applications.

If you want more examples to help get your configuration working, maybe filling in the blanks I left, see other posts by
- Dimitri - Social Sign-in with Google and Facebook
- Ciprian - Social Sign-in with Microsoft Azure
- Adrian - Social Sign-in without a wallet

- Adrian - Certificates
- Morten - Authentication with Microsoft
- Mahmoud - Forum solution with Google

APEX 18.x Application Session Sharing

For quite some time I've been crafting multiple applications that, to the end user, appear as one.

This is possible with some Session Sharing attributes in relevant the Authentication Scheme, a feature agnostic to the Scheme Type.

This ultimately means you can set up two different applications that use two different methods of authentication, thereby letting in two different sets of users in the 'same' application - you just need to make some UX decisions.

APEX 18.x introduced a 'Type' option, and renamed the section from a less descriptive 'Session Cookie Attributes'.

Session Sharing Attributes

I thought this was offering something a little bit extra, but it appears to just make the process a little simpler for the likely majority.

We can still segment our applications, where users of applications with the same cookie name / colour may have links that jump seemlessly between applications, such as Earth and Mars.

Application sharing examples

This is possible only if you use the declarative 'link to different application' target, or include the session ID when you build the URL

f?p=EARTH:HOME:&SESSION.::

If the user tried to jump to Jupiter, then they will be prompted for login details. Ceres and Vesta stand alone with the default option, sharing authentication with no other apps, not even between themselves (unlike a certain spacecraft).

Upon returning to the authentication scheme for Earth, I noticed the Type actually changed back to Custom, and I saw an (undocumented) substitution string WORKSPACE_COOKIE used.

This saves us from having to nominate the cookie name, and store the literal ourselves, perhaps as a custom application substitution string. It just presumes that all apps in our workspace we nominate as such are to share authentication.

I thought I read some attempts on the forums to change the 'current' authentication scheme on the fly, but I don't see any mention in the documentation - Correction.

Thursday Thought: Imposter!

Ever feel like you're struggling to keep up? With anything at all?

I'm feeling a bit of that with APEX right now.

Partially because I've been at a site that is a version or so behind, but who isn't, right?

But those fine specimens on the Oracle APEX team keep producing so many nifty things, I'm finding it increasing difficult to keep up. Same with the database itself. So many great new practical development features in 18.x.

Time to buy?

I'm sure I'm not the only one, and the fact I'm writing this 6 months after I noted the idea kind of illustrates my point. It was spurred a little bit from Tim Hall's post about the little funk he's feeling. Some of the issues he's been feeling resonate with my own. Work has been good and bad, my presentation game is a little off, and I'm not blogging as much. Heck, sometimes I used to schedule a few posts in advance, instead of posting a flurry.

There are a few major factors involved, including, but not limited to:

1. Managing a house containing a little human learning to communicate
   
   
2. Moving house
   
   
3. Needing to work hard to prove what seems blazingly obvious to us - that building enterprise applications with APEX is an advantage.

They're all positives in their own right, just a little disruptive if you're used to having a little extra time to learn on the side.

However, as I'm sure I've moaned before, I miss the writing. The learning process of breaking something down enough to try explain the topic in a blog post - mostly so future me can remember how something worked, then replicate/adopt it for my new problem. "Oracle Things I Got to Remember Not to Forget"
It frees my mind a little, knowing I can let it go because I've noted this resource effectively for the future.

I also like sharing with the community. Let's wreck this together, right? If we're all helping each other, we're all building better applications, we're all pleasing our customers, we're all sustaining our careers.

Point 3 has a richer story I can't share here, but it is still great for learning how to build applications with a rich user experience. I think my UX & logic building is currently on point, but I'm seeing posts from people like Adrian, Morten, & Vincent just killing it, exploring new avenues, doing funky things.

Not that's really been my bag here on my blog, I've always been focussed on practical solutions with any feature available to us. Features that anyone could be using, possibly shown in a way you may not have seen before, or reinforcing and idea you have. So worrying what others are doing just feeds doubt.

So I'd like to tie this rambling back to learning APEX, and whatever funk may be nearby.

I feel my progress using this development tool has shown a reasonable learning curve, punctuated by a few events - such as the availability of the Page Designer, introduction of the Universal Theme, and understanding how to effectively use Dynamic Actions.

And when I use the term punctuated, I mean I might be working effectively with the tools I'm aware for some time, learn a new trick, gain a new feature, then things improve dramatically for a time.

And just as I started to write this post, I found an article (whatever he's selling aside) that really nailed what I was thinking. I recommend the read, by anyone with a career: https://taylorpearson.me/punctuated/

So if you're feeling in a funk, maybe it's just status-quo.
The next interesting development may be just around the corner.

If you still feeling a little of the imposter syndrome, particularly if you're currently working in a small team, I recommend considering the comments on this tweet from tech commentator @SwiftOnSecurity.

Of course, one solution to my problems is less Twitter, but... input.

Happy new year!