【背景】
折腾：
【已解决】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&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
4.然后如果用
admin
admin
登陆后，
进去后看到的用户，还是openfire的数据库中的用户
而不是另外的那个数据库中的用户。。
所以还是没成功。。。
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
改为：
jdbc:mysql://localhost:3306/qqqqqqqqqqqqqqq?user=root&password=root
然后退出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
使用django的用户帐号登录openfire – 江湖 – 51CTO技术博客
Openfire 整合数据库 – JarrahWu – 博客园
openfire自定义数据库集成指南-Harries Blog™
去添加：
admin.authorizedUsernames
的设置，加上用户123，看看能否允许第三方数据库的用户名去登陆。
注销，重新用123去登陆试试。
11.搜：
openfire 集成现有数据库
参考：
openfire3.6.4外连数据库(mysql) – rainbird – 51CTO技术博客
再去添加类似：
(‘admin.authorizedJIDs’,’rainbird@localhost’),
的值：
注销后，重新试试用123去登陆，错误依旧。
并且发现，admin也无法登陆了。
此时可以确定：
的确是：
admin.authorizedJIDs
决定了，管理员登陆的用户支持哪些。
由于把默认的admin去掉了，添加了
但是估计是无效的值，或者写错了，所以才出错的。
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
然后注销后，重新登陆试试：
还是不行。
14.参考：
去把123改为123@localhost
但是重新登陆后还是不行。
15.看到：
[记录] Openfire集成现有系统数据库用户 – OK Computer
整理的很全。
再去把123换成另外一个用户1000001去试试：
重启openfire，再去登陆试试，还是不行。
再去随便改改其他值，删除其他值。
再去改改另外要接入的数据库的用户的密码，为
原先的openfire_oooooooooo中的admin的密码是：
fdead603f65d552db24ccc102ab5fdff6d8da12777312a3d
然后放到llllllllllll中的：
才发现之前已经放过来了，不过是放到123这个用户上的：
然后之前试过了用123，是没法登陆的。。
此处，顺便看到了：
admin的email是：admin@bogon
觉得不对，去改为：admin@localhost
还是去试试，把10000001的密码，从：
95e2b97393bd7264c402a0fc247e817e53e83434c5cd5951
改为：
然后再去试试：
还是不行。
现在去：
用openfire_aaaaaaaa中创建一个用户和密码，然后把用户名和密码放到aaaaaaaaa中。然后再删掉openfire_aaaaaaaaa中的，看看能不能登陆。
然后去看看数据库中的用户名和密码：
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
然后再去加到另外的数据库中：
果然：
iiiiiiiiiiiiiiii中的
（用户10000001的）密码是123456，加密后是：
fdead603f65d552db24ccc102ab5fdff6d8da12777312a3d
而openfire_aaaaaaaaaaa中的（用户123456的）的密码123456，加密后是：
5f52e5aa779a19892af855a75766641d47c72e96b26f47e2
是不一样的。
把原先的加的123和123456两个新加用户都删除掉，然后确保另外的数据库中都有对应用户名和密码。
再去加到配置中。
（发现：
当创建用户，勾选了：
是否为管理员，授予Openfire管理员权限
时，系统会自动把其信息：
加入到：
admin.authorizedJIDs
中的）
再去重新登陆试试：
结果
123
123
123456
123456
10000001
admin
都无法登陆。。。
再去故意把：
之前的log都清空：
四种错误都清空了。
然后再重新用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：
然后用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
ofUserdatabase 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
感觉是：
现在拿了
用户名：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&password=root');
UPDATE `openfire`.`ofProperty` SET propValue='org.jivesoftware.openfire.auth.JDBCAuthProvider' WHERE name='provider.auth.className';等等信息后，还是无法连接第三方的数据库呢。。
看了下：
的代码，
感觉是：
后面调用到的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
作为普通的密码，去登陆
结果真的就登陆进去了：
说明：
此处的密码的类型
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
看看效果：
是可以登陆进去的：
所以目前是通过：
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.需要修改哪些属性配置：
那就是需要你自己参考包括官网和其他人写的文档，详见：
去写出自己的配置了。
此处贴上我自己的一些配置供参考：
INSERT INTO `ofproperty` VALUES ('jdbcProvider.driver', 'com.mysql.jdbc.Driver');
INSERT INTO `ofproperty` VALUES ('jdbcProvider.connectionString', 'jdbc:mysql://localhost:3306/xxxxxxxxxxx?user=root&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&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）：
配置的加密方式：
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中无法使用自定义数据库中用户去登录