OCI & ORDS: Setting up your database REST service and securing it with OAUTH2 (no scripts required!)
Intro
When building your REST APIs using Oracle Rest Data Services on an Oracle database, APEX can greatly simplify the steps to set up the REST functions we need. APEX leverages ORDS tools to wrap any SQL statement, PL/SQL function or package procedure into a REST call, enabling the client applications to interact with the database using JSON objects and common HTTP verbs/methods such as GET, PUT, POST, DELETE (I call them verbs and methods interchangeably here, but they are, strictly speaking, methods). Well, APEX allows us to wrap a SQL object with a REST call with almost no code.
Almost.
Because if we need to secure access to our resources using OAUTH2 we eventually need to do some additional configuration steps on the database, and this involves setting up manually some SQL scripts and running them.
A few words about OAUTH2
OAUTH2 (or oAuth 2) stands for Open Standard Authorization Framework 2. It's a framework to implement the authorization service (NOT authentication, which is another topic) to access and use resources exposed from web applications. In other words, it assigns the client application the privileges to use our REST APIs. It's up to us to define the privileges needed to run our APIs. Since OAUTH2 grants a standard way to access REST services to other applications, it's commonly used in mobile applications like Android and iOS apps, web applications backends, microservices communications and so on.
Hands-on
If you have ever tried to use APEX to secure your REST API with OAUTH2, you have probably met these PL/SQL scripts to configure and run:
--this one registers the client for the REST API
begin
oauth.create_client(
p_name => 'your client name',
p_grant_type => 'client_credentials',
p_description => 'some kind of description',
p_support_email => 'e-mail address',
p_privilege_names => 'the name of the privilege');
commit;
end;
oauth.create_client(
p_name => 'your client name',
p_grant_type => 'client_credentials',
p_description => 'some kind of description',
p_support_email => 'e-mail address',
p_privilege_names => 'the name of the privilege');
commit;
end;
begin
oauth.grant_client_role(
p_client_name => 'your client name',
p_role_name => 'the role name you created'
);
commit;
end;
This time I'll show a solution to get the same result on an OCI Autonomous Database instance using Oracle Database Actions (Formerly known as "SQL Developer Web", it might have a different name when you read this post, as it seems that there is a bonus in Oracle when they change a product name).
The main difference with the use of APEX is that I will not need to write and run any script, which makes the whole thing a little more friendly.
TIER 1: Defining a simple REST endpoint
Of course, you need to work on a database with a REST-enabled schema. You can enable a schema by logging into the ADMIN account and properly setting the properties of the schema you want to use.
After that, log in to the schema and let's start writing a simple query to change into a REST endpoint: from our ADB console select "Database actions" and then in the "Development" area select "REST"
Our aim is to REST-erize a query against the table SH.CUSTOMERS, returning a JSON object representing a customer using a GET request, the query is:
select c.cust_id,
c.cust_first_name,
c.cust_last_name,
c.cust_city,
c.cust_credit_limit,
c.cust_income_level,
c.cust_state_province
from SH.customers c
where c.cust_id=:id
The steps to set up a REST endpoint with Database Actions are pretty straightforward, each one is ordered into a sequence by the wizard.
As with APEX, the first step is to create a module:
Click on the button "Create module", this shows a modal pop-up window from the right
Choose a module name, mine is a simple one from the usual proposed name.
Choose the base path for the REST API, mine is the one proposed by default
Be sure to set the option "Published" to on
Leave "Pagination size" to 25. In some cases, you need to set it to zero, I'll tell you about this in a future post about how to get a nested JSON response
Select "not protected" (we'll do it later on) from the list box
Check "Go to module after creation" to jump to the module definition after
Click on "Create"
Now the wizard takes you to the module management page, where you create the template for the REST endpoint URI
Click on "Create template", once again a modal pop-up window shows up from the right
in the "URI template" text we define:
- the API endpoint.
- the parameter(s) to pass to the service if any. In this case, we define the ":id" parameter which matches the parameter used in the select statement described above.
We are going to select a customer by its id matching the URI parameter with the one needed by the query. By the way, it is a good practice to avoid the use of the HTTP verbs in the URI definition, such as "getcustomer", "postcustomer" etc.
Be sure to check "Go to Handlers after creation" and click "Create"
Ok, now we are going to the core of the service definition binding the service call to the SQL procedure, then getting the query output and returning it in JSON format to the client
click on "Create handler" and the handler definition window pops up
Here we define:
- the HTTP method the handler responds to. Notice that the only allowed HTTP methods allowed are GET, POST, PUT, and DELETE that map the four Create, Read, Update, and Delete operations. Since this is a read operation we choose GET.
- For the source type of the data, in this case, we are using a simple query statement returning a collection (of one row in this case), therefore we choose "Collection Query" from the list
- The source of our SQL statement. Notice the matching between the ":id" parameter names, the same both in the query and the URI.
Click on the "Create" button and the REST service is ready to work.
If we go back to the "Modules" page we can see the template we have created, open it and get to the handler for our REST call (the icon colour has a meaning)
in other words, it calls the REST service and shows the response in the browser in a raw, ugly format. The second icon copies the URL in the clipboard, and that's what we want. Copy the URL and open the old, good Postman to test the REST service.
On Postman, open a new tab and paste the URL you copied. Check that GET is the method selected, notice that Postman detects the ":id" parameter in the URL and shows the path variables panel with a key/value grid to assign values for the parameters. Try with 1445 and send the request.
You'll get the response body with the information about Zylia Reid in a pretty JSON format, directly from our ADB database.
TIER 2: enabling OAUTH2 on our REST service
Now we'll get to the next level, securing our REST endpoint with OAUTH2. The steps involved are pretty similar to those you make if you are using APEX. We are going to create an OAUTH2 Client Credential set since our goal is to grant access to an external application, such as a mobile app using our endpoint, not a user.
We do this by assigning to our client app two pieces of information, a client ID and a client secret.
Step 1: Create a role
The first thing is to create a role, to which we'll assign a privilege later. To do this, on the "Database Actions launchpad" select "REST" and then "Roles"
On the role page, you can see all the roles currently existing. Click on "Create role" on the upper right of the page
A modal window pops up, where you give a name for the role you want to create. I'm not very good when it comes to naming objects and I don't actually know if there is a sort of naming convention for this, so I'll simply call this oracle.rest.role.mobile
If you toggle "Show code" you can see the SQL statement that is used to create the role (nice to know)
Click on "Create" and the new role is shown in the list.
Step 2: Create a privilege
Go back to the "Database Actions launchpad" select "REST" and then "Privileges", these steps are similar to those we just made to create a role.
On the privileges page, you can see the privileges currently existing. Click on "Create privilege" on the upper right of the page
Now the window to create the privilege appears from the right of the page, it consists of four sections or panes:
Privilege Definitions, here we define:
- a Label for the privilege, let's say "Mobile services privilege"
- a Name for the privilege, e.g. oracle.rest.privilege.mobile
- a Description of the privilege, let's say "privilege for mobile applications services"
- an optional comment
Roles, here we define the roles to bind our privilege to, in this case, we choose only oracle.rest.role.mobile and move it to the right list
Protected Modules, same as Roles, here we choose the modules we want to protect. In this case, we choose the module previously created for the REST service at the beginning of this post.
Protected Resources: nothing to do here
Click on "Create"
Now, if you go back to the "Modules" page, you'll notice that the module icon has turned from orange to green. This means the module is now protected.
if you click on the three dots on the upper right and edit the module, you can see that now it is protected by the privilege we defined
Step 3: Create a client
Now, to enable our client application(s) to use the REST service, we need to create a "client" to define a client secret and a client ID that can be used to grant access to the REST service. On "Database Actions" go back to the main page of the REST section and select "Clients"
Then select "Create OAuth Client"
This will show up on the form to configure all the information needed to create our client. The form consists of four sections or panes:
Client definition, here we define:
- a name for the client, let's say "mobile_service_client"
- a description for the client, you can choose whatever you want
- a support (help) URI, you can choose whatever you want, I wrote down the default
- a support (help) email, you can choose whatever you want, I wrote down the default
Roles, here we choose the role we set up previously, i.e. oracle.rest.role.mobile and move it to the list on the right:
Allowed Origins, in this case, you can leave it empty
Privileges: here we choose the privilege we previously created, i.e. oracle.rest.privilege.mobile and move it to the list on the right
then click on "Create"
The following page shows the credential generated by the system to use in our client application, the "Client ID" and the "Client Secret"
If you click on the eye icon you can see the value of the credentials, while the copy icon allows you to copy the value and paste it where you need to use it.
There is some more thing we need, the so-called "Access token URL", which is a URL where to exchange the credentials for an access token. Click on the three dots on the upper right and choose "Get Bearer Token" (a bearer token is a type of access token)
The pop-up window shows several pieces of information. Here we find:
- the current token to use for authentication, generated by the client ID and the client secret
- its expiration date (yes, tokens expire and need to be gotten again)
- a button to get a new token
- a curl command to copy and paste in a terminal window to get the new token. Here we find what we need, the "Access token URL". Noteworthy, any URL of this kind ends with /OAuth/token
Now copy the access token URL, the client ID and the client secret into a text editor.
If you like, try to run the curl command in a terminal window and look at the response information it sends back. They may be quite hard to understand for someone, but they carry out some useful information I'm going to use in the next steps. Those pieces of information drive the following configuration of Postman.
Tier 3: configure the credentials on the Postman test instance
Now go back to Postman and try to call again the REST service, this time you'll end up with something like this:
Now let's configure Postman to use the requested authorization for the REST call.
On Postman, go to the "Authorization" tab and enter the information needed to configure it as a client to the REST call:
- Type: in the authorization type select "OAuth 2.0" from the list
- Add authorization to: select "Request Headers"
- Configure New Token: in the "Configuration Options" tab set the values as follows (you probably need to scroll to see all settings):
- Token Name: whatever you like to name it
- Grant Type: choose "Client Credentials"
- Access Token URL: the URL you pasted in the text editor from the client configuration of the REST service
- Client ID: the client ID you pasted in the text editor from the client configuration of the REST service
- Client Secret: the client secret you pasted in the text editor from the client configuration of the REST service
- Scope: leave this empty
- Client authentication: select "Send as Basic Auth header" from the list
After setting all the options, click on "Get New Access Token" to get a new token and set it in the parameters of the REST call. Under the hood, it performs the same operation you see in the curl command above. Click on "Use Token" to add it to the header parameters.
Bonus: use this REST service in a simple Python program
As a sample usage, I'll show a simple Python program that calls the REST service we made.
The following code is from David Sha's GitHub repo. I made some changes to have a different output format:
from requests import request import json from datetime import datetime # some constants SPACE = " " CLIENT_ID = "your client ID" CLIENT_SECRET = "your client secret" ITEM_ID = "1445" # set call to obtain access token url = "https://[your access token URL]" data = { "grant_type": "client_credentials", "scope": "oapi" } # execute the call to obtain access token: notice the usage of the POST verb response = request("POST", url, auth=(CLIENT_ID, CLIENT_SECRET), data=data) print(json.dumps(response.json(), indent=4)) access_token = response.json()["access_token"] token_type = response.json()["token_type"] # call the REST service using the token now = datetime.now().isoformat() url = f"https://[your REST service URL]/{ITEM_ID}" headers = { "Authorization": token_type + SPACE + access_token, "Content-Type": "application/json" } response = request("GET", url, headers=headers) print(json.dumps(response.json(), indent=4))
the usage is pretty straightforward:
first, the script sets up the parameters needed to call the access token URL to obtain the token. Notice that this is a POST request.
Then the script sets the authorization key in the header parameters and calls the REST service.
The response with the data returned by the service is printed to the console in JSON format
And that's all for this post.
OAuth 2.0 is obviously only a little part of the security you need to implement. Do not forget that REST APIs are web applications in the end and they need the same level of security as any non-trivial web application, such as API gateways, service mesh and so on. The Oracle Cloud Infrastructure implements all the security features to get the right job done.
Other resources:
Commenti
Posta un commento