github.com/merlinepedra/gophish1@v0.9.0/db/db_mysql/migrations/20190105192341_0.8.0_rbac.sql (about)

     1  
     2  -- +goose Up
     3  -- SQL in section 'Up' is executed when this migration is applied
     4  CREATE TABLE IF NOT EXISTS `roles` (
     5      `id`          INTEGER PRIMARY KEY AUTO_INCREMENT,
     6      `slug`        VARCHAR(255) NOT NULL UNIQUE,
     7      `name`        VARCHAR(255) NOT NULL UNIQUE,
     8      `description` VARCHAR(255)
     9  );
    10  
    11  ALTER TABLE `users` ADD COLUMN `role_id` INTEGER;
    12  
    13  CREATE TABLE IF NOT EXISTS `permissions` (
    14      `id`          INTEGER PRIMARY KEY AUTO_INCREMENT,
    15      `slug`        VARCHAR(255) NOT NULL UNIQUE,
    16      `name`        VARCHAR(255) NOT NULL UNIQUE,
    17      `description` VARCHAR(255)
    18  );
    19  
    20  
    21  CREATE TABLE IF NOT EXISTS `role_permissions` (
    22      `role_id`       INTEGER NOT NULL,
    23      `permission_id` INTEGER NOT NULL
    24  );
    25  
    26  INSERT INTO `roles` (`slug`, `name`, `description`)
    27  VALUES
    28      ("admin", "Admin", "System administrator with full permissions"),
    29      ("user", "User", "User role with edit access to objects and campaigns");
    30  
    31  INSERT INTO `permissions` (`slug`, `name`, `description`)
    32  VALUES
    33      ("view_objects", "View Objects", "View objects in Gophish"),
    34      ("modify_objects", "Modify Objects", "Create and edit objects in Gophish"),
    35      ("modify_system", "Modify System", "Manage system-wide configuration");
    36  
    37  -- Our rules for generating the admin user are:
    38  -- * The user with the name `admin`
    39  -- * OR the first user, if no `admin` user exists
    40  -- MySQL apparently makes these queries gross. Thanks MySQL.
    41  UPDATE `users` SET `role_id`=(
    42      SELECT `id` FROM `roles` WHERE `slug`="admin")
    43  WHERE `id`=(
    44      SELECT `id` FROM (
    45          SELECT * FROM `users`
    46      ) as u WHERE `username`="admin"
    47      OR `id`=(
    48          SELECT MIN(`id`) FROM (
    49              SELECT * FROM `users`
    50          ) as u
    51      ) LIMIT 1);
    52  
    53  -- Every other user will be considered a standard user account. The admin user
    54  -- will be able to change the role of any other user at any time.
    55  UPDATE `users` SET `role_id`=(
    56      SELECT `id` FROM `roles` AS role_id WHERE `slug`="user")
    57  WHERE role_id IS NULL;
    58  
    59  -- Our default permission set will:
    60  -- * Allow admins the ability to do anything
    61  -- * Allow users to modify objects
    62  
    63  -- Allow any user to view objects
    64  INSERT INTO `role_permissions` (`role_id`, `permission_id`)
    65  SELECT r.id, p.id FROM roles AS r, `permissions` AS p
    66  WHERE r.id IN (SELECT `id` FROM roles WHERE `slug`="admin" OR `slug`="user")
    67  AND p.id=(SELECT `id` FROM `permissions` WHERE `slug`="view_objects");
    68  
    69  -- Allow admins and users to modify objects
    70  INSERT INTO `role_permissions` (`role_id`, `permission_id`)
    71  SELECT r.id, p.id FROM roles AS r, `permissions` AS p
    72  WHERE r.id IN (SELECT `id` FROM roles WHERE `slug`="admin" OR `slug`="user")
    73  AND p.id=(SELECT `id` FROM `permissions` WHERE `slug`="modify_objects");
    74  
    75  -- Allow admins to modify system level configuration
    76  INSERT INTO `role_permissions` (`role_id`, `permission_id`)
    77  SELECT r.id, p.id FROM roles AS r, `permissions` AS p
    78  WHERE r.id IN (SELECT `id` FROM roles WHERE `slug`="admin")
    79  AND p.id=(SELECT `id` FROM `permissions` WHERE `slug`="modify_system");
    80  
    81  -- +goose Down
    82  -- SQL section 'Down' is executed when this migration is rolled back
    83  DROP TABLE `roles`
    84  DROP TABLE `user_roles`
    85  DROP TABLE `permissions`