​APEX Social Sign-In has opened up a whole new world of possibilities for authenticating users into APEX. Whilst it is a catchy name, I propose the name ‘Social Sign-In’ is selling these new authentication capabilities short. I believe ‘Social Sign-In’ actually has far wider reaching implications for both Enterprise and B2C APEX applications.

For sure, APEX Social Sign-In allows you to authenticate users using Google and Facebook and that is great. Many enterprises, however, are using dedicated cloud-based identity management providers such as Okta and Auth0 to authenticate their employees. In addition, B2C web sites and mobile Apps also use these products to handle identity management for their customers. These products provide a host of identity management features such as Single Sign-On, Lifecycle Management, User Management, Multi-factor Authentication to name a few. Being able to use products like these with APEX just by entering a few configurations in a setup page (rather than having to write hundreds of lines of code) is a game changer. It also raises the profile (and credibility) of APEX in an enterprise and consumer setting.
 
Level set on what is federated identity management
Instead of me taking up space in this post, I came across the following article which is a great primer on federated identity management aimed at developers. https://hackernoon.com/federated-identities-a-developers-primer-655a160d66cb

So, why do I even need a third part identity provider? As with most things ‘Cloud’, the answer is security and convenience. Let’s take a look at each of these justifications in a little more detail.

Security
My job is to develop secure, scalable web applications that provide real business value. I am not an expert in cryptography or how you should best prevent user accounts from being compromised. Reputable companies that do nothing else but build identity management solutions, on which their reputation depends, will do a better job than me. Also, in this day and age, you do not want to be responsible for the safe keeping of your user’s credentials. There are more than enough documented nightmare scenarios out there to convince us of that.
 
Convenience
Let’s say my IT department demands that all users start using two-factor authentication. If I had built my own authentication solution, I am now on the hook for building two-factor authentication into my application. While that sounds like a fun side project, it is certainly not a cheap one. Again, we are back to can I implement this more securely than an expert? Using products like Okta and Auth0, it is simply a matter of enabling the feature (and paying for it). Products like these also take care of forgotten passwords, user on-boarding and many other tedious aspects of providing identity management for your users.

So, what did we have to do before Social Login was available? I was rudely reminded of this recently when I was asked to build a custom authentication scheme (in APEX 4) to allow users of an internet-facing B2B APEX application to login via Okta. I had to hand code every URL re-direct, every web service call and don’t get me started on how I had to implement the callback to APEX after authentication into Okta. Don’t get me wrong, the fact that this was even possible in APEX 4 is a testament to the flexibility of the APEX Authentication Scheme. There was, however, clearly room for improvement.

All this changed with the introduction of APEX Social Sign-In. For a basic authentication scenario, all you need to do on the APEX side is create a set of Web Credentials and fill out 4 fields in an Authentication scheme. Zero code required!
 
In a bid to convince the same APEX 4 customer to upgrade to APEX 18.2, I created a demo showing them how easily we could authenticate Okta users using Social Sign-In. For the remainder of this post, I will focus on what configurations were required to complete this demo and authenticate Okta users into APEX.

​Let’s start with the obligatory diagram to show what is going on when you authenticate an Okta user into APEX.

1. The user accesses a page in the APEX application. If the user already has a valid APEX session, then they will be allowed to access the page. If they do not, then the APEX authentication scheme will direct the user to Okta.
2. If the user already has a valid Okta session, then they will be immediately re-directed back to APEX, otherwise they will be directed to an Okta login page.
3. The user will enter their Okta username and password.
4. Assuming login is successful, Okta will re-direct the user back to APEX.
5. When Okta calls the ‘Callback URL’, it provides a Java Web Token (JWT) which includes the following information:
   1. The ‘state’, which is a value APEX passed to Okta at the beginning of the flow. APEX will verify that the ‘state’ returned in the JWT matches the one it created at the beginning.
   2. The user name of the Okta user.
   3. An authorization code which can be used to use to get a token to call additional Okta web services.
6. Once logged in, we can call web services in Okta to get additional information about the user e.g. what groups they belong to, their full name, address etc.
7. At this point, APEX will direct the user to the home page of the APEX App.

 
There, aren’t you glad you didn’t have to code any of that (well at least up until step 6)! 

