Spring Security自定义数据表完整实现
创建MySQL数据表的语句：
SET FOREIGN_KEY_CHECKS=0;-------------------------------- 创建管理员帐号表t_admin-- ----------------------------CREATE TABLE `t_admin` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `passwd` varchar(12) NOT NULL DEFAULT '' COMMENT '用户密码', `nickname` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名字', `phoneno` varchar(32) NOT NULL DEFAULT '' COMMENT '电话号码', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ------------------------------ 添加3个管理帐号 -- ----------------------------INSERT INTO `t_admin` VALUES ('1', 'admin', 'admin', '');INSERT INTO `t_admin` VALUES ('4', '123456', 'test', '');INSERT INTO `t_admin` VALUES ('5', '111111', '111111', '');-- ------------------------------ 创建权限表t_role-- ----------------------------CREATE TABLE `t_role` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `role` varchar(40) NOT NULL DEFAULT '', `descpt` varchar(40) NOT NULL DEFAULT '' COMMENT '角色描述', `category` varchar(40) NOT NULL DEFAULT '' COMMENT '分类', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=utf8;-- ------------------------------ 加入4个操作权限-- ----------------------------INSERT INTO `t_role` VALUES ('1', 'ROLE_ADMIN', '系统管理员', '系统管理员');INSERT INTO `t_role` VALUES ('2', 'ROLE_UPDATE_FILM', '修改', '影片管理');INSERT INTO `t_role` VALUES ('3', 'ROLE_DELETE_FILM', '删除', '影片管理');INSERT INTO `t_role` VALUES ('4', 'ROLE_ADD_FILM', '添加', '影片管理');-- ------------------------------ 创建权限组表-- ----------------------------CREATE TABLE `t_group` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `groupname` varchar(50) NOT NULL DEFAULT '', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ------------------------------ 添加2个权限组-- ----------------------------INSERT INTO `t_group` VALUES ('1', 'Administrator');INSERT INTO `t_group` VALUES ('2', '影片维护');-- ------------------------------ 创建权限组对应权限表t_group_role-- ----------------------------CREATE TABLE `t_group_role` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `groupid` bigint(20) unsigned NOT NULL, `roleid` bigint(20) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `groupid2` (`groupid`,`roleid`), KEY `roleid` (`roleid`), CONSTRAINT `t_group_role_ibfk_1` FOREIGN KEY (`groupid`) REFERENCES `t_group` (`id`), CONSTRAINT `t_group_role_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `t_role` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=83 DEFAULT CHARSET=utf8;-- ------------------------------ 加入权限组与权限的对应关系-- ----------------------------INSERT INTO `t_group_role` VALUES ('1', '1', '1');INSERT INTO `t_group_role` VALUES ('2', '2', '2');INSERT INTO `t_group_role` VALUES ('4', '2', '4');-- ------------------------------ 创建管理员所属权限组表t_group_user-- ----------------------------CREATE TABLE `t_group_user` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `userid` bigint(20) unsigned NOT NULL, `groupid` bigint(20) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `userid` (`userid`), KEY `groupid` (`groupid`), CONSTRAINT `t_group_user_ibfk_2` FOREIGN KEY (`groupid`) REFERENCES `t_group` (`id`), CONSTRAINT `t_group_user_ibfk_3` FOREIGN KEY (`userid`) REFERENCES `t_admin` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;-- ------------------------------ 将管理员加入权限组-- ----------------------------INSERT INTO `t_group_user` VALUES ('1', '1', '1');INSERT INTO `t_group_user` VALUES ('2', '4', '2');-- ------------------------------ 创建管理员对应权限表t_user_role-- 设置该表可跳过权限组，为管理员直接分配权限-- ----------------------------CREATE TABLE `t_user_role` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `userid` bigint(20) unsigned NOT NULL, `roleid` bigint(20) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `userid` (`userid`), KEY `roleid` (`roleid`), CONSTRAINT `t_user_role_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `t_admin` (`id`), CONSTRAINT `t_user_role_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `t_role` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;?
?
配置文件applicationContext-security.xml
<?xml version="1.0" encoding="UTF-8"?><beans:beans xmlns="http://www.springframework.org/schema/security"xmlns:b="http://www.springframework.org/schema/beans" xmlns:beans="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/security http://www.springframework.org/schema/security/spring-security-3.0.xsd"><http ><!-- 不拦截login.jsp --><intercept-url pattern="/login.jsp*" access="IS_AUTHENTICATED_ANONYMOUSLY" /><!--仅拦截到manager下面的内容，具备access对应权限的--><intercept-url pattern="/manager/**" access="ROLE_ADMIN,ROLE_UPDATE_FILM,ROLE_DELETE_FILM,ROLE_ADD_FILM" /><!-- 登录表单设置 --><form-login login-page="/login.jsp"default-target-url="/manager/films.jsp"authentication-failure-url="/login.jsp?error=true" /><!-- 登出操作后跳转到该页面 --><logout logout-success-url="/loggedout.jsp"/><remember-me /><!-- SESSION超时后跳转到该页面 --><session-management invalid-session-url="/timeout.jsp"></session-management></http><authentication-manager alias="authenticationManager"><authentication-provider><!-- 直接使用SQL语句查询登录帐号对应权限，users-by-username-query：查询登录用户是否存在authorities-by-username-query：查询登录用户权限(登录用户可以不属于任何组，从t_user_role表中获取权限)group-authorities-by-username-query：查询登录用户所在组的权限--><jdbc-user-service data-source-ref="dataSource"group-authorities-by-username-query="SELECT g.id,g.groupname,role.role FROM t_group AS g LEFT OUTER JOIN t_group_role AS grouprole ON (g.id = grouprole.groupid) LEFT OUTER JOIN t_role AS role ON (role.id = grouprole.roleid) LEFT OUTER JOIN t_group_user AS groupuser on (g.id = groupuser.groupid) LEFT OUTER JOIN t_admin ON (t_admin.id = groupuser.userid) WHERE t_admin.nickname = ?"users-by-username-query="SELECT t_admin.nickname AS username,t_admin.passwd as password,'true' AS enabled FROM t_admin WHERE t_admin.nickname = ?"authorities-by-username-query="SELECT t_admin.nickname AS username,role.role as authorities FROM t_admin LEFT OUTER JOIN t_user_role AS userrole ON(t_admin.id = userrole.userid) LEFT OUTER JOIN t_role AS role ON (userrole.roleid = role.id) WHERE t_admin.nickname = ?" /></authentication-provider></authentication-manager><!-- 自定义消息 --><b:bean id="messageSource" value="classpath:org/springframework/security/messages" /></b:bean><beans:bean id="dataSource" name="code"><authentication-provider><!-- 直接使用SQL语句查询登录帐号对应权限，users-by-username-query：查询登录用户是否存在authorities-by-username-query：查询登录用户权限(登录用户可以不属于任何组，从t_user_role表中获取权限)group-authorities-by-username-query：查询登录用户所在组的权限--><jdbc-user-service data-source-ref="dataSource"group-authorities-by-username-query="SELECT g.id,g.groupname,role.roleFROM t_group AS g LEFT OUTER JOIN t_group_role AS grouprole ON (g.id = grouprole.groupid)LEFT OUTER JOIN t_role AS role ON (role.id = grouprole.roleid)LEFT OUTER JOIN t_group_user AS groupuser on (g.id = groupuser.groupid)LEFT OUTER JOIN t_admin ON (t_admin.id = groupuser.userid)WHERE t_admin.nickname = ?"users-by-username-query="SELECT t_admin.nickname AS username,t_admin.passwd as password,'true' AS enabledFROM t_adminWHERE t_admin.nickname = ?"authorities-by-username-query="SELECT t_admin.nickname AS username,role.role as authoritiesFROM t_admin LEFT OUTER JOIN t_user_role AS userrole ON(t_admin.id = userrole.userid)LEFT OUTER JOIN t_role AS role ON (userrole.roleid = role.id)WHERE t_admin.nickname = ?" /></authentication-provider>
?通过user的用户名进行登录，并且去查询该用户所拥有的权限。
?
films.jsp页面
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8" %><%@ taglib prefix="security" uri="http://www.springframework.org/security/tags"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html> <head> <title>权限操作</title> </head> <body> <security:authorize ifAnyGranted="ROLE_ADMIN,ROLE_ADD_FILM">登录帐号具备ROLE_ADMIN权限或者ROLE_ADD_FILM权限可显示 </security:authorize> <br/><security:authorize ifAnyGranted="ROLE_ADMIN,ROLE_UPDATE_FILM">登录帐号具备ROLE_ADMIN权限或者ROLE_UPDATE_FILM权限可显示</security:authorize><br/> <security:authorize ifAnyGranted="ROLE_ADMIN,ROLE_DELETE_FILM"> 登录帐号具备ROLE_ADMIN权限或者ROLE_DELETE_FILM权限可显示 </security:authorize> </body></html>
?