SQL表设计-创建RBAC七表模型语句

RBAC七表模型是指基于角色的访问控制模型，包括用户表、角色表、权限表、资源表、用户角色关系表、角色权限关系表和资源权限关系表。以下是创建这七个表的SQL语句：

用户表：

CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT, username varchar(50) NOT NULL, password varchar(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

角色表：

CREATE TABLE role ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

权限表：

CREATE TABLE permission ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, url varchar(200) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

资源表：

CREATE TABLE resource ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, url varchar(200) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

用户角色关系表：

CREATE TABLE user_role ( id int(11) NOT NULL AUTO_INCREMENT, user_id int(11) NOT NULL, role_id int(11) NOT NULL, PRIMARY KEY (id), KEY user_id (user_id), KEY role_id (role_id), CONSTRAINT user_role_ibfk_1 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT user_role_ibfk_2 FOREIGN KEY (role_id) REFERENCES role (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

角色权限关系表：

CREATE TABLE role_permission ( id int(11) NOT NULL AUTO_INCREMENT, role_id int(11) NOT NULL, permission_id int(11) NOT NULL, PRIMARY KEY (id), KEY role_id (role_id), KEY permission_id (permission_id), CONSTRAINT role_permission_ibfk_1 FOREIGN KEY (role_id) REFERENCES role (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT role_permission_ibfk_2 FOREIGN KEY (permission_id) REFERENCES permission (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

资源权限关系表：

CREATE TABLE resource_permission ( id int(11) NOT NULL AUTO_INCREMENT, resource_id int(11) NOT NULL, permission_id int(11) NOT NULL, PRIMARY KEY (id), KEY resource_id (resource_id), KEY permission_id (permission_id), CONSTRAINT resource_permission_ibfk_1 FOREIGN KEY (resource_id) REFERENCES resource (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT resource_permission_ibfk_2 FOREIGN KEY (permission_id) REFERENCES permission (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

以上七个表构成了RBAC七表模型，可以用于实现基于角色的访问控制。