欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Spring Security自定义数据表完整实现

程序员文章站 2024-03-14 13:46:34
...

创建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" class="org.springframework.context.support.ReloadableResourceBundleMessageSource">
		<b:property name="basename" value="classpath:org/springframework/security/messages" />
	</b:bean>
	
	<beans:bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"	destroy-method="close">
		<b:property name="driverClass">
			<b:value>com.mysql.jdbc.Driver</b:value>
		</b:property>
		<b:property name="jdbcUrl">
			<b:value>jdbc:mysql://localhost:3306/security</b:value>			
		</b:property>
		<b:property name="user">
			<b:value>root</b:value>
		</b:property>
		<b:property name="password">
			<b:value>root</b:value>
		</b:property>
		<b:property name="initialPoolSize">
			<b:value>10</b:value>
		</b:property>
		<b:property name="minPoolSize">
			<b:value>5</b:value>
		</b:property>
		<b:property name="maxPoolSize">
			<b:value>30</b:value>
		</b:property>
		<b:property name="acquireIncrement">
			<b:value>5</b:value>
		</b:property>
		<b:property name="maxIdleTime">
			<b:value>10</b:value>
		</b:property>
		<b:property name="maxStatements">
			<b:value>0</b:value>
		</b:property>
	</beans:bean>
	
</beans:beans>

 

 

值得注意的是:

<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>

 通过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>

 

相关标签: security