浏览 2343 次
锁定老帖子 主题:求助各位了解RBAC的朋友。
该帖已经被评为新手帖
|
|
---|---|
作者 | 正文 |
发表时间:2007-07-30
以下是我的数据库建模。 DROP DATABASE RoleBasedAccessControl; CREATE DATABASE RoleBasedAccessControl; USE RoleBasedAccessControl; /*操作表*/ CREATE TABLE operation ( id INT PRIMARY KEY, /*操作的名称*/ name VARCHAR(20) UNIQUE NOT NULL, /*操作的描述*/ description VARCHAR(200) ); /*为操作建立索引*/ CREATE UNIQUE INDEX IDX_NAME ON operation (name); INSERT INTO operation VALUES (0, '读', null); INSERT INTO operation VALUES (1, '写', null); INSERT INTO operation VALUES (2, '删', null); INSERT INTO operation VALUES (3, '改', null); /*资源表*/ CREATE TABLE resource ( id INT PRIMARY KEY, /*资源内容*/ name VARCHAR(100) UNIQUE NOT NULL, /*资源的描述*/ description VARCHAR(200) ); /*为资源内容建立索引*/ CREATE UNIQUE INDEX IDX_RESOURCE ON resource (name); INSERT INTO resource VALUES (0, 'index.jsp', null); INSERT INTO resource VALUES (1, 'addOperation.jsp', null); /*权限表*/ CREATE TABLE authorization ( id INT PRIMARY KEY, /*权限的名称*/ name VARCHAR(50) NOT NULL, /*权限对应的资源*/ resource INT NOT NULL, /*权限使用的操作*/ operation INT NOT NULL ); /*为权限名称建立索引*/ CREATE UNIQUE INDEX IDX_NAME ON authorization (name); /*为资源、操作建立索引*/ CREATE UNIQUE INDEX IDX_RESOURCE_OPERATION ON authorization (resource, operation); /*为权限资源建立外键*/ ALTER TABLE authorization ADD CONSTRAINT FK_RESOURCE FOREIGN KEY (resource) REFERENCES resource(id); /*为权限操作建立外键*/ ALTER TABLE authorization ADD CONSTRAINT FK_OPERATION FOREIGN KEY (operation) REFERENCES operation(id); INSERT INTO authorization VALUES (0, 'index_r', 0, 0); INSERT INTO authorization VALUES (1, 'index_w', 0, 1); INSERT INTO authorization VALUES (2, 'index_d', 0, 2); INSERT INTO authorization VALUES (3, 'index_u', 0, 3); /*角色表*/ CREATE TABLE role ( id INT PRIMARY KEY, /*父角色*/ parent INT NOT NULL, /*角色名称*/ name VARCHAR(20) UNIQUE NOT NULL, /*角色描述*/ description VARCHAR(200) ); --ALTER TABLE role ADD CONSTRAINT FK_PARENT FOREIGN KEY (parent) REFERENCES role (parent); INSERT INTO role VALUES (0, 0, 'anonym', null); INSERT INTO role VALUES (1, 0, 'user', null); INSERT INTO role VALUES (2, 1, 'admin', null); INSERT INTO role VALUES (3, 1, 'guest', null); /*授权表*/ CREATE TABLE permission ( /*角色*/ role INT NOT NULL, /*权限*/ authorization INT NOT NULL ); /*为授权表建立主键*/ ALTER TABLE permission ADD CONSTRAINT PK_PERMISSION PRIMARY KEY (role, authorization); ALTER TABLE permission ADD CONSTRAINT FK_ROLE FOREIGN KEY (role) REFERENCES role (id); ALTER TABLE permission ADD CONSTRAINT FK_AUTHORIZATION FOREIGN KEY (authorization) REFERENCES authorization (id); INSERT INTO permission VALUES (0, 0); INSERT INTO permission VALUES (1, 1); INSERT INTO permission VALUES (2, 2); INSERT INTO permission VALUES (2, 3); INSERT INTO permission VALUES (3, 3); 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |