时间戳

首页 > 解决方案 > 黑名单/白名单表设计

mysql - 黑名单/白名单表设计

问题描述

我们有一组用户

CREATE TABLE `users` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `email` varchar(254) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `unique_email` (`email`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED

每个用户可以有一个或多个域，例如

CREATE TABLE `domains` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `user_id` varchar(11) NOT NULL,
   `domain` varchar(254) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `domain` (`domain`),
   CONSTRAINT `domains_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED

我们有一个包含某种数据的表，对于这个例子，它包含什么并不重要

CREATE TABLE `some_data` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `content` TEXT NOT NULL,
   PRIMARY KEY (`id`),
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED

我们希望某些元素some_data只能被某些users或仅某些domains（白名单情况）访问。some_data在其他情况下，我们希望每个人都可以访问元素，但某些users或某些domains（黑名单情况）。理想情况下，我们希望some_data在单个查询中检索给定元素可访问的域列表，理想情况下执行相反的操作（列出给定域可以访问的所有数据）

到目前为止，我们的方法是单个表

CREATE TABLE `access_rules` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `rule_type` enum('blacklist','whitelist')
   `some_data_id` int(11) NOT NULL,
   `user_id` int(11) NOT NULL,
   `domain_id` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
   CONSTRAINT `access_rules_some_data_id_fk` FOREIGN KEY (`some_data_id`) REFERENCES `some_data` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED

然而问题是我们需要查询数据库两次（以确定给定的数据条目是在操作黑名单还是白名单[白名单具有更高的优先级]）。（编辑：可以在单个查询中完成）此外，由于 domain_id 可以为空（以允许将整个用户列入黑名单/白名单）加入并不容易

将使用此模式的 API 目前每秒达到 4-5k 次，因此性能很重要。该users表相对较小（50k+ 行），domains表大约有 150 万个条目。some_data也相对较小（低于 100k 行）

编辑：问题更多的是围绕语义和最佳实践。有了上面的结构，我相信我们可以让它工作，但是模式“感觉不对”，我想知道是否有更好的方法

标签： mysqlsql

解决方案

有两个问题需要考虑，规范化和管理。要按照传统方式进行标准化，您需要 4 个表。

设置 3 个主表 USER、DOMAIN、OtherDATA。

使用 User_Id、Domain_Id、OtherDATA_Id、PermissionLevel 设置子表

这提供了最少数量的重复数据。它还使用户域级别的管理变得更加容易。您还可以在用户和域表中添加默认的白名单/黑名单字段。这样，脚本可以自动填充子表，然后经理可以进入并调整所需的一个值。

如果您有两张不同的表，一张用于白名单，一张用于黑名单，您可能会意外地在两个列表中获得用户或域。实际上它将是 4 个表，2 个用于用户，2 个用于域。管理会更复杂。

推荐阅读