Oracle自治数据库SODA OAuth客户端凭证认证异常排查及解决

测试背景 #

我正在测试Oracle Autonomous Database的JSON SODA功能，在「使用OAuth客户端凭证的SODA for REST」章节前，所有步骤均正常运行，但测试该章节时出现异常。

首次错误现象 #

执行以下curl命令：

curl -i -H "Authorization: Bearer zSK2VYerEjIB4TmX7SVrGg" -X GET https://gxxxxxxxxxxxxxx-yyyyyy.adb.eu-stockholm-1.oraclecloudapps.com/ords/my_client/soda/latest

返回404错误：

HTTP/1.1 404 Not Found
Date: Sat, 12 Oct 2024 09:43:55 GMT
Content-Type: application/problem+json
Content-Length: 181
Connection: keep-alive

{
"code": "NotFound",
"message": "Not Found",
"type": "tag:oracle.com,2020:error/NotFound",
"instance": "tag:oracle.com,2020:ecid/2f98809191ff9c3094347f5c031a0cdb"
}

按指导操作后的状态 #

按照建议操作后，成功获取到OAuth token，但执行SODA查询时返回401未授权：

获取token的命令及结果 #

curl \
--user aaaaaaaaaa..:bbbbbbbbbb.. \
--data 'grant_type=client_credentials' \
https://gyyyyyyyyyy-zzzzzz.adb.eu-stockholm-1.oraclecloudapps.com/ords/peka/oauth/token

{"access_token":"Bp9iAzk-ICda0q0bPYxJvQ","token_type":"bearer","expires_in":3600}

执行查询的命令及结果 #

curl -i -X POST \
-H "Authorization: Bearer Bp9iAzk-ICda0q0bPYxJvQ" \
-H "Content-Type: application/json" \
--data '{}' \
https://gyyyyyyyyyy-zzzzzz.adb.eu-stockholm-1.oraclecloudapps.com/ords/peka/soda/latest/MyTest?action=query

HTTP/1.1 401 Unauthorized
Date: Sat, 12 Oct 2024 17:23:11 GMT
Content-Type: application/json
Transfer-Encoding: chunked
Connection: keep-alive
Strict-Transport-Security: max-age=31536000;includeSubDomains
X-Content-Type-Options: nosniff
X-Frame-Options: SAMEORIGIN

{"type":"http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.2","status":401,"title":"unauthorized","detail":"Not authorized."}

最终解决方案 #

通过添加-k参数忽略SSL证书校验，成功完成OAuth认证并执行SODA查询：

获取有效token的命令 #

curl -i -k --user "xxxxxxxxx..:yyyyyyyyyyy.." \
--data "grant_type=client_credentials" \
"https://zzzzzzzz-99999999.adb.eu-stockholm-1.oraclecloudapps.com/ords/peka/oauth/token"

Result: XdXMYiAMfhACM1QtT6qAxg

执行查询的命令及结果 #

curl -s -H "Authorization: Bearer XdXMYiAMfhACM1QtT6qAxg" -X GET https://zzzzzzzz-99999999.adb.eu-stockholm-1.oraclecloudapps.com/ords/peka/soda/latest/MyJson | jq -r '.items[0].value.name'

Result: Frank Zappa

结果正确，问题解决。

内容的提问来源于stack exchange，提问作者PeterK