​Before we get into the APEX configurations, we need to create an Okta account and configure our application in Okta. Okta has a free developer account (https://developer.okta.com/signup/) which allows you to try Okta out.
 
As previously mentioned, Okta has a whole bunch of features which I won’t get into in this post. Instead I will review the Okta configurations needed for the Demo APEX App to login using OpenID Connect (OIDC). Note: The example below was created using our corporate Okta account, but this should also work using an Okta developer account.
 
Application Definition
You need to define each application you want to authenticate in Okta. This is much the same as you would for Google or Office365 authentication. Start by Adding an application in Okta, be sure to select Platform = ‘Web’ and Sign on method = ‘OpenID Connect’.

​The following screen shots show the already configured application followed by descriptions of what values to provide.

​At this stage, we are essentially just picking the Application type and saying we want to use the ‘Authorization Code’ grant type OAuth flow.

This is the interesting part:

• Login redirect URIs
  • This is the APEX URL that Okta will re-direct to when your user has successfully logged into Okta. apex_authentication.callback is a special URL that accepts a JWT from the identity provider and authenticates the user into APEX.
• Logout redirect URIs
  • This is the URL that Okta will direct your user to when they logout.
• Login initiated by
  • Here you can decide if only your App can initiate login or if login can be initiated by Okta. We want ‘App Only’.
• Initiate login URI
  • This is not applicable when Login initiated by is ‘App Only’.

​Okta will generate client credentials for each Application you create. We will use these when creating our APEX ‘Web Credentials’ below.

​In the above section, we are identifying that we want to use OpenID Connect as our authentication method.

​Finally, in the above section, we can determine which Okta users (and or Groups) should have access to our Application.

​With our Okta configurations complete, we can now configure our APEX Authentication Scheme.
 
Web Credentials (these come from Okta)
Start by creating a set of ‘Web Credentials’. Here you will provide the Client ID and Client Secret provided by Okta above.

• Credential Store
  • This is the credential store we defined previously.
• Authentication Provider
  • I chose ‘OpenID Connect Provider’ because a) it is supported by Okta and b) it involves the least configuration. It essentially self discovers all of the information it needs to authenticate against the provider from a JSON document which is publicly accessible.
• Discovery URL
  • This is the discovery URL which APEX uses to find out everything it needs to know to authenticate against the provider. Okta provides this information via a JSON document. The JSON document includes all of the web service end-points (/token, /userinfo, /logout etc.), supported scopes etc.
• Scope
  • The list of scopes determine what pieces of additional information about the user you are allowed to fetch after login to Okta. This information can be accessed by calling the Okta /userinfo web service. In this example, I can get openid information, email address, user profile information and the groups a user belongs to.
• Username Attribute
  • This is the attribute returned from Okta that APEX uses to set the APEX username (:APP_USER) of the logged in user.

And, that is it, the APEX configuration is complete!
​

Patching for APEX 18.2​
If you are running APEX 18.2, be sure to apply the PSE bundle patch for APEX 18.2 ‘Patch 28727865: PSE BUNDLE FOR APEX 18.2 (PSES ON TOP OF 18.2.0.00.12)’. This fixes a bug when using ‘OpenID Connect Provider’ as the ‘Authentication Provider’ in your authentication scheme.

Firewalls, Wallets and Proxies
If you are working with APEX on-premise you have to consider how APEX is going to call out to the identity provider and how the identity provider is going to be able to callback into APEX. For the call out to happen, you have to either install the appropriate certificates from the identity provider in a wallet on your database server or create a proxy to facilitate this. You may also have to create ACLs to even allow network traffic to leave your database. For the identity provider to make the callback, your APEX environment must be accessible to the internet.

In this post, I have tried to illustrate how easy it is to configure APEX Social Sign-In to authenticate APEX users with products like Okta. Okta is one of many cloud identity management providers that you can use to for handling identity management for your APEX users. With APEX Social Sign-In there are less and less reasons to store passwords in your database. 

Hopefully I have convinced you that its scope reaches far beyond Twitter and Facebook. So, what name should we use instead of 'APEX Social Sign-In'? My vote goes for 'Super simple mechanism for logging users into APEX using just about any consumer or enterprise identity provider you care to mention with zero code'. Something tells me, however, this won't fly with the marketing folks!

