# 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}.

```plaintext
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!
```

6.  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!
    
7.  At this point you can test your service:
    
    http://localhost:8080/ords/anton/antonmodule/sqltest/ANTON/ANTON\_TABLE
    
8.  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;
    
9.  Now you will find it protected:
    
    http://localhost:8080/ords/anton/antonmodule/sqltest/ANTON/ANTON\_TABLE
    
10.  Create a client that is allowed to access it:
    

```plaintext
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;
```

11.  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).
    

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

12.  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!!):
    

```plaintext
\-- 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.
```

13.  Test getting a bearer token
    

```plaintext
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
```

14.  Test getting your CSV
    

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