permissions_example.sql

DROP SCHEMA IF EXISTS `dennis_db` ;
CREATE SCHEMA IF NOT EXISTS `dennis_db` DEFAULT CHARACTER SET utf8 ;
USE `dennis_db` ;

-- -----------------------------------------------------
-- Table `dennis_db`.`resource`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dennis_db`.`resource` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `dennis_db`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dennis_db`.`user` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(15) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `dennis_db`.`access`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dennis_db`.`access` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `resource_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`, `resource_id`),
  INDEX `fk_action_resource1_idx` (`resource_id` ASC),
  CONSTRAINT `fk_action_resource1`
    FOREIGN KEY (`resource_id`)
    REFERENCES `dennis_db`.`resource` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `dennis_db`.`privilege`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dennis_db`.`privilege` (
  `role_id` INT(11) NOT NULL,
  `resource_id` INT(11) NOT NULL,
  `access_id` INT NOT NULL,
  PRIMARY KEY (`role_id`, `resource_id`, `access_id`),
  INDEX `fk_role_has_access_access1_idx` (`access_id` ASC, `resource_id` ASC),
  INDEX `fk_role_has_access_role1_idx` (`role_id` ASC),
  CONSTRAINT `fk_role_has_access_role1`
    FOREIGN KEY (`role_id`)
    REFERENCES `dennis_db`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_role_has_access_access1`
    FOREIGN KEY (`access_id` , `resource_id`)
    REFERENCES `dennis_db`.`access` (`id` , `resource_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;




-- SAMPLE DATA

insert into user values (1,"dennis_the_cook");

insert into resource values (1,"Kitchen");
insert into resource values (2,"Accounting");
insert into resource values (3,"Shipping");

insert into access values (1, 1, "cook");
insert into access values (2, 2, "pay");
insert into access values (3, 3, "pay");



-- If I want to allow dennis_the_cook to cook in the Kitchen I do:
insert into privilege (role_id, resource_id, access_id) values (1,1,1);

-- I can also allow users to: 
-- pay in Accounting 
-- pay in Shipping