8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : Basic and Digest Authentication on Tomcat using JDBCRealm

This article demonstrates the use of the Tomcat JDBCRealm to control authentication to Oracle REST Data Services (ORDS) web services.

Thanks to Marcel Boermann for helping me with this. I spent a lot of time trying to get this to work, then Marcel sent me an example "web.xml" file, which made it clear you need the full basic authentication setup in Tomcat, in addition to the normal ORDS security setup. Once that was working, digest authentication was relatively easy to configure.

Related articles.

Web Service Setup

This article assumes you have completed the following setup, that is common to most ORDS authentication methods.

At this point, if you attempt to access the test web service you get a HTTP 401 unauthorized status returned, as the test web service is protected by the "emp_role" role.

$ curl -i -k https://localhost:8443/ords/hr/employees/7788 | grep HTTP
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 16005  100 16005    0     0  55529      0 --:--:-- --:--:-- --:--:-- 55572
HTTP/1.1 401
$

Authentication Schema Setup

Create tables to hold the users and roles information, if you don't already have some tables containing this information in your application. You can read about the requirements for the tables here.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

-- DROP USER tomcat_auth CASCADE;

CREATE USER tomcat_auth IDENTIFIED BY tomcat_auth_passwd
  QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO tomcat_auth;


CONN tomcat_auth/tomcat_auth_passwd@pdb1

DROP TABLE tomcat_user_roles PURGE;
DROP TABLE tomcat_users PURGE;

CREATE TABLE tomcat_users (
  user_name  VARCHAR2(15),
  user_pass  VARCHAR2(150) NOT NULL,
  CONSTRAINT tomcat_users_pk PRIMARY KEY (USER_NAME)
);

CREATE TABLE tomcat_user_roles (
  user_name  VARCHAR2(15) NOT NULL,
  role_name  VARCHAR2(15) NOT NULL,
  CONSTRAINT tomcat_user_roles_pk PRIMARY KEY (user_name, role_name),
  CONSTRAINT tcur_tcus_fk FOREIGN KEY (user_name) REFERENCES tomcat_users(user_name)
);

We create a new user called "emp_user" with a plain text password and associate it with the "emp_role" role, which ties up with the ORDS role defined in the setup.

INSERT INTO tomcat_users VALUES ('emp_user', 'Password1');
INSERT INTO tomcat_user_roles VALUES ('emp_user', 'emp_role');
COMMIT;

JDBC Driver (ojdbc8.jar)

Download the "ojdbc8.jar" file from here and place it in the "CATALINA_BASE/lib" directory. In my case I just used the file from the database ORACLE_HOME.

mkdir -p $CATALINA_BASE/lib/
cp $ORACLE_HOME/jdbc/lib/ojdbc8.jar $CATALINA_BASE/lib/

Basic Authentication

Edit the "$CATALINA_BASE/conf/server.xml" file, adding the JDBCRealm configuration. Notice the database configuration and details of the tables and columns used to identify authenticated users.

From this:

      <Realm className="org.apache.catalina.realm.LockOutRealm">
        <!-- This Realm uses the UserDatabase configured in the global JNDI
                          resources under the key "UserDatabase".  Any edits
             that are performed against this UserDatabase are immediately
             available for use by the Realm.  -->
        <Realm className="org.apache.catalina.realm.UserDatabaseRealm"
               resourceName="UserDatabase"/>
      </Realm>

To this:     
        
      <Realm className="org.apache.catalina.realm.LockOutRealm">
        <Realm className="org.apache.catalina.realm.JDBCRealm"
          driverName="oracle.jdbc.driver.OracleDriver"
          connectionURL="jdbc:oracle:thin:@localhost:1521/pdb1"
          connectionName="tomcat_auth"
          connectionPassword="tomcat_auth_passwd"
          userTable="TOMCAT_USERS" userNameCol="USER_NAME" userCredCol="USER_PASS"
          userRoleTable="TOMCAT_USER_ROLES" roleNameCol="ROLE_NAME"
          />
      </Realm>

Add the following to the "$CATALINA_BASE/conf/web.xml" file before the final "web-app" tag.

    <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>*</role-name>
    </security-role>

