最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【已解决】OpenFire中无法使用自定义数据库中用户去登录

Openfire crifan 8055浏览 0评论

【背景】

折腾:

【已解决】Openfire重新安装后无法用管理员账户admin登录

之后,结果发现:

结果没法用openfire中配置的外部数据库的用户去登陆,没效果。

 

【解决过程】

1.然后自己也看了看对应的log:

root@bogon:logs# ls -la
total 36
drwxr-xr-x  2 daemon daemon  4096 Jul  8 04:42 .
drwxr-x--- 10 daemon daemon  4096 Jul  8 04:35 ..
-rw-r--r--  1 daemon daemon     0 Jul  8 04:36 debug.log
-rw-r--r--  1 daemon daemon  2215 Jul  8 04:43 error.log
-rw-r--r--  1 daemon daemon 13840 Jul  8 04:44 info.log
-rw-r--r--  1 daemon daemon   102 Jul  8 04:43 nohup.out
-rw-r--r--  1 daemon daemon  3750 Jul  8 04:44 warn.log
root@bogon:logs# cat nohup.out 
Openfire 3.10.2 [2015-7-8 4:43:01]
管理平台开始监听:
  http://bogon:9090
  https://bogon:9091
root@bogon:logs# cat warn.log 
2015.07.08 04:36:11 org.jivesoftware.openfire.XMPPServer - Unable to determine local hostname.
java.net.UnknownHostException: bogon: bogon: No address associated with hostname
        at java.net.InetAddress.getLocalHost(Unknown Source)
        at org.jivesoftware.openfire.XMPPServer.initialize(XMPPServer.java:356)
        at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:491)
        at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:216)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at java.lang.Class.newInstance(Unknown Source)
        at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:105)
        at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:56)
Caused by: java.net.UnknownHostException: bogon: No address associated with hostname
        at java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method)
        at java.net.InetAddress$1.lookupAllHostAddr(Unknown Source)
        at java.net.InetAddress.getAddressesFromNameService(Unknown Source)
        ... 11 more
2015.07.08 04:38:52 org.jivesoftware.openfire.http.HttpSessionManager - HttpSessionManager.init() recreate sendPacketPool
2015.07.08 04:39:14 index.jsp - Failed to fetch RSS feed: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: root certificate not trusted of [vanity2.jiveon.com]
2015.07.08 04:42:59 org.jivesoftware.openfire.XMPPServer - Unable to determine local hostname.
java.net.UnknownHostException: bogon: bogon: No address associated with hostname
        at java.net.InetAddress.getLocalHost(Unknown Source)
        at org.jivesoftware.openfire.XMPPServer.initialize(XMPPServer.java:356)
        at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:491)
        at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:216)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at java.lang.Class.newInstance(Unknown Source)
        at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:105)
        at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:56)
Caused by: java.net.UnknownHostException: bogon: No address associated with hostname
        at java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method)
        at java.net.InetAddress$1.lookupAllHostAddr(Unknown Source)
        at java.net.InetAddress.getAddressesFromNameService(Unknown Source)
        ... 11 more
2015.07.08 04:43:00 org.jivesoftware.util.XMLProperties - XML Property 'provider.auth.className' differs from what is stored in the database.  Please make property changes in the database instead of the configuration file.
2015.07.08 04:43:00 org.jivesoftware.util.XMLProperties - XML Property 'provider.user.className' differs from what is stored in the database.  Please make property changes in the database instead of the configuration file.
2015.07.08 04:43:07 org.jivesoftware.openfire.http.HttpSessionManager - HttpSessionManager.init() recreate sendPacketPool
2015.07.08 04:44:31 index.jsp - Failed to fetch RSS feed: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: root certificate not trusted of [vanity2.jiveon.com]
2015.07.08 04:44:46 org.jivesoftware.util.XMLProperties - XML Property 'provider.group.className' differs from what is stored in the database.  Please make property changes in the database instead of the configuration file.
root@bogon:logs# pwd
/opt/openfire/logs
root@bogon:logs#

是有:

Problems with Custom Database Integration Guide… | Ignite Realtime

