TABLE users (
username VARCHAR(50) NOT NULL PRIMARY KEY,
password VARCHAR(50) NOT NULL,
enabled BIT NOT NULL
);
CREATE TABLE authorities (
username VARCHAR(50) NOT NULL,
authority VARCHAR(50) NOT NULL
);
Spring Security 2配置精讲 上(6)
时间:2011-10-16
不过这种设计方式在实际生产环境中基本上不会采用。一般来说，我们会使用逻辑主键ID来标示每个User和每个Authorities（Role）。而且从典型意义上讲，他们之间是一个多对多的关系，我们会采用3张表来表示，下面是我在MySQL中建立的3张表的schema示例：
Java代码
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`password` varchar(255) default NULL,
`disabled` int(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `role` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `user_role` (
`user_id` int(11) NOT NULL,
`role_id` int(11) NOT NULL,
PRIMARY KEY (`user_id`,`role_id`),
UNIQUE KEY `role_id` (`role_id`),
KEY `FK143BF46AF6AD4381` (`user_id`),
KEY `FK143BF46A51827FA1` (`role_id`),
CONSTRAINT `FK143BF46A51827FA1` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
CONSTRAINT `FK143BF46AF6AD4381` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`password` varchar(255) default NULL,
`disabled` int(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `role` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `user_role` (
`user_id` int(11) NOT NULL,
`role_id` int(11) NOT NULL,
PRIMARY KEY (`user_id`,`role_id`),
UNIQUE KEY `role_id` (`role_id`),
KEY `FK143BF46AF6AD4381` (`user_id`),
KEY `FK143BF46A51827FA1` (`role_id`),
CONSTRAINT `FK143BF46A51827FA1` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
CONSTRAINT `FK143BF46AF6AD4381` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Spring Security 2配置精讲 上(7)
时间:2011-10-16
通过配置SQL来模拟用户和权限
有了数据库的表设计，我们就可以在Spring Security中，通过配置SQL，来模拟用户和权限，这依然通过<authentication-provider>来完成：
Xml代码
<authentication-provider>
<jdbc-user-service data-source-ref="dataSource"
users-by-username-query="SELECT U.username, U.password, U.accountEnabled AS ''enabled'' FROM User U where U.username=?"
authorities-by-username-query="SELECT U.username, R.name as ''authority'' FROM User U JOIN Authority A ON u.id = A.userId JOIN Role R ON R.id = A.roleId WHERE U.username=?"/>
</authentication-provider>
<authentication-provider>
<jdbc-user-service data-sou
|