With the config changes in place we need to restart Tomcat.

$CATALINA_HOME/bin/shutdown.sh
$CATALINA_HOME/bin/startup.sh

tail -f $CATALINA_BASE/logs/catalina.out

Without authentication we still get the HTTP 401 Unauthorized response.

$ curl -i -k https://localhost:8443/ords/hr/employees/7788 | grep HTTP
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 16005  100 16005    0     0  59365      0 --:--:-- --:--:-- --:--:-- 59498
HTTP/1.1 401
$

If we provide the user credentials we authenticate as expected.

$ curl -k --user emp_user:Password1 https://localhost:8443/ords/hr/employees/7788
{"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://localhost:8443/ords/hr/employees/7788"},
{"rel":"edit","href":"https://localhost:8443/ords/hr/employees/7788"},
{"rel":"describedby","href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"},
{"rel":"collection","href":"https://localhost:8443/ords/hr/employees/"}]}
$

Digest Authentication

In the example above we used a plain text password to keep it simple, but you shouldn't be storing plain text passwords in the database. If you want to use a Digest authentication you can digest it and store the digest, rather than the clear text password. In this example we digest our password of "Password1". We are using a realm name of "ORDS" as the salt. You can use any salt, but you have to be consistent in the Tomcat config, making sure it is specified as the realm name.

# Digest a password.
$CATALINA_HOME/bin/digest.sh -a md5 -s 0 -i 1 emp_user:ORDS:Password1 
emp_user:ORDS:Password1:ac3a427cff7e83e492b8117c2bcd40d6

We have to replace the user credentials we set above, storing the digest password instead of the plain text password.

CONN tomcat_auth/tomcat_auth_passwd@pdb1

DELETE FROM tomcat_user_roles;
DELETE FROM tomcat_users;

INSERT INTO tomcat_users VALUES ('emp_user', 'ac3a427cff7e83e492b8117c2bcd40d6');

INSERT INTO tomcat_user_roles VALUES ('emp_user', 'emp_role');
COMMIT;

Edit the "$CATALINA_BASE/conf/server.xml" file, adding the resourceName attribute and "md5" credential handler to the existing realm definition from the basic authentication setup.

      <Realm className="org.apache.catalina.realm.LockOutRealm">
        <Realm className="org.apache.catalina.realm.JDBCRealm"
          driverName="oracle.jdbc.driver.OracleDriver"
          connectionURL="jdbc:oracle:thin:@localhost:1521/pdb1"
          connectionName="tomcat_auth"
          connectionPassword="tomcat_auth_passwd"
          userTable="TOMCAT_USERS" userNameCol="USER_NAME" userCredCol="USER_PASS"
          userRoleTable="TOMCAT_USER_ROLES" roleNameCol="ROLE_NAME"
          resourceName="ORDS">
          <CredentialHandler className="org.apache.catalina.realm.MessageDigestCredentialHandler" algorithm="md5" />
        </Realm>
      </Realm>

Edit the "$CATALINA_BASE/conf/web.xml" file, amending basic authentication setup from the previous section. Notice the auth-method has changed to DIGEST and the realm-name is ORDS, which matches the salt we used when digesting the password and the resource name of the realm.

    <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>DIGEST</auth-method>
        <realm-name>ORDS</realm-name>
    </login-config>
    <security-role>
       <role-name>*</role-name>
    </security-role>

With the config changes in place we need to restart Tomcat.

$CATALINA_HOME/bin/shutdown.sh
$CATALINA_HOME/bin/startup.sh

tail -f $CATALINA_BASE/logs/catalina.out

We can test digest authentication using curl, provided the include the --digest flag, so it understands it need to do the call/response associated with digest authentication.

$ curl -k --digest --user emp_user:Password1 https://localhost:8443/ords/hr/employees/7788
{"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://localhost:8443/ords/hr/employees/7788"},
{"rel":"edit","href":"https://localhost:8443/ords/hr/employees/7788"},
{"rel":"describedby","href":"https://localhost:8443/ords/hr/metadata-catalog/employees/item"},
{"rel":"collection","href":"https://localhost:8443/ords/hr/employees/"}]}
$

Thoughts

Just some thoughts about this approach.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.