下面我们就使用之前所学习到的SQL alchemy来使用创建数据库，实现一个简单的堡垒机程序。
首先需要对整个表进行一个构造。下图为我们描述了整个程序所需要的模块以及表的结构。
class UserProfile(Base): __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_profiles‘)#通过第三表连接与bind_host的多对多关系 host_groups = relationship(‘HostGroup‘,secondary = ‘user_m2m_group‘,backref = ‘user_profiles‘)#通过第三表连接与group的多对多关系 def __repr__(self): return self.username
class HostGroup(Base): __tablename__ = ‘hostgroup‘ id = Column(Integer, primary_key=True) name = Column(String(64), unique=True) bind_hosts = relationship(‘BindHost‘, secondary=‘bindhost_m2m_group‘, backref=‘host_groups‘)#通过第三表连接与bindhost的多对多关系 def __repr__(self): return self.name
class Host(Base): __tablename__ = ‘host‘ id = Column(Integer, primary_key=True) hostname = Column(String(64), unique=True)#主机名唯一 ip = Column(String(64), unique=True)#IP唯一 port = Column(Integer, default=22) def __repr__(self): return self.hostname
class RemoteUser(Base): __tablename__ = ‘remote_user‘ __table_args__ = ( UniqueConstraint(‘auth_type‘, ‘username‘, ‘password‘, name=‘_user_passwd_uc‘),) # name是存在数据库里的联合唯一键,将这三者联系在一起 AuthTypes = [ #包含password和key两种认证方式 (‘ssh-password‘, ‘SSHH/Password‘), # 第一个真正存到数据库里的，第二个是从SQL alchemy对外显示的 (‘ssh-key‘, ‘SSH/KEY‘), ] id = Column(Integer, primary_key=True) auth_type = Column(ChoiceType(AuthTypes)) username = Column(String(32)) password = Column(String(128)) def __repr__(self): return self.username
class BindHost(Base): __tablename__ = ‘bindhost‘ __table_args__ = (UniqueConstraint(‘host_id‘, ‘remoteuser_id‘, name=‘_host_remote_uc‘),) # 确保联合唯一，用host_remote_uc储存 id = Column(Integer, primary_key=True) host_id = Column(Integer, ForeignKey(‘host.id‘))#外键引入host.id remoteuser_id = Column(Integer, ForeignKey(‘remote_user.id‘))#外键引入remote_user.id host = relationship(‘Host‘, backref=‘bind_hosts‘)#跟Host绑定 remote_user = relationship(‘RemoteUser‘, backref=‘bind_hosts‘)#跟RemoteUser绑定 def __repr__(self): return ‘<%s -- %s>‘ % (self.host.ip,self.remote_user.username)#返回主机IP和远端用户名
Table(‘user_m2m_bindhost‘,#堡垒机与bindhost关联
Base.metadata,
Column(‘userprofile_id‘,Integer,ForeignKey(‘userprofile.id‘)),
Column(‘bindhost_id‘,Integer,ForeignKey(‘bindhost.id‘)),
)
Table(‘bindhost_m2m_group‘,#分组与bindhost关联
Base.metadata,
Column(‘bindhost_id‘,Integer,ForeignKey(‘bindhost.id‘)),
Column(‘hostgroup_id‘,Integer,ForeignKey(‘hostgroup.id‘)),
)
Table(‘user_m2m_group‘,#堡垒机账户与分组关联
Base.metadata,
Column(‘userprofile_id‘,Integer,ForeignKey(‘user_profile.id‘)),
Column(‘hostgroup_id‘,Integer,ForeignKey(‘hostgroup.id‘)),
)
表结构的完整代码为如下，并存在名为modules_v2的py文件中：
__Author__ = "Panda-J" from sqlalchemy import Table, String, Integer, ForeignKey, Column, Enum, UniqueConstraint from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base from sqlalchemy_utils import ChoiceType from sqlalchemy import create_engine Base = declarative_base() Table(‘user_m2m_bindhost‘,#堡垒机与bindhost关联 Base.metadata, Column(‘userprofile_id‘,Integer,ForeignKey(‘user_profile.id‘)), Column(‘bindhost_id‘,Integer,ForeignKey(‘bindhost.id‘)), ) Table(‘bindhost_m2m_group‘,#分组与bindhost关联 Base.metadata, Column(‘bindhost_id‘,Integer,ForeignKey(‘bindhost.id‘)), Column(‘hostgroup_id‘,Integer,ForeignKey(‘hostgroup.id‘)), ) Table(‘user_m2m_group‘,#堡垒机账户与分组关联 Base.metadata, Column(‘userprofile_id‘,Integer,ForeignKey(‘user_profile.id‘)), Column(‘hostgroup_id‘,Integer,ForeignKey(‘hostgroup.id‘)), ) class Host(Base): __tablename__ = ‘host‘ id = Column(Integer, primary_key=True) hostname = Column(String(64), unique=True)#主机名唯一 ip = Column(String(64), unique=True)#IP唯一 port = Column(Integer, default=22) def __repr__(self): return self.hostname # 如何建立表结构让host对应的hostgroup访问remoteuser时只有一种权限。而不是拥有remoteuser端的所有权限 # 不要让主机关联主机组，一个主机加一个用户关联在一起属于一个主机组 class HostGroup(Base): __tablename__ = ‘hostgroup‘ id = Column(Integer, primary_key=True) name = Column(String(64), unique=True) bind_hosts = relationship(‘BindHost‘, secondary=‘bindhost_m2m_group‘, backref=‘host_groups‘)#通过第三表连接与bindhost的多对多关系 def __repr__(self): return self.name class RemoteUser(Base): __tablename__ = ‘remote_user‘ __table_args__ = ( UniqueConstraint(‘auth_type‘, ‘username‘, ‘password‘, name=‘_user_passwd_uc‘),) # name是存在数据库里的联合唯一键,将这三者联系在一起 AuthTypes = [ #包含password和key两种认证方式 (‘ssh-password‘, ‘SSHH/Password‘), # 第一个真正存到数据库里的，第二个是从SQL alchemy对外显示的 (‘ssh-key‘, ‘SSH/KEY‘), ] id = Column(Integer, primary_key=True) auth_type = Column(ChoiceType(AuthTypes)) username = Column(String(32)) password = Column(String(128)) def __repr__(self): return self.username class BindHost(Base): __tablename__ = ‘bindhost‘ __table_args__ = (UniqueConstraint(‘host_id‘, ‘remoteuser_id‘, name=‘_host_remote_uc‘),) # 确保联合唯一，用host_remote_uc储存 id = Column(Integer, primary_key=True) host_id = Column(Integer, ForeignKey(‘host.id‘))#外键引入host.id remoteuser_id = Column(Integer, ForeignKey(‘remote_user.id‘))#外键引入remote_user.id host = relationship(‘Host‘, backref=‘bind_hosts‘)#跟Host绑定 remote_user = relationship(‘RemoteUser‘, backref=‘bind_hosts‘)#跟RemoteUser绑定 def __repr__(self): return ‘<%s -- %s>‘ % (self.host.ip,self.remote_user.username)#返回主机IP和远端用户名 class UserProfile(Base): __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_profiles‘)#通过第三表连接与bind_host的多对多关系 host_groups = relationship(‘HostGroup‘,secondary = ‘user_m2m_group‘,backref = ‘user_profiles‘)#通过第三表连接与group的多对多关系 def __repr__(self): return self.username # class AuditLog(Base): # pass if __name__ == ‘__main__‘: engine = create_engine(‘mysql+pymysql://root:123456@localhost:3306/baoleiji?charset=utf8‘) Base.metadata.create_all(engine)
创建完表结构，下面往表结构中创建参数，我现在这里把主干代码写出来，import的内容暂时略过，并会在注释中说明。
def syncdb(argvs):
print("Syncing DB....")
modules_v2.Base.metadata.create_all(engine) #创建所有表结构
def create_hosts(argvs):
‘‘‘
create hosts
:param argvs:
:return:
‘‘‘
if ‘-f‘ in argvs:
hosts_file = argvs[argvs.index("-f") +1 ]#要求用户指定一个文件名,-f类似位置指示符。
else:
print_err("invalid usage, should be:\ncreate_hosts -f <the new hosts file>",quit=True)
source = yaml_parser(hosts_file)#传文件名
if source:#如果能加载回来证明有数据
print(source)
for key,val in source.items():
print(key,val)
obj = modules_v2.Host(hostname=key,ip=val.get(‘ip‘), port=val.get(‘port‘) or 22)#定义一个数据库对象
session.add(obj)#加入数据库
session.commit()
def create_users(argvs):
‘‘‘
create hosts
:param argvs:
:return:
‘‘‘
if ‘-f‘ in argvs:
user_file = argvs[argvs.index("-f") +1 ]
else:
print_err("invalid usage, should be:\ncreateusers -f <the new users file>",quit=True)
source = yaml_parser(user_file)
if source:
for key,val in source.items():
print(key,val)
obj = modules_v2.UserProfile(username=key,password=val.get(‘password‘))#调用UserProfile函数创建数据
if val.get(‘groups‘):#如果有用户分组
groups = common_filters.group_filter(val)#进行分组的筛选，判断是否有该group
obj.groups = groups
if val.get(‘bind_hosts‘):#如果有bind_host数据
bind_hosts = common_filters.bind_hosts_filter(val)#进行bindhost的筛选，判断是否有该group
obj.bind_hosts = bind_hosts
print(obj)
session.add(obj)
session.commit()#执行添加指令
def create_remoteusers(argvs):
‘‘‘
create remoteusers
:param argvs:
:return:
‘‘‘
if ‘-f‘ in argvs:
remoteusers_file = argvs[argvs.index("-f") +1 ]
else:
print_err("invalid usage, should be:\ncreate_remoteusers -f <the new remoteusers file>",quit=True)
source = yaml_parser(remoteusers_file)
if source:
for key,val in source.items():
print(key,val)
obj = modules_v2.RemoteUser(username=val.get(‘username‘),auth_type=val.get(‘auth_type‘),password=val.get(‘password‘))
session.add(obj)
session.commit()
def create_groups(argvs):
‘‘‘
create groups
:param argvs:
:return:
‘‘‘
if ‘-f‘ in argvs:
group_file = argvs[argvs.index("-f") +1 ]
else:
print_err("invalid usage, should be:\ncreategroups -f <the new groups file>",quit=True)
source = yaml_parser(group_file)
if source:
for key,val in source.items():
print(key,val)
obj = modules_v2.HostGroup(name=key)
if val.get(‘bind_hosts‘):
bind_hosts = common_filters.bind_hosts_filter(val)
obj.bind_hosts = bind_hosts
if val.get(‘user_profiles‘):
user_profiles = common_filters.user_profiles_filter(val)
obj.user_profiles = user_profiles
session.add(obj)
session.commit()
def create_bindhosts(argvs):
‘‘‘
create bind hosts
:param argvs:
:return:
‘‘‘
if ‘-f‘ in argvs:#相当于一个位置符号，定位用户输入的命令
bindhosts_file = argvs[argvs.index("-f") +1 ]
else:
print_err("invalid usage, should be:\ncreate_hosts -f <the new bindhosts file>",quit=True)
source = yaml_parser(bindhosts_file)#这里是使用yml批量导入。
if source:
for key,val in source.items():#对yml中的数据进行逐行提取
#print(key,val)
host_obj = session.query(modules_v2.Host).filter(modules_v2.Host.hostname==val.get(‘hostname‘)).first()#查询主机
assert host_obj#断言是否有该主机，如果没有则报错。
for item in val[‘remote_users‘]:#循环远端的参数
print(item )
assert item.get(‘auth_type‘)#断言是否有认证类型，没有则返回错误
if item.get(‘auth_type‘) == ‘ssh-password‘:#如果认证类型为密码认证
remoteuser_obj = session.query(modules_v2.RemoteUser).filter(
modules_v2.RemoteUser.username==item.get(‘username‘),
modules_v2.RemoteUser.password==item.get(‘password‘)
).first()#判断用户密码是否正确
else:
remoteuser_obj = session.query(modules_v2.RemoteUser).filter(
modules_v2.RemoteUser.username==item.get(‘username‘),
modules_v2.RemoteUser.auth_type==item.get(‘auth_type‘),
).first()#判断认证类型和用户名是否符合ssh-key认证
if not remoteuser_obj:#如果远端数据为空
print_err("RemoteUser obj %s does not exist." % item,quit=True )#打印远端客户不存在
bindhost_obj = modules_v2.BindHost(host_id=host_obj.id,remoteuser_id=remoteuser_obj.id)#关联并添加远端id和主机id
session.add(bindhost_obj)
if source[key].get(‘groups‘):#跟Group关联
group_objs = session.query(modules_v2.HostGroup).filter(modules_v2.HostGroup.name.in_(source[key].get(‘groups‘))).all()#查询该分组是否存在
assert group_objs#断言避免数据为空
print(‘groups:‘, group_objs)#打印分组数据
bindhost_obj.host_groups = group_objs#返回对应的组id
#for user_profiles this host binds to
if source[key].get(‘user_profiles‘):#跟用户关联
userprofile_objs = session.query(modules_v2.UserProfile).filter(modules_v2.UserProfile.username.in_(source[key].get(‘user_profiles‘))).all()#查询该堡垒机账户是否存在
assert userprofile_objs#断言避免错误
print("userprofiles:",userprofile_objs)#打印账户
bindhost_obj.user_profilesuser_profiles = userprofile_objs
#print(bindhost_obj)
session.commit()
def auth():
‘‘‘
do the user login authentication
:return:
‘‘‘
count = 3#尝试次数
while count !=0:
username = input("\033[32;1mUsername:\033[0m").strip()#获取输入用户名
if len(username) ==0:continue
password = input("\033[32;1mPassword:\033[0m").strip()#获取用户密码
if len(password) ==0:continue
user_obj = session.query(modules_v2.UserProfile).filter(modules_v2.UserProfile.username==username,
modules_v2.UserProfile.password==password).first()#对用户名进行匹配
if user_obj:
return user_obj#如果符合数据库中数据，则返回
else:
count -= 1#尝试次数减一
print("wrong username or password, you have %s more chances." %(3-count))#如果不符合，则打印错误，并提示尝试次数
else:
print_err("too many attempts.")#尝试次数太多跳出循环
def welcome_msg(user):
WELCOME_MSG = ‘‘‘\033[32;1m
------------- Welcome [%s] login Baoleiji -------------
\033[0m‘‘‘% user.username
print(WELCOME_MSG)
def start_session(argvs):
print(‘going to start sesssion ‘)
user = auth()#调用认证
if user:#如果有返回即认证成功
welcome_msg(user)#调用welcome
print(user.bind_hosts)#打印bindhost列表
print(user.host_groups)#打印host group列表
exit_flag = False#设置退出标志
while not exit_flag:
if user.bind_hosts:#如果有bindhost则打印
print(‘\033[32;1mz.\tungroupped hosts (%s)\033[0m‘ %len(user.bind_hosts) )#返回未分组的主机数
for index,host_group in enumerate(user.host_groups):#循环打印所在的用户组列表
print(‘\033[32;1m%s.\t%s (%s)\033[0m‘ %(index,host_group.name, len(host_group.bind_hosts)) )
choice = input("[%s]:" % user.username).strip()
if len(choice) == 0:continue
if choice == ‘z‘:
print("------ Group: ungroupped hosts ------" )
for index,bind_host in enumerate(user.bind_hosts):#打印为绑定用户组的详细数据
print(" %s.\t%s@%s(%s)"%(index,
bind_host.remote_user.username,
bind_host.host.hostname,
bind_host.host.ip,
))
print("----------- END -----------" )
elif choice.isdigit():
choice = int(choice)
if choice < len(user.host_groups):#检查数据有效性
print("------ Group: %s ------" % user.host_groups[choice].name )
for index,bind_host in enumerate(user.host_groups[choice].bind_hosts):#打印绑定的用户组详细信息
print(" %s.\t%s@%s(%s)"%(index,
bind_host.remote_user.username,
bind_host.host.hostname,
bind_host.host.ip,
))
print("----------- END -----------" )
#host selection
while not exit_flag:#如果没有退出的情况下
user_option = input("[(b)back, (q)quit, select host to login]:").strip()
if len(user_option)==0:continue#继续循环
if user_option == ‘b‘:break#跳出循环
if user_option == ‘q‘:
exit_flag=True#退出程序
if user_option.isdigit():
#进行第二层判断，即选择主机
user_option = int(user_option)
# 检查数据有效性
if user_option < len(user.host_groups[choice].bind_hosts) :#检查数据有效性
print(‘host:‘,user.host_groups[choice].bind_hosts[user_option])
print(‘audit log:‘,user.host_groups[choice].bind_hosts[user_option].audit_logs)#记录日志
#开始登陆程序
ssh_login.ssh_login(user,
user.host_groups[choice].bind_hosts[user_option],
session,
log_recording)
else:
print("no this option..")
def log_recording(user_obj,bind_host_obj,logs):
‘‘‘
flush user operations on remote host into DB
:param user_obj:
:param bind_host_obj:
:param logs: list format [logItem1,logItem2,...]
:return:
‘‘‘
print("\033[41;1m--logs:\033[0m",logs)
session.add_all(logs)
session.commit()
通过以上代码就简单的搭建了一个堡垒机账户，主要有一下几个缺点：
1 安全性差：密码直接以明文的形式存在数据库中
2 重复的多对多关系（比如UserProfile分别与group和bindhost进行多对多映射，导致了重复且未完全实现未分组host的筛选功能，待完善
3 没有修改模块，一旦创建只能手工在数据库中进行修改，效率较低，待完善
原文：http://www.cnblogs.com/BigJ/p/7490666.html