Request Description: How to configure JDBC Realm in Ephesoft

Versions: ALL

Solution:

Users can be configured with database using the JDBCRealm.

1. In server.xml file, commented MemoryRealm for context path “/dcma”.
2. Added a JDBCRealm as shown in snapshot below

Using Windows Authentication:

<Realm
className=”org.apache.catalina.realm.JDBCRealm”
driverName=”net.sourceforge.jtds.jdbc.Driver”
connectionURL=”jdbc:jtds:sqlserver://localhost:1433;databaseName=<<database_name>>;sendStringParametersAsUnicode=true;prepareSQL=3;domain=<<domain_name>>”
connectionName=””
connectionPassword=””
userTable=”<<table name where users exist>>”
userNameCol=”<<column name having users>>”
userCredCol=”<<column name having password>>”
userRoleTable=”<<table name having roles>>”
roleNameCol=”<<column name having roles>>” />

 

Using Basic Authentication

<Realm
className=”org.apache.catalina.realm.JDBCRealm”
driverName=”net.sourceforge.jtds.jdbc.Driver”
connectionURL=”jdbc:jtds:sqlserver://localhost:1433;databaseName=Test;sendStringParametersAsUnicode=true;prepareSQL=3″
connectionName=”<<username>>”
connectionPassword=”<<password>>”
userTable=”<<table name where users exist>>”
userNameCol=”<<column name having users>>”
userCredCol=”<<column name having password>>”
userRoleTable=”<<table name having roles>>”
roleNameCol=”<<column name having roles>>” />

3. Created three tables in database namely, users and users_roles, roles.

  • Added columns user_name and user_pass in users table
  • Added columns user_name and role_name in user_roles table

DDL Script for Table Creation:
create table users
(
user_name varchar(15) not null primary key,
user_pass varchar(15) not null
);

create table roles
(
role_name varchar(15) not null primary key
);

create table user_roles
(
user_name varchar(15) not null,
role_name varchar(15) not null,
primary key( user_name, role_name )
);

4. Restarted the server.
5. The rest service user was successfully authenticated by the database.

Reference wiki article: 

https://tomcat.apache.org/tomcat-3.3-doc/JDBCRealm-howto.html

Was this article helpful to you?

Abhishek Jain