Spring—Acegi系统安全—身份证管理—基于数据库存储用户信息认证

DROP DATABASE IF EXISTS sampledb;

CREATE DATABASE sampledb DEFAULT CHARACTER SET utf8;

USE sampledb;

CREATE TABLE T_USER (

?? USER_ID INTEGER NOT NULL AUTO_INCREMENT,

?? USERNAME VARCHAR(30) NOT NULL,

?? PASSWORD VARCHAR(30) DEFAULT NULL,

?? STATUS TINYINT(1) NOT NULL DEFAULT '0',

?? PRIMARY KEY (`USER_ID`),

?? UNIQUE KEY `USERNAME` (`USERNAME`)

);

CREATE TABLE T_USER_PRIV (

? USER_ID INTEGER NOT NULL DEFAULT '0',

? PRIV_NAME VARCHAR(30) DEFAULT NULL,

? PRIMARY KEY (USER_ID, PRIV_NAME)

);

?

INSERT INTO T_USER (USER_ID, USERNAME, PASSWORD, STATUS) VALUES

? (1,'tom','tom',1),

? (2,'john','john',1);

?

INSERT INTO T_USER_PRIV (USER_ID, PRIV_NAME) VALUES

? (1,'PRIV_1'),

? (1,'PRIV_2'),

? (1,'PRIV_COMMON'),

? (2,'PRIV_1'),

? (2,'PRIV_COMMON');

?????? <property name="usersByUsernameQuery">

?????????? <value>

??????????????? SELECT username,password,1 FROM t_user WHERE status='1'

??????????????? AND username = ?

?????????? </value>

?????????? <!-- 根据用户名查询用户的SQL语句 -->

?????? </property>

?????? <property name="authoritiesByUsernameQuery">

?????????? <value>

??????????????? SELECT u.username,p.priv_name FROM t_user u,t_user_priv

??????????????? p WHERE u.user_id =p.user_id AND u.username = ?

?????????? </value>

?????? <!-- 根据用户名查询用户权限的sql语句 -->

?????? </property>

? </bean>

//jdbcDaoImpl通过usersByUsernameQuery和authoritiesByUsernameQuery属性的定义查询用户信息和用户的权限的sql语句，实际上jdbcDaoImpl为以上两个属性提供了默认