EugeneX MiniWeb

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Создание пользователей и групп ======
В этом примере указанно как правильно создавать пользователей и применять их к группам с заменой прав как отдельного пользователя так и группы на определенный доступ...
<code SQL>
DROP TABLES IF EXISTS `rules_to_users`, `user_to_groups`, `rules_to_groups`, `users`, `rules`, `groups`;

CREATE TABLE users (
    `id` INTEGER UNSIGNED AUTO_INCREMENT,
    `name` VARCHAR(50),
    PRIMARY KEY (`id`),
    UNIQUE (`name`)
) ENGINE=InnoDB;

CREATE TABLE `rules` (
    `id` INTEGER UNSIGNED AUTO_INCREMENT,
    `title` VARCHAR(50),
    `path` VARCHAR(127),
    `order` ENUM('ALLOW', 'DENY') NOT NULL DEFAULT 'DENY',
    PRIMARY KEY (`id`),
    UNIQUE (`title`)
) ENGINE=InnoDB;

CREATE TABLE `groups` (
    `id` INTEGER UNSIGNED AUTO_INCREMENT,
    `name` VARCHAR(50),
    PRIMARY KEY (`id`),
    UNIQUE (`name`)
) ENGINE=InnoDB;

CREATE TABLE `user_to_groups` (
    `user_id` INTEGER UNSIGNED,
    `group_id` INTEGER UNSIGNED,
    UNIQUE(`user_id`, `group_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `rules_to_groups` (
    `group_id` INTEGER UNSIGNED NOT NULL,
    `rule_id`  INTEGER UNSIGNED NOT NULL,
    `order` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    INDEX (`order`),
    UNIQUE (`group_id`, `rule_id`),
    FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`)
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`rule_id`) REFERENCES `rules`(`id`)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `rules_to_users` (
    `user_id` INTEGER UNSIGNED NOT NULL,
    `rule_id`  INTEGER UNSIGNED NOT NULL,
    `order` INTEGER UNSIGNED NOT NULL DEFAULT 0,
    INDEX (`order`),
    UNIQUE (`user_id`, `rule_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`rule_id`) REFERENCES `rules`(`id`)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT `rules` (`title`, `path`, `order`) VALUES
    ('Default Rule! Don not Delete!!!', '/', 'DENY'),
    ('User profile read', '/user/get/', 'ALLOW'),
    ('User profile write own', '/user/post/', 'ALLOW'),
    ('Admin: User profile write others', '/user/push/', 'ALLOW'),
    ('List users on engine', '/user/list/', 'ALLOW'),
    ('Admin: General politics', '/admin/gpedit/', 'ALLOW'),
    ('Admin: General politics: Deny Write', '/admin/gpedit/save', 'DENY');


INSERT `users` (`name`) VALUES ("testuser");
INSERT `groups` (`id`, `name`) VALUES
    (NULL, "guest"),
    (NULL, "user"),
    (NULL, "moderator"),
    (NULL, "admin");

 -- Allow users to get profile info
INSERT `rules_to_groups` (`group_id`, `rule_id`) VALUES
    (2, 2), (2, 3);

INSERT `rules_to_users` (`user_id`, `rule_id`, `order`) VALUES
    (1, 6, 1), (1, 7, 0);

SELECT *, 'def' `definer`, 0 `order` FROM rules WHERE id = 1 -- id=1 DEFAULT RULE ID - FOR ALL!
UNION (SELECT r.*, 'grp', rtg.`order` FROM rules `r`
    LEFT JOIN `rules_to_groups` rtg ON rtg.rule_id = r.id
    WHERE rtg.group_id IN (1, 2, 3) -- IN ... USER_GROUPS ARRAY CONSTANT
    ORDER BY rtg.`order` DESC)
UNION (SELECT ur.*, 'usr', rtu.`order` FROM rules `ur`
    LEFT JOIN `rules_to_users` rtu ON rtu.rule_id = ur.id
    WHERE rtu.user_id = 1  -- user_id = ... USER_ID CONSTANT
    ORDER BY rtu.`order` DESC);
</code>
EugeneX MiniWeb

User Tools

Site Tools

Page Tools
