基于角色的用户权限管理一般在任何项目中都是一个必不可少的功能。在此设计里，主要有用户、角色两个方面，角色需要有动态，用户的角色分配，用户与角色之间是多对多或一对多的关系，数据库表设计如下：
一、数据库表设计
1、User（用户表）：
记录用户的基本信息
-- Table "user" DDL
CREATE TABLE `user` (
`UserID` int(11) NOT NULL AUTO_INCREMENT,
`Username` varchar(50) NOT NULL DEFAULT '',
`Password` varchar(50) NOT NULL DEFAULT '',
`email` varchar(255) NOT NULL DEFAULT '',
`sex` char(1) NOT NULL DEFAULT '',
`createDate` varchar(50) NOT NULL DEFAULT '',
`lastLoginDate` varchar(50) DEFAULT '无',
`photo` varchar(255) DEFAULT NULL,
`telephone` varchar(50) DEFAULT '无',
`birthDate` varchar(50) DEFAULT '无',
`address` varchar(255) DEFAULT '无',
`post` varchar(10) DEFAULT '无',
`status` char(10) NOT NULL DEFAULT '',
PRIMARY KEY (`UserID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=gbk;
2、Role（角色表）：
-- Table "role" DDL
CREATE TABLE `role` (
`RoleID` int(11) NOT NULL AUTO_INCREMENT,
`RoleName` varchar(50) NOT NULL,
`description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`RoleID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk;
3、UserFunction（用户功能表）：
记录系统中所有的功能标识，此表的数据相对不变化小
-- Table "userfunction" DDL
CREATE TABLE `userfunction` (
`FunctionID` int(11) NOT NULL AUTO_INCREMENT,
`FunctionCode` varchar(255) NOT NULL,
`FunctionName` varchar(255) NOT NULL,
PRIMARY KEY (`FunctionID`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=gbk;
4、UserAndRole（用户与角色关联表）：
用于记录用户与角色的关联关系，此表的设计通常为多对多关系的中间表，分别记录着用户 ID和角色 ID。
-- Table "userandrole" DDL
CREATE TABLE `userandrole` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`UserID` int(11) DEFAULT NULL,
`RoleID` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `FK_Reference_1` (`UserID`),
KEY `FK_Reference_2` (`RoleID`),
CONSTRAINT `FK_Reference_1` FOREIGN KEY (`UserID`) REFERENCES `user` (`UserID`),
CONSTRAINT `FK_Reference_2` FOREIGN KEY (`RoleID`) REFERENCES `role` (`RoleID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=gbk;
5、RoleAndFunction（角色与功能表）：
记录着一个角色对应的功能 ID
-- Table "roleandfunction" DDL
CREATE TABLE `roleandfunction` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`RoleID` int(11) DEFAULT NULL,
`FunctionID` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `FK_Reference_3` (`RoleID`),
KEY `FK_Reference_4` (`FunctionID`),
CONSTRAINT `FK_Reference_3` FOREIGN KEY (`RoleID`) REFERENCES `role` (`RoleID`),
CONSTRAINT `FK_Reference_4` FOREIGN KEY (`FunctionID`) REFERENCES `userfunction` (`FunctionID`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=gbk;
二、工程目录结构下：
主要涉及的类包，Action、Service、DAO、框架基础类、工具类、实体类等
三、以登录功能为例的实现
用户在页面输入用户名与密码和验证码，开始登录，然后利用Struts2的可配置验证框架来检查用户输入的合法性，比如必填验证，当验证失败后，反回登录页面并提示错误信息，验证成功后，进入Action验证验证码的合法性，通过后就可以执行登录业务逻辑了，查询用户是否存在，以及查询用户当前状态是禁用还是启用，都通过后，查询用户对应的权限列表，并同用户信息一起保存在用户登录的环境对象中（BaseUserContext），具体的代码实现ru。
DAO 的实现（使用 JDBC ）
public interface IUserInfoDao {
public UserInfo login(String username,String password)throws PermissionException;
public Map<String,String> findFunctionCode(Integer userid)throws PermissionException;
}
@SuppressWarnings("all")
public class UserInfoDaoImpl extends JdbcTemplete implements IUserInfoDao {
public UserInfo login(String username, String password)
throws PermissionException {
Stringsql="selectu.UserID,u.Username,u.Password,u.email,u.sex,u.createDate,u.lastLoginDate,u.photo,u.telephone,u.birthDate,u.address,u.post,u.status from user u where u.Username=? and u.Password=?";
UserInfo user = null;
try {
user = (UserInfo) query(sql, new ResultSetHandler() {
@Override
public Object resultSetHandler(ResultSet rs)
throws SQLException {
UserInfo u = null;
if (rs.next()) {
u = new UserInfo();
u.setUserID(rs.getInt(1));
u.setUsername(rs.getString(2));
u.setPassword(rs.getString(3));
u.setEmail(rs.getString(4));
u.setSex(rs.getString(5));
u.setCreateDate(rs.getString(6));
u.setLastLoginDate(rs.getString(7));
u.setPhoto(rs.getString(8));
u.setTelephone(rs.getString(9));
u.setBirthDate(rs.getString(10));
u.setAddress(rs.getString(11));
u.setPost(rs.getString(12));
u.setStatus(rs.getString(13));
}
return u;
}
}, username, password);
} catch (SQLException e) {
throw new PermissionException("用户登录失败");
}
return user;
}
@Override
public Map<String, String> findFunctionCode(Integer userid)
throws PermissionException {
String sql = "select functionCode,functionName from userandrole uar ,roleandfunction raf,userfunction uf where uar.RoleID=raf.RoleID and raf.FunctionID=uf.FunctionID and uar.UserID=?";
try {
return (Map<String, String>) query(sql, new ResultSetHandler() {
@Override
public Object resultSetHandler(ResultSet rs)
throws SQLException {
Map<String, String> map = new HashMap<String, String>();
while (rs.next()) {
map.put(rs.getString(1), rs.getString(2));
}
return map;
}
}, userid);
} catch (SQLException e) {
e.printStackTrace();
throw new PermissionException("查询用户权限列表失败");
}
}
}
Service实现：
public interface IUserInfoService {
public BaseUserContext login(String username,String password)throws PermissionException;
}
public class UserInfoServiceImpl implements IUserInfoService {
private IUserInfoDao dao = new UserInfoDaoImpl();
@Override
public BaseUserContext login(String username, String password)
throws PermissionException {
IUserInfoDao dao = new UserInfoDaoImpl();
BaseUserContext buc = null;
UserInfo user = dao.login(username, password);
if(user==null){
throw new PermissionException("用户名或密码错误");
}
if("禁用".equals(user.getStatus())){
throw new PermissionException("用户状态不可用，请与管理员联系");
}
Map<String,String> map = dao.findFunctionCode(user.getUserID());
buc = new BaseUserContext();
buc.setUserID(user.getUserID());
buc.setUsername(user.getUsername());
buc.setPassword(user.getPassword());
buc.setEmail(user.getEmail());
buc.setPhoto(user.getPhoto());
buc.setAddress(user.getAddress());
buc.setPost(user.getPost());
buc.setSex(user.getStatus());
buc.setLastLoginDate(DateFormatUtil.format(new Date()));
buc.setUserFunction(map);
return buc;
}
}
Action实现：
public class LoginAction extends ActionSupport implements SessionAware {
private static final long serialVersionUID = 1L;
private String username;
private String password;
private String security;
public String getSecurity() {
return security;
}
public void setSecurity(String security) {
this.security = security;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String execute() throws Exception {
//验证 “验证码”是否正确
String sysSecurity = (String)sessionMap.get(Constants.SECURITY_IMAGE);
System.out.println(sysSecurity+"--sysSecurity");
if(!sysSecurity.equalsIgnoreCase(security)){
addActionError("验证码输入错误");
return INPUT;
}
IUserInfoService service = new UserInfoServiceImpl();
try {
BaseUserContext buc = service.login(username, password);
sessionMap.put(Constants.BASEUSERCONTEXT, buc);
return SUCCESS;
} catch (Exception e) {
e.printStackTrace();
addActionError(e.toString());
return INPUT;
}
}
private Map<String,Object> sessionMap;
@Override
public void setSession(Map<String, Object> arg0) {
sessionMap = arg0;
}
}