Mailing-List: contact users-help@tomcat.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <users@tomcat.apache.org>
Received-SPF: pass (nike.apache.org: domain of setevoy4@gmail.com designates
 209.85.214.171 as permitted sender)
MIME-Version: 1.0
Date: Mon, 16 Dec 2013 14:17:56 +0200
Message-ID: 
 <CAAEmDoPHLofZ9x4CzLMkb_jXjgVvL2Lwqbc5PKSE52pw1F9iow@mail.gmail.com>
Subject: Tomcat JBDCRealm with Oracle DB
From: =?UTF-8?B?0JDRgNGB0LXQvdC40Lkg0JfQuNC90YfQtdC90LrQvg==?=
 <setevoy4@gmail.com>
To: users@tomcat.apache.org
Content-Type: multipart/alternative; boundary=089e011607a44f0e9804eda5cd80

--089e011607a44f0e9804eda5cd80
Content-Type: text/plain; charset=UTF-8

Hi.

I configured two JDBCRealm's - for MySQL and Oracle databases.

Both DB have same tables with same content:

mysql> show tables;
+----------------------+
| Tables_in_tmc_access |
+----------------------+
| user_roles           |
| users                |
+----------------------+

mysql> desc user_roles;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| user_name | varchar(100) | NO   | PRI | NULL    |       |
| role_name | varchar(100) | NO   | PRI | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc users;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| user_name | varchar(100) | NO   | PRI | NULL    |       |
| user_pass | varchar(100) | NO   |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from users,user_roles;
+------------+-----------+------------+------------+
| user_name  | user_pass | user_name  | role_name  |
+------------+-----------+------------+------------+
| indexadmin | password  | indexadmin | indexadmin |
+------------+-----------+------------+------------+
1 row in set (0.00 sec)

Oracle:

SQL> SELECT table_name FROM user_tables;

TABLE_NAME
------------------------------
TMC_USERS_SET
USER_ROLES_SET

SQL> desc USER_ROLES_SET;
 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 USER_NAME                                 NOT NULL VARCHAR2(100)
 ROLE_NAME                                 NOT NULL VARCHAR2(100)

SQL> desc TMC_USERS_SET;
 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 USER_NAME                                 NOT NULL VARCHAR2(100)
 USER_PASS                                 NOT NULL VARCHAR2(100)


SQL> select * from TMC_USERS_SET,USER_ROLES_SET;

USER_NAME
--------------------------------------------------------------------------------
USER_PASS
--------------------------------------------------------------------------------
USER_NAME
--------------------------------------------------------------------------------
ROLE_NAME
--------------------------------------------------------------------------------
indexadmin
password
indexadmin
indexadmin

server.xml config:

<!--      <Realm  className="org.apache.catalina.realm.JDBCRealm"
             driverName="org.gjt.mm.mysql.Driver"
          connectionURL="jdbc:mysql://10.***.***.239/tmc_access"
         connectionName="tmc_access" connectionPassword="password"
              userTable="users" userNameCol="user_name"
userCredCol="user_pass"
          userRoleTable="user_roles" roleNameCol="role_name" /> -->


      <Realm  className="org.apache.catalina.realm.JDBCRealm"
             driverName="oracle.jdbc.driver.OracleDriver"
          connectionURL="jdbc:oracle:thin:@oraclehost:1521:correctscheme"
         connectionName="tmc" connectionPassword="tmc"
              userTable="tmc_users_set" userNameCol="user_name"
userCredCol="user_pass"
          userRoleTable="user_roles_set" roleNameCol="role_name" />

web.xml:


<security-constraint>

  <web-resource-collection>
    <web-resource-name>Restricted Area</web-resource-name>
    <url-pattern>/index.jsp</url-pattern>
  </web-resource-collection>

  <auth-constraint>
    <role-name>indexadmin</role-name>
  </auth-constraint>

</security-constraint>

<login-config>
  <auth-method>BASIC</auth-method>
</login-config>

<security-role>
  <role-name>indexadmin</role-name>
</security-role>


So, when I switch config to Oracle Realm - it is not working (just return
again login-window)... With MySQL - working perfect.

What I'm doing wrong?

--089e011607a44f0e9804eda5cd80--