项目中使用数据库表+视图+存储过程+缓存的方式实现用户权限的控制。通过用户表、角色表、权限表以及用户角色表、角色权限表两个中间表可以得到一个用户对应的权限有哪些。创建一个视图将这五个表连接起来,可以查询出每个用户对应的权限有哪些。Java层通过调用存储过程,存储过程再查询该视图,用户权限可以传递到Java层。Java层将用户权限缓存起来,可以提高效率。本文主要介绍:“涉及到的数据库表”、“创建连接表的视图”、“调用视图的存储过程”、“Java层加载权限信息”、“Java层判断用户是否有权限”、“可以使用Excel管理角色权限信息”。
1、涉及到的数据库表
涉及5个数据库表:
(1)用户表t_staff
DROP TABLE IF EXISTS T_Staff;
CREATE TABLE T_Staff -- 门店用户
(
F_ID INT NOT NULL AUTO_INCREMENT, -- ID
F_Name VARCHAR(12) NOT NULL, -- 店员名称
F_Phone VARCHAR(32) NOT NULL, -- 手机号码
F_ICID VARCHAR(20) NULL, -- 身份证号码
F_WeChat VARCHAR(20) NULL, -- 微信号
F_OpenID VARCHAR(100) NULL, -- 微信用户的唯一标识。
F_Unionid VARCHAR(100) NULL, -- 只有将公众号绑定到微信开放平台帐号后,才会出现该字段,同一用户的unionid是唯一的。
F_pwdEncrypted VARCHAR(0) NULL, -- 公钥加密后的用户密码
F_Salt VARCHAR(32) NOT NULL, -- 加盐后的MD5值
F_PasswordExpireDate DATETIME NULL, -- 密码有效期
F_IsFirstTimeLogin INT NOT NULL DEFAULT 1, -- 首次登录成功?
F_ShopID INT NOT NULL, -- 门店ID
F_DepartmentID INT NOT NULL, -- 所属的部门。默认为1
F_Status INT NOT NULL, -- 0,在职。1,离职。
F_CreateDatetime DATETIME NOT NULL DEFAULT now(), -- 创建时间
F_UpdateDatetime DATETIME NOT NULL DEFAULT now(), -- 修改时间
PRIMARY KEY (F_ID),
FOREIGN KEY (F_ShopID) REFERENCES T_Shop(F_ID),
FOREIGN KEY (F_DepartmentID) REFERENCES T_Department(F_ID)
-- FOREIGN KEY (F_IDInPOS) REFERENCES T_POS(F_ID) -- ,
-- UNIQUE KEY F_ICID (F_ICID),
-- UNIQUE KEY F_WeChat (F_WeChat)
-- UNIQUE KEY F_Phone (F_Phone) status为1的phone可以重新创建一个正常使用的staff。
)
ENGINE=InnoDB
DEFAULT CHARACTER SET='utf8' COLLATE='utf8_unicode_ci';
(2)角色表t_role
DROP TABLE IF EXISTS T_Role;
CREATE TABLE T_Role -- 角色
(
F_ID INT NOT NULL AUTO_INCREMENT, -- ID
F_Name VARCHAR(20) NOT NULL, -- 角色名称
F_CreateDatetime DATETIME NOT NULL DEFAULT now(), -- 创建时间
F_UpdateDatetime DATETIME NOT NULL DEFAULT now(), -- 修改时间
PRIMARY KEY (F_ID)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET='utf8' COLLATE='utf8_unicode_ci';
(3)权限表t_permission
关键字段F_SP
DROP TABLE IF EXISTS T_Permission;
CREATE TABLE T_Permission -- 操作权限表
(
F_ID INT NOT NULL AUTO_INCREMENT, -- ID
F_SP VARCHAR(80) NOT NULL, -- 对应操作的SP
F_Name VARCHAR(20) NOT NULL, -- 操作名称
F_Domain VARCHAR(16) NOT NULL , -- 领域名称
F_Remark VARCHAR(32) NOT NULL, -- 操作备注
F_CreateDatetime DATETIME NOT NULL DEFAULT now(), -- 创建时间
PRIMARY KEY (F_ID)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET='utf8' COLLATE='utf8_unicode_ci';
(4)用户角色表t_staffrole
DROP TABLE IF EXISTS T_StaffRole;
CREATE TABLE T_StaffRole -- 用户角色表
(
F_ID INT NOT NULL AUTO_INCREMENT, -- ID
F_StaffID INT NOT NULL, -- 用户ID
F_RoleID INT NOT NULL, -- 角色ID
PRIMARY KEY (F_ID),
FOREIGN KEY (F_StaffID ) REFERENCES T_Staff(F_ID),
FOREIGN KEY (F_RoleID ) REFERENCES T_Role(F_ID),
UNIQUE KEY F_StaffID(F_StaffID) -- ... 一个员工有且只有一个角色
)
ENGINE=InnoDB
DEFAULT CHARACTER SET='utf8' COLLATE='utf8_unicode_ci';
(5)角色权限表t_role_permission
DROP TABLE IF EXISTS T_Role_Permission;
CREATE TABLE T_Role_Permission -- 角色操作中间表
(
F_ID INT NOT NULL AUTO_INCREMENT, -- ID
F_RoleID INT NOT NULL, -- 角色ID
F_PermissionID INT NOT NULL, -- 操作ID
PRIMARY KEY (F_ID),
FOREIGN KEY (F_RoleID ) REFERENCES T_Role(F_ID),
FOREIGN KEY (F_PermissionID ) REFERENCES T_Permission(F_ID)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET='utf8' COLLATE='utf8_unicode_ci';
2、创建连接表的视图
根据5张数据库表创建用户-角色-权限视图v_staff_permission:
DROP VIEW IF EXISTS V_Staff_Permission; -- 用户, 角色, 权限视图
CREATE VIEW V_Staff_Permission
AS
SELECT s.F_ID AS StaffID, s.F_Name AS StaffName,r.F_ID AS RoleID, r.F_Name AS RoleName, p.F_SP, p.F_Name PermissionName, p.F_Remark FROM t_staff s
LEFT JOIN t_staffrole sr ON sr.F_StaffID = s.F_ID
JOIN t_role r ON sr.F_RoleID = r.F_ID
JOIN t_role_permission ap ON ap.F_RoleID = r.F_ID
JOIN t_permission p ON p.F_ID = ap.F_PermissionID
WHERE s.F_Status = 0;
3、调用视图的存储过程
存储过程SP_Staff_RetrieveNPermission调用视图,Java层调用存储过程获取所有的用户权限:
DROP PROCEDURE IF EXISTS `SP_Staff_RetrieveNPermission`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_Staff_RetrieveNPermission` (
OUT iErrorCode INT,
OUT sErrorMsg VARCHAR(64),
IN iPageIndex INT,
IN iPageSize INT,
OUT iTotalRecord INT
)
BEGIN
DECLARE recordIndex INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET iErrorCode := 3;
SET sErrorMsg := 'Êý¾Ý¿â´íÎó';
ROLLBACK;
END;
START TRANSACTION;
SET iPageIndex = iPageIndex - 1;
SET recordIndex = iPageIndex * iPageSize;
SET @i = 1;
SELECT @i:=@i+1 AS F_ID, StaffID, StaffName, RoleID, RoleName, F_SP, PermissionName, F_Remark
FROM V_Staff_Permission
ORDER BY F_ID DESC LIMIT recordIndex,iPageSize;
SELECT count(1) into iTotalRecord
FROM V_Staff_Permission;
SET iErrorCode := 0;
SET sErrorMsg := '';
COMMIT;
END;
4、Java层加载权限信息
StaffPermissionCache类用来缓存用户权限信息,存放到hashtable中:
/** 权限的查询需要支持根据staffID查询,也要支持根据staffID和sp名查询。后者用于检查特定用户有无特定权限,操作频繁,需要快速查询到 */
@Component("staffPermissionCache")
@Scope("prototype")
public class StaffPermissionCache extends BaseCache {
private Log logger = LogFactory.getLog(BaseCache.class);
/** key:staffID+sp名 <br />
* value:StaffPermission */
public Hashtable<String, BaseModel> ht;
@Resource
private StaffPermissionBO staffPermissionBO;
……
在程序启动时,开始加载用户权限信息:
/** 加载公共DB中的公司缓存和每个私有DB的普通缓存和同步缓存。 */
@PostConstruct
private void load() {
resolveCurrentEnvAndDomain();
……
staffPermissionCache.load(com.getDbName());
load调用doLoad方法:
public void load(String dbName) {
doLoad(dbName);
register(dbName);
}
doLoad方法调用staffPermissionBO的retrieveNObject,最后调用了SP_Staff_RetrieveNPermission存储过程,获取到所有的用户和他们对应的权限信息,存放到List<?> ls:
@SuppressWarnings("unchecked")
protected void doLoad(String dbName) {
logger.info("加载缓存(" + sCacheName + ")...");
BaseModel b = getMasterModel(dbName);
DataSourceContextHolder.setDbName(dbName);
List<?> ls = getMasterBO().retrieveNObject(BaseBO.SYSTEM, BaseBO.INVALID_CASE_ID, b);
if (getMasterBO().getLastErrorCode() != EnumErrorCode.EC_NoError) {
logger.error("加载缓存(" + sCacheName + ")失败!请重启服务器!!错误信息:" + getMasterBO().printErrorInfo());
throw new RuntimeException(BaseModel.ERROR_Tag);
}
doLoadSlave(ls, dbName);
logger.info("加载缓存(" + sCacheName + ")成功!");
writeN((List<BaseModel>) ls); // 将DB的数据缓存进本对象的hashtable中
}
writeN((List<BaseModel>) ls) 方法把ls写入到了hashtable容器中:
/** 清除所有旧的缓存,写入新的数据 <br />
* TODO:目前存在的问题:load()中缓存加载时,并不能确定要加载多少个对象。 */
public void writeN(List<BaseModel> ls) {
System.out.println("writeN正在加锁...." + lock.writeLock().getHoldCount());
lock.writeLock().lock();
try {
doWriteN(ls);
} catch (Exception e) {
logger.error("writeN异常:" + e.getMessage());
}
lock.writeLock().unlock();
System.out.println("writeN已经解锁" + lock.writeLock().getHoldCount());
}
protected void doWriteN(List<BaseModel> ls) {
setCache(ls);
}
@Override
protected void setCache(List<BaseModel> list) {
listToHashtable(list);
}
以staffID+Sp为key,bm为Value存放到HashTable容器缓存起来:
@Override
protected Hashtable<String, BaseModel> listToHashtable(List<BaseModel> ls) {
if (ls == null) {
throw new RuntimeException("参数ls为null!");
}
ht = new Hashtable<String, BaseModel>(ls.size()); // 每一次设置缓存,都不会、也不能污染到本对象的副本中的ht数据成员
for (BaseModel bm : ls) {
ht.put(String.valueOf(((StaffPermission) bm).getStaffID()) + ((StaffPermission) bm).getSp(), bm);
}
return ht;
}
5、Java层判断用户是否有权限
所有用户的对数据库的操作都是通过调用存储过程来实现的,在Action层调用BO层的方法的时候,已经指定了操作对应的存储过程,如下图的第二个参数BaseBO.CASE_ResetMyPassword,它对应了一个存储过程:
Staff staffUpdated = (Staff) staffBO.updateObject(getStaffFromSession(session).getID(), BaseBO.INVALID_CASE_ID, staff);
调用过程如下:
@SuppressWarnings("static-access")
protected BaseModel update(int staffID, int iUseCaseID, BaseModel s) {
checkMapper();
if (!checkUpdatePermission(staffID, iUseCaseID, s)) {
lastErrorCode = EnumErrorCode.EC_NoPermission;
lastErrorMessage = "权限不足";
return null;
}
……
@Override
protected boolean checkUpdatePermission(int staffID, int iUseCaseID, BaseModel s) {
switch (iUseCaseID) {
case CASE_ResetMyPassword:
return checkStaffPermission(staffID, SP_Staff_ResetPassword);
case CASE_ResetOtherPassword:
return checkStaffPermission(staffID, SP_Staff_ResetPassword);
case CASE_Staff_Update_OpenidAndUnionid:
return checkStaffPermission(staffID, SP_Staff_Update_OpenidAndUnionid);
case CASE_Staff_Update_Unsubscribe:
return checkStaffPermission(staffID, SP_Staff_Update_Unsubscribe);
default:
return checkStaffPermission(staffID, SP_Staff_Update);
}
}
在checkStaffPermission方法检查是否有权限:
/** 检查1个staff有无权限permission */
protected boolean checkStaffPermission(int staffID, String permission) {
if (staffID == SYSTEM) {
return true;
}
StaffPermissionCache spc = (StaffPermissionCache) CacheManager.getCache(DataSourceContextHolder.getDbName(), EnumCacheType.ECT_StaffPermission);
ErrorInfo ecOut = new ErrorInfo();
StaffPermission sp = spc.read1(staffID, permission, ecOut);
if (sp == null) {
lastErrorMessage = "Staff(" + staffID + ")没有权限(" + permission + ")";
logger.debug(lastErrorMessage);
return false;
}
return true;
}
在read1方法中,根据用户ID和存储过程拼接key,查找hashtable是否有这个key,从而判断用户是否有权限:
public StaffPermission read1(int staffID, String permissionName, ErrorInfo ecOut) {
StaffPermission bmToRead = null;
lock.readLock().lock();
String key = String.valueOf(staffID) + permissionName;
if (ht.containsKey(key)) {
bmToRead = (StaffPermission) ht.get(key);
ecOut.setErrorCode(EnumErrorCode.EC_NoError);
} else {
bmToRead = null;
ecOut.setErrorCode(EnumErrorCode.EC_NoSuchData);
logger.debug("无此权限。key=" + key);
}
lock.readLock().unlock();
return bmToRead;
}
6、可以使用Excel管理角色权限信息
使用excel可以方便管理角色权限信息:
后面几个工作表配置角色的权限:
所有评论(0)