Use SODA for REST with OAuth Client Credentials

You can access SODA for REST on Autonomous Database using OAuth authentication. Depending on your application, accessing SODA for REST with OAuth authentication can improve performance and security.

Perform the following steps to use OAuth authentication to provide limited access to SODA for REST on Autonomous Database:

  1. As the ADMIN user, access Database Actions and create a user with the required privileges.
    1. Access Database Actions as ADMIN.
      See Access Database Actions as ADMIN for more information.
    2. In Database Actions, click navigation icon to show the available actions.
    3. In Database Actions, under Administration select Database Users.
    4. Click Create User.
    5. In the Create User area, on the User tab enter User Name and a Password and confirm the password.
    6. Select Web Access.
    7. In the Create User area, select the Granted Roles tab and grant DWROLE to the user.
    8. Click Create User.
  2. Use a SQL worksheet in Database Actions to grant user privileges required to load data.
    1. Access Database Actions as ADMIN.
      See Access Database Actions as ADMIN for more information.
    2. In Database Actions, click navigation icon to show the available actions.
    3. In Database Actions, under Development click SQL to open a SQL worksheet.
    4. Grant user privileges required to load data to the user from Step 1.
      GRANT UNLIMITED TABLESPACE TO user_name;
  3. Sign out as the ADMIN user.
  4. Sign in to Database Actions as the user that is setting up to use OAuth authentication.
  5. In Database Actions, use a SQL worksheet to register the OAuth client.
    1. Register the OAuth client.
      For example, enter the following commands into the SQL worksheet, where you supply the appropriate values for your user and your client application.
      BEGIN
        OAUTH.create_client(
          p_name            => 'my_client',
          p_grant_type      => 'client_credentials',
          p_owner           => 'Example Company',
          p_description     => 'A client for my SODA REST resources',
          p_support_email   => 'user_name@example.com',
          p_privilege_names => 'my_priv'
        );
       
        OAUTH.grant_client_role(
          p_client_name => 'my_client',
          p_role_name   => 'SQL Developer'
        );
       
        OAUTH.grant_client_role(
          p_client_name => 'my_client',
          p_role_name   => 'SODA Developer'
        );
        COMMIT;
      END;
      /
    2. In the SQL worksheet, click Run Script to run the command.

    See OAUTH PL/SQL Package Reference for more information.

    This registers a client named my_client to access the my_priv privilege using OAuth client credentials.

  6. Obtain the client_id and client_secret required to generate the access token.
    For example, in the SQL worksheet run the following command:
    SELECT id, name, client_id, client_secret FROM user_ords_clients;
  7. Obtain the access token. To get an access token you send a REST GET request to database_ORDS_urluser_name/oauth/token.

    The database_ORDS_url is available by clicking COPY URL in the RESTful Services and Soda area on the Autonomous Database Service Console. See Access RESTful Services and SODA for REST for more information.

    In the following command, use the client_id and the client_secret you obtained in Step 6.

    The following example uses the cURL command line tool (http://curl.haxx.se/) to submit REST requests to Autonomous Database. However, other 3rd party REST clients and libraries should work as well.

    You can use the cURL command line tool to submit the REST GET request. For example:

    > curl -i -k --user SBA-iO9Xe12cdZHYfryBGQ..:vvUQ1AagTqAqdA2oN7afSg.. --data "grant_type=client_credentials"https://mqssyowmqvgac1y-doc.adb.region.oraclecloudapps.com/ords/user_name/oauth/token
    HTTP/1.1 200 OK
    Date: Mon, 22 Jun 2020 15:17:11 GMT
    Content-Type: application/jsonTransfer-Encoding: chunked
    Connection: keep-alive
    X-Frame-Options: SAMEORIGIN  
    
    {"access_token":"JbOKtAuDgEh2DXx0QhvPGg","token_type":"bearer","expires_in":3600}

    To specify both the client_id and the client_secret with the curl --user argument, enter a colon to separate the client_id and the client_secret. If you only specify the user name, client_id, curl prompts for a password and you can enter the client_secret at the prompt.

  8. Use the access token to access the protected resource.

    The token obtained in the previous step is passed in the Authorization header. For example:

    > curl -i -H "Authorization: Bearer JbOKtAuDgEh2DXx0QhvPGg" -X GET https://database_id.adb.region.oraclecloudapps.com/ords/user_name/soda/latest
    HTTP/1.1 200 OK
    Date: Mon, 22 Jun 2020 15:20:58 GMT
    Content-Type: application/json
    Content-Length: 28
    Connection: keep-alive
    X-Frame-Options: SAMEORIGIN
    Cache-Control: private,must-revalidate,max-age=0
    
    
    {"items":[],"hasMore":false}

See Configuring Secure Access to RESTful Services for complete information on secure access to RESTful Services.