使用CAS连接Mysql数据库

CAS安装好默认使用的是静态用户和密码,但实际使用中,我们可能连接LDAP、数据库等其他情况。接下来,我们看一下如何使用CAS连接Mysql数据库。

创建数据库

1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into users values ('admin','123456');

配置

使用数据库需要cas-server-support-jdbc的支持,在此依赖中的目录cas-4.2.1/cas-server-support-jdbc/src/main/java/org/jasig/cas/adaptors/jdbc中有多类组件完成配置。 以QueryDatabaseAuthenticationHandler为例,可参考官方文档

注意使用QueryDatabaseAuthenticationHandler作为数据验证方式需屏蔽一下类QueryAndEncodeDatabaseAuthenticationHandler,如下

QueryAndEncodeDatabaseAuthenticationHandler.java注释内容如下;

1
2
3
4
5
......
//import org.springframework.stereotype.Component;
......
//@Component("queryAndEncodeDatabaseAuthenticationHandler")
......

修改完成后进入目录cas-4.2.1/cas-server-support-jdbc,重新执行gradle clean build,生成jar包cas-4.2.1/cas-server-support-jdbc/build/libs/cas-server-support-jdbc-4.2.1.jar, 并将此jar包放入webapps/cas/WEB-INF/lib中,由于本例使用mysql,所以需将mysql驱动包mysql-connector-java-5.1.25.jar也放到lib目录中。

接下来在文件webapps/cas/WEB-INF/deployerConfigContext.xml中增加以下配置,并注释掉一行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<bean id="dataSource"
class="com.mchange.v2.c3p0.ComboPooledDataSource"
p:driverClass="${database.driverClass}"
p:jdbcUrl="${database.url}"
p:user="${database.user}"
p:password="${database.password}"
p:initialPoolSize="${database.pool.minSize}"
p:minPoolSize="${database.pool.minSize}"
p:maxPoolSize="${database.pool.maxSize}"
p:maxIdleTimeExcessConnections="${database.pool.maxIdleTime}"
p:checkoutTimeout="${database.pool.maxWait}"
p:acquireIncrement="${database.pool.acquireIncrement}"
p:acquireRetryAttempts="${database.pool.acquireRetryAttempts}"
p:acquireRetryDelay="${database.pool.acquireRetryDelay}"
p:idleConnectionTestPeriod="${database.pool.idleConnectionTestPeriod}"
p:preferredTestQuery="${database.pool.connectionHealthQuery}" />

<alias name="queryDatabaseAuthenticationHandler" alias="primaryAuthenticationHandler" />
<alias name="dataSource" alias="queryDatabaseDataSource" />

<!--<alias name="acceptUsersAuthenticationHandler" alias="primaryAuthenticationHandler" />此行注释-->

接着配置/webapps/cas/WEB-INF/cas.properties,添加以下内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
# add by hode begin

# == Basic database connection pool configuration ==
database.driverClass=com.mysql.jdbc.Driver
database.url=jdbc:mysql://192.167.48.1:3306/hode
database.user=root
database.password=kroot
database.pool.minSize=6
database.pool.maxSize=18

# Maximum amount of time to wait in ms for a connection to become
# available when the pool is exhausted
database.pool.maxWait=10000

# Amount of time in seconds after which idle connections
# in excess of minimum size are pruned.
database.pool.maxIdleTime=120

# Number of connections to obtain on pool exhaustion condition.
# The maximum pool size is always respected when acquiring
# new connections.
database.pool.acquireIncrement=6

# == Connection testing settings ==

# Period in s at which a health query will be issued on idle
# connections to determine connection liveliness.
database.pool.idleConnectionTestPeriod=30

# Query executed periodically to test health
database.pool.connectionHealthQuery=select 1

# == Database recovery settings ==

# Number of times to retry acquiring a _new_ connection
# when an error is encountered during acquisition.
database.pool.acquireRetryAttempts=5

# Amount of time in ms to wait between successive aquire retry attempts.
database.pool.acquireRetryDelay=2000

# add by hode end

同时需取消注释项cas.jdbc.authn.query.sql,并添加sql语句

1
cas.jdbc.authn.query.sql=select password from users where username=?

配置完成。重启tomcat即可用hode/123456进行登录,完成验证。

当然文明密码保存到数据库中是相当不安全的,所以需要保存加密后的密码,配置如下,此时md5后的结果与数据库的密码进行比较

1
2
3
4
5
6
deployerConfigContext.xml增加一行
<alias name="defaultPasswordEncoder" alias="passwordEncoder" />

cas.properties增加两行
cas.authn.password.encoding.char=UTF-8
cas.authn.password.encoding.alg=MD5

你的支持我的动力