Forum Stats

  • 3,768,656 Users
  • 2,252,827 Discussions


OAuth token - 401 Unauthorized



  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,062 Employee

    open a service request with My Oracle Support.

    You'll need a test case, on standalone (no Tomcat) - where you

    rest enable the schema

    create the restful service

    create the role, privilege

    create the oauth client

    grant the oauth client the role

    call the service with cURL

    The only other weird thing here is that I saw you protected the entire hr/* pattern with that privilege - what instead you tied the privilege to the specific MODULE instead?

    And since you mentioned it, what happens if the rest enabled schema alias matches the schema? Maybe there's a bug there, but that would be unexpected as most folks are aliasing their schemas, oauth or no oauth.

  • partlycloudy
    partlycloudy Member Posts: 8,073 Silver Trophy

    What's the fastest way to setup the test case for Support? Can you point me to instructions? Leave APEX aside. I just want a minimal set of steps to reproduce the oauth token issue. Thanks

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,062 Employee

    See the list above...those should be shown/demonstrated with the raw sql/plsql/api calls.

    the service can just be a hello world type example

    in creating your test case, it's very likely you'll stumble upon what went wrong with your first scenario.

  • partlycloudy
    partlycloudy Member Posts: 8,073 Silver Trophy

    Right, I understand that part. What I don't know is how I can start with

    1. ords.war
    2. Rename to apex.war
    3. Listen on port, say, 8080
    4. Point it to my defaults.xml file containing connection strings for the Oracle database
    5. Run the list of PL/SQL commands above to a) enable REST b) add Hello World service/module/template/handler/role/privelege/client
    6. Use cURL to test

    I need help with Steps 1-4

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,062 Employee

    Good news - I didn't include those things in the required steps b/c those things aren't required to setup your test case. So you can skip straight to Step 5.

  • partlycloudy
    partlycloudy Member Posts: 8,073 Silver Trophy
    edited Feb 17, 2021 1:39PM

    Here is the entire script to set everything up. Still the same error from cURL

    @thatJeffSmith-Oracle Is this sufficient for Oracle Support to investigate?

    -- Enable REST access to EDATA schema using /apex/edbrest
      p_enabled       => TRUE,
      p_schema       => 'EDATA',
      p_url_mapping_type  => 'BASE_PATH',
      p_url_mapping_pattern => 'edbrest',
      p_auto_rest_auth   => FALSE
    -- Verify
    select * from user_ords_schemas
    -- Define REST module to house all API endpoints
      p_module_name  => 'api-v1',
      p_base_path   => 'api-v1/',
      p_items_per_page => 0);
    -- Verify
    select * from user_ords_modules
    -- Define template for testing headers
      p_module_name  => 'api-v1',
      p_pattern    => 'testing/headers/');
    -- define handler  
      p_module_name  => 'api-v1',
      p_pattern    => 'testing/headers/',
      p_method     => 'GET',
      p_source_type  => ORDS.source_type_plsql,
      p_source     => q'[BEGIN
                   HTP.p('List from PRINT_CGI_ENV including <br /> terminator:');
      p_items_per_page => 15); 
    -- Verify
    select * from user_ords_templates;
    select * from user_ords_handlers;
     l_roles   OWA.VC_ARR;
     l_modules  OWA.VC_ARR;
     l_patterns OWA.VC_ARR;
    -- define role and privilege
     ORDS.CREATE_ROLE(p_role_name => 'test_role');
     l_roles(1) := 'test_role';
     l_modules(1) := 'api-v1';
     l_patterns(1) := '/testing/*';
       p_privilege_name => 'test_priv',
       p_roles     => l_roles,
       p_patterns    => l_patterns,
       p_modules    => l_modules,
       p_label     => 'Test oauth token'
    -- verify
    select * from user_ords_privileges;
    select * from user_ords_roles;
    -- create oauth client
      p_name      => 'oauth_client',
      p_grant_type   => 'client_credentials',
      p_owner      => 'Owner',
      p_description   => 'Test REST',
      p_support_email  => '[email protected]',
      p_privilege_names => 'test_priv'
    -- verify
    select * from user_ords_clients
    -- grant client role
      p_client_name => 'oauth_client',
      p_role_name  => 'test_role'
    -- verify
    SELECT name,client_name
    FROM  user_ords_client_privileges;
    SELECT client_name, role_name
    FROM  user_ords_client_roles;
    -- get client id/secret
    select name,client_id,client_secret
    from user_ords_clients
  • partlycloudy
    partlycloudy Member Posts: 8,073 Silver Trophy

    Here is the cURL command, returns 401 error

    curl -i -k 'https://[obfuscated]/apex/edbrest/oauth/token' --user client_id:client_secret --data 'grant_type=client_credentials'

    401 Unauthorized

    2021-02-17T01:27:08.378Z | Juw5qadNZZ3_N8K8ioSUjA

    Access to this resource is protected. Please sign in to access this resource.

    Debug Trace

     is not authorized to access: oracle.dbtools.oauth.client.application
     does not have any of the required roles: AnnotationPrivilegeConstraint [name=oracle.dbtools.oauth.client.application, roles=[OAuth2 Client Application], challenges=[]]
    [TE] POST /apex/edbrest/oauth/token start: 2021-02-17T01:27:08.378Z duration: 32ms

    Stack Trace

    UnauthorizedException [statusCode=401, reasons=[]]
    	at oracle.dbtools.http.auth.RequestAuthorizationProvider.authorize(
    	at oracle.dbtools.http.auth.AuthorizationDispatchHook.before(
    	at oracle.dbtools.http.dispatch.hooks.DispatchHookChain.before(
    	at oracle.dbtools.http.dispatch.hooks.DispatchHooks.before(
    	at oracle.dbtools.http.entrypoint.Dispatcher.dispatch(

  • User_AB36P
    User_AB36P Member Posts: 1 Blue Ribbon

    Did you get the issue resolved? I am having the same issue as well.

  • Danny*D201
    Danny*D201 Member Posts: 114 Bronze Badge
    edited Sep 22, 2021 2:58AM

    I run ords 19.4 on tomcat, no OAuth, had this error and fixed it with creating a new ORDS user with access to the role required by the ords privilege.

    For example, if test_role is the required role by the test_priv

    C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps>java -jar ords.war user test_user test_role

    Enter a password for user test_user:

    Confirm password for user test_user:

    2021-09-22T02:09:45.522Z INFO  Created user: test_user in file: C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps\config\localsetup\ords\credentials

    And restarted Tomcat...

    Change Windows syntax to Linux if you need, key is the "java -jar ords.war user test_user test_role"

    Before I did this, I got the same error, afterwards I was able to login with the test_user and access the web service.

    Hope it helps