openfire整合已有用户数据库(在logs里出现问题: – IamThat – 博客园

提到的:

org.jivesoftware.util.XMLProperties – XML Property ‘provider.group.className’ differs from what is stored in the database.  Please make property changes in the database instead of the configuration file.

的,所以应该是:

像那位说的,通过数据库中去导入。

2.所以去试试:

INSERT INTO `ofproperty` VALUES ('jdbcProvider.driver', 'com.mysql.jdbc.Driver');
INSERT INTO `ofproperty` VALUES ('jdbcProvider.connectionString', 'jdbc:mysql://localhost:3306/xxx?user=root&amp;password=root');
UPDATE `openfire`.`ofProperty` SET propValue='org.jivesoftware.openfire.auth.JDBCAuthProvider' WHERE name='provider.auth.className';
UPDATE `openfire`.`ofProperty` SET propValue='org.jivesoftware.openfire.user.JDBCUserProvider' WHERE name='provider.user.className';
UPDATE `openfire`.`ofProperty` SET propValue='org.jivesoftware.openfire.group.JDBCGroupProvider' WHERE name='provider.group.className';
INSERT INTO `ofproperty` VALUES ('jdbcAuthProvider.passwordSQL', 'select password from UserSecurity where userId=?');
INSERT INTO `ofproperty` VALUES ('jdbcAuthProvider.passwordType', 'plain');
INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.loadUserSQL', 'SELECT t1.username as name, t2.email as email FROM UserBase t1, UserSecurity t2 userId=?');
INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.userCountSQL', 'SELECT COUNT(*) FROM UserSecurity');
INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.allUsersSQL', 'select userId as username from UserSecurity');
INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.searchSQL', 'SELECT userId as username FROM UserSecurity WHERE');
INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.usernameField', 'username');
INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.nameField', 'name');
INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.emailField', 'email');
INSERT INTO `ofproperty` VALUES ('jdbcGroupProvider.groupCountSQL', 'SELECT count(*) FROM GroupInfo');
INSERT INTO `ofproperty` VALUES ('jdbcGroupProvider.allGroupsSQL', 'SELECT groupId as groupName FROM GroupInfo');
INSERT INTO `ofproperty` VALUES ('jdbcGroupProvider.userGroupsSQL', 'SELECT groupId as groupName FROM GroupUser WHERE userId=?');
INSERT INTO `ofproperty` VALUES ('jdbcGroupProvider.descriptionSQL', 'SELECT groupName as groupDescription FROM GroupInfo WHERE groupId=?');
INSERT INTO `ofproperty` VALUES ('jdbcGroupProvider.loadMembersSQL', 'SELECT userId as username FROM GroupUser WHERE groupId=? AND isAdmin=\'N\'');
INSERT INTO `ofproperty` VALUES ('jdbcGroupProvider.loadAdminsSQL', 'SELECT userId as username FROM GroupUser WHERE groupId=? AND isAdmin=\'Y\'');

 

写好了sql语句:

然后去导入:

root@bogon:develop# mysql -u root -p openfire_zzz < openfire_external_mysql.sql 
Enter password: 
ERROR 1062 (23000) at line 1: Duplicate entry 'jdbcProvider.driver' for key 'PRIMARY'

再进入mysql中看看结果:

root@bogon:develop# mysql -u root -p openfire_ccc
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24993
Server version: 5.5.44-cll-lve MySQL Community Server (GPL) by Atomicorp

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from ofProperty;
+--------------------------------------------+------------------------------------------------------------------------------------------+
| name                                       | propValue                                                                         |
+--------------------------------------------+------------------------------------------------------------------------------------------+
| adminConsole.port                          | 9090                                                                         |
| adminConsole.securePort                    | 9091                                                                    |
| connectionProvider.className               | org.jivesoftware.database.DefaultConnectionProvider                                      |
| database.defaultProvider.connectionTimeout | 1.0                                                          |
| database.defaultProvider.driver            | com.mysql.jdbc.Driver                                      |
| database.defaultProvider.maxConnections    | 25                                                            |
| database.defaultProvider.minConnections    | 5                                                               |
| database.defaultProvider.password          | 033b026b73e667e577e75db8e1e89be18a5e2b73f753b7d0                                         |
| database.defaultProvider.serverURL         | jdbc:mysql://localhost:3306/openfire_ddddd                                           |
| database.defaultProvider.testAfterUse      | false                                                              |
| database.defaultProvider.testBeforeUse     | false                                                             |
| database.defaultProvider.testSQL           | select 1                                                            |
| database.defaultProvider.username          | 5dd94410019658abf45bed61292a9278e345af92686462d9                                         |
| jdbcAuthProvider.passwordSQL               | select password from UserSecurity where userId=?                                         |
| jdbcAuthProvider.passwordType              | plain                                                               |
| jdbcGroupProvider.allGroupsSQL             | SELECT groupId as groupName FROM GroupInfo                                               |
| jdbcGroupProvider.descriptionSQL           | SELECT groupName as groupDescription FROM GroupInfo WHERE groupId=?                      |
| jdbcGroupProvider.groupCountSQL            | SELECT count(*) FROM GroupInfo           |
| jdbcGroupProvider.loadAdminsSQL            | SELECT userId as username FROM GroupUser WHERE groupId=? AND isAdmin='Y'                 |
| jdbcGroupProvider.loadMembersSQL           | SELECT userId as username FROM GroupUser WHERE groupId=? AND isAdmin='N'                 |
| jdbcGroupProvider.userGroupsSQL            | SELECT groupId as groupName FROM GroupUser WHERE userId=?                                |
| jdbcProvider.connectionString              | jdbc:mysql://localhost:3306/rrrrrrrrr?user=root&password=root                            |
| jdbcProvider.driver                        | com.mysql.jdbc.Driver                                               |
| jdbcUserProvider.allUsersSQL               | select userId as username from UserSecurity  |
| jdbcUserProvider.emailField                | email                                                                   |
| jdbcUserProvider.loadUserSQL               | SELECT t1.username as name, t2.email as email FROM UserBase t1, UserSecurity t2 userId=? |
| jdbcUserProvider.nameField                 | name                                                                  |
| jdbcUserProvider.searchSQL                 | SELECT userId as username FROM UserSecurity WHERE                                        |
| jdbcUserProvider.userCountSQL              | SELECT COUNT(*) FROM UserSecurity          |
| jdbcUserProvider.usernameField             | username                                                        |
| locale                                     | zh_CN                                                                                  |
| passwordKey                                | IbM546F0lyV9PSm                                                      |
| provider.admin.className                   | org.jivesoftware.openfire.admin.DefaultAdminProvider                                     |
| provider.auth.className                    | org.jivesoftware.openfire.auth.DefaultAuthProvider                                       |
| provider.group.className                   | org.jivesoftware.openfire.group.DefaultGroupProvider                                     |
| provider.lockout.className                 | org.jivesoftware.openfire.lockout.DefaultLockOutProvider                                 |
| provider.securityAudit.className           | org.jivesoftware.openfire.security.DefaultSecurityAuditProvider                          |
| provider.user.className                    | org.jivesoftware.openfire.user.DefaultUserProvider                                       |
| provider.vcard.className                   | org.jivesoftware.openfire.vcard.DefaultVCardProvider                                     |
| setup                                      | true                                                                                     |
| update.lastCheck                           | 1436297725067                                                         |
| xmpp.auth.anonymous                        | true                                                                      |
| xmpp.domain                                | bogon                                                                         |
| xmpp.session.conflict-limit                | 0                                                                             |
| xmpp.socket.ssl.active                     | true                                                                           |
+--------------------------------------------+------------------------------------------------------------------------------------------+
45 rows in set (0.00 sec)

mysql>

好像基本都更新完毕了。

3.重启openfire,用另外的那个数据库的用户,重新登陆试试,还是无法登陆:

login failed make sure your username and password are correct and that your’re an admin or moderator

login failed make sure your username and password are correct

4.然后如果用

admin

admin

登陆后,

进去后看到的用户,还是openfire的数据库中的用户

use admin admin login only see admin

而不是另外的那个数据库中的用户。。

Navicat for MySQL show another database user 123

所以还是没成功。。。

5.搜:

openfire Custom Database not work

参考:

integration – Openfire Custom DB inegration fails after upgrade – Stack Overflow

properties – How do I configure Openfire to use custom database using openfire.xml? – Stack Overflow

没用。

6.再去重启mysql和OpenFire:

root@bogon:develop# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
root@bogon:develop# service openfire restart
Shutting down openfire:                                    [  OK  ]
Starting openfire:

结果使用:用户123,还是无法登陆。

7.搜:

openfire custom database Login failed: make sure your username and password are correct

参考:

Cannot Login to Admin Console Openfire 3.6.4 | Ignite Realtime

mysql – Openfire Custom Database Login – Stack Overflow

Openfire/openfire_i18n_en.properties at master · igniterealtime/Openfire · GitHub

Custom auth database | Ignite Realtime

8.然后去解决:

【已解决】OpenFire无法解析domain:org.jivesoftware.openfire.XMPPServer – Unable to determine local hostname

9.参考:

Openfire integration with Custom Mysql database… | Ignite Realtime

好像是需要改那个defaultProvider,抽空去试试。

但是从含义上很明显:defaultProvider写了用哪个,OpenFire就会去连接哪个。

先去从OpenFire控制台中的系统属性,中去把

database.defaultProvider.serverURL =  jdbc:mysql://localhost:3306/openfire_eeeeeeeeeeee

openfire system properties database.defaultProvider.serverURL change

改为:

jdbc:mysql://localhost:3306/qqqqqqqqqqqqqqq?user=root&amp;password=root

edit database.defaultProvider.serverURL to another

然后退出OpenFire,重新登陆试试:

结果还是不行。

10.搜:

openfire 自定义数据库

参考:

openfire使用自定义用户表 – nomousewch的专栏 – 博客频道 – CSDN.NET

看看作者是如何将默认的DefaultAuthProvider配置为JDBCAuthProvider的。

没看到,但是看到有个:

(‘jdbcAuthProvider.useConnectionProvider’, ‘true’);

是之前没看到的,所以去研究一下。

搜:

jdbcAuthProvider useConnectionProvider

参考:

JDBCAuthProvider (Openfire 3.10.2 Javadoc)

“In order to use the configured JDBC connection provider do not use a JDBC connection string, set the following property

  • jdbcAuthProvider.useConnectionProvider = true

Openfire/JDBCAuthProvider.java at master · igniterealtime/Openfire · GitHub

https://github.com/igniterealtime/Openfire/blob/master/src/java/org/jivesoftware/openfire/auth/JDBCAuthProvider.java

使用django的用户帐号登录openfire – 江湖 – 51CTO技术博客

Openfire 整合数据库 – JarrahWu – 博客园

openfire自定义数据库集成指南-Harries Blog™

去添加:

admin.authorizedUsernames

的设置,加上用户123,看看能否允许第三方数据库的用户名去登陆。

注销,重新用123去登陆试试。

11.搜:

openfire 集成现有数据库

参考:

openfire3.6.4外连数据库(mysql) – rainbird – 51CTO技术博客

再去添加类似:

(‘admin.authorizedJIDs’,’rainbird@localhost’),

的值:

change admin.authorizedJIDs to 123 localhost

注销后,重新试试用123去登陆,错误依旧。

并且发现,admin也无法登陆了。

此时可以确定:

的确是:

admin.authorizedJIDs

决定了,管理员登陆的用户支持哪些。

由于把默认的admin去掉了,添加了

123@localhost

但是估计是无效的值,或者写错了,所以才出错的。

12.搜:

admin.authorizedJIDs

参考:

Adding or deleting admin accounts to Openfire | Ignite Realtime

去设置:

root@bogon:logs# mysql -u root -p openfire_ooooooooo
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32473
Server version: 5.5.44-cll-lve MySQL Community Server (GPL) by Atomicorp

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DELETE FROM OFPROPERTY WHERE NAME='admin.authorizedJIDs'; 
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO OFPROPERTY VALUES('admin.authorizedJIDs','admin@localhost,123');  
Query OK, 1 row affected (0.03 sec)

mysql> select * from ofproperty;
+--------------------------------------------+------------------------------------------------------------------------------------------+
| name                                       | propValue                                                                         |
+--------------------------------------------+------------------------------------------------------------------------------------------+
| admin.authorizedJIDs                       | admin@localhost,123                                           |
| admin.authorizedUsernames                  | 123                                                                   |
| adminConsole.port                          | 9090                                                                         |
| adminConsole.securePort                    | 9091                                                                    |
| connectionProvider.className               | org.jivesoftware.database.DefaultConnectionProvider                                      |
| database.defaultProvider.connectionTimeout | 1.0                                                          |
| database.defaultProvider.driver            | com.mysql.jdbc.Driver                                      |
| database.defaultProvider.maxConnections    | 25                                                            |
| database.defaultProvider.minConnections    | 5                                                               |
| database.defaultProvider.password          | 033b026b73e667e577e75db8e1e89be18a5e2b73f753b7d0                                         |
| database.defaultProvider.serverURL         | jdbc:mysql://localhost:3306/openfire_pppppppppp                                           |
| database.defaultProvider.testAfterUse      | false                                                              |
| database.defaultProvider.testBeforeUse     | false                                                             |
| database.defaultProvider.testSQL           | select 1                                                            |
| database.defaultProvider.username          | 5dd94410019658abf45bed61292a9278e345af92686462d9                                         |
| jdbcAuthProvider.passwordSQL               | select password from UserSecurity where userId=?                                         |
| jdbcAuthProvider.passwordType              | plain                                                               |
| jdbcGroupProvider.allGroupsSQL             | SELECT groupId as groupName FROM GroupInfo                                               |
| jdbcGroupProvider.descriptionSQL           | SELECT groupName as groupDescription FROM GroupInfo WHERE groupId=?                      |
| jdbcGroupProvider.groupCountSQL            | SELECT count(*) FROM GroupInfo           |
| jdbcGroupProvider.loadAdminsSQL            | SELECT userId as username FROM GroupUser WHERE groupId=? AND isAdmin='Y'                 |
| jdbcGroupProvider.loadMembersSQL           | SELECT userId as username FROM GroupUser WHERE groupId=? AND isAdmin='N'                 |
| jdbcGroupProvider.userGroupsSQL            | SELECT groupId as groupName FROM GroupUser WHERE userId=?                                |
| jdbcProvider.connectionString              | jdbc:mysql://localhost:3306/wwwwwwwwwwwwwww?user=root&password=root                            |
| jdbcProvider.driver                        | com.mysql.jdbc.Driver                                               |
| jdbcUserProvider.allUsersSQL               | select userId as username from UserSecurity  |
| jdbcUserProvider.emailField                | email                                                                   |
| jdbcUserProvider.loadUserSQL               | SELECT t1.username as name, t2.email as email FROM UserBase t1, UserSecurity t2 userId=? |
| jdbcUserProvider.nameField                 | name                                                                  |
| jdbcUserProvider.searchSQL                 | SELECT userId as username FROM UserSecurity WHERE                                        |
| jdbcUserProvider.userCountSQL              | SELECT COUNT(*) FROM UserSecurity          |
| jdbcUserProvider.usernameField             | username                                                        |
| locale                                     | zh_CN                                                                                  |
| locale.timeZone                            | Asia/Taipei                                                                 |
| passwordKey                                | IbM546F0lyV9PSm                                                      |
| provider.admin.className                   | org.jivesoftware.openfire.admin.DefaultAdminProvider                                     |
| provider.auth.className                    | org.jivesoftware.openfire.auth.DefaultAuthProvider                                       |
| provider.group.className                   | org.jivesoftware.openfire.group.DefaultGroupProvider                                     |
| provider.lockout.className                 | org.jivesoftware.openfire.lockout.DefaultLockOutProvider                                 |
| provider.securityAudit.className           | org.jivesoftware.openfire.security.DefaultSecurityAuditProvider                          |
| provider.user.className                    | org.jivesoftware.openfire.user.DefaultUserProvider                                       |
| provider.vcard.className                   | org.jivesoftware.openfire.vcard.DefaultVCardProvider                                     |
| setup                                      | true                                                                                     |
| update.lastCheck                           | 1436297725067                                                         |
| xmpp.auth.anonymous                        | true                                                                      |
| xmpp.domain                                | localhost                                                                     |
| xmpp.session.conflict-limit                | 0                                                                             |
| xmpp.socket.ssl.active                     | true                                                                           |
+--------------------------------------------+------------------------------------------------------------------------------------------+
48 rows in set (0.00 sec)

mysql> quit
Bye
root@bogon:logs#

再去登陆openfire后台看看:

结果无法用admin或123去登陆。。。

重启openfire再去试试,结果是可以用admin登陆了。

13.参考:

openfire用户整合遇到UnsupportedOperationException异常及解决方法 – newbenagui的专栏 – 博客频道 – CSDN.NET

http://blog.csdn.net/newbenagui/article/details/37764509

去把:

jdbcUserProvider.useConnectionProvider设置为true

change jdbcUserProvider.useConnectionProvider to true

然后注销后,重新登陆试试:

还是不行。

14.参考:

openfire 整合现有表_完美冠军之家_新浪博客

去把123改为123@localhost

 

add admin.authorizedJIDs 123 and admin localhost

但是重新登陆后还是不行。

15.看到:

[记录] Openfire集成现有系统数据库用户 – OK Computer

整理的很全。

再去把123换成另外一个用户1000001去试试:

admin.authorizedJIDs change to admin and another

重启openfire,再去登陆试试,还是不行。

再去随便改改其他值,删除其他值。

再去改改另外要接入的数据库的用户的密码,为

原先的openfire_oooooooooo中的admin的密码是:

fdead603f65d552db24ccc102ab5fdff6d8da12777312a3d

然后放到llllllllllll中的:

才发现之前已经放过来了,不过是放到123这个用户上的:

change 123 user password to encrypted one

然后之前试过了用123,是没法登陆的。。

此处,顺便看到了:

admin的email是:admin@bogon

觉得不对,去改为:admin@localhost

admin email to admin at localhost

还是去试试,把10000001的密码,从:

95e2b97393bd7264c402a0fc247e817e53e83434c5cd5951

改为:

change 1000001 user password to fe one

然后再去试试:

还是不行。

 

现在去:

用openfire_aaaaaaaa中创建一个用户和密码,然后把用户名和密码放到aaaaaaaaa中。然后再删掉openfire_aaaaaaaaa中的,看看能不能登陆。

create new user to test login

openfire user added new one

然后去看看数据库中的用户名和密码:

root@bogon:logs# mysql -u root -p openfire_zzzzzzzzzzzzzz     
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34195
Server version: 5.5.44-cll-lve MySQL Community Server (GPL) by Atomicorp

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from ofuser;
+----------+---------------+--------------------------------------------------+---------------+-----------------+-----------------+------------------+
| username | plainPassword | encryptedPassword                                | name          | email           | creationDate    | modificationDate |
+----------+---------------+--------------------------------------------------+---------------+-----------------+-----------------+------------------+
| admin    | NULL          | fdead603f65d552db24ccc102ab5fdff6d8da12777312a3d | Administrator | admin@localhost | 001436301510664 | 0                |
| zbl      | NULL          | 688a878593a905ebfa694ebf96d6b1a1                 | zhangbinglian | [email protected]     | 001436447295864 | 001436447295864  |
+----------+---------------+--------------------------------------------------+---------------+-----------------+-----------------+------------------+
2 rows in set (0.00 sec)

mysql>

然后把:

用户名:zzzzzzzz

(加密后的)密码:688a878593a905ebfa694ebf96d6b1a1

先删除掉:

再去另外aaaaaaaaaaaa数据库中加上:

结果无法加,因为用户名必须是数字。。。

所以放弃,换成新建用户123:

用户名:123

密码:123

新建后,去看123加密后的密码是:

mysql> select * from ofuser;
+----------+---------------+--------------------------------------------------+---------------+-----------------+-----------------+------------------+
| username | plainPassword | encryptedPassword                                | name          | email           | creationDate    | modificationDate |
+----------+---------------+--------------------------------------------------+---------------+-----------------+-----------------+------------------+
| 123      | NULL          | 55e42f035823a0276b6992943a304bce                 | 123           | [email protected]     | 001436447511460 | 001436447511460  |
| admin    | NULL          | fdead603f65d552db24ccc102ab5fdff6d8da12777312a3d | Administrator | admin@localhost | 001436301510664 | 0                |
+----------+---------------+--------------------------------------------------+---------------+-----------------+-----------------+------------------+
2 rows in set (0.00 sec)

用户名:123

(加密后的)密码:55e42f035823a0276b6992943a304bce

放到另外的数据库中,

另外再去弄一个:

用户名:123456

密码:123456

结果是:

用户名:123456

密码:

123456

加密后是:5f52e5aa779a19892af855a75766641d47c72e96b26f47e2

然后再去加到另外的数据库中:

use new created 123 and 123456 encrypted password put to mysql

果然:

iiiiiiiiiiiiiiii中的

(用户10000001的)密码是123456,加密后是:

fdead603f65d552db24ccc102ab5fdff6d8da12777312a3d

而openfire_aaaaaaaaaaa中的(用户123456的)的密码123456,加密后是:

5f52e5aa779a19892af855a75766641d47c72e96b26f47e2

是不一样的。

 

把原先的加的123和123456两个新加用户都删除掉,然后确保另外的数据库中都有对应用户名和密码。

再去加到配置中。

(发现:

当创建用户,勾选了:

是否为管理员,授予Openfire管理员权限

时,系统会自动把其信息:

xxx@localhost

加入到:

admin.authorizedJIDs

中的)

added 123 and 123456 to admin.authorizedJIDs

 

再去重新登陆试试:

结果

123

123

 

123456

123456

 

10000001

admin

都无法登陆。。。

 

再去故意把:

之前的log都清空:

openfire log to clear

四种错误都清空了。

然后再重新用123456,10000001登陆看看log:

看到warn.log

2015.07.09 22:30:43 index.jsp - Failed to fetch RSS feed: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: root certificate not trusted of [vanity2.jiveon.com]
2015.07.09 22:31:34 org.jivesoftware.admin.LoginLimitManager - Failed admin console login attempt by 10000001 from 58.208.67.76
2015.07.09 22:31:44 org.jivesoftware.admin.LoginLimitManager - Failed admin console login attempt by 123 from 58.208.67.76
2015.07.09 22:31:50 index.jsp - Failed to fetch RSS feed: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: root certificate not trusted of [vanity2.jiveon.com]

好像关系不大?

去启用调试的log:

enabled openfire log show debug info

然后用123456去登录,对应的debug.log:

2015.07.09 22:33:35 org.jivesoftware.openfire.admin.DefaultAdminProvider - DefaultAdminProvider: Property was set: log.debug.enabled
2015.07.09 22:33:52 org.jivesoftware.util.WebManager - Unexpected exception (which is ignored) while trying to obtain user.
java.lang.NullPointerException
 at org.jivesoftware.util.WebManager.getUser(WebManager.java:147)
 at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:154)
 at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
 at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585)
 at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
 at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)
 at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)
 at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127)
 at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
 at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
 at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061)
 at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
 at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:215)
 at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:110)
 at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
 at org.eclipse.jetty.server.Server.handle(Server.java:497)
 at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310)
 at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
 at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
 at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
 at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
 at java.lang.Thread.run(Unknown Source)
