2 Replies Latest reply on Jan 15, 2020 9:58 PM by HughR

    How to get Tomcat JDBCRealm Authentication to pass the user role to ORDS OAuth

    HughR

      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.