ORDS: how to restore the OAuth client secret

I have ORDS configuration in Dev database, when Dev db is refreshed from PROD db using RMAN or pluggable database feature, the ORDS configuration in DEV is replaced by PROD, and that break the application unless we update the app with new ORDS client secret.

This document discover the way how to

1. export and import ORDS client

2. restore the ORDS client secret to previous value

3. verify this method works.

This demo how to export and import ORDS client on DEV database, further more, modify the ORDS oauth secret back to previous value.

This demo how to export and import ORDS client on DEVDB database, further more, modify the ORDS oauth secret back to previous value.

Collect existing ORDS configuration: run as dba user

1. Which schema has ORDS enabled?

SQL> select s.*,m.pattern,type from DBA_ORDS_SCHEMAS s, DBA_ORDS_URL_MAPPINGS m where s.url_mapping_id=m.id;

ID URL_MAPPING_ID PARSING_SCHEMA STATUS AUTO_REST_ OPS_ALLOWED PRE_HOOK UPDATED_O UPDATED_BY CREATED_O CREATED_BY PATTERN TYPE

---------- -------------- -------------------- ---------- ---------- ----------- ---------- --------- --------------- --------- --------------- -------------------- ----------

10161 10160 TESTUSER2 ENABLED DISABLED 06-JUL-23 JIULUSUN 06-JUL-23 JIULUSUN TESTUSER2 BASE_PATH

10001 10080 TESTUSER1 ENABLED DISABLED 12-JUN-23 TESTUSER1 06-JUN-23 TESTUSER1 hr BASE_PATH

10353 10352 JIULUSUNTEST ENABLED DISABLED 12-JUL-23 JIULUSUNTEST 12-JUL-23 JIULUSUNTEST jiulusuntest BASE_PATH

24845 24844 SPLORDS ENABLED DISABLED 19-AUG-23 SPLORDS 19-AUG-23 SPLORDS web BASE_PATH

2. How Client and Role are mapped:

SQL> select distinct client_id,(select parsing_schema from DBA_ORDS_SCHEMAS where id= cr.CLIENT_SCHEMA_ID) client_schema,client_name, role_id,role_name,(select parsing_schema from DBA_ORDS_SCHEMAS where id= cr.ROLE_SCHEMA_ID) role_schema

from ords_metadata.ORDS_CLIENT_ROLES cr;

CLIENT_ID CLIENT_SCHEMA CLIENT_NAME ROLE_ID ROLE_NAME ROLE_SCHEMA

---------- -------------------- ------------------------------ ---------- -------------------- --------------------

10288 TESTUSER1 emp_client 10284 emp_role TESTUSER1

10180 TESTUSER2 emp_client2 10181 emp_role2 TESTUSER2

3. How privs are mapped.

SQL> select distinct (select parsing_schema from DBA_ORDS_SCHEMAS where id= m.SCHEMA_ID) schema, m.* from ords_metadata.ORDS_PRIVILEGE_MAPPINGS m;

SCHEMA PRIVILEGE_ID NAME PATTERN SCHEMA_ID CREATED_BY CREATED_O UPDATED_BY UPDATED_O

-------------------- ------------ ------------------------------------------------------------ -------------------- ---------- --------------- --------- --------------- ---------