2015.07.09 22:34:03 org.jivesoftware.util.Log - 
org.jivesoftware.openfire.auth.UnauthorizedException
 at org.jivesoftware.openfire.auth.DefaultAuthProvider.authenticate(DefaultAuthProvider.java:83)
 at org.jivesoftware.openfire.auth.AuthFactory.authenticate(AuthFactory.java:213)
 at org.jivesoftware.openfire.admin.login_jsp._jspService(login_jsp.java:173)
 at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
 at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:808)
 at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1669)
 at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:39)
 at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
 at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:74)
 at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
 at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:50)
 at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
 at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:78)
 at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
 at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:159)
 at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
 at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585)
 at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
 at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)
 at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)
 at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127)
 at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
 at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
 at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061)
 at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
 at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:215)
 at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:110)
 at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
 at org.eclipse.jetty.server.Server.handle(Server.java:497)
 at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310)
 at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
 at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
 at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
 at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
 at java.lang.Thread.run(Unknown Source)
2015.07.09 22:34:11 org.jivesoftware.util.Log - 
org.jivesoftware.openfire.auth.UnauthorizedException
 at org.jivesoftware.openfire.auth.DefaultAuthProvider.authenticate(DefaultAuthProvider.java:83)
 at org.jivesoftware.openfire.auth.AuthFactory.authenticate(AuthFactory.java:213)
 at org.jivesoftware.openfire.admin.login_jsp._jspService(login_jsp.java:173)
 at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
 at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:808)
 at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1669)
 at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:39)
 at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
 at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:74)
 at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
 at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:50)
 at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
 at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:78)
 at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
 at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:159)
 at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
 at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585)
 at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
 at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)
 at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)
 at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127)
 at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
 at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
 at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061)
 at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
 at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:215)
 at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:110)
 at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
 at org.eclipse.jetty.server.Server.handle(Server.java:497)
 at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310)
 at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
 at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
 at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
 at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
 at java.lang.Thread.run(Unknown Source)
