day13-堡垒机表结构设计

最新推荐文章于 2025-09-12 14:04:02 发布

weixin_34128237

最新推荐文章于 2025-09-12 14:04:02 发布

阅读量110

点赞数

CC 4.0 BY-SA版权

文章标签：运维数据库

原文链接：http://www.cnblogs.com/zhangqigao/articles/7827504.html

本文介绍了如何使用Python的SQLAlchemy库来设计堡垒机系统的数据库表结构。主要涉及Host、HostGroup、RemoteUser、BindHost及UserProfile等类的设计，并通过多对多关系连接表实现灵活的权限管理。

一、前言

　　我们之前谈到的堡垒机，进行了paramiko源码修改，接下来我们来设置堡垒机的表结构

二、表结构设计图

二、代码

from sqlalchemy import  ForeignKey,Column,String,Integer,UniqueConstraint,Table
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_utils import ChoiceType

Base = declarative_base()


user_m2m_bindhost = Table("user_m2m_bindhost",Base.metadata,
                          Column("userprofile_id",Integer,ForeignKey("userprofile.id")),
                          Column("bindhost_id",Integer,ForeignKey("bind_host.id"))
                          )

bindhost_m2m_hostgroup=Table("bindhost_m2m_hostgroup",Base.metadata,
                             Column("bindhost_id",Integer,ForeignKey("bindhost.id")),
                             Column("hostgroup_id",Integer,ForeignKey("hostgroup.id"))
                             )

userprofile_m2m_hostgroup=Table("userprofile_m2m_hostgroup",Base.metadata,
                             Column("userprofile_id",Integer,ForeignKey("user_profile.id")),
                             Column("hostgroup_id",Integer,ForeignKey("host_group.id"))
                             )

class Host(Base):
    __tablename__ = "host"
    id = Column(Integer,primary_key=True,autoincrement=True)
    hostname = Column(String(64),unique=True)
    ip = Column(String(64),unique=True)
    port = Column(Integer,unique=True)

    def __repr__(self):
        return self.hostname

class HostGroup(Base):
    __tablename__ = "host_group"
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(64),unique=True)

    bind_hosts = relationship("BindHost",secondary="bindhost_m2m_hostgroup",backref="host_groups")
    def __repr__(self):
        return self.name


class RemoteUser(Base):
    __tablename__ = "remote_user"
    __table_args__ = (UniqueConstraint("auth_type","username","password",name="_user_password_uc"))
    id = Column(Integer,primary_key=True,autoincrement=True)
    AuthTypes = [
        ("ssh-password","SSH/Password"), #第1个值存数据库，第2个值是sqlalchemy显示的
        ("ssh-key","SSH/KEY")
    ]  #设置枚举值
    auth_type = Column(ChoiceType(AuthTypes))
    username = Column(String(64))
    password = Column(String(128))

    def __repr__(self):
        return  self.username

class BindHost(Base):
    "ip + 主机用户名 + 组名"
    __tablename__ = "bind_host"
    __table_args__ = (UniqueConstraint("host_id","group_id","remoteuser_id",name="_host_group_remoteuser_uc"))   #组合唯一

    id = Column(Integer,primary_key=True)
    host_id = Column(Integer,ForeignKey("host.id"))
    #group_id = Column(Integer,ForeignKey("group.id"))
    remoteuser_id = Column(Integer,ForeignKey("remoteuser.id"))

    host = relationship("Host",backref="bind_hosts")
    #host_group = relationship("HostGroup",backref="bind_hosts")
    remote_user = relationship("RemoteUser",backref="bind_hosts")

    def __repr__(self):
        return "<{0}--{1}--{2}>".format(self.host.ip,self.remote_user.username,self.host_group.name)



class UserProfile(Base):
    __tablename__ = "user_profile"
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(32),unique=True)
    password = Column(String(128))
    bind_hosts = relationship("BindHost",secondary="user_m2m_bindhost",backref="user_profiles")
    host_groups = relationship("HostGroup",secondary="userprofile_m2m_hostgroup",backref="user_profiles")
    def __repr__(self):
        return self.username

转载于:https://www.cnblogs.com/zhangqigao/articles/7827504.html