TESTUSER1 10285 emp_priv /employees/* 10001 TESTUSER1 06-JUL-23 TESTUSER1 06-JUL-23

TESTUSER1 10003 oracle.soda.privilege.developer /soda/* 10001 TESTUSER1 06-JUN-23 TESTUSER1 06-JUN-23

TESTUSER2 10190 emp_priv /emp/* 10161 TESTUSER2 06-JUL-23 TESTUSER2 06-JUL-23

TESTUSER2 10182 emp_priv2 /employees/* 10161 TESTUSER2 06-JUL-23 TESTUSER2 06-JUL-23

SPLORDS 24847 oracle.soda.privilege.developer /soda/* 24845 SPLORDS 19-AUG-23 SPLORDS 19-AUG-23

TESTUSER2 10163 oracle.soda.privilege.developer /soda/* 10161 JIULUSUN 06-JUL-23 JIULUSUN 06-JUL-23

JIULUSUNTEST 10355 oracle.soda.privilege.developer /soda/* 10353 JIULUSUNTEST 12-JUL-23 JIULUSUNTEST 12-JUL-23

7 rows selected.

4. How privs are mapped to client

COLUMN client_priv_id FORMAT 999999 HEADING "Client Privilege ID"

COLUMN client_priv_created_by FORMAT A20 HEADING "Created By"

COLUMN client_priv_updated_by FORMAT A20 HEADING "Updated By"

COLUMN client_priv_client_id FORMAT 999999 HEADING "Client ID"

COLUMN priv_id FORMAT 999999 HEADING "Privilege ID"

COLUMN name FORMAT A20 HEADING "Name"

COLUMN label FORMAT A20 HEADING "Label"

COLUMN description FORMAT A30 HEADING "Description"

COLUMN parsing_schema FORMAT A15 HEADING "Parsing Schema"

COLUMN client_name FORMAT A15 HEADING "Client Name"

COLUMN client_created_by FORMAT A20 HEADING "Client Created By"

SELECT p.id client_priv_id,

p.created_by client_priv_created_by,

p.updated_by client_priv_updated_by,

p.client_id client_priv_client_id,

sec.id priv_id,

sec.name,

sec.label,

sec.description,

sch.parsing_schema,

c.name client_name,

c.created_by client_created_by

FROM ords_metadata.ords_schemas sch,

ords_metadata.sec_privileges sec,

ords_metadata.oauth_client_privileges p,

ords_metadata.oauth_clients c

WHERE sch.id = p.schema_id

AND sch.id = c.schema_id

AND sec.id = p.privilege_id

AND p.client_id = c.id

;

Client Privilege ID Created By Updated By Client ID Privilege ID Name Label Description Parsing Schema Client Name Client Created By

------------------- -------------------- -------------------- --------- ------------ -------------------- -------------------- ------------------------------ --------------- --------------- --------------------

10289 My Company Limited My Company Limited 10288 10285 emp_priv EMP Data Allow access to the EMP data. TESTUSER1 emp_client My Company Limited

5. Get existing client_id and secret, run as testuser1

SQL> COLUMN name FORMAT A20

SQL>

SQL> SELECT id, name, client_id, client_secret FROM user_ords_clients;

ID Name CLIENT_ID CLIENT_SECRET

---------- -------------------- -------------------------------- --------------------------------

10288 emp_client G4_adk6MvZfkvbgoZx8kKw.. EVwk938qHbno9L2ORRkobA..

Verify the client_id, secret and token works:

C:\DBASQL>curl -i -k --user G4_adk6MvZfkvbgoZx8kKw..:EVwk938qHbno9L2ORRkobA.. --data "grant_type=client_credentials" https://ORDSDEV/ords/DEVDB/hr/oauth/token

HTTP/1.1 200 OK

Content-Type: application/json

X-ORDS_DEBUG: true

X-Frame-Options: SAMEORIGIN

Transfer-Encoding: chunked

{"access_token":"bAp6yMYPWeDIOFhA2O1m9Q","token_type":"bearer","expires_in":3600}

C:\DBASQL>curl -i -k -H"Authorization: Bearer bAp6yMYPWeDIOFhA2O1m9Q" https://ORDSDEV/ords/DEVDB/hr/employees/7788

HTTP/1.1 200 OK

Content-Type: application/json

X-ORDS_DEBUG: true

ETag: "UutI4vFaOb3Mq4HKcmCd1uswFJqGeYlYjRtTC9JRxPgjiqpNpskdjF3JW9ZBmrJK7/8nNlwqleOhxG6CAMJVCg=="

Transfer-Encoding: chunked

{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-19T00:00:00Z","sal":3000,"comm":null,"deptno":20,"links":[{"rel":"self","href":"https://ORDSDEV/ords/DEVDB/hr/employees/7788"},{"rel":"edit","href":"https://ORDSDEV/ords/DEVDB/hr/employees/7788"},{"rel":"describedby","href":"https://ORDSDEV/ords/DEVDB/hr/metadata-catalog/employees/item"},{"rel":"collection","href":"https://ORDSDEV/ords/DEVDB/hr/employees/"}]}

Export OAUTH client: run as testuser1, choose emp_client to export, it generate the script that can be used to import

SQL> set linesize 200

SQL> set serveroutput on

-- export oauth_client

DECLARE

-- select name from user_ords_clients

v_client_name VARCHAR2(100) := 'emp_client';

v_include_security BOOLEAN := TRUE; -- Set to TRUE to include security definitions or FALSE to exclude.

v_export_result CLOB;

BEGIN

v_export_result := ORDS_METADATA.ords_export.EXPORT_OAUTH_CLIENT(

P_CLIENT_NAME => v_client_name,

P_INCLUDE_SECURITY_DEFINITIONS => v_include_security

);

DBMS_OUTPUT.PUT_LINE(v_export_result);

END;

-- Generated by ORDS REST Data Services 23.1.3.r1371032

-- Schema: TESTUSER1 Date: Wed Nov 22 02:31:25 2023

DECLARE

l_roles OWA.VC_ARR;

l_modules OWA.VC_ARR;

l_patterns

OWA.VC_ARR;

BEGIN

-- Warning: privileges do not protect modules during

-- OAuth Client standalone export.

ORDS.CREATE_ROLE(p_role_name => 'emp_role');

l_roles(1) := 'emp_role';

l_patterns(1) := '/employees/*';

ORDS.DEFINE_PRIVILEGE(

p_privilege_name => 'emp_priv',

p_roles => l_roles,

p_patterns => l_patterns,

p_modules =>

l_modules,

p_label => 'EMP Data',

p_description => 'Allow access to the EMP data.',

p_comments => NULL);

l_roles.DELETE;

l_modules.DELETE;

l_patterns.DELETE;

ORDS_METADATA.OAUTH.IMPORT_CLIENT(

p_name => 'emp_client',

p_client_id => 'G4_adk6MvZfkvbgoZx8kKw..',

p_grant_type =>

'client_credentials',

p_owner => 'TESTUSER1',

p_description => 'A client for Emp management',

p_origins_allowed => NULL,

p_redirect_uri => NULL,

p_support_email => 'tim@example.com',

p_support_uri => NULL,

p_privilege_names => 'emp_priv');

ORDS_METADATA.OAUTH.GRANT_CLIENT_ROLE(

p_client_name => 'emp_client',

p_role_name => 'emp_role');

COMMIT;

END;

PL/SQL procedure successfully completed.

Drop the client:

SQL> BEGIN

OAUTH.delete_client(

p_name => 'emp_client'

);

COMMIT;

END;

PL/SQL procedure successfully completed.

Import OAUTH client:

SQL> DECLARE

2 l_roles OWA.VC_ARR;

3 l_modules OWA.VC_ARR;

4 l_patterns

5 OWA.VC_ARR;

7 BEGIN

8 -- Warning: privileges do not protect modules during

9 -- OAuth Client standalone export.

11 ORDS.CREATE_ROLE(p_role_name => 'emp_role');

13 l_roles(1) := 'emp_role';

15 l_patterns(1) := '/employees/*';

17 ORDS.DEFINE_PRIVILEGE(

18 p_privilege_name => 'emp_priv',

19 p_roles => l_roles,

20 p_patterns => l_patterns,

21 p_modules =>

22 l_modules,

23 p_label => 'EMP Data',

24 p_description => 'Allow access to the EMP data.',

25 p_comments => NULL);

27 l_roles.DELETE;

28 l_modules.DELETE;

30 l_patterns.DELETE;

33 ORDS_METADATA.OAUTH.IMPORT_CLIENT(

34 p_name => 'emp_client',

35 p_client_id => 'G4_adk6MvZfkvbgoZx8kKw..',

36 p_grant_type =>

37 'client_credentials',

38 p_owner => 'TESTUSER1',

39 p_description => 'A client for Emp management',

40 p_origins_allowed => NULL,

41 p_redirect_uri => NULL,

43 p_support_email => 'tim@example.com',

44 p_support_uri => NULL,

45 p_privilege_names => 'emp_priv');

47 ORDS_METADATA.OAUTH.GRANT_CLIENT_ROLE(

48 p_client_name => 'emp_client',

50 p_role_name => 'emp_role');

52 COMMIT;

54 END;

55 /

PL/SQL procedure successfully completed.

Verify the client_id, secret, privs etc: run as dba user, result should be same as before.

--notice the timestamp is updated for emp_privs, client_id remain same, but secret have new value

SQL> -- view schema,client, role

2 from ords_metadata.ORDS_CLIENT_ROLES cr;

CLIENT_ID CLIENT_SCHEMA CLIENT_NAME ROLE_ID ROLE_NAME ROLE_SCHEMA

---------- -------------------- ------------------------------ ---------- -------------------- --------------------

10180 TESTUSER2 emp_client2 10181 emp_role2 TESTUSER2

107323 TESTUSER1 emp_client 10284 emp_role TESTUSER1

SQL> -- view privs

SQL> select distinct (select parsing_schema from DBA_ORDS_SCHEMAS where id= m.SCHEMA_ID) schema, m.* from ords_metadata.ORDS_PRIVILEGE_MAPPINGS m;

SCHEMA PRIVILEGE_ID NAME PATTERN SCHEMA_ID CREATED_BY CREATED_O UPDATED_BY UPDATED_O

-------------------- ------------ ------------------------------------------------------------ -------------------- ---------- --------------- --------- --------------- ---------

TESTUSER1 10003 oracle.soda.privilege.developer /soda/* 10001 TESTUSER1 06-JUN-23 TESTUSER1 06-JUN-23

TESTUSER2 10190 emp_priv /emp/* 10161 TESTUSER2 06-JUL-23 TESTUSER2 06-JUL-23

TESTUSER2 10182 emp_priv2 /employees/* 10161 TESTUSER2 06-JUL-23 TESTUSER2 06-JUL-23

SPLORDS 24847 oracle.soda.privilege.developer /soda/* 24845 SPLORDS 19-AUG-23 SPLORDS 19-AUG-23

TESTUSER1 10285 emp_priv /employees/* 10001 TESTUSER1 22-NOV-23 TESTUSER1 22-NOV-23

TESTUSER2 10163 oracle.soda.privilege.developer /soda/* 10161 JIULUSUN 06-JUL-23 JIULUSUN 06-JUL-23

JIULUSUNTEST 10355 oracle.soda.privilege.developer /soda/* 10353 JIULUSUNTEST 12-JUL-23 JIULUSUNTEST 12-JUL-23

7 rows selected.

SQL> -- view client privs

SQL> COLUMN client_priv_id FORMAT 999999 HEADING "Client Privilege ID"

SQL> COLUMN client_priv_created_by FORMAT A20 HEADING "Created By"

SQL> COLUMN client_priv_updated_by FORMAT A20 HEADING "Updated By"

SQL> COLUMN client_priv_client_id FORMAT 999999 HEADING "Client ID"

SQL> COLUMN priv_id FORMAT 999999 HEADING "Privilege ID"

SQL> COLUMN name FORMAT A20 HEADING "Name"

SQL> COLUMN label FORMAT A20 HEADING "Label"

SQL> COLUMN description FORMAT A30 HEADING "Description"

SQL> COLUMN parsing_schema FORMAT A15 HEADING "Parsing Schema"

SQL> COLUMN client_name FORMAT A15 HEADING "Client Name"

SQL> COLUMN client_created_by FORMAT A20 HEADING "Client Created By"

SQL> SELECT p.id client_priv_id,

2 p.created_by client_priv_created_by,

3 p.updated_by client_priv_updated_by,

4 p.client_id client_priv_client_id,

5 sec.id priv_id,

6 sec.name,

7 sec.label,

8 sec.description,

9 sch.parsing_schema,

10 c.name client_name,

11 c.created_by client_created_by

12 FROM ords_metadata.ords_schemas sch,

13 ords_metadata.sec_privileges sec,

14 ords_metadata.oauth_client_privileges p,

15 ords_metadata.oauth_clients c

16 WHERE sch.id = p.schema_id

17 AND sch.id = c.schema_id

18 AND sec.id = p.privilege_id

19 AND p.client_id = c.id

20 ;

Client Privilege ID Created By Updated By Client ID Privilege ID Name Label Description Parsing Schema Client Name Client Created By

107324 TESTUSER1 TESTUSER1 107323 10285 emp_priv EMP Data Allow access to the EMP data. TESTUSER1 emp_client TESTUSER1

SQL> SELECT id, name, client_id, client_secret FROM user_ords_clients;

ID Name CLIENT_ID CLIENT_SECRET

---------- -------------------- -------------------------------- --------------------------------

107323 emp_client G4_adk6MvZfkvbgoZx8kKw.. r3Pgc7VBo1cZGFtVg0SdOg..

Generate token using old and new secret respectively, the old one failed, but new one works.

C:\DBASQL>curl -i -k --user G4_adk6MvZfkvbgoZx8kKw..:EVwk938qHbno9L2ORRkobA.. --data "grant_type=client_credentials" https://ORDSDEV/ords/DEVDB/hr/oauth/token

HTTP/1.1 401 Unauthorized

Content-Type: application/problem+json

Content-Length: 10020

{

"code": "Unauthorized",

"message": "Unauthorized",

"type": "tag:oracle.com,2020:error/Unauthorized",

"instance": "tag:oracle.com,2020:ecid/QD4aTNMEvmCG9MdAvjAb3w",

"diagnosticTrace": "[TE] url-mapping start: 2023-11-22T22:41:48.409203061Z duration: 0ms\n",

"stackTrace": "UnauthorizedException [statusCode=401, logLevel=FINER, reasons=[]]\n\tat oracle.dbtools.http.auth.RequestAuthorizationProvider.authorize(RequestAuthorizationProvider.java:162)\n\tat oracle.dbtools.http.auth.AuthorizationDispatchHook.before(AuthorizationDispatchHook.java:40)\n\tat oracle.dbtools.http.dispatch.hooks.DispatchHookChain.before(DispatchHookChain.java:34)\n\tat oracle.dbtools.http.dispatch.hooks.DispatchHooks.before(DispatchHooks.java:49)\n\tat oracle.dbtools.http.entrypoint.Dispatcher.dispatch(Dispatcher.java:122)\n\tat oracle.dbtools.http.entrypoint.EntryPoint$FilteredServlet.service(EntryPoint.java:170)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:73)\n\tat oracle.dbtools.http.forwarding.QueryFilteringRewrite.doFilter(QueryFilteringRewrite.java:90)\n\tat oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.http.forwarding.ForwardingFilter.doFilter(ForwardingFilter.java:69)\n\tat oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.http.cors.CORSPreflightFilter.doFilter(CORSPreflightFilter.java:68)\n\tat oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.http.cookies.auth.CookieSessionCSRFFilter.doFilter(CookieSessionCSRFFilter.java:71)\n\tat oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.http.auth.AuthenticationFilter.authenticate(AuthenticationFilter.java:101)\n\tat oracle.dbtools.http.auth.AuthenticationFilter.doFilter(AuthenticationFilter.java:64)\n\tat oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.url.mapping.RequestMapperImpl.doFilter(RequestMapperImpl.java:158)\n\tat oracle.dbtools.url.mapping.URLMappingBase.doFilter(URLMappingBase.java:89)\n\tat oracle.dbtools.url.mapping.db.DatabaseTenantMapping.dispatchSelf(DatabaseTenantMapping.java:212)\n\tat oracle.dbtools.url.mapping.db.DatabaseTenantMappingBase.doFilter(DatabaseTenantMappingBase.java:51)\n\tat oracle.dbtools.url.mapping.tenant.TenantMappingDispatcher.dispatch(TenantMappingDispatcher.java:59)\n\tat oracle.dbtools.url.mapping.db.DatabaseTenantMappingBase.dispatchChild(DatabaseTenantMappingBase.java:152)\n\tat oracle.dbtools.url.mapping.db.DatabaseTenantMappingBase.doFilter(DatabaseTenantMappingBase.java:49)\n\tat oracle.dbtools.url.mapping.tenant.TenantMappingDispatcher.dispatch(TenantMappingDispatcher.java:59)\n\tat oracle.dbtools.url.mapping.db.DatabaseTenantMappingBase.dispatchChild(DatabaseTenantMappingBase.java:152)\n\tat oracle.dbtools.url.mapping.db.DatabaseTenantMappingBase.doFilter(DatabaseTenantMappingBase.java:49)\n\tat oracle.dbtools.jdbc.pools.local.DefaultLocalTenantMapping.doFilter(DefaultLocalTenantMapping.java:100)\n\tat oracle.dbtools.url.mapping.tenant.TenantMappingDispatcher.dispatch(TenantMappingDispatcher.java:59)\n\tat oracle.dbtools.url.mapping.tenant.TenantMappingFilter.doFilter(TenantMappingFilter.java:84)\n\tat oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.http.forwarding.ForwardingFailedFilter.doFilter(ForwardingFailedFilter.java:41)\n\tat oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.http.auth.external.ExternalSessionFilter.doFilter(ExternalSessionFilter.java:59)\n\tat oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.apex.support.auth.ApexSessionQueryRewriteFilter.doFilter(ApexSessionQueryRewriteFilter.java:58)\n\tat oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.http.cors.CORSResponseFilter.doFilter(CORSResponseFilter.java:90)\n\tat oracle.dbtools.http.filters.HttpResponseFilter.doFilter(HttpResponseFilter.java:45)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.http.filters.AbsoluteLocationFilter.doFilter(AbsoluteLocationFilter.java:65)\n\tat oracle.dbtools.http.filters.HttpResponseFilter.doFilter(HttpResponseFilter.java:45)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.http.auth.external.ExternalAccessValidationFilter.doFilter(ExternalAccessValidationFilter.java:59)\n\tat oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.http.errors.ErrorPageFilter.doFilter(ErrorPageFilter.java:87)\n\tat oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.http.secure.ForceHttpsFilter.doFilter(ForceHttpsFilter.java:74)\n\tat oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.http.auth.ForceAuthFilter.doFilter(ForceAuthFilter.java:44)\n\tat oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)\n\tat oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)\n\tat oracle.dbtools.http.filters.Filters.filter(Filters.java:67)\n\tat oracle.dbtools.http.entrypoint.EntryPoint.service(EntryPoint.java:70)\n\tat oracle.dbtools.http.entrypoint.EntryPointServlet.service(EntryPointServlet.java:130)\n\tat oracle.dbtools.entrypoint.WebApplicationRequestEntryPoint.service(WebApplicationRequestEntryPoint.java:50)\n\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:590)\n\tat org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)\n\tat org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:529)\n\tat org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)\n\tat org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1571)\n\tat org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)\n\tat org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1383)\n\tat org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)\n\tat org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)\n\tat org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1544)\n\tat org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)\n\tat org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1305)\n\tat org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)\n\tat org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:192)\n\tat org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141)\n\tat org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:173)\n\tat org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)\n\tat org.eclipse.jetty.server.Server.handle(Server.java:563)\n\tat org.eclipse.jetty.server.HttpChannel.lambda$handle$0(HttpChannel.java:505)\n\tat org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:762)\n\tat org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:497)\n\tat org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282)\n\tat org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)\n\tat org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)\n\tat org.eclipse.jetty.io.ssl.SslConnection$DecryptedEndPoint.onFillable(SslConnection.java:558)\n\tat org.eclipse.jetty.io.ssl.SslConnection.onFillable(SslConnection.java:379)\n\tat org.eclipse.jetty.io.ssl.SslConnection$2.succeeded(SslConnection.java:146)\n\tat org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)\n\tat org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)\n\tat org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:421)\n\tat org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:390)\n\tat org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:277)\n\tat org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.lambda$new$0(AdaptiveExecutionStrategy.java:139)\n\tat org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)\n\tat org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:933)\n\tat org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1077)\n\tat java.base/java.lang.Thread.run(Thread.java:833)\nCaused by: NotAuthorizedException [authConstraint=oracle.dbtools.oauth.client.application, error=null]\n\tat oracle.dbtools.http.auth.RequestAuthorizationProvider.authorize(RequestAuthorizationProvider.java:152)\n\t... 105 more\n"

}

C:\DBASQL>curl -i -k --user G4_adk6MvZfkvbgoZx8kKw..:r3Pgc7VBo1cZGFtVg0SdOg.. --data "grant_type=client_credentials" https://ORDSDEV/ords/DEVDB/hr/oauth/token

HTTP/1.1 200 OK

Content-Type: application/json

X-ORDS_DEBUG: true

X-Frame-Options: SAMEORIGIN

Transfer-Encoding: chunked

{"access_token":"celDJ6c-664MShM5Q8Ieng","token_type":"bearer","expires_in":3600}

C:\DBASQL>curl -i -k -H"Authorization: Bearer celDJ6c-664MShM5Q8Ieng" https://ORDSDEV/ords/DEVDB/hr/employees/7788

HTTP/1.1 200 OK

Content-Type: application/json

X-ORDS_DEBUG: true

ETag: "UutI4vFaOb3Mq4HKcmCd1uswFJqGeYlYjRtTC9JRxPgjiqpNpskdjF3JW9ZBmrJK7/8nNlwqleOhxG6CAMJVCg=="

Transfer-Encoding: chunked

The above steps demostrate how to export ORDS OAUTH client settings and import with same client_id, but secret has new value.

More advanced try:

Where is the secret saved? it's in table ORDS_METADATA.OAUTH_CLIENTS

SQL> select c.id,PARSING_SCHEMA,name,client_id,client_secret from ORDS_METADATA.OAUTH_CLIENTS c,dba_ords_schemas s where c.schema_id=s.id;

ID PARSING_SCHEMA NAME CLIENT_ID CLIENT_SECRET

---------- -------------------- -------------------- -------------------------------- --------------------------------

10180 TESTUSER2 emp_client2 DrM1YsUpmUv7PXpz68DVOg.. MFW-IsMshhaw1cw9_a2cCQ..

107323 TESTUSER1 emp_client G4_adk6MvZfkvbgoZx8kKw.. r3Pgc7VBo1cZGFtVg0SdOg..

What if I want to update the secret to the previous value, will that still work?

SQL> update ORDS_METADATA.OAUTH_CLIENTS set CLIENT_SECRET='EVwk938qHbno9L2ORRkobA..' where client_id='G4_adk6MvZfkvbgoZx8kKw..' and id=107323;

1 row updated.

SQL> commit;

Commit complete.

C:\DBASQL>curl -i -k --user G4_adk6MvZfkvbgoZx8kKw..:EVwk938qHbno9L2ORRkobA.. --data "grant_type=client_credentials" https://ORDSDEV/ords/DEVDB/hr/oauth/token

HTTP/1.1 200 OK

Content-Type: application/json

X-ORDS_DEBUG: true

X-Frame-Options: SAMEORIGIN

Transfer-Encoding: chunked

{"access_token":"QhgRa8bXhITif6_KQQn3Kw","token_type":"bearer","expires_in":3600}

C:\DBASQL>curl -i -k -H"Authorization: Bearer QhgRa8bXhITif6_KQQn3Kw" https://ORDSDEV/ords/DEVDB/hr/employees/7788

HTTP/1.1 200 OK

Content-Type: application/json

X-ORDS_DEBUG: true

ETag: "UutI4vFaOb3Mq4HKcmCd1uswFJqGeYlYjRtTC9JRxPgjiqpNpskdjF3JW9ZBmrJK7/8nNlwqleOhxG6CAMJVCg=="

Transfer-Encoding: chunked

WORKS! now we have a solution how to make ORDS OAUTH work when non-production database is refreshed from PROD db.

DBA

Search This Blog

ORDS: how to restore the OAuth client secret

Comments

Post a Comment

Popular posts from this blog

Opatch apply/lsinventory error: oneoff is corrupted or does not exist

non-existent process lock port on windows server

oracle dba_hist_sysmetric_summary