2015.07.09 22:34:16 org.jivesoftware.util.CertificateManager - Parsing otherName for subject alternative names: 1.3.6.1.5.5.7.8.5
2015.07.09 22:34:16 org.jivesoftware.util.CertificateManager - ... processing DERTaggedObject: [0][0]*.bogon
2015.07.09 22:34:16 org.jivesoftware.util.CertificateManager - Parsing otherName for subject alternative names: 1.3.6.1.5.5.7.8.5
2015.07.09 22:34:16 org.jivesoftware.util.CertificateManager - ... processing DERTaggedObject: [0][0]*.bogon
2015.07.09 22:34:16 org.jivesoftware.util.CertificateManager - Check for certificate for 'localhost' using algorithm RSA returned: false
2015.07.09 22:34:16 org.jivesoftware.util.log.util.CommonsLogFactory - Set parameter http.connection.timeout = 3000
2015.07.09 22:34:16 org.jivesoftware.util.log.util.CommonsLogFactory - Set parameter http.socket.timeout = 3000
2015.07.09 22:34:16 org.jivesoftware.util.log.util.CommonsLogFactory - Open connection to community.igniterealtime.org:443
2015.07.09 22:34:16 org.jivesoftware.util.log.util.CommonsLogFactory - >> "GET /blogs/ignite/feeds/posts HTTP/1.1[\r][\n]"
2015.07.09 22:34:16 org.jivesoftware.util.log.util.CommonsLogFactory - Adding Host request header
2015.07.09 22:34:16 org.jivesoftware.util.log.util.CommonsLogFactory - >> "Accept-Encoding: gzip[\r][\n]"
2015.07.09 22:34:16 org.jivesoftware.util.log.util.CommonsLogFactory - >> "User-Agent: Jakarta Commons-HttpClient/3.1[\r][\n]"
2015.07.09 22:34:16 org.jivesoftware.util.log.util.CommonsLogFactory - >> "Host: community.igniterealtime.org[\r][\n]"
2015.07.09 22:34:16 org.jivesoftware.util.log.util.CommonsLogFactory - >> "[\r][\n]"
2015.07.09 22:34:16 org.jivesoftware.util.log.util.CommonsLogFactory - Closing the connection.
2015.07.09 22:34:16 org.jivesoftware.util.log.util.CommonsLogFactory - Method retry handler returned false. Automatic recovery will not be attempted
2015.07.09 22:34:16 org.jivesoftware.util.log.util.CommonsLogFactory - Releasing connection back to connection manager.
2015.07.09 22:34:16 org.jivesoftware.util.log.util.CommonsLogFactory - Releasing connection back to connection manager.
2015.07.09 22:34:16 org.jivesoftware.util.CertificateManager - Parsing otherName for subject alternative names: 1.3.6.1.5.5.7.8.5
2015.07.09 22:34:16 org.jivesoftware.util.CertificateManager - ... processing DERTaggedObject: [0][0]*.bogon
2015.07.09 22:34:16 org.jivesoftware.util.CertificateManager - Parsing otherName for subject alternative names: 1.3.6.1.5.5.7.8.5
2015.07.09 22:34:16 org.jivesoftware.util.CertificateManager - ... processing DERTaggedObject: [0][0]*.bogon
2015.07.09 22:34:16 org.jivesoftware.util.CertificateManager - Check for certificate for 'localhost' using algorithm RSA returned: false

 

