65 lines
2.2 KiB
SQL
65 lines
2.2 KiB
SQL
|
||
CREATE TABLE identity (
|
||
identity_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
network_id INT NOT NULL,
|
||
-- 业务侧来源
|
||
subject_type ENUM('token', 'instance', 'user', 'service') NOT NULL,
|
||
subject_id VARCHAR(128) NOT NULL,
|
||
|
||
created_at INT(10) NOT NULL DEFAULT 0,
|
||
expired_at INT(10) NOT NULL DEFAULT 0,
|
||
|
||
UNIQUE KEY uk_subject (network_id, subject_type, subject_id)
|
||
);
|
||
|
||
-- policy 表（权限集合，可复用）
|
||
CREATE TABLE policy (
|
||
policy_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
network_id INT NOT NULL,
|
||
name VARCHAR(64) NOT NULL,
|
||
description VARCHAR(255),
|
||
created_at INT(10) NOT NULL DEFAULT 0
|
||
);
|
||
|
||
-- identity_policy（多对多关系）
|
||
CREATE TABLE identity_policy (
|
||
identity_id INT NOT NULL,
|
||
policy_id INT NOT NULL,
|
||
PRIMARY KEY (identity_id, policy_id)
|
||
);
|
||
|
||
CREATE TABLE rule (
|
||
rule_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
network_id INT NOT NULL,
|
||
-- 来源限制（可选）
|
||
src_policy_id INT NOT NULL,
|
||
-- 目标限制（可选，允许 NULL 表示 any）
|
||
dst_policy_id INT NULL,
|
||
-- 6=TCP, 17=UDP
|
||
proto TINYINT NOT NULL,
|
||
-- 0~65535
|
||
port INT NOT NULL,
|
||
action ENUM('allow', 'deny') NOT NULL,
|
||
created_at INT(10) NOT NULL DEFAULT 0,
|
||
INDEX idx_src (src_policy_id),
|
||
INDEX idx_dst (dst_policy_id)
|
||
);
|
||
|
||
-- 实际操作逻辑
|
||
-- 1. 通过Token获取user+password的方式找到对应的identity_id，每个端都会有一个对应的identity_id值
|
||
-- 2. 数据访问的时候SDLData结构会携带一个identity_id, 被访问端会先查找自身的cache是否有对应identity_id(src_identity_id)的规则
|
||
|
||
|
||
-- 难点
|
||
-- 通过src_identity_id, dst_identity_id 查找到对应的rules
|
||
|
||
-- 查找来源对应的rules
|
||
-- $src_policy_ids = select * from identity_policy where identity_id = $src_identity_id
|
||
-- $drt_policy_ids = select * from identity_policy where identity_id = $dst_identity_id
|
||
|
||
-- 来源 src_policy_id 可以是Any (UNION {0})；目标 dst_policy_id不能是Any
|
||
-- select * from rule where src_policy_id in ($src_policy_ids UNION {0}) and dst_policy_id in $drt_policy_ids
|
||
|
||
-- 然后合并全部的rules
|
||
|