Bastion host table structure design and bastion host Table Structure Design

Bastion host table structure design and bastion host Table Structure Design
I. bastion host table structure

Ii. Create a table

#-*-Coding: UTF-8-*-from sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import Column, Integer, String, UniqueConstraint, Table, ForeignKey, DateTimefrom sqlalchemy. orm import relationshipfrom sqlalchemy_utils import ChoiceTypeBase = declarative_base () # user_m2m_bindhost = Table ("user_m2m_bindhost", Base. metadata, Column ("id", Integer, primary_key = True), Column ('user _ profile_id ', Integer, ForeignKey ("user_profile.id ")), column ('Bind _ host_id ', Integer, ForeignKey ("bind_host.id") # bindhost_m2m_hostgroup = Table ("bindhost_m2m_hostgroup", Base. metadata, Column ("id", Integer, primary_key = True), Column ('host _ groups_id ', Integer, ForeignKey ("host_groups.id ")), column ('Bind _ host_id ', Integer, ForeignKey ("bind_host.id") # user_m2m_hostgroup = Table ("user_m2m_hostgroup", Base. metadata, Column ("id", Integer, primary_key = True), Column ('user _ profile_id ', Integer, ForeignKey ("user_profile.id ")), column ('host _ group_id ', Integer, ForeignKey ("host_groups.id") class host (Base ): # host table _ 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) def _ repr _ (self): return self. hostnameclass HostGroup (Base): # host group table _ tablename _ = 'host _ groupup' id = Column (Integer, primary_key = True) name = Column (String (64 ), unique = True) function = Column (String (64) bind_hosts = relationship ('bindhost', secondary = 'bindhost _ m2m_hostgroup ', backref = 'host _ group ') def _ repr _ (self): return self. nameclass RemoteUser (Base): # remote user table _ tablename _ = 'remote _ user' # Union unique _ table_args _ = (UniqueConstraint ("auth_type ", "username", "password", name = "_ user_password_uc"),) id = Column (Integer, primary_key = True) username = Column (String (32 )) password = Column (String (128) # 1st values are stored in the database. The 2nd values are AuthTypes displayed by sqlalchemy = [("ssh-password", "SSH/Password "), ("ssh-key", "SSH/KEY")] # Set the enumerated value auth_type = Column (ChoiceType (AuthTypes) def _ repr _ (self): return self. usernameclass BindHost (Base ): '''bind host group, host, and remote user ''' _ tablename _ = 'Bind _ host' _ table_args _ = (UniqueConstraint ("host_id ", "remoteuser_id", name = "_ host_remoteuser"),) id = Column (Integer, primary_key = True) host_id = Column (Integer, ForeignKey ('host. id ') remoteuser_id = Column (Integer, ForeignKey ('remote _ user. id ') host = relationship ('host', backref = 'Bind _ hosts') remoteuser = relationship ('remoteuser', backref = 'Bind _ hosts ') def _ repr _ (self): return "% s" % (self. host. ip, self. remoteuser. username) class UserProfile (Base): # bastion host table _ tablename _ = 'user _ profile 'id = Column (Integer, primary_key = True) username = Column (String (32), unique = True) password = Column (String (128) bind_hosts = relationship ("BindHost", secondary = 'user _ m2m_bindhost ', backref = 'user _ profile ') host_groups = relationship ("HostGroup", secondary = 'user _ m2m_hostgroup', backref = 'user _ profile ') def _ repr _ (self): return self. usernameclass AuditLog (Base): # log table _ tablename _ = 'audit _ log' id = Column (Integer, primary_key = True) user_id = Column (Integer, foreignKey ('user _ profile. id ') bind_host_id = Column (Integer, ForeignKey ('Bind _ host. id ') action_choices = [(0, 'cmd'), (1, 'login'), (2, 'logout'), (3, 'getfile '), (4, 'sendfile'), (5, 'exception'),] action_choices2 = [(u 'cmd', u 'cmd'), (u 'login ', u'login'), (u'logout', u'logout'), # (3, 'getfile'), # (4, 'sendfile'), # (5, 'exception'),] action_type = Column (ChoiceType (action_choices2) # action_type = Column (String (64) cmd = Column (String (255) date = Column (DateTime) user_profile = relationship ("UserProfile", backref = 'audit _ logs') bind_host = relationship ("BindHost", backref = 'audit _ logs ') def _ repr _ (self): return "% s" % (self. date, self. action_type, self. cmd) if _ name _ = '_ main _': pass

Create a table