加上warn.log:

2015.07.09 22:30:43 index.jsp - Failed to fetch RSS feed: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: root certificate not trusted of [vanity2.jiveon.com]
2015.07.09 22:31:34 org.jivesoftware.admin.LoginLimitManager - Failed admin console login attempt by 10000001 from 58.208.67.76
2015.07.09 22:31:44 org.jivesoftware.admin.LoginLimitManager - Failed admin console login attempt by 123 from 58.208.67.76
2015.07.09 22:31:50 index.jsp - Failed to fetch RSS feed: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: root certificate not trusted of [vanity2.jiveon.com]
2015.07.09 22:34:03 org.jivesoftware.admin.LoginLimitManager - Failed admin console login attempt by 123456 from 58.208.67.76
2015.07.09 22:34:11 org.jivesoftware.admin.LoginLimitManager - Failed admin console login attempt by 123 from 58.208.67.76
2015.07.09 22:34:16 index.jsp - Failed to fetch RSS feed: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: root certificate not trusted of [vanity2.jiveon.com]

基本可以看出来:

上述的log中,就是

多次的用户名无法登陆的log和后面的RSS的错误的log

对应的是调用:

org.jivesoftware.openfire.auth.UnauthorizedException
 at org.jivesoftware.openfire.auth.DefaultAuthProvider.authenticate(DefaultAuthProvider.java:83)
 at org.jivesoftware.openfire.auth.AuthFactory.authenticate(AuthFactory.java:213)
 at org.jivesoftware.openfire.admin.login_jsp._jspService(login_jsp.java:173)

