|
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; |
|
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; |
|
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; |
|
|
|
DROP SCHEMA IF EXISTS `mydb` ; |
|
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; |
|
USE `mydb` ; |
|
|
|
-- ----------------------------------------------------- |
|
-- Table `mydb`.`users` |
|
-- ----------------------------------------------------- |
|
DROP TABLE IF EXISTS `mydb`.`users` ; |
|
|
|
CREATE TABLE IF NOT EXISTS `mydb`.`users` ( |
|
`u_id` INT NOT NULL AUTO_INCREMENT , |
|
`u_username` VARCHAR(127) NOT NULL , |
|
`u_password` VARCHAR(255) NOT NULL , |
|
PRIMARY KEY (`u_id`) ) |
|
ENGINE = InnoDB; |
|
|
|
|
|
-- ----------------------------------------------------- |
|
-- Table `mydb`.`roles` |
|
-- ----------------------------------------------------- |
|
DROP TABLE IF EXISTS `mydb`.`roles` ; |
|
|
|
CREATE TABLE IF NOT EXISTS `mydb`.`roles` ( |
|
`r_id` INT NOT NULL AUTO_INCREMENT , |
|
`r_name` VARCHAR(255) NOT NULL , |
|
PRIMARY KEY (`r_id`) ) |
|
ENGINE = InnoDB; |
|
|
|
|
|
-- ----------------------------------------------------- |
|
-- Table `mydb`.`role_inheritance` |
|
-- ----------------------------------------------------- |
|
DROP TABLE IF EXISTS `mydb`.`role_inheritance` ; |
|
|
|
CREATE TABLE IF NOT EXISTS `mydb`.`role_inheritance` ( |
|
`role_id` INT NOT NULL , |
|
`parent_role_id` INT NOT NULL , |
|
PRIMARY KEY (`role_id`, `parent_role_id`) , |
|
INDEX `role` (`role_id` ASC) , |
|
INDEX `parent_role` (`parent_role_id` ASC) , |
|
CONSTRAINT `role` |
|
FOREIGN KEY (`role_id` ) |
|
REFERENCES `mydb`.`roles` (`r_id` ) |
|
ON DELETE CASCADE |
|
ON UPDATE CASCADE, |
|
CONSTRAINT `parent_role` |
|
FOREIGN KEY (`parent_role_id` ) |
|
REFERENCES `mydb`.`roles` (`r_id` ) |
|
ON DELETE CASCADE |
|
ON UPDATE CASCADE) |
|
ENGINE = InnoDB; |
|
|
|
|
|
-- ----------------------------------------------------- |
|
-- Table `mydb`.`users_roles` |
|
-- ----------------------------------------------------- |
|
DROP TABLE IF EXISTS `mydb`.`users_roles` ; |
|
|
|
CREATE TABLE IF NOT EXISTS `mydb`.`users_roles` ( |
|
`user_id` INT NOT NULL , |
|
`role_id` INT NOT NULL , |
|
PRIMARY KEY (`user_id`, `role_id`) , |
|
INDEX `users` (`user_id` ASC) , |
|
INDEX `roles` (`role_id` ASC) , |
|
CONSTRAINT `users` |
|
FOREIGN KEY (`user_id` ) |
|
REFERENCES `mydb`.`users` (`u_id` ) |
|
ON DELETE CASCADE |
|
ON UPDATE CASCADE, |
|
CONSTRAINT `roles` |
|
FOREIGN KEY (`role_id` ) |
|
REFERENCES `mydb`.`roles` (`r_id` ) |
|
ON DELETE CASCADE |
|
ON UPDATE CASCADE) |
|
ENGINE = InnoDB; |
|
|
|
|
|
-- ----------------------------------------------------- |
|
-- Table `mydb`.`resources` |
|
-- ----------------------------------------------------- |
|
DROP TABLE IF EXISTS `mydb`.`resources` ; |
|
|
|
CREATE TABLE IF NOT EXISTS `mydb`.`resources` ( |
|
`r_id` VARCHAR(127) NOT NULL , |
|
`parent_resource` VARCHAR(127) NULL , |
|
PRIMARY KEY (`r_id`) , |
|
INDEX `r_parent` (`parent_resource` ASC) , |
|
CONSTRAINT `r_parent` |
|
FOREIGN KEY (`parent_resource` ) |
|
REFERENCES `mydb`.`resources` (`r_id` ) |
|
ON DELETE CASCADE |
|
ON UPDATE CASCADE) |
|
ENGINE = InnoDB; |
|
|
|
|
|
-- ----------------------------------------------------- |
|
-- Table `mydb`.`role_resource_privilege` |
|
-- ----------------------------------------------------- |
|
DROP TABLE IF EXISTS `mydb`.`role_resource_privilege` ; |
|
|
|
CREATE TABLE IF NOT EXISTS `mydb`.`role_resource_privilege` ( |
|
`role_id` INT NOT NULL , |
|
`resource_id` VARCHAR(127) NOT NULL , |
|
`privilege` VARCHAR(127) NOT NULL , |
|
PRIMARY KEY (`role_id`, `resource_id`, `privilege`) , |
|
INDEX `p_roles` (`role_id` ASC) , |
|
INDEX `p_resources` (`resource_id` ASC) , |
|
CONSTRAINT `p_roles` |
|
FOREIGN KEY (`role_id` ) |
|
REFERENCES `mydb`.`roles` (`r_id` ) |
|
ON DELETE CASCADE |
|
ON UPDATE CASCADE, |
|
CONSTRAINT `p_resources` |
|
FOREIGN KEY (`resource_id` ) |
|
REFERENCES `mydb`.`resources` (`r_id` ) |
|
ON DELETE CASCADE |
|
ON UPDATE CASCADE) |
|
ENGINE = InnoDB; |
|
|
|
|
|
-- ----------------------------------------------------- |
|
-- Table `mydb`.`user_resource_privilege` |
|
-- ----------------------------------------------------- |
|
DROP TABLE IF EXISTS `mydb`.`user_resource_privilege` ; |
|
|
|
CREATE TABLE IF NOT EXISTS `mydb`.`user_resource_privilege` ( |
|
`user_id` INT NOT NULL , |
|
`resource_id` VARCHAR(127) NOT NULL , |
|
`privilege` VARCHAR(127) NOT NULL , |
|
PRIMARY KEY (`user_id`, `resource_id`, `privilege`) , |
|
INDEX `u_users` (`user_id` ASC) , |
|
INDEX `u_resources` (`resource_id` ASC) , |
|
CONSTRAINT `u_users` |
|
FOREIGN KEY (`user_id` ) |
|
REFERENCES `mydb`.`users` (`u_id` ) |
|
ON DELETE CASCADE |
|
ON UPDATE CASCADE, |
|
CONSTRAINT `u_resources` |
|
FOREIGN KEY (`resource_id` ) |
|
REFERENCES `mydb`.`resources` (`r_id` ) |
|
ON DELETE CASCADE |
|
ON UPDATE CASCADE) |
|
ENGINE = InnoDB; |
|
|
|
|
|
|
|
SET SQL_MODE=@OLD_SQL_MODE; |
|
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; |
|
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; |
|
|
|
-- ----------------------------------------------------- |
|
-- Data for table `mydb`.`users` |
|
-- ----------------------------------------------------- |
|
START TRANSACTION; |
|
USE `mydb`; |
|
INSERT INTO `mydb`.`users` (`u_id`, `u_username`, `u_password`) VALUES (1, 'admin', 'admin'); |
|
INSERT INTO `mydb`.`users` (`u_id`, `u_username`, `u_password`) VALUES (2, 'tester', 'tester'); |
|
|
|
COMMIT; |
|
|
|
-- ----------------------------------------------------- |
|
-- Data for table `mydb`.`roles` |
|
-- ----------------------------------------------------- |
|
START TRANSACTION; |
|
USE `mydb`; |
|
INSERT INTO `mydb`.`roles` (`r_id`, `r_name`) VALUES (1, 'guest'); |
|
INSERT INTO `mydb`.`roles` (`r_id`, `r_name`) VALUES (2, 'tester'); |
|
INSERT INTO `mydb`.`roles` (`r_id`, `r_name`) VALUES (3, 'manager'); |
|
INSERT INTO `mydb`.`roles` (`r_id`, `r_name`) VALUES (4, 'admin'); |
|
|
|
COMMIT; |
|
|
|
-- ----------------------------------------------------- |
|
-- Data for table `mydb`.`role_inheritance` |
|
-- ----------------------------------------------------- |
|
START TRANSACTION; |
|
USE `mydb`; |
|
INSERT INTO `mydb`.`role_inheritance` (`role_id`, `parent_role_id`) VALUES (2, 1); |
|
INSERT INTO `mydb`.`role_inheritance` (`role_id`, `parent_role_id`) VALUES (3, 2); |
|
INSERT INTO `mydb`.`role_inheritance` (`role_id`, `parent_role_id`) VALUES (4, 3); |
|
|
|
COMMIT; |
|
|
|
-- ----------------------------------------------------- |
|
-- Data for table `mydb`.`users_roles` |
|
-- ----------------------------------------------------- |
|
START TRANSACTION; |
|
USE `mydb`; |
|
INSERT INTO `mydb`.`users_roles` (`user_id`, `role_id`) VALUES (1, 4); |
|
INSERT INTO `mydb`.`users_roles` (`user_id`, `role_id`) VALUES (2, 2); |
|
|
|
COMMIT; |
|
|
|
-- ----------------------------------------------------- |
|
-- Data for table `mydb`.`resources` |
|
-- ----------------------------------------------------- |
|
START TRANSACTION; |
|
USE `mydb`; |
|
INSERT INTO `mydb`.`resources` (`r_id`, `parent_resource`) VALUES ('default', NULL); |
|
INSERT INTO `mydb`.`resources` (`r_id`, `parent_resource`) VALUES ('default::login', 'default'); |
|
INSERT INTO `mydb`.`resources` (`r_id`, `parent_resource`) VALUES ('default::logout', 'default'); |
|
INSERT INTO `mydb`.`resources` (`r_id`, `parent_resource`) VALUES ('special', NULL); |
|
|
|
COMMIT; |
|
|
|
-- ----------------------------------------------------- |
|
-- Data for table `mydb`.`role_resource_privilege` |
|
-- ----------------------------------------------------- |
|
START TRANSACTION; |
|
USE `mydb`; |
|
INSERT INTO `mydb`.`role_resource_privilege` (`role_id`, `resource_id`, `privilege`) VALUES (1, 'default::login', 'ALL'); |
|
INSERT INTO `mydb`.`role_resource_privilege` (`role_id`, `resource_id`, `privilege`) VALUES (2, 'default::logout', 'ALL'); |
|
INSERT INTO `mydb`.`role_resource_privilege` (`role_id`, `resource_id`, `privilege`) VALUES (4, 'default', 'ALL'); |
|
|
|
COMMIT; |
|
|
|
-- ----------------------------------------------------- |
|
-- Data for table `mydb`.`user_resource_privilege` |
|
-- ----------------------------------------------------- |
|
START TRANSACTION; |
|
USE `mydb`; |
|
INSERT INTO `mydb`.`user_resource_privilege` (`user_id`, `resource_id`, `privilege`) VALUES (1, 'special', 'SPECIAL'); |
|
|
|
COMMIT; |