Environment:
Oracle 12.2.0.1.0 Running on Solaris 11.3
ORDS 19.2.0.r1991647
Tomcat 9.0.29
ojdbc8.jar
Configured ORDS and Tomcat basic authentication using the following documentation
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication#database-authentication
We created the same roles in our roles table in the Oracle DB with an associated client emp_client with privilege emp_priv, then mapped the privilege to the web service
We are able to access protected routes using CURL
curl -i -k --user tmyers:password https://10.172.150.20:8443/ords/emp1/votb/412
1. First-Party Basic Authentication works using the ORDS credentials file from a local directory.
2. Tomcat user file works to authenticate the user and pass the role to OAuth.
3. Tomcat JDBCRealm is not working!!!
Followed the instruction on the article below and created a user table and a role table with the recommended constraints and cannot get OAuth to work.
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-basic-and-digest-authentication-on-tomcat-using-jdb…
RESULTS
1. https://10.172.150.20:8443/ords/emp1/oauth/auth?response_type=code&client_id=E6QzgaM1x6RPkDpFVZFI0Q..&state=ORDS8D7A713E…
returns the redirect URL but the role is missing in the user that was authenticated.
https://10.172.150.20:8443/ords/emp1/redirect?code=GWC4_SNSrH-ENZMllDTnNA&state=ORDS8D7A713E93372E0406A38A8C93S9
404 Not Found ---- Expected result.
Debug Trace
mapped request using: /emp1/* to: +EMP1|apex|pu|, cryptoKeys=*****, identityDomain=__NO_IDENTITY_DOMAIN__
Could not find any dispatcher to handle request:
--Attributes--
org.apache.tomcat.util.net.secure_protocol_version = TLSv1.2
javax.servlet.request.key_size = 256
javax.servlet.request.ssl_session_mgr = org.apache.tomcat.util.net.jsse.JSSESupport@37582469
javax.servlet.request.cipher_suite = TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
javax.servlet.request.ssl_session_id = 5dfc01b65b8a7f69cde3696c170e42da8b15a85866d2cb3d635bdbed884aa3fa
oracle.dbtools.http.ecid = k7dfgXA_TyuwyDS69ecJWw
ECID-Principal = ECIDPrincipal [ecid=k7dfgXA_TyuwyDS69ecJWw]
oracle.dbtools.common.di.Services = URL Mapped Scope
oracle.dbtools.plugin.api.di.InstanceLocator = oracle.dbtools.common.di.Services$InstanceLocator@4aa72266
--Attributes--
GET /ords/emp1/redirect?code=GWC4_SNSrH-ENZMllDTnNA&state=ORDS8D7A713E93372E0406A38A8C93S9 HTTP/1.1
Host: 10.172.150.20
accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
accept-language: en-US
upgrade-insecure-requests: 1
user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.102 Safari/537.36 Edge/18.18362
accept-encoding: gzip, deflate, br
host: 10.172.150.20:8443
connection: Keep-Alive
authorization: Basic dG__TQ=
Principal: {user: tmyers, roles: []} -- Unexpected. Should see the role [Manager]
org.apache.catalina.connector.CoyoteInputStream@2a5dc2a4
mapped request using: /emp1/* to: +EMP1|apex|pu|, cryptoKeys=*****, identityDomain=__NO_IDENTITY_DOMAIN__
Could not find any dispatcher to handle request:
--Attributes--
org.apache.tomcat.util.net.secure_protocol_version = TLSv1.2
javax.servlet.request.key_size = 256
javax.servlet.request.ssl_session_mgr = org.apache.tomcat.util.net.jsse.JSSESupport@37582469
javax.servlet.request.cipher_suite = TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
javax.servlet.request.ssl_session_id = 5dfc01b65b8a7f69cde3696c170e42da8b15a85866d2cb3d635bdbed884aa3fa
oracle.dbtools.http.ecid = k7dfgXA_TyuwyDS69ecJWw
ECID-Principal = ECIDPrincipal [ecid=k7dfgXA_TyuwyDS69ecJWw]
oracle.dbtools.common.di.Services = URL Mapped Scope
oracle.dbtools.plugin.api.di.InstanceLocator = oracle.dbtools.common.di.Services$InstanceLocator@699e6bf0
--Attributes--
GET /ords/emp1/redirect?code=GWC4_SNSrH-ENZMllDTnNA&state=ORDS8D7A713E93372E0406A38A8C93S9 HTTP/1.1
Host: 10.172.150.20
accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
accept-language: en-US
upgrade-insecure-requests: 1
user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.102 Safari/537.36 Edge/18.18362
accept-encoding: gzip, deflate, br
host: 10.172.150.20:8443
connection: Keep-Alive
authorization: Basic dG__TQ=
Principal: {user: tmyers, roles: []} -- Unexpected. Should see the role [Manager]
......
The CURL call fails as OAuth see the user as not authenticated.
2. curl -i -k --user E6QzgaM1x6RPkDpFVZFI0Q..:fC6Sij2RrIVRN-wK7RHIfg.. --data "grant_type=authorization_code&code=GWC4_SNSrH-ENZMllDTnNA" https://10.172.150.20:8443/ords/emp1/oauth/token
HTTP/1.1 401
WWW-Authenticate: Basic realm="Authentication required"
Content-Type: text/html;charset=utf-8
Content-Language: en
Content-Length: 669
Date: Thu, 19 Dec 2019 23:08:22 GMT
3. Ran Tomcat in Debug mode and saw the following in the Tomcat logs:
19-Dec-2019 00:01:43.950 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.authenticator.AuthenticatorBase.invoke Calling authenticate()
19-Dec-2019 00:01:43.954 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.realm.CombinedRealm.authenticate Attempting to authenticate user [tmyers]
with realm [org.apache.catalina.realm.JDBCRealm]
19-Dec-2019 00:01:43.961 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.realm.CombinedRealm.authenticate Authenticated user [tmyers] with realm [
org.apache.catalina.realm.JDBCRealm]
19-Dec-2019 00:01:43.961 FINE [https-jsse-nio-8443-exec-4] org.apache.catalina.authenticator.AuthenticatorBase.invoke Security checking request GET /ords/e
ws1/jet/css/libs/oj/v6.2.0/alta/oj-alta-min.css
19-Dec-2019 00:01:43.962 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.authenticator.AuthenticatorBase.register Authenticated 'tmyers' with type
'BASIC'
19-Dec-2019 00:01:43.963 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.authenticator.AuthenticatorBase.invoke Calling accessControl()
19-Dec-2019 00:01:43.963 FINE [https-jsse-nio-8443-exec-4] org.apache.catalina.realm.RealmBase.findSecurityConstraints Checking constraint 'SecurityConst
raint[ords]' against GET /ews1/jet/css/libs/oj/v6.2.0/alta/oj-alta-min.css --> true
19-Dec-2019 00:01:43.964 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.realm.RealmBase.hasResourcePermission Checking roles GenericPrincipal[tmye
rs(Manager,)]
19-Dec-2019 00:01:43.964 FINE [https-jsse-nio-8443-exec-4] org.apache.catalina.realm.RealmBase.findSecurityConstraints Checking constraint 'SecurityConst
raint[ords]' against GET /ews1/jet/css/libs/oj/v6.2.0/alta/oj-alta-min.css --> true
19-Dec-2019 00:01:43.964 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.realm.RealmBase.hasRole Username [tmyers] does NOT have role [Business_Ma
nager]
19-Dec-2019 00:01:43.965 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.realm.RealmBase.hasResourcePermission No role found: Business_Manager
19-Dec-2019 00:01:43.965 FINE [https-jsse-nio-8443-exec-4] org.apache.catalina.authenticator.AuthenticatorBase.invoke Calling hasUserDataPermission()
19-Dec-2019 00:01:43.965 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.realm.RealmBase.hasRole Username [tmyers] does NOT have role [President]
19-Dec-2019 00:01:43.966 FINE [https-jsse-nio-8443-exec-4] org.apache.catalina.realm.RealmBase.hasUserDataPermission User data constraint has no restrict
ions
19-Dec-2019 00:01:43.966 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.realm.RealmBase.hasResourcePermission No role found: President
19-Dec-2019 00:01:43.967 FINE [https-jsse-nio-8443-exec-4] org.apache.catalina.authenticator.AuthenticatorBase.invoke Calling authenticate()
19-Dec-2019 00:01:43.967 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.realm.RealmBase.hasRole Username [tmyers] has role [Manager]
19-Dec-2019 00:01:43.967 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.realm.RealmBase.hasResourcePermission Role found: Manager
19-Dec-2019 00:01:43.968 FINE [https-jsse-nio-8443-exec-4] org.apache.catalina.realm.CombinedRealm.authenticate Attempting to authenticate user [tmyers]
with realm [org.apache.catalina.realm.JDBCRealm]
19-Dec-2019 00:01:43.968 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.realm.RealmBase.hasRole Username [tmyers-TR] does NOT have role [Secretary]
19-Dec-2019 00:01:43.969 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.realm.RealmBase.hasResourcePermission No role found: Secretary
19-Dec-2019 00:01:43.969 FINE [https-jsse-nio-8443-exec-5] org.apache.catalina.authenticator.AuthenticatorBase.invoke Successfully passed all security constraints
Verified all configuration files are correct.
web.xml
Add the following to the "$CATALINA_BASE/conf/web.xml" file before the final "web-app" tag.
<!-- Added to configure a JDBC Realm for ORDS authentication -->
<security-constraint>
<web-resource-collection>
<web-resource-name>ords</web-resource-name>
<url-pattern>/*</url-pattern>
</web-resource-collection>
<auth-constraint>
<role-name>*</role-name>
</auth-constraint>
</security-constraint>
<login-config>
<auth-method>BASIC</auth-method>
</login-config>
<security-role>
<role-name>Manager</role-name>
<role-name>Business_Manager</role-name>
<role-name>President</role-name>
<role-name>Secretary</role-name>
</security-role>
Tomcat server.xml
<Realm className="org.apache.catalina.realm.LockOutRealm">
<Realm className="org.apache.catalina.realm.UserDatabaseRealm"
resourceName="UserDatabase"/>
<Realm className="org.apache.catalina.realm.JDBCRealm"
<!-- driverName="oracle.jdbc.driver.OracleDriver" -->
driverName="oracle.jdbc.OracleDriver"
connectionURL="jdbc:oracle:thin:@10.172.150.20:1521/orcl"
connectionName="emp1"
connectionPassword="emp1"
userTable="EMP_USERS" userNameCol="USERNAME" userCredCol="USERPASS"
userRoleTable="EMP_USER_ROLE" roleNameCol="ROLENAME"
/>
</Realm>
ANY HELP WOULD BE GREATLY APPRECIATED. Can seems to get pass this error to get to our resources after user authentication. Thanks.