中的

DefaultAuthProvider.authenticate

去验证的。

相关代码和api解释是:

DefaultAuthProvider (Openfire 3.10.2 Javadoc)

Default AuthProvider implementation. It authenticates against the ofUser database table and supports plain text and digest authentication. Because each call to authenticate() makes a database connection, the results of authentication should be cached whenever possible.

Openfire/DefaultAuthProvider.java at master · Connectify/Openfire · GitHub

code autheticate for openfire user login

感觉是:

现在拿了

用户名:123456

密码:123456

去登陆,然后系统连接 原先openfire自己的数据库

openfire_aaaaaaaaa

然后当然是找不到,这个本来属于另外一个数据库aaaaaaaaa中的用户名了。

但是问题在于:

为何我们之前按照教程去配置了:

INSERT INTO `ofproperty` VALUES ('jdbcProvider.driver', 'com.mysql.jdbc.Driver');
INSERT INTO `ofproperty` VALUES ('jdbcProvider.connectionString', 'jdbc:mysql://localhost:3306/aaaaaa?user=root&amp;password=root');
UPDATE `openfire`.`ofProperty` SET propValue='org.jivesoftware.openfire.auth.JDBCAuthProvider' WHERE name='provider.auth.className';

等等信息后,还是无法连接第三方的数据库呢。。

 

看了下:

https://github.com/Connectify/Openfire/blob/master/src/java/org/jivesoftware/openfire/auth/AuthFactory.java

的代码,

感觉是:

后面调用到的authProvider,都是初始化好的那个authProvider:

private static void initProvider() {
    // Convert XML based provider setup to Database based
    JiveGlobals.migrateProperty("provider.auth.className");
    String className = JiveGlobals.getProperty("provider.auth.className",
            "org.jivesoftware.openfire.auth.DefaultAuthProvider");
    // Check if we need to reset the auth provider class
    if (authProvider == null || !className.equals(authProvider.getClass().getName())) {
        try {
            Class c = ClassUtils.forName(className);
            authProvider = (AuthProvider)c.newInstance();
        }
        catch (Exception e) {
            Log.error("Error loading auth provider: " + className, e);
            authProvider = new DefaultAuthProvider();
        }
    }
}

然后就去分析代码。

对于:

JiveGlobals.migrateProperty(“provider.auth.className”);

由于我的

/opt/openfire/conf/openfire.xml

中没有(已删除多余的配置,直接写到数据库里面了)

所以会不会被冲突掉为空了?

如何才能调试看到内部处理过程?

去查

JiveGlobals.migrateProperty

参考:

JiveGlobals (Openfire 3.8.2 Javadoc)

migrateProperty
public static void <b>migrateProperty</b>(<a href="http://java.sun.com/j2se/1.5.0/docs/api/java/lang/String.html">String</a> name)
Convenience routine to migrate an XML property into the database storage method. Will check for the XML property being null before migrating.
Parameters:
name – the name of the property to migrate.

应该是没有冲突掉。

但是代码执行期间到底何值,也还是不清楚。

 

参考:

openfire整合现有系统用户 – 时迁 – ITeye技术网站

http://loushi135.iteye.com/blog/1922089

去试试:

把原先的另外数据库的用户的密码的加密方式,从plain,改为别的试试:

*密码类型可以是:plain(文本),md5,sha1,sha256,sha512

md5:

把:

| jdbcAuthProvider.passwordSQL               | select password from UserSecurity where userId=?                                         |
| jdbcAuthProvider.passwordType              | plain

用:

mysql> UPDATE ofproperty SET propValue = 'md5' WHERE `name` = 'jdbcAuthProvider.passwordType';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

改为md5:

| jdbcAuthProvider.passwordType              | md5

然后重启Openfire再去登陆试试。

还是不行。

再去改为sha1,结果还是不行。

再去改为sha256,也还是不行。

 

搜:

openfire DefaultAuthProvider JDBCAuthProvider

参考:

Openfire Authentication | Ignite Realtime

看了讨论,还是没太看懂。

尤其是那个salt和passwordKey

Openfire to use custom user table – Database – Database Skill

有价值的参考资料:

1.各种属性值的含义:

Openfire Properties | Ignite Realtime

把:

provider.auth.className=org.jivesoftware.openfire.auth.DefaultAuthProvider

改为:

provider.auth.className=org.jivesoftware.openfire.auth.JDBCAuthProvider

 

然后此刻:

openfire中,admin都无法登陆了:

看来的确生效了。

但是进不去了。

貌似是密码类型错了。

所以再改回plain:

 

需要再去改:

| provider.admin.className                   | org.jivesoftware.openfire.admin.DefaultAdminProvider                       |
| provider.auth.className                    | org.jivesoftware.openfire.auth.JDBCAuthProvider                                   |
| provider.group.className                   | org.jivesoftware.openfire.group.DefaultGroupProvider                         |
| provider.lockout.className                 | org.jivesoftware.openfire.lockout.DefaultLockOutProvider                   |
| provider.securityAudit.className           | org.jivesoftware.openfire.security.DefaultSecurityAuditProvider       |
| provider.user.className                    | org.jivesoftware.openfire.user.DefaultUserProvider                                   |
| provider.vcard.className                   | org.jivesoftware.openfire.vcard.DefaultVCardProvider

为:

| provider.admin.className                   | org.jivesoftware.openfire.admin.DefaultAdminProvider                         |
| provider.auth.className                    | org.jivesoftware.openfire.auth.JDBCAuthProvider                                     |
| provider.group.className                   | org.jivesoftware.openfire.group.DefaultGroupProvider                           |
| provider.lockout.className                 | org.jivesoftware.openfire.lockout.DefaultLockOutProvider                   |
| provider.securityAudit.className           | org.jivesoftware.openfire.security.DefaultSecurityAuditProvider         |
| provider.user.className                    | org.jivesoftware.openfire.user.JDBCUserProvider                                       |
| provider.vcard.className                   | org.jivesoftware.openfire.vcard.DefaultVCardProvider

确保用户名和密码是对的。

 

弄到最后,终于对了:

通过sql语句:

