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;
6
7 BEGIN
8 -- Warning: privileges do not protect modules during
9 -- OAuth Client standalone export.
10
11 ORDS.CREATE_ROLE(p_role_name => 'emp_role');
12
13 l_roles(1) := 'emp_role';
14
15 l_patterns(1) := '/employees/*';
16
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);
26
27 l_roles.DELETE;
28 l_modules.DELETE;
29
30 l_patterns.DELETE;
31
32
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,
42
43 p_support_email => 'tim@example.com',
44 p_support_uri => NULL,
45 p_privilege_names => 'emp_priv');
46
47 ORDS_METADATA.OAUTH.GRANT_CLIENT_ROLE(
48 p_client_name => 'emp_client',
49
50 p_role_name => 'emp_role');
51
52 COMMIT;
53
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
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
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
{"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/"}]}
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
{"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/"}]}
WORKS! now we have a solution how to make ORDS OAUTH work when non-production database is refreshed from PROD db.
Comments
Post a Comment