权限管理系统(struts+hibernate )

--drop database rightSys create database rightSys go use rightSys go create table sys_user ( [usr_id] [bigint] IDENTITY (1, 1) NOT NULL, [usr_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS unique NOT NULL ,--用户名唯一 [usr_password] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [usr_flag] [int] NOT NULL,--1 正常 0删除 ) ON [PRIMARY] go --权限表 CREATE TABLE [dbo].[sys_right] ( [right_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , --菜单编码 [right_parent_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--父菜单编码 [right_type] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,--菜单类别，Document或Folder [right_text] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--菜单名称 [right_url] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,--菜单URL [right_tip] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL --菜单提示 ) ON [PRIMARY] GO --角色表 CREATE TABLE [dbo].[sys_role] ( [role_id] [bigint] IDENTITY (1, 1) NOT NULL , [role_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [role_desc] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [role_flag] [int] NULL ) ON [PRIMARY] GO --角色权限中间表 CREATE TABLE [dbo].[sys_role_right] ( [rf_id] [bigint] IDENTITY (1, 1) NOT NULL , [rf_role_id] [bigint] NOT NULL , [rf_right_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY] GO --用户角色中间表 CREATE TABLE [dbo].[sys_user_role] ( [ur_id] [bigint] IDENTITY (1,1) NOT NULL, [ur_usr_id] [bigint] NOT NULL, [ur_role_id] [bigint] NOT NULL ) ALTER TABLE [dbo].[sys_right] WITH NOCHECK ADD CONSTRAINT [PK_sys_right] PRIMARY KEY CLUSTERED ( [right_code] ) ON [PRIMARY] GO ALTER TABLE [dbo].[sys_role] WITH NOCHECK ADD CONSTRAINT [PK_RoleInfo] PRIMARY KEY CLUSTERED ( [role_id] ) ON [PRIMARY] GO ALTER TABLE [dbo].[sys_role_right] WITH NOCHECK ADD CONSTRAINT [PK_RoleRight] PRIMARY KEY CLUSTERED ( [rf_id] ) ON [PRIMARY] GO ALTER TABLE [dbo].[sys_user] WITH NOCHECK ADD CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED ( [usr_id] ) ON [PRIMARY] GO ALTER TABLE [dbo].[sys_user_role] WITH NOCHECK ADD CONSTRAINT [PK_UserRole] PRIMARY KEY CLUSTERED ( [ur_id] ) ON [PRIMARY] GO ALTER TABLE [dbo].[sys_role_right] ADD CONSTRAINT [FK_sys_role_right_sys_right] FOREIGN KEY ( [rf_right_code] ) REFERENCES [dbo].[sys_right] ( [right_code] ), CONSTRAINT [FK_sys_role_right_sys_role] FOREIGN KEY ( [rf_role_id] ) REFERENCES [dbo].[sys_role] ( [role_id] ) GO ALTER TABLE [dbo].[sys_user_role] ADD CONSTRAINT [FK_sys_user_role_sys_user] FOREIGN KEY ( [ur_usr_id] ) REFERENCES [dbo].[sys_user] ( [usr_id] ), CONSTRAINT [FK_sys_user_role_sys_role] FOREIGN KEY ( [ur_role_id] ) REFERENCES [dbo].[sys_role] ( [role_id] ) GO GO --select * from sys_right insert into sys_right values('0','-1','Folder','权限管理','#','权限管理') insert into sys_right values('1','0','Folder','用户管理','#','用户管理') insert into sys_right values('2','1','Document','添加用户','user.do?o=addUser','添加用户') insert into sys_right values('3','1','Document','查询用户','usermanager/selectUser.jsp','查询用户') insert into sys_right values('4','0','Folder','角色管理',null,'角色管理') insert into sys_right values('5','4','Document','添加角色','rolemanager/addRole.jsp','添加角色') insert into sys_right values('6','4','Document','查询角色','role.do?o=toList','查询角色') --update sys_right set right_url='rolemanager/addRole.jsp' where right_code=5 --update sys_right set right_url='role.do?o=toList' where right_code=6 select * from sys_right go --select * from sys_role insert into sys_role values('系统管理员',null,1) --1 正常 0删除 --select * from sys_role_right insert into sys_role_right values(1,'0') insert into sys_role_right values(1,'1') insert into sys_role_right values(1,'2') insert into sys_role_right values(1,'3') insert into sys_role_right values(1,'4') insert into sys_role_right values(1,'5') insert into sys_role_right values(1,'6') --select * from sys_user insert into sys_user values('admin','admin',1) --select * from sys_user_role insert into sys_user_role values(1,1) select * from sys_role select * from sys_right where right_code in (0,1,3) select * from sys_role_right select r.right_code from sys_role ro ,sys_right r,sys_role_right rr where ro.role_id=rr.rf_role_id and r.right_code=rr.rf_right_code select * from sys_user select * from sys_role select * from sys_user_role