UPDATE ofproperty SET propValue = 'org.jivesoftware.openfire.user.JDBCUserProvider' WHERE `name` = 'provider.user.className';
UPDATE ofproperty SET propValue = 'org.jivesoftware.openfire.auth.JDBCAuthProvider' WHERE `name` = 'provider.auth.className';

等价于之前别人写入到配置文件:

/opt/openfire/conf/openfire.xml

中的:

  <provider>
    <auth>
      <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>
    </auth>  
    <user>
      <className>org.jivesoftware.openfire.user.JDBCUserProvider</className>
    </user>  
    <group>
      <className>org.jivesoftware.openfire.group.JDBCGroupProvider</className>
    </group>
  </provider>

 

如此使得了:

Openfire对于用户user和用户的密码验证auth,都去调用对应的:

JDBCUserProvider

JDBCAuthProvider

而不是之前默认的:

DefaultUserProvider

DefaultAuthProvider

了,就可以正常的去验证了。

对应的再去改剩下那个。

 

然后再去试试,之前已经有的账号

10000001

密码是加了密的:

123456

即:

fdead603f65d552db24ccc102ab5fdff6d8da12777312a3d

所以需要去改加密为SHA1

(暂不确定是不是这个。。。)

然后再去登陆试试:

结果不行:

 

然后改了半天密码类型:

mysql> UPDATE ofproperty SET propValue = 'md5' WHERE `name` = 'jdbcAuthProvider.passwordType';    
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE ofproperty SET propValue = 'sha256' WHERE `name` = 'jdbcAuthProvider.passwordType';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE ofproperty SET propValue = 'sha512' WHERE `name` = 'jdbcAuthProvider.passwordType';  
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE ofproperty SET propValue = 'sha1' WHERE `name` = 'jdbcAuthProvider.passwordType';      
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE ofproperty SET propValue = 'plain' WHERE `name` = 'jdbcAuthProvider.passwordType';    
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>

改为plain后再去试试:

用加了密的:

fdead603f65d552db24ccc102ab5fdff6d8da12777312a3d

作为普通的密码,去登陆

use 1000001 and plain password then login

结果真的就登陆进去了:

openfire use 1000001 login see current user

说明:

此处的密码的类型

jdbcAuthProvider.passwordType

真的是有效的

且在mysql数据库中改了之后,无需重启Openfire,即可实时去验证是否可以登陆。

 

前后试了:

md5

sha1

sha256

sha512

都不行。

只有plain可以。

 

当然,可能也是:

另外的数据库的密码的算法和此处设置的不同:

好像对方用的是Bluefish。。。

 

去查了查:

再去试试MD5的123456.

再去试试:

结果好像由于得到的md5值不对?

导致无法登陆。。。

 

估计需要抽空确认能否得到真正的md5(或其他加密算法的)的值,才好继续验证。。。

然后后续又去试了其他加密方式:

mysql> UPDATE ofproperty SET propValue = 'md5' WHERE `name` = 'jdbcAuthProvider.passwordType';    
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE ofproperty SET propValue = 'sha256' WHERE `name` = 'jdbcAuthProvider.passwordType';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE ofproperty SET propValue = 'sha512' WHERE `name` = 'jdbcAuthProvider.passwordType';  
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE ofproperty SET propValue = 'sha1' WHERE `name` = 'jdbcAuthProvider.passwordType';      
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE ofproperty SET propValue = 'plain' WHERE `name` = 'jdbcAuthProvider.passwordType';    
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE ofproperty SET propValue = 'md5' WHERE `name` = 'jdbcAuthProvider.passwordType';    
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE ofproperty SET propValue = 'plain' WHERE `name` = 'jdbcAuthProvider.passwordType';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE ofproperty SET propValue = 'md5' WHERE `name` = 'jdbcAuthProvider.passwordType';  
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE ofproperty SET propValue = 'plain' WHERE `name` = 'jdbcAuthProvider.passwordType';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE ofproperty SET propValue = 'sha1' WHERE `name` = 'jdbcAuthProvider.passwordType';    
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>

 

再去试试:

123456的SHA1加密后的:

7c4a8d09ca3762af61e59520943dc26494f8941b

 

看看效果:

是可以登陆进去的:

use 123456 sha1 to login also ok

 

所以目前是通过:

mysql> UPDATE ofproperty SET propValue = 'sha1' WHERE `name` = 'jdbcAuthProvider.passwordType';    
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

设置了加密算法是SHA1,

然后另外的数据库中有对应的

用户名:123456

密码:明文123456用SHA1加密后的:7c4a8d09ca3762af61e59520943dc26494f8941b

然后就可以登陆了。

 

【总结】

之前一直未能使用已有数据库去登陆的原因是:

1.自己没有设置正确对应的属性值:

provider.auth.className=org.jivesoftware.openfire.auth.JDBCAuthProvider
provider.user.className=org.jivesoftware.openfire.user.JDBCUserProvider
provider.group.className=org.jivesoftware.openfire.group.JDBCGroupProvider

尤其是其中的:

org.jivesoftware.openfire.auth.JDBCAuthProvider

org.jivesoftware.openfire.user.JDBCUserProvider

最关键。

 

2.而之所以没有设置好上述对应的值,是因为:

之前自己参考别人的写法,去写了个sql语句,即schema:

UPDATE `openfire`.`ofProperty` SET propValue='org.jivesoftware.openfire.auth.JDBCAuthProvider' WHERE name='provider.auth.className';
UPDATE `openfire`.`ofProperty` SET propValue='org.jivesoftware.openfire.user.JDBCUserProvider' WHERE name='provider.user.className';
UPDATE `openfire`.`ofProperty` SET propValue='org.jivesoftware.openfire.group.JDBCGroupProvider' WHERE name='provider.group.className';

而此处我的Openfire的数据库不是openfire,而是openfire_aaaaaaaa。

所以上述三句话根本没有生效。。。

而我自己误以为生效了。

 

3.再说说,对应的,正确配置上述三个值的做法是:

(1)如何在合适的位置设置对应的openfire的配置

官网说是在对应的配置文件:

不要把配置加到官网说的配置文件:

/opt/openfire/conf/openfire.xml

中,而是去把配置直接加到openfire数据库中的ofProperty中。

因为代码执行内部,也最终还是会通过JiveGlobals.migrateProperty把xml中的配置搬到数据库中的。

所以:

为了不产生歧义,直接去数据库中配置就好了。

 

4.如何把配置加到数据库中:

问题变成了:

如何查看和修改到当前的真正生效的openfire的配置

(1)去mysql数据库中看:

先登陆mysql数据库:

mysql -u root -p openfire_aaaaaaaa

进去后,查看ofproperty中的属性值:

select * from ofproperty;

可以找到对应的配置,比如:

| provider.auth.className                    | org.jivesoftware.openfire.auth.JDBCAuthProvider                                         |
| provider.group.className                   | org.jivesoftware.openfire.group.DefaultGroupProvider                               |                              | 
| provider.user.className                    | org.jivesoftware.openfire.user.JDBCUserProvider