​A new configuration file parameter showed up in Oracle REST Data Services (ORDS) version 18.3. The parameter is called ‘procedure.rest.preHook’. Whilst this may seem like a pretty innocuous change, I believe it opens up a number of new possibilities for ORDS, which I will explore in this post.

​The parameter essentially allows you to identify a PL/SQL procedure which is called by ORDS prior to every web service call in a particular connection pool. This includes GET, POST, PUT and DELETE services. This gives you a spot where you can execute any code you want just before (and in the same database session as) every web service. ORDS expects the function to return a Boolean, and it will automatically return a HTTP status of 403 ‘forbidden’ and stop the execution of the handler code if the function returns a value of false.
 
Per the documentation, the parameter ‘procedure.rest.preHook’ : “Specifies the function to be invoked prior to dispatching each Oracle REST Data Services based REST Service. The function can perform configuration of the database session, perform additional validation or authorization of the request. If the function returns true, then processing of the request continues. If the function returns false, then processing of the request is aborted and an HTTP 403 Forbidden status is returned.”

Adding the parameter to the ORDS parameter file is pretty straight forward. Add the following entry to your defaults.xml (or add it to a specific connection pool if applicable):
<entry key="procedure.rest.preHook">xxrest.rest_utl.pre_hook</entry>
 
Note: The above entry assumes I have a package called rest_utl with a function called pre_hook, which I created in the schema xxords. Here is the package specification for the above example:

​Grants
The procedure needs to be executable from all schemas that your ORDS services are run from. For example, if you create a Prehook function called REST_UTL_PK.PRE_HOOK in the schema XXORDS, and you are running ORDS services from schemas XXORDS and XXREST, then you must grant execute on XXORDS.REST_UTL_PK.PRE_HOOK to the schema XXREST. If you do not, you will get the following error when running services from XXREST:

​Logging ORDS activity to a database table is probably the subject of a longer post. In short, there are two primary reasons you may want to log invocations of your ORDS services. The first is to track usage. Knowing what methods and resources are being utilized is extremely helpful. You can keep track of which services are and are not being used, which clients are using which services etc.
 
The second reason is to track, and trouble shoot errors that occur during web service execution. Obviously, the Prehook function won’t help us with logging errors raised by our web services because at the time it is called, the service has not yet run. It can, however, be used to perform all of our usage logging.
 
In some cases, we could already do this, by adding the log table insert to our handler logic. This did not, however, work for GET services where our handler was a SQL statement.
 
Let’s look at how we could go about adding table usage logging for all our ORDS services using the Prehook function.

​First you will need to create a logging table with columns that can capture information about the service invocation. Let’s consider the following table called rest_log:

​This table is by no means comprehensive but serves the purpose for this example. Our Prehook function to populate this table could look something like this:

​That’s not a lot of code to log every ORDS web service call!
 
You may notice in the above example that I am tracking a custom HTTP Header ‘X_USER_NAME’. This is to illustrate that you may want to introduce custom http headers so that you can track additional attributes about the client.

​Setting database session context is a particularly good use of this parameter. Let’s say you are operating in an Oracle e-Business Suite (EBS) environment. Each call to a web service needs to set the EBS session context based on the user and responsibility. This ensures the correct user and operating unit is used when querying data or calling EBS public APIs to create and update records. This is typically performed using the EBS API fnd_global.apps_initialize.
 
Before now, we could do this for POST services by including a call to fnd_global.apps_initialize at the start of the handler logic. This was not possible, however, for a GET based on a SELECT statement. This meant we had to build GET services using a PL/SQL block and emit the JSON manually using APEX_JSON.
 
The Prehook function allows us to call fnd_global.apps_initialize at the start of the session and have that context applied when the web service is called. This is much cleaner.

Of course, there are use cases outside of the EBS world where you need to set session information especially if you are using VPD (Virtual Private Database) or something similar to perform row-based security on your data.

​As the documentation suggests, the primary use for the Prehook function is to perform custom authorization. All you have to do is have the function return false, and ORDS will automatically return a 403 ‘Forbidden’ HTTP response code to the caller. If you use a custom token for authorization, you can use this function to verify the token, user name and password etc.

For example, you may want client X to have access to the GET and POST handler for a resource but not the DELETE handler. In this example, you can use the ‘REQUEST_METHOD’ CGI environment variable to determine the method (GET, POST, PUT or DELETE) and a combination of ‘PATH_INFO’ and ‘SCRIPT_NAME’ to determine the resource. Assuming you pass an identifier for the client in a http header, based on these values you can then decide if the call should be allowed or not.
 
