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

SSH项目中加入spring security(二)--加入自定义数据表

程序员文章站 2022-04-25 13:32:20
...

接着上一篇 

SSH项目中加入spring security(一)

上一篇中基本的spring security已经加入到项目当中,本篇将spring security链接数据库

 

首先,将数据表插入到数据库当中


创建用户表、角色表、用户角色表,并插入跟上一篇相对应的数据

我项目当中Id用的是char(32),这里就不修改了,可以按照自己项目目前的结构进行修改。

CREATE TABLE `user_role` (
  `id` char(32) NOT NULL,
  `role_id` char(32) DEFAULT NULL,
  `user_id` char(32) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into ` user_role `(`id`,`role_id`,`user_id`,`create_date`) values ('402846814019e1b0014019e27eed0000','402846814019e1b0014019e27eed0000','402846814019e1b0014019e27eed0000','2013-07-29 00:00:00'),('402846814019e1b0014019e27eed0001','402846814019e1b0014019e27eed0001','402846814019e1b0014019e27eed0000','2013-07-29 00:00:00'),('402846814019e1b0014019e27eed0002','402846814019e1b0014019e27eed0001','402846814019e1b0014019e27eed0001','2013-07-29 00:00:00');

CREATE TABLE `role ` (
  `id` char(32) NOT NULL,
  `role_no` varchar(4) DEFAULT NULL,
  `role_name` varchar(128) DEFAULT NULL,
  `role_des` varchar(512) DEFAULT NULL,
  `disable` tinyint(1) DEFAULT '0',
  `creat_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `role`(`id`,`role_no`,`role_name`,`role_des`,`disable`,`creat_date`) values ('402846814019e1b0014019e27eed0000','1','ROLE_ADMIN','管理员角色',0,NULL),('402846814019e1b0014019e27eed0001','2','ROLE_USER','用户角色',0,NULL);

CREATE TABLE `user` (
  `id` char(32) NOT NULL,
  `username` varchar(64) DEFAULT NULL,
  `pwd` varchar(64) DEFAULT NULL,
  `enabled` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `user`(`id`,`username`,`pwd`,`enabled`) values ('402846814019e1b0014019e27eed0000','admin','admin',1),('402846814019e1b0014019e27eed0001','sozhike','111111',1);

 

查询用户帐户的sql

 

select u.username,u.pwd,u.enabled
from user as u
where u.username = 'admin'

 

 

查询用户权限的sql

 

select u.username,r.role_name
from user as u
join user_role as p
on u.id = p.user_id
join role as r
on r.id = p.role_id
where u.username = 'admin'

 

注意:

上面的sql,返回的结构不能修改,不然会抛出错误哦。也就是查询用户的时候,需要返回用户名+密码+是否启用,查询用户权限时,需要返回用户名+role名

 

修改applicationContext-security.xml

 

<authentication-manager>
    	<authentication-provider>
	      	<user-service>
	        	<user name="admin" password="admin" authorities="ROLE_USER, ROLE_ADMIN" />
	        	<user name="sozhike" password="111111" authorities="ROLE_USER" />
	      	</user-service>
    	</authentication-provider>
  	</authentication-manager>

 

 

改为

<authentication-manager>
    	<authentication-provider>
	      	<jdbc-user-service data-source-ref="dataSource"
            users-by-username-query="select u.username,u.pwd,u.enabled
									from user as u
									where u.username = ?"
            authorities-by-username-query="select u.username,r.role_name
										from user as u
										join user_role as p
										on u.id = p.user_id
										join role as r
										on r.id = p.role_id
										where u.username = ?"/>
    	</authentication-provider>
  	</authentication-manager>

 

重启项目,用第一篇的数据测试,OK啦

原创文章,转载请标明出处(http://sunliyings17.iteye.com/blog/1915008),谢谢

 

未完待续.....