(2)在openfire管理控制台,即登陆进去后的页面中的:

服务器->服务器管理器->系统属性

中,也同样可以看到当前的属性值:

该界面同时支持修改、添加、删除等操作:

比mysql中的命令行界面中去操作,更加方便些。

 

5.需要修改哪些属性配置:

那就是需要你自己参考包括官网和其他人写的文档,详见:

【整理】Openfire使用心得+资料整理

去写出自己的配置了。

此处贴上我自己的一些配置供参考:

INSERT INTO `ofproperty` VALUES ('jdbcProvider.driver', 'com.mysql.jdbc.Driver');
INSERT INTO `ofproperty` VALUES ('jdbcProvider.connectionString', 'jdbc:mysql://localhost:3306/xxxxxxxxxxx?user=root&amp;password=root');
UPDATE `ofProperty` SET propValue='org.jivesoftware.openfire.auth.JDBCAuthProvider' WHERE name='provider.auth.className';
UPDATE `ofProperty` SET propValue='org.jivesoftware.openfire.user.JDBCUserProvider' WHERE name='provider.user.className';
UPDATE `ofProperty` SET propValue='org.jivesoftware.openfire.group.JDBCGroupProvider' WHERE name='provider.group.className';
INSERT INTO `ofproperty` VALUES ('jdbcAuthProvider.passwordSQL', 'select password from UserSecurity where userId=?');
INSERT INTO `ofproperty` VALUES ('jdbcAuthProvider.passwordType', 'plain');
INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.loadUserSQL', 'SELECT t1.username as name, t2.email as email FROM UserBase t1, UserSecurity t2 userId=?');
INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.userCountSQL', 'SELECT COUNT(*) FROM UserSecurity');
INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.allUsersSQL', 'select userId as username from UserSecurity');
INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.searchSQL', 'SELECT userId as username FROM UserSecurity WHERE');
INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.usernameField', 'username');
INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.nameField', 'name');
INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.emailField', 'email');
INSERT INTO `ofproperty` VALUES ('jdbcGroupProvider.groupCountSQL', 'SELECT count(*) FROM GroupInfo');
INSERT INTO `ofproperty` VALUES ('jdbcGroupProvider.allGroupsSQL', 'SELECT groupId as groupName FROM GroupInfo');
INSERT INTO `ofproperty` VALUES ('jdbcGroupProvider.userGroupsSQL', 'SELECT groupId as groupName FROM GroupUser WHERE userId=?');
INSERT INTO `ofproperty` VALUES ('jdbcGroupProvider.descriptionSQL', 'SELECT groupName as groupDescription FROM GroupInfo WHERE groupId=?');
INSERT INTO `ofproperty` VALUES ('jdbcGroupProvider.loadMembersSQL', 'SELECT userId as username FROM GroupUser WHERE groupId=? AND isAdmin=\'N\'');
INSERT INTO `ofproperty` VALUES ('jdbcGroupProvider.loadAdminsSQL', 'SELECT userId as username FROM GroupUser WHERE groupId=? AND isAdmin=\'Y\'');

于此相对应的xml的配置:

/opt/openfire/conf/openfire.xml

也贴出来,供参考:

 

  <jdbcProvider>
    <driver>com.mysql.jdbc.Driver</driver>  
    <connectionString>jdbc:mysql://localhost:3306/xxxxxxxxx?user=root&amp;password=root</connectionString>
  </jdbcProvider>  
  <provider>
    <auth>
      <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>
    </auth>  
    <user>
      <className>org.jivesoftware.openfire.user.JDBCUserProvider</className>
    </user>  
    <group>
      <className>org.jivesoftware.openfire.group.JDBCGroupProvider</className>
    </group>
  </provider>  
  <jdbcAuthProvider>
    <passwordSQL>select password from UserSecurity where userId=?</passwordSQL>  
    <passwordType>plain</passwordType>
  </jdbcAuthProvider>  
  <jdbcUserProvider>
    <loadUserSQL>SELECT t1.username as name, t2.email as email FROM UserBase t1, UserSecurity t2 userId=?</loadUserSQL>  
    <userCountSQL>SELECT COUNT(*) FROM UserSecurity</userCountSQL>  
    <allUsersSQL>select userId as username from UserSecurity</allUsersSQL>  
    <searchSQL>SELECT userId as username FROM UserSecurity WHERE</searchSQL>
    <usernameField>username</usernameField>  
    <nameField>name</nameField>  
    <emailField>email</emailField>
  </jdbcUserProvider>  
  <jdbcGroupProvider>
    <groupCountSQL>SELECT count(*) FROM GroupInfo</groupCountSQL>  
    <allGroupsSQL>SELECT groupId as groupName FROM GroupInfo</allGroupsSQL>
    <userGroupsSQL>SELECT groupId as groupName FROM GroupUser WHERE userId=?</userGroupsSQL>  
    <descriptionSQL>SELECT groupName as groupDescription FROM GroupInfo WHERE groupId=?</descriptionSQL>  
    <loadMembersSQL>SELECT userId as username FROM GroupUser WHERE groupId=? AND isAdmin='N'</loadMembersSQL>  
    <loadAdminsSQL>SELECT userId as username FROM GroupUser WHERE groupId=? AND isAdmin='Y'</loadAdminsSQL>
  </jdbcGroupProvider>

注:不推荐用xml去配置

如此,再去用已经存在的,第三方的,数据库中的用户,去登录时,即可正常登陆进去了。

 

注意事项:

1.已有数据库中的用户的密码加密方式,要和你此处的配置的加密方式一致:

如果是plain,则都是plain:

已有数据库的用户密码加密方式:

取决你自己需要去搞清楚,比如我此处可以通过Navicat打开已有数据库看到密码(并且从相关人员口中确认密码加密方式是plain):

openfire mysql user password encryption method

配置的加密方式:

INSERT INTO `ofproperty` VALUES ('jdbcAuthProvider.passwordType', 'plain');

2.如果想要用除了admin之外的其他用户去登陆,则可以修改对应的配置admin.authorizedJIDs,值为xxx@domain的形式,如果有多个用户,则用逗号’,’隔开。

比如:

INSERT INTO `ofproperty` VALUES ('admin.authorizedJIDs', 'admin@localhost,new_use1@localhost,new_user2@localhost');

其中的new_use1@localhost,new_user2@localhost是你自己的已有数据库的用户名。

当然,如果数据库在别的服务器,则domain就不是localhost了。

转载请注明:在路上 » 【已解决】OpenFire中无法使用自定义数据库中用户去登录

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

网友最新评论 (1)

  1. 看了半天没卵用
    dd7年前 (2017-08-02)回复
88 queries in 0.187 seconds, using 22.26MB memory