I still recommend you use ORDS standard OAUTH token security as your primary method of authorization. If, however, the client cannot support OAUTH, or you need to supplement standard OAUTH with some additional checks, the Prehook function is a great option.

​I would also like to see a postHook function parameter, this would hopefully give developers access to the resultant http status and allow us to log the final status of each web service call. Ideally, it would also have access to the :body_text and response payloads. This would allow developers to implement a complete table logging solution for ORDS.

​The Prehook function capability is a well thought out feature that will be a big help to ORDS web service developers. Hopefully this post gave you some ideas on how it could be used, although I have no doubt there are many more uses cases out there.

There is an entire section of the ORDS documentation dedicated to this feature which includes examples.
 
P.S. It will be interesting to see if and how the ORDS development team plans to expose parameters like these to multi-tenant PaaS offerings such as Exadata Express. This may prove a challenge given the parameter is currently set in the parameter file on the server. 

​REST enabled SQL was first introduced in ORDS 17.4. It is an important feature in the continued evolution of ORDS as a product. REST enabled SQL fits very well into Oracle’s Cloud mantra, in that it enables functionality much akin to a database link but without incurring the resource overhead and the security shortfalls of database links. It essentially provides developers to run Queries, DML, DDL and PL/SQL  statements against an Oracle database via a REST interface.

GraphQL is a relatively new open-source technology, introduced by Facebook. GraphQL is a data query and manipulation language for APIs, and a runtime for fulfilling queries with existing data.

​While the relationship between GraphQL and REST enabled SQL may not be immediately obvious, I believe there are many similarities and plan to describe them in this post.

From Wikipedia: “GraphQL is an open-source data query and manipulation language for APIs, and a runtime for fulfilling queries with existing data. GraphQL was developed internally by Facebook in 2012 before being publicly released in 2015. On 7 November 2018, the GraphQL project was moved from Facebook to the newly-established GraphQL foundation, hosted by the non-profit Linux Foundation”.
 
Essentially, GraphQL allows a front-end developer to generate a dynamic data query without needing to know the underlying database technology. Much like REST services therefore, GraphQL abstracts the developer from the underlying database. Because of its dynamic nature, however, GraphQL removes the need to build a REST end-point to cover every resource and data element. Instead, you build one back end end-point to handle all requests. You then dynamically build your query on the front end (using a special JSON format) and submit your query to the server for execution. This avoids the overhead of calling multiple REST resources and then consolidating the responses.
 
GraphQL is not a database, it is a query language that allows you to inquire on data. It requires a backend (think middleware server) that handles the query and dispatches the query to the appropriate database(s) to get the actual data. These databases could be MySQL, Oracle, Postgress etc. An example of a GraphQL API server is an open source product called Prisma.

​GraphQL allows the client to decide exactly what data it wants. If you are just looking for the customer name and number, you can limit your GraphQL query to just those fields. Most REST services would force you to retrieve all attributes of a customer this bloating the payload.
 
GraphQL is strongly typed and each type describes a set of available fields. This not only facilitates descriptive error messages but also allows you to inquire on the types that are supported by the database.
 
So, what does a GraphQL query look like?

​You can see that the query itself defines the shape of the data that should be returned.
 
This all sounds great but the GraphQL server does not come for free. It obviously needs to understand everything about the underlying databases. Forgetting the infrastructure requirements, the work of mapping all of that data can be a big hurdle.
 
So how can Oracle RAD stack developers achieve some of the benefits of GraphQL using the software they already own? ORDS and REST enabled SQL to the rescue.

REST enabled SQL allows a client to send any SQL statement to an Oracle database and have the database execute that statement.
These statements can include:

• SELECT, UPDATE, DELETE
• PL/SQL Blocks
• SQL*Plus and SQLCL Commands

​Attributes of REST Enabled SQL that are similar to Graph include:

