Super Quick Oracle REST Service with OAuth2 and client_credentials

I had the need to allow system A to talk to system B via a REST service. The data was sensitive and powers above me requested that system A use OAuth2 to connect to system B. This REST service call does not involve an end user, it's system A pulling a CSV extract from system B. There are many ways to protect this, but the decision was to use OAuth2. Below is a cookbook on how to do this. This example assumes you have ORDS 3.x installed with the ORDS_PUBLIC_USER and ORDS_METADA schemas configured.

I am running all commands as the user ANTON.

1. For this example I will create a data source for our query:
   -- create the table in the ANTON database schema
   create table anton_table (c1 varchar2(500), c2 varchar2(500) );

   -- insert some sample data
   insert into anton_table (c1, c2)
   select owner, table_name

   from all\_tables  
   where rownum <= 20;
   

   -- commit
   commit;

2. Enable REST on the ANTON schema:

   DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN

   ORDS.ENABLE_SCHEMA(p_enabled => TRUE,

                      p\_schema => 'ANTON',  
                      p\_url\_mapping\_type => 'BASE\_PATH',  
                      p\_url\_mapping\_pattern => 'anton',  
                      p\_auto\_rest\_auth => FALSE);  
   
   commit;
   

   END;

   -- check to see that it worked
   select id, parsing_schema from user_ords_schemas;

3. Define a REST Module:

   begin ORDS.DEFINE_MODULE(
   p_module_name => 'antonModule',
   p_base_path => '/antonmodule',
   p_items_per_page => 25,
   p_status => 'PUBLISHED',
   p_comments => NULL );

commit;
end; 5. Define a template. This is a URL pattern associated with the module "antonModule." Bind variables are contained in squiggly brackets: {bindVariableName}.

begin    
  ORDS.DEFINE\_TEMPLATE(  
   p\_module\_name => 'antonModule',  
   p\_pattern     => '/sqltest/{abc}/{def}',  
   p\_priority    => 0,  
   p\_etag\_type   => 'HASH',  
   p\_etag\_query  => NULL,  
   p\_comments    => NULL );  

  commit;  
end;  

**This is interesting so take note**! Notice my bind variables are abc and def. I tried using c1 and c2 but it seems that the bind variable names can not have numerals in them. Developer beware!

1. Define a REST Handler based upon a sql query that takes two bind variables (abc and def) and returns a CSV file:

   begin
   ORDS.DEFINE_HANDLER(
   p_module_name => 'antonModule',
   p_pattern => '/sqltest/{abc}/{def}',
   p_method => 'GET',
   p_source_type => ords.source_type_csv_query,
   p_source => q'[select c1, c2 from anton_table where c1 = :abc and c2 = :def ]',
   p_items_per_page => 25,
   p_mimes_allowed => NULL ,
   p_comments => NULL );

   commit;
   end;

   This is worth repeating: Notice my bind variables are abc and def. I tried using c1 and c2 but it seems that the bind variable names can not have numerals in them. Developer beware!

2. At this point you can test your service:

   http://localhost:8080/ords/anton/antonmodule/sqltest/ANTON/ANTON_TABLE

3. Define a privilege to protect it with OAuth2:

   declare

   l_roles owa.vc_arr;
   l_patterns owa.vc_arr;
   l_modules owa.vc_arr;
   begin

   -- l_roles intentionally left empty

   -- populate arrays
   l_modules(1) := 'antonModule';

   ORDS.DEFINE_PRIVILEGE(
   p_privilege_name => 'antonpriv',
   p_roles => l_roles,
   p_patterns => l_patterns,
   p_modules => l_modules,
   p_label => 'antonTestingPriv',
   p_description => 'anton testing priv',
   p_comments => null);

   commit;

   end;

4. Now you will find it protected:

   http://localhost:8080/ords/anton/antonmodule/sqltest/ANTON/ANTON_TABLE

5. Create a client that is allowed to access it:

begin  
  OAUTH.create\_client(  
   p\_name            => 'antonclient',  
   p\_grant\_type      => 'client\_credentials',  
   p\_owner           => 'anton',  
   p\_description     => NULL,  
   --p\_origins\_allowed => NULL, -- param name depends on ORDS version  
   p\_allowed\_origins => NULL,   -- param name depends on ORDS version  
   p\_redirect\_uri    => 'http://localhost:8080/redirect',  
   p\_support\_email   => 'anton@somewhere.com',  
   p\_support\_uri     => 'http://localhost:8080/support',  
   p\_privilege\_names => 'antonpriv');  

   commit;  
end;

1. Get the client_id and client_secret. You will need to log in as a user that has access to select from the ords_metadata tables (e.g. ORDS_METADATA or SYSTEM).

select \* from ords\_metadata.oauth\_clients;

1. If you want to be able to do this from an http (not https) URL (which you should NEVER do in production--this is just for testing!!):

\-- turn off need for SSL  
1.    Locate the folder where the Oracle REST Data Services configuration is stored.  
2.    Edit the file named defaults.xml.  
3.    Add the following setting to the end of this file just before the </properties> tag.  
4.    <entry key="security.verifySSL">false</entry>  
5.    Save the file.  
6.    Restart Oracle REST Data Services if it is running.

1. Test getting a bearer token

curl -i -d "grant\_type=client\_credentials" --user "\[client\_id\]:\[client\_secret\]" http://localhost:8080/ords/anton/oauth/token  

You should receive a response like this:  

HTTP/1.1 200 OK  
Content-Type: application/json  
X-Frame-Options: SAMEORIGIN  
Transfer-Encoding: chunked  

{"access\_token":"Zc9b9HDoP9rUKB189Bf1Yg..","token\_type":"bearer","expires\_in":3600}  


Notes:  
\-- curl is a tool for making http(s) requests from the command line  
\--  -i allows you do to this as http -- though you SHOULD use https  
\--  -d allows you to pass in data  
\--  --user allows you to pass a user:password for basic authentication  
\--  then pass the appropriate URL to get a bearer token

1. Test getting your CSV

curl -i --header "Authorization: Bearer \[token from step 10\]" http://localhost:8080/ords/anton/antonmodule/sqltest/ANTON/ANTON\_TABLE --output anton.csv