堡垒机-数据库设计
数据库表结构设计:
层次结构:
import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base #Column导入 from sqlalchemy import Column,Enum, Integer, String,UniqueConstraint,ForeignKey,Table from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy_utils import ChoiceType,PasswordType Base = declarative_base()#生成orm基类 user_m2m_bindhost = Table('user_m2m_bindhost', Base.metadata, Column('userprofile_id', Integer, ForeignKey('user_profile.id')), Column('bindhost_id', Integer, ForeignKey('bind_host.id')), ) bindhost_m2m_hostgroup=Table('bindhost_m2m_hostgroup', Base.metadata, Column('bindhost_id',Integer,ForeignKey('bind_host.id')), Column('hostgroup_id',Integer,ForeignKey('host_group.id')) ) user_m2m_hostgroup=Table('user_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) hostname =Column(String(64),unique=True) ip = Column(String(64),unique=True) port=Column(Integer,default=22) # remote_users =relationship('RemoteUser', secondary = host_m2m_remoteuser, backref='hosts') def __repr__(self): return self.hostname class HostGroup(Base): __tablename__ = 'host_group' id = Column(Integer, primary_key=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' #创建联合唯一auth_type、username、password __table_args__ = (UniqueConstraint('auth_type', 'username', 'password', name='_user_passwd_uc'),) AuthTypes = [ ('ssh-password', 'SSH/Password'),#第一个是存到数据库的,第二个是显示给我们看的 ('ssh-key', 'SSH/KEY'), ] id = Column(Integer, primary_key=True, autoincrement=True) auth_type = Column(ChoiceType(AuthTypes)) username = Column(String(64), nullable=False) password = Column(String(255)) def __repr__(self): return self.username class BindHost(Base): ''' 192.168.1.11 web 192.168.1.11 mysql ''' __tablename__ = "bind_host" __table_args__ = (UniqueConstraint('host_id','remoteuser_id', name='_host_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('remote_user.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 "<%s -- %s >" %(self.host.ip, self.remote_user.username ) class UserProfile(Base): __tablename__ = 'user_profile' id = Column(Integer, primary_key=True) username = Column(String(32), unique=True) password = Column(String(128)) bindhosts =relationship('BindHost', secondary=user_m2m_bindhost, backref = 'user_profiles') host_groups = relationship("HostGroup", secondary = user_m2m_hostgroup, backref = "user_profiles") def __repr__(self): return self.username # class AuditLog(Base): # pass if __name__ == '__main__': Base.metadata.create_all() # 创建所有表结构