J2EE-RBAC权限管理

/* Created 2007-8-27 Modified 2007-10-28 Project rbac Model Company Author Version Database mySQL 5 */ drop table IF EXISTS T_ASSIGNMENT; drop table IF EXISTS T_PRIVILEGE; drop table IF EXISTS T_ROLE_PRIVILEGE; drop table IF EXISTS T_USER_PRIVILEGE; drop table IF EXISTS T_USER; drop table IF EXISTS T_ROLE; drop table IF EXISTS T_FORMULA; drop table IF EXISTS T_OPERATION; drop table IF EXISTS T_RESOURCE; Create table T_RESOURCE ( RESOURCE_ID Bigint NOT NULL AUTO_INCREMENT, RESOURCE_NAME Varchar(255) NOT NULL, RESOURCE_CAPTION Varchar(255), RESOURCE_DESCRIPTION Text, UNIQUE (RESOURCE_NAME), Primary Key (RESOURCE_ID)) ENGINE = InnoDB; Create table if not exists T_OPERATION ( OPERATION_ID Bigint NOT NULL AUTO_INCREMENT, OPERATION_NAME Varchar(64) NOT NULL, OPERATION_CAPTION Varchar(255), OPERATION_DESCRIPTION Text, UNIQUE (OPERATION_NAME), Primary Key (OPERATION_ID)) ENGINE = InnoDB; Create table T_FORMULA ( RESOURCE_ID Bigint NOT NULL, OPERATION_ID Bigint NOT NULL, Primary Key (RESOURCE_ID,OPERATION_ID)) ENGINE = InnoDB; Create table T_ROLE ( ROLE_ID Bigint NOT NULL AUTO_INCREMENT, ROLE_NAME Varchar(64) NOT NULL, ROLE_CAPTION Varchar(255), ROLE_DESCRIPTION Text, UNIQUE (ROLE_NAME), Primary Key (ROLE_ID)) ENGINE = InnoDB; Create table T_USER ( USER_ID Bigint NOT NULL, Primary Key (USER_ID)) ENGINE = InnoDB; Create table T_USER_PRIVILEGE ( USER_ID Bigint NOT NULL, RESOURCE_ID Bigint NOT NULL, OPERATION_ID Bigint NOT NULL, FORBIDDEN Bool NOT NULL, Primary Key (USER_ID,RESOURCE_ID,OPERATION_ID)) ENGINE = InnoDB; Create table T_ROLE_PRIVILEGE ( ROLE_ID Bigint NOT NULL, RESOURCE_ID Bigint NOT NULL, OPERATION_ID Bigint NOT NULL, FORBIDDEN Bool NOT NULL, Primary Key (ROLE_ID,RESOURCE_ID,OPERATION_ID)) ENGINE = InnoDB; Create table T_PRIVILEGE ( UID Bigint NOT NULL, RID Bigint NOT NULL, RNAME Varchar(255) NOT NULL, OID Bigint NOT NULL, ONAME Varchar(64) NOT NULL, FORBID Bool NOT NULL, Primary Key (UID,RID,OID,FORBID)) ENGINE = InnoDB; Create table T_ASSIGNMENT ( USER_ID Bigint NOT NULL, ROLE_ID Bigint NOT NULL, Primary Key (USER_ID,ROLE_ID)) ENGINE = InnoDB; Create Index IDX_PRIVILEGE ON T_PRIVILEGE (UID,RNAME,ONAME,FORBID); Alter table T_FORMULA add Foreign Key (RESOURCE_ID) references T_RESOURCE (RESOURCE_ID) on delete restrict on update restrict; Alter table T_FORMULA add Foreign Key (OPERATION_ID) references T_OPERATION (OPERATION_ID) on delete restrict on update restrict; Alter table T_USER_PRIVILEGE add Foreign Key (RESOURCE_ID,OPERATION_ID) references T_FORMULA (RESOURCE_ID,OPERATION_ID) on delete restrict on update restrict; Alter table T_ROLE_PRIVILEGE add Foreign Key (RESOURCE_ID,OPERATION_ID) references T_FORMULA (RESOURCE_ID,OPERATION_ID) on delete restrict on update restrict; Alter table T_ROLE_PRIVILEGE add Foreign Key (ROLE_ID) references T_ROLE (ROLE_ID) on delete restrict on update restrict; Alter table T_ASSIGNMENT add Foreign Key (ROLE_ID) references T_ROLE (ROLE_ID) on delete restrict on update restrict; Alter table T_USER_PRIVILEGE add Foreign Key (USER_ID) references T_USER (USER_ID) on delete restrict on update restrict; Alter table T_ASSIGNMENT add Foreign Key (USER_ID) references T_USER (USER_ID) on delete restrict on update restrict; drop view VW_FORMULA; create view VW_FORMULA as select a.*, b.* from T_RESOURCE a, T_OPERATION b, T_FORMULA c where c.RESOURCE_ID=a.RESOURCE_ID and c.OPERATION_ID=b.OPERATION_ID; drop view VW_USER_PRIVILEGE; create view VW_USER_PRIVILEGE as select a.USER_ID,a.FORBIDDEN, b.*, c.* from T_USER_PRIVILEGE a, T_RESOURCE b, T_OPERATION c where a.RESOURCE_ID=b.RESOURCE_ID and a.OPERATION_ID=c.OPERATION_ID; drop view VW_ROLE_PRIVILEGE; create view VW_ROLE_PRIVILEGE as select a.*,b.FORBIDDEN,c.*,d.* from T_ROLE a,T_ROLE_PRIVILEGE b,T_RESOURCE c, T_OPERATION d where a.ROLE_ID=b.ROLE_ID and b.RESOURCE_ID=c.RESOURCE_ID and b.OPERATION_ID=d.OPERATION_ID; drop view VW_ASSIGNMENT; create view VW_ASSIGNMENT as select a.USER_ID,b.* from T_ASSIGNMENT a, T_ROLE b where a.ROLE_ID=b.ROLE_ID; drop view VW_PRIVILEGE; create view VW_PRIVILEGE as select a.USER_ID, c.RESOURCE_ID, c.RESOURCE_NAME, c.RESOURCE_CAPTION, c.RESOURCE_DESCRIPTION, d.OPERATION_ID, d.OPERATION_NAME, d.OPERATION_CAPTION, d.OPERATION_DESCRIPTION, b.FORBIDDEN from T_USER a, T_USER_PRIVILEGE b, T_RESOURCE c, T_OPERATION d where a.USER_ID=b.USER_ID and b.RESOURCE_ID=c.RESOURCE_ID and b.OPERATION_ID=d.OPERATION_ID union select a.USER_ID, d.RESOURCE_ID, d.RESOURCE_NAME, d.RESOURCE_CAPTION, d.RESOURCE_DESCRIPTION, e.OPERATION_ID, e.OPERATION_NAME, e.OPERATION_CAPTION, e.OPERATION_DESCRIPTION, c.FORBIDDEN from T_USER a, T_ASSIGNMENT b, T_ROLE_PRIVILEGE c, T_RESOURCE d, T_OPERATION e where a.USER_ID=b.USER_ID and b.ROLE_ID=c.ROLE_ID and c.RESOURCE_ID=d.RESOURCE_ID and c.OPERATION_ID=e.OPERATION_ID; -- int resources. insert into T_RESOURCE(RESOURCE_ID, RESOURCE_NAME, RESOURCE_CAPTION, RESOURCE_DESCRIPTION) values(1, '%', 'all resources', 'all resources.'); insert into T_RESOURCE(RESOURCE_ID, RESOURCE_NAME, RESOURCE_CAPTION, RESOURCE_DESCRIPTION) values(2, 'rbac.user', 'rbac user', 'rbac core USER resource.'); insert into T_RESOURCE(RESOURCE_ID, RESOURCE_NAME, RESOURCE_CAPTION, RESOURCE_DESCRIPTION) values(3, 'rbac.role', 'rbac role', 'rbac core ROLE resource.'); insert into T_RESOURCE(RESOURCE_ID, RESOURCE_NAME, RESOURCE_CAPTION, RESOURCE_DESCRIPTION) values(4, 'rbac.resource', 'rbac resource', 'rbac core RESOURCE resource.'); insert into T_RESOURCE(RESOURCE_ID, RESOURCE_NAME, RESOURCE_CAPTION, RESOURCE_DESCRIPTION) values(5, 'rbac.operation', 'rbac operation', 'rbac core OPERATION resource.'); insert into T_RESOURCE(RESOURCE_ID, RESOURCE_NAME, RESOURCE_CAPTION, RESOURCE_DESCRIPTION) values(6, 'rbac.formula', 'rbac formula', 'rbac core FORMULA resource.'); -- init operations. insert into T_OPERATION(OPERATION_ID, OPERATION_NAME, OPERATION_CAPTION, OPERATION_DESCRIPTION) values(1, '%', 'all operations', 'all operations.'); insert into T_OPERATION(OPERATION_ID, OPERATION_NAME, OPERATION_CAPTION, OPERATION_DESCRIPTION) values(2, 'insert', 'insert', 'rbac core INSERT operation.'); insert into T_OPERATION(OPERATION_ID, OPERATION_NAME, OPERATION_CAPTION, OPERATION_DESCRIPTION) values(3, 'delete', 'delete', 'rbac core DELETE operation.'); insert into T_OPERATION(OPERATION_ID, OPERATION_NAME, OPERATION_CAPTION, OPERATION_DESCRIPTION) values(4, 'select', 'select', 'rbac core SELECT operation.'); insert into T_OPERATION(OPERATION_ID, OPERATION_NAME, OPERATION_CAPTION, OPERATION_DESCRIPTION) values(5, 'update', 'update', 'rbac core UPDATE operation.'); -- init formulas. insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(1, 1); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(2, 2); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(2, 3); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(2, 4); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(2, 5); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(3, 2); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(3, 3); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(3, 4); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(3, 5); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(4, 2); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(4, 3); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(4, 4); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(4, 5); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(5, 2); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(5, 3); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(5, 4); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(5, 5); insert into T_FORMULA(RESOURCE_ID, OPERATION_ID) values(6, 2