• The client can inquire on what tables and views are available in the database by inquiring on the data dictionary.
• The client can inquire on the structure of specific tables and views and receive detailed information on each column (i.e. columnName, jsonColumnName, type, precision, scale and isNullable)
• The client can request exactly what data it wants by defining it in the SQL statement.
• REST enabled SQL uses a single end-point “https://<HOST>/ords/<SchemaAlias>/_/sql“ for all inquiries (not multiple separate REST services).
• Because it is using the Oracle database, the data is strongly typed.
• You can join multiple tables (an or use views) to consolidate data into one request that would otherwise have required you to call multiple REST resources and then merge the data together.

 
​An interesting point to note here is that much like with GraphQL, a ‘smart’ client can navigate an inquire on a database with no pre-programmed knowledge of the structure of the database.
 
Other attributes of REST Enabled SQL:

• Queries can be expressed as a simple select statement ‘SELECT SYSDATE FROM DUAL’ or by passing a structured JSON document.
• REST enabled SQL can be secured by:
  • Schema Authentication (the database schema user name and password).
  • OAuth 2 Client Credentials (client gets a token and then uses that token for subsequent calls)
  • First Party Authentication (Basic Authentication). Utilize a user and password created in Oracle REST Data Services with the SQL Developer role.

 
Probably the biggest mismatch between GraphQL and REST Enabled SQL is that REST enabled SQL assumes the client/developer knows the Oracle database. While this may sound like heresy to the open source world, in the RAD stack world this is not a bad thing. In fact, embracing the power of the Oracle database is something every RAD stack developer should be looking to do.

​To enable REST enabled SQL for your instance, you need to add the following entry to the ORDS parameter file on your server (typically defaults.xml):
<entry key="restEnabledSql.active">true</entry>
 
In order to use REST enabled SQL for a specific schema, you also need to REST enable the schema, just like you would to allow creation of any ORDS services in a schema.

• Connect to the Schema
• Run the command: exec ords.enable_schema;

 
Important Note: As mentioned in the documentation “Enabling the REST- Enabled SQL service enables authentication against the Oracle REST Data Service enabled database schemas. This makes the database schemas accessible over HTTPS, using the database password. Oracle highly recommends that you provide strong secure database passwords”.

In view of this warning described above, I advise that you create an entirely separate schema for use with REST enabled SQL (and make the schema password very complex). This at least limits your exposure to that one schema. Added to that, you should also avoid using Schema Authentication (i.e. use schema name and password) whenever possible and instead use OAUTH2 Client Credentials Authentication.
 
If a third party needs to access your database using REST enabled SQL, setup OAUTH2 Client Credentials Authentication by performing the following steps:

• Create a new schema e.g. SYSTEMX
• Login as SYSTEMX and run ords.enable_schema to enable ORDS in the schema
• Create the appropriate grants to the schema SYSTEMX for the tables and views you want to provide access to.
• Create an OAUTH client and assign the role ‘SQL Developer’
• Provide the client with the client_id and client_secret values from the table ORDS_METADATA.OAUTH_CLIENTS.
• The client would then use the client id and secret to get a token and use that and would never know the schema password.

​Unfortunately, even when you configure OAUTH2 Client Credentials Authentication you can still use Schema Authentication. I would like to see a way of turning Schema Authentication off for a schema that has OAUTH2 Client Credentials Authentication.

In this example, I am going to be using the ‘application/sql’ Content-Type. This indicates to ORDS that it should expect a SQL statement in the payload. You can also use ‘application/json’ and pass a structured JSON document. You may notice that I am using OAUTH2 Client Credentials security and passing a token with each call.
 
I have created a table called atx_ev_charging_stations which contains a list of the electric vehicle charging stations in Austin. The first think I need to do is get a definition of the table. REST enabled SQL will always return a metadata array in the response, this describes the columns in the SELECT statement. All I need to do is pass a SQL statement that I know will return no rows and I can get the metadata e.g. 'select * from  atx_ev_charging_stations where 1=2'

​I have just shown the first column in the metadata array, but you can see from this the metadata information that is returned about each column.
 
Now I understand the table structure I can form a query to get the charging station name and first line of the address for charging stations at apartments in the zip code 78704.

​Notice I am only asking for two columns from the table. An equivalent REST resource would need to contain all of the columns in order to fulfill the requirements of all inquiries on this resource:

​In this post, I provided some insight into what GraphQL is and how it can be compared to ORDS and REST enabled SQL. I also provided a high-level overview of REST enabled SQL along with a basic example. From this, I hope to convey what a powerful feature REST enabled SQL is and how you may be able to use it in your applications.