For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Am a DBA, i would like to upgrade our current oracle 11.2 0.1 to 12.2 0.1 on linux redhat 7.
I would appreciate if anyone has a step by step document on that. Thanks
Anyone? @kris-rice-oracle @thatjeffsmith-oracle
tell us what you're doing, in detail
I thought I did. I followed the articles I linked to setup a REST service. When it is not secured using a role/privilege, it works fine. When I add a client using the OAUTH API and secure the service using a Oauth client credentials, I am unable to access the /oauth/token endpoint to retrieve the Bearer token.
The steps are in this article https://learncodeshare.net/2018/12/10/how-to-secure-your-ords-service/ curl -i -k --user X:Y --data "grant_type=client_credentials" https://apex.oracle.com/pls/apex/vikasa/oauth/token where X:Y are the client_id:client_secret values from the user_ords_clients view returns a 401/Unauthorized error Any ideas?
i'd rather not read through the articles and guess what you did
you mention wls, tomcat...but are you using those?
"I am unable to access the /oauth/token endpoint to retrieve the Bearer token." Show us?
you're on apex.oracle.com - that's hosted, and i'm not sure it supports the oauth2 workflow In general we assume you've installed oracle, apex, and ORDS yourself when posting questions here...if you're using apex.oracle.com, that's a different rig/setup altogether, and better answered/supported over in the APEX specific community or their slack channel
Fair enough. See my last post. On my own environment, I am using Tomcat. But for the purpose of demonstrating the problem, I am using apex.oracle.com. The symptoms I see are identical so I thought it makes sense to use when seeking help on this forum. Anyway, on my own environment, the /ords/hr/employees/7788 endpoint works fine which would indicate that ORDS is setup correctly and talking to the ORDS metadata engine. However, when I use OAUTH.create_client, OAUTH.grant_client_role and then attempt to use the /ords/hr/oauth/token endpoint using curl, I get a 401 error instead of the Bearer token. How can I troubleshoot this?
You should be going somewhere like here to get your token curl -i -k \--user [client_id]:[client_secret] \--data "grant_type=client_credentials"\https://[obfuscated.com/ords/schema/oauth/token
If you're using SQLDev web, we make it easy to get your token, or we'll even generate the cURL command for you https://www.thatjeffsmith.com/archive/2020/11/sqldev-web-securing-restful-services-in-oracle-autonomous-database-with-oauth2/
That talks about autonomous, but will work anywhere you have ords 20.4 going and sqldev web enabled
Yes, that's exactly the curl command I am using but it throws a 401 error! My ORDS version is 19.4
are you sure your client id and secret are right? are you sure your oauth client has the proper privs? apparently not, hence the 401
here's another in-depth example showing exactly what to do https://blogs.oracle.com/developers/microservices-the-easy-way-with-ords-and-micronaut-part-1
ords 19.4 is a year+ old, but nothing's changed here in this area - just saying
OK I followed the steps in that in-depth example but I still get the same error.
Here is the code I used. It ran with no errors. Then I queried user_ords_clients and used the curl command you suggested to get the Bearer token but still got the 401 error What am I missing?
begin ORDS.create_role( p_role_name => 'role_0215' ); ORDS.create_privilege( p_name => 'priv_0215', p_role_name => 'role_0215', p_label => 'EMP Data', p_description => 'Allow access to the EMP data.'); ORDS.create_privilege_mapping( p_privilege_name => 'priv_0215', p_pattern => '/hr/*'); OAUTH.create_client( p_name => 'client_0215', p_grant_type => 'client_credentials', p_owner => 'My Company', p_description => 'Test REST', p_support_email => 'joe@example.com', p_privilege_names => 'priv_0215' ); OAUTH.grant_client_role( p_client_name => 'client_0215', p_role_name => 'role_0215' ); commit; end;
I enabled debug on my ORDS config and here is what I get in Chrome/Postman. I am not sure I understand how oracle.dbtools.oauth.client.application and [Oauth2 client application] role is involved here. The URL I am POST-ing to in Postman is https://server.domain.com/ords/schema-name/oauth/token Any ideas? @thatjeffsmith-oracle
Can you include the entire trace stack?
Also, instead of using postman, can you just try with cURL - it's harder to make mistakes in cURL - by checking some option or something incorrectly...
Here you go
curl -i -k 'https://[obfuscated]/apex/edb/oauth/token' --user client_id:client_secret --data 'grant_type=client_credentials' -w "%{http_code}"
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/edb/oauth/token start: 2021-02-16T15:29:17.187Z duration: 32ms
Stack Trace
UnauthorizedException [statusCode=401, reasons=[]] at oracle.dbtools.http.auth.RequestAuthorizationProvider.authorize(RequestAuthorizationProvider.java:151) at oracle.dbtools.http.auth.AuthorizationDispatchHook.before(AuthorizationDispatchHook.java:40) at oracle.dbtools.http.dispatch.hooks.DispatchHookChain.before(DispatchHookChain.java:34) at oracle.dbtools.http.dispatch.hooks.DispatchHooks.before(DispatchHooks.java:49) at oracle.dbtools.http.entrypoint.Dispatcher.dispatch(Dispatcher.java:122) at oracle.dbtools.http.entrypoint.EntryPoint$FilteredServlet.service(EntryPoint.java:239) at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:73) at oracle.dbtools.http.forwarding.QueryFilteringRewrite.doFilter(QueryFilteringRewrite.java:90) at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47) at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64) at oracle.dbtools.http.forwarding.ForwardingFilter.doFilter(ForwardingFilter.java:68) at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47) at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64) at oracle.dbtools.http.cors.CORSPreflightFilter.doFilter(CORSPreflightFilter.java:68) at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47) at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64) at oracle.dbtools.http.cookies.auth.CookieSessionCSRFFilter.doFilter(CookieSessionCSRFFilter.java:71) at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47) at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64) at oracle.dbtools.http.auth.AuthenticationFilter.authenticate(AuthenticationFilter.java:104) at oracle.dbtools.http.auth.AuthenticationFilter.doFilter(AuthenticationFilter.java:64) at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47) at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64) at oracle.dbtools.url.mapping.RequestMapperImpl.doFilter(RequestMapperImpl.java:160) at oracle.dbtools.url.mapping.URLMappingBase.doFilter(URLMappingBase.java:92) at oracle.dbtools.url.mapping.filter.URLMappingFilter.doFilter(URLMappingFilter.java:140) at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47) at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64) at oracle.dbtools.http.auth.external.ExternalSessionFilter.doFilter(ExternalSessionFilter.java:59) at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
please confirm the REST Web Service AND OAUTH2 client are all defined in the same schema
Yes, they are. To confirm, I queried the USER_ORDS_CLIENTS, USER_ORDS_PRIVILEGES, USER_ORDS_PRIVILEGE_MAPPINGS, etc. views from that schema (edb)
and schema edb also has the module/template in USER_ORDS? If so, i'm running out of ideas.
Yes, all ORDS and OAUTH API calls were run in the same schema. The only thing is that the schema is aliased for REST access. Yikes, if you're out of ideas, what are mere mortals like me to do!
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.
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
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.
Right, I understand that part. What I don't know is how I can start with ords.war Rename to apex.war Listen on port, say, 8080 Point it to my defaults.xml file containing connection strings for the Oracle database Run the list of PL/SQL commands above to a) enable REST b) add Hello World service/module/template/handler/role/privelege/client Use cURL to test I need help with Steps 1-4
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.
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 BEGIN ORDS.enable_schema( p_enabled => TRUE, p_schema => 'EDATA', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'edbrest', p_auto_rest_auth => FALSE ); COMMIT; END; -- Verify select * from user_ords_schemas -- Define REST module to house all API endpoints BEGIN ORDS.define_module( p_module_name => 'api-v1', p_base_path => 'api-v1/', p_items_per_page => 0); commit; end; -- Verify select * from user_ords_modules -- Define template for testing headers begin ORDS.define_template( p_module_name => 'api-v1', p_pattern => 'testing/headers/'); -- define handler ORDS.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 OWA_UTIL.mime_header('text/plain'); HTP.p('List from PRINT_CGI_ENV including <br /> terminator:'); HTP.p('======================================================='); OWA_UTIL.print_cgi_env; END;]', p_items_per_page => 15); commit; end; -- Verify select * from user_ords_templates; select * from user_ords_handlers; DECLARE l_roles OWA.VC_ARR; l_modules OWA.VC_ARR; l_patterns OWA.VC_ARR; -- define role and privilege BEGIN ORDS.CREATE_ROLE(p_role_name => 'test_role'); l_roles(1) := 'test_role'; l_modules(1) := 'api-v1'; l_patterns(1) := '/testing/*'; ORDS.DEFINE_PRIVILEGE( p_privilege_name => 'test_priv', p_roles => l_roles, p_patterns => l_patterns, p_modules => l_modules, p_label => 'Test oauth token' ); l_roles.DELETE; l_modules.DELETE; l_patterns.DELETE; COMMIT; END; -- verify select * from user_ords_privileges; select * from user_ords_roles; -- create oauth client begin OAUTH.create_client( p_name => 'oauth_client', p_grant_type => 'client_credentials', p_owner => 'Owner', p_description => 'Test REST', p_support_email => 'user@example.com', p_privilege_names => 'test_priv' ); end; -- verify select * from user_ords_clients -- grant client role begin OAUTH.grant_client_role( p_client_name => 'oauth_client', p_role_name => 'test_role' ); commit; end; -- 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
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
UnauthorizedException [statusCode=401, reasons=[]] at oracle.dbtools.http.auth.RequestAuthorizationProvider.authorize(RequestAuthorizationProvider.java:151) at oracle.dbtools.http.auth.AuthorizationDispatchHook.before(AuthorizationDispatchHook.java:40) at oracle.dbtools.http.dispatch.hooks.DispatchHookChain.before(DispatchHookChain.java:34) at oracle.dbtools.http.dispatch.hooks.DispatchHooks.before(DispatchHooks.java:49) at oracle.dbtools.http.entrypoint.Dispatcher.dispatch(Dispatcher.java:122)
Did you get the issue resolved? I am having the same issue as well.
No
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
Basic Auth - you're going to be passing the username AND password on every single request, please please please at least ensure you're ALWAYS in HTTPS. It would be much preferable to sort out your OAuth2 workflow issues and use that.
Thanks for the advice Jeff, yes to https, definitely. I was just hoping to provide some clue for OP and others looking at this post to move pass the error without OAuth in the mix. Will invest in setting up OAuth in production systems. Thanks.
FYI the example provided works for vanilla ords setup without apex. It is perhaps good idea to enable _sdw sqldeveloper web (db actions). If it works then it is very likely your rest service will work as well. IF there is somekind of frontend www-server for apex setup, then you need to bypass it if it is doing authentication before letting in to the url in question.
% curl -i -k --user clientidgoeshere:clientsecretgoeshere --data "grant_type=client_credentials" https://fqdn/ordswarrenamedforapi/yourenabledschemapath/oauth/token > oauth2_token.txt % echo "Authorization: Bearer " | tr -d "\n" > header-auth.txt % cat oauth2_token.txt | grep -Eo '"access_token":(\d*?,|.*?[^\\]",)' |awk -F':' '{print $2}' | sed -e 's/",//' |sed -e 's/"//' >> header-auth.txt % cat header-auth.txt Authorization: Bearer youroauth2token % curl -vvv -i -k -H @header-auth.txt https://fqdn/ordswarrenamedforapi/yourenabledschemapath/api-v1/testing/headers
you might want to fix curl ca bundle: https://daniel.haxx.se/blog/2018/11/07/get-the-ca-cert-for-curl/ hmm what was the ords oauth2 revoke token trick :) ? rgrds Paavo
Thanx @thatjeffsmith-oracle about the hint of getting oauth2 bearer token or even example curl for getting oauth2 token. It really rox, see why it is good to get the _sdw working. rgrds Paavo
Hi,
I am getting an access token. When I send an access token in headers to access the web-service I getting 401 unauthorised error.
Thanks,
I am also facing this error, weblogic version 14.1.1, ords version 24.2.3.r2011847.
Were you able to solve this problem? Please share the solution if you did.