我的python之路【第十章】堡垒机与数据库操作
在实现堡垒机之前，我们首先要了解paramiko模块
paramiko模块的作用就是基于ssh来连接服务器并执行命令操作的。用于连接远程服务器并执行基本命令大大
1.基于用户名密码连接-远程执行命令
# 用于连接远程服务器并执行基本命令 import paramiko # 创建SSH对象 ssh = paramiko.SSHClient() # 允许连接不在know_hosts文件中的主机 ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy()) # 连接服务器 ssh.connect(hostname='192.168.12.78', port=22, username='root', password='123456') # 执行命令 stdin, stdout, stderr = ssh.exec_command('df') # 获取命令结果 result = stdout.read() print(result.decode()) # 关闭连接 ssh.close() ''' #SSHClient封装Transport import paramiko transport = paramiko.Transport(('hostname', 22)) transport.connect(username='wupeiqi', password='123') ssh = paramiko.SSHClient() ssh._transport = transport stdin, stdout, stderr = ssh.exec_command('df') print stdout.read() transport.close() '''
2.基于用户名密钥连接-远程执行命令
#!/usr/bin/env python # -*- coding:utf-8 -*- #liuhao import paramiko #需要将key保存在一个独立的文件中 private_key = paramiko.RSAKey.from_private_key_file('/home/auto/.ssh/id_rsa') # 创建SSH对象 ssh = paramiko.SSHClient() # 允许连接不在know_hosts文件中的主机 ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy()) # 连接服务器 ssh.connect(hostname='192.168.12.78', port=22, username='root', pkey=private_key) # 执行命令 stdin, stdout, stderr = ssh.exec_command('df') # 获取命令结果 result = stdout.read() # 关闭连接 ssh.close() ''' #SSHClient封装Transport import paramiko private_key = paramiko.RSAKey.from_private_key_file('/home/auto/.ssh/id_rsa') transport = paramiko.Transport(('hostname', 22)) transport.connect(username='wupeiqi', pkey=private_key) ssh = paramiko.SSHClient() ssh._transport = transport stdin, stdout, stderr = ssh.exec_command('df') transport.close() SSHClient 封装 Transport '''
3.基于私钥字符串连接-远程执行命令
import paramiko from io import StringIO key_str = """-----BEGIN RSA PRIVATE KEY----- MIIEoAIBAAKCAQEAofI560WqI22It/D8PfTjnVrZLIjdhlExExAnfL9ZhQEZknew 8gVeThaZKwx/tqa8DIEGbtoYAHr6jBg76IrsPS1sljpWDTlIPt+40eaUZizFNtD9 /k4V6swa45Bvdrk3QWco8EKQyUiNZ8kHmW+0xl5i+cB/5yUX0QujeZN9ooHSsCIo 0GERZT+aJR6PlPaH7R/wLuAMLVRegJIzCu7jRubKBxepINo4AsvWzhI86cMWsS/o A1brFfnOFeohbXlNuiRpjzbbyXV5OP23BbfnvODpWjnTz+LMaAp5CPmKigyXNVO+ xBCVcobxdV6pKJgoBIeKGOcU6cMlhDjvAZYeGwIBIwKCAQBly3TdBze+f11Aa5c8 4xK6riIb/kIZ6eRVIBjSEeB/fQjCdx7EA1/ZUAiBdZIbCbgH3BKsFA8WPqwdi5Ne +Dy4vXd0Xy7Ge8cC8wZ1TxtzbJk4Zhv+70D5/KMv9GNR8MOldAsarX+Udr9IjP12 Y3jxt6vtca97WSTpzM0n2Q0kUJn7m14N7TYqikqlzLmfM7kwOximj8zFDwpFFtV3 hTQDAYTPudsE8YC9lTgcJA7uj0O66hPkT90CZu3F+A3g/C8kMKudBS9Q2NDWl4TF uLk8m+BqvdKf2pIcuxbWMrS8ozaMjbDgKm3qEsazFj7el5oOh+AuzTFrkbSqRio2 UI/7AoGBANek+QKfGorwOzJRrsgHXTLJzSx3iOULtrV0NngrA7ia7viHJm3rM8n+ TxnEyPTgvVQw6/W0AX7DtItVEaowZP0qc4x4Iw8RIyMNuJgsAdxwTxboMGQv9EZt MSyDZ5SVU4DILHuG4BA/G7ty3QRhC/mqJTap3tvaSpUy0Ev//SdfAoGBAMBAsflJ qjvrhXy0ZEMnmH/5gGJRQ9W5kV6HK8Rb9FftAQnYYNMiJjh5yYat+KSkxL95z4zY eUbluMh6QjKc93ntY276XQFx4iEy9voqylj3yGHIOIlEhykqO0sZhxw8+z+k0XtM 7QTh8D+3DMxlbHNxz/pqZLBBbdmfm+t8pG7FAoGAaL3YCJZr+ldtNbKlWdew7Mhq +FdRHsss28q7XvBSQ7j3uorJhdikIEhSTlhEWa7837/9lJlCjgdJEHnGwGCmFJD2 S4rPMzQ1o0/OsE/jpZWiwfu/u6JDcqoQkfaup0E3L+wy3Om9SbhAq4DvAiCCKM78 Id1639BtXmkjWB1AcjUCgYAxb7i8cgcz+rvs3e3lYfQDpo66Midi1/IuQAPwqe5f wJnd9c/BQ0uoH1EUANmJbDKXovrbExffB98O+t3SgCJhLmoGmCaLV8xu79kbwdxC w19pmeKm9F1FGXz2DeDqAQ1oIxFLl3Al07LOjifcyZ+LZmiuG1u4S1bC7oct7NnT VwKBgFuagfheJiwLkqUMsm6Cwi2+UpNqTC+ZjQ7G0nZtovafC6X0i+opkpMFbJzf n3Yj+7sLm8sCx3SUhYLEmlhDPuPZflyLuOyz927veXpJGHd9TdaktZhyPX/BdkQh LRxssxuDBvd8eJagIdCNfDN7TKRkHulwZdCcEP8wb3MPO7oR -----END RSA PRIVATE KEY-----""" ssh = paramiko.SSHClient() private_key = paramiko.RSAKey(file_obj=StringIO(key_str)) # paramiko.RSAKey.from_private_key() transport = paramiko.Transport(('172.16.50.192', 22)) #注意这里的是pkey transport.connect(username='root', pkey=private_key) ssh._transport = transport stdin, stdout, stderr = ssh.exec_command('df') result = stdout.read().decode() ssh.close() print(result)
4.基于用户名密码上传下载
import paramiko transport = paramiko.Transport(('hostname', 22)) transport.connect(username='wupeiqi', password='123') sftp = paramiko.SFTPClient.from_transport(transport) # 将location.py 上传至服务器 /tmp/test.py sftp.put('/tmp/location.py', '/tmp/test.py') # 将remove_path 下载到本地 local_path sftp.get('remove_path', 'local_path') transport.close()
5.基于用户名秘钥上传下载
import paramiko private_key = paramiko.RSAKey.from_private_key_file('/home/auto/.ssh/id_rsa') transport = paramiko.Transport(('hostname', 22)) transport.connect(username='wupeiqi', pkey=private_key) sftp = paramiko.SFTPClient.from_transport(transport) # 将location.py 上传至服务器 /tmp/test.py sftp.put('/tmp/location.py', '/tmp/test.py') # 将remove_path 下载到本地 local_path sftp.get('remove_path', 'local_path') transport.close()
python通过pymysql操作数据
import pymysql # 创建连接 conn = pymysql.connect(host='172.16.50.192', port=3306, user='root', passwd='123456', db='db') # 创建游标 cursor = conn.cursor() username = input('请输入用户名：') pwd = input('请输入密码：') # cursor.execute 执行SQL，并返回受影响行数 effect_row = cursor.execute("select * from userinfo where name=%s and password = %s", (username,pwd,)) print(effect_row) # root or 1==1 -- # adfasdf 会出现sql注入 # sql = "select * from userinfo where username=%s and pwd = %s" %(username,pwd,) # select * from userinfo where username=root or 1==1 -- and pwd = %s # effect_row = cursor.execute(sql) # 提交，不然无法保存新建或者修改的数据 conn.commit() #获取返回信息 ret=cursor.fetchall() print(ret) # 关闭游标 cursor.close() # 关闭连接 conn.close()
通过以上的内容，我们可以简单的做一个堡垒机的功能了
实现功能: - 用户登录 （用户表中做验证，id） 通过连接数据库查询user表，实现用户登录验证 - 显示当前用户管理的所有主机列表（） 通过输入主机id进行批量管理主机, - 选中一批机器远程执行命令： 通过线程池批量执行命令： 根据host表中pwd_type类型区分验证方式：用户名密码验证 和 用户名私钥验证
部分代码展示
1.首先我们要创建一个数据库，这里我使用的是mysql数据库
/* Navicat MySQL Data Transfer Source Server : 172.16.50.192 Source Server Version : 50532 Source Host : 192.168.0.106:3306 Source Database : db Target Server Type : MYSQL Target Server Version : 50532 File Encoding : 65001 Date: 2017-03-31 00:05:49 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for host -- ---------------------------- DROP TABLE IF EXISTS `host`; CREATE TABLE `host` ( `host_id` int(11) NOT NULL AUTO_INCREMENT, `ip` char(32) DEFAULT NULL, `port` char(32) DEFAULT NULL, `username` char(32) DEFAULT NULL, `password` text, `pwd_type` char(32) DEFAULT NULL, PRIMARY KEY (`host_id`), UNIQUE KEY `host` (`ip`) USING HASH ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of host -- ---------------------------- INSERT INTO `host` VALUES ('1', '192.168.0.102', '22', 'root', '123456', 'pwd'); INSERT INTO `host` VALUES ('2', '192.168.0.103', '22', 'root', '-----BEGIN RSA PRIVATE KEY-----\r\nMIIEoAIBAAKCAQEAofI560WqI22It/D8PfTjnVrZLIjdhlExExAnfL9ZhQEZknew\r\n8gVeThaZKwx/tqa8DIEGbtoYAHr6jBg76IrsPS1sljpWDTlIPt+40eaUZizFNtD9\r\n/k4V6swa45Bvdrk3QWco8EKQyUiNZ8kHmW+0xl5i+cB/5yUX0QujeZN9ooHSsCIo\r\n0GERZT+aJR6PlPaH7R/wLuAMLVRegJIzCu7jRubKBxepINo4AsvWzhI86cMWsS/o\r\nA1brFfnOFeohbXlNuiRpjzbbyXV5OP23BbfnvODpWjnTz+LMaAp5CPmKigyXNVO+\r\nxBCVcobxdV6pKJgoBIeKGOcU6cMlhDjvAZYeGwIBIwKCAQBly3TdBze+f11Aa5c8\r\n4xK6riIb/kIZ6eRVIBjSEeB/fQjCdx7EA1/ZUAiBdZIbCbgH3BKsFA8WPqwdi5Ne\r\n+Dy4vXd0Xy7Ge8cC8wZ1TxtzbJk4Zhv+70D5/KMv9GNR8MOldAsarX+Udr9IjP12\r\nY3jxt6vtca97WSTpzM0n2Q0kUJn7m14N7TYqikqlzLmfM7kwOximj8zFDwpFFtV3\r\nhTQDAYTPudsE8YC9lTgcJA7uj0O66hPkT90CZu3F+A3g/C8kMKudBS9Q2NDWl4TF\r\nuLk8m+BqvdKf2pIcuxbWMrS8ozaMjbDgKm3qEsazFj7el5oOh+AuzTFrkbSqRio2\r\nUI/7AoGBANek+QKfGorwOzJRrsgHXTLJzSx3iOULtrV0NngrA7ia7viHJm3rM8n+\r\nTxnEyPTgvVQw6/W0AX7DtItVEaowZP0qc4x4Iw8RIyMNuJgsAdxwTxboMGQv9EZt\r\nMSyDZ5SVU4DILHuG4BA/G7ty3QRhC/mqJTap3tvaSpUy0Ev//SdfAoGBAMBAsflJ\r\nqjvrhXy0ZEMnmH/5gGJRQ9W5kV6HK8Rb9FftAQnYYNMiJjh5yYat+KSkxL95z4zY\r\neUbluMh6QjKc93ntY276XQFx4iEy9voqylj3yGHIOIlEhykqO0sZhxw8+z+k0XtM\r\n7QTh8D+3DMxlbHNxz/pqZLBBbdmfm+t8pG7FAoGAaL3YCJZr+ldtNbKlWdew7Mhq\r\n+FdRHsss28q7XvBSQ7j3uorJhdikIEhSTlhEWa7837/9lJlCjgdJEHnGwGCmFJD2\r\nS4rPMzQ1o0/OsE/jpZWiwfu/u6JDcqoQkfaup0E3L+wy3Om9SbhAq4DvAiCCKM78\r\nId1639BtXmkjWB1AcjUCgYAxb7i8cgcz+rvs3e3lYfQDpo66Midi1/IuQAPwqe5f\r\nwJnd9c/BQ0uoH1EUANmJbDKXovrbExffB98O+t3SgCJhLmoGmCaLV8xu79kbwdxC\r\nw19pmeKm9F1FGXz2DeDqAQ1oIxFLl3Al07LOjifcyZ+LZmiuG1u4S1bC7oct7NnT\r\nVwKBgFuagfheJiwLkqUMsm6Cwi2+UpNqTC+ZjQ7G0nZtovafC6X0i+opkpMFbJzf\r\nn3Yj+7sLm8sCx3SUhYLEmlhDPuPZflyLuOyz927veXpJGHd9TdaktZhyPX/BdkQh\r\nLRxssxuDBvd8eJagIdCNfDN7TKRkHulwZdCcEP8wb3MPO7oR\r\n-----END RSA PRIVATE KEY-----', 'private_key'); -- ---------------------------- -- Table structure for user_to_host -- ---------------------------- DROP TABLE IF EXISTS `user_to_host`; CREATE TABLE `user_to_host` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_to_id` int(11) NOT NULL, `host_to_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `host_id_wj` (`host_to_id`), KEY `user_id_wj` (`user_to_id`), CONSTRAINT `host_id_wj` FOREIGN KEY (`host_to_id`) REFERENCES `host` (`host_id`), CONSTRAINT `user_id_wj` FOREIGN KEY (`user_to_id`) REFERENCES `userinfo` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of user_to_host -- ---------------------------- INSERT INTO `user_to_host` VALUES ('1', '1', '1'); INSERT INTO `user_to_host` VALUES ('2', '1', '2'); INSERT INTO `user_to_host` VALUES ('3', '2', '1'); -- ---------------------------- -- Table structure for user_type -- ---------------------------- DROP TABLE IF EXISTS `user_type`; CREATE TABLE `user_type` ( `type_id` int(2) NOT NULL AUTO_INCREMENT, `type` varchar(32) DEFAULT NULL, PRIMARY KEY (`type_id`), KEY `type` (`type`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of user_type -- ---------------------------- INSERT INTO `user_type` VALUES ('2', '普通用户'); INSERT INTO `user_type` VALUES ('1', '管理员'); -- ---------------------------- -- Table structure for userinfo -- ---------------------------- DROP TABLE IF EXISTS `userinfo`; CREATE TABLE `userinfo` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `password` char(20) NOT NULL, `user_type` int(255) DEFAULT NULL, PRIMARY KEY (`user_id`), KEY `user_type_wj` (`user_type`), CONSTRAINT `user_type_wj` FOREIGN KEY (`user_type`) REFERENCES `user_type` (`type_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of userinfo -- ---------------------------- INSERT INTO `userinfo` VALUES ('1', 'liuhao', 'liuhao', '2'); INSERT INTO `userinfo` VALUES ('2', 'alex', 'alex', '2'); INSERT INTO `userinfo` VALUES ('3', 'administrator', '123456', '1');
堡垒机程序目录结构
/core/main.py
2.程序主体/core/main.py
#!/usr/bin/env python # -*- coding:utf-8 -*- #liuhao from concurrent.futures import ThreadPoolExecutor import paramiko,pymysql from conf.setting import * from io import StringIO import time import threading # print(config['host']) class myconn(object): def __init__(self): self.user_id=False self.all_host=False self.pool = ThreadPoolExecutor(5) # conn = pymysql.connect(host='172.16.50.192', port=3306, user='root', passwd='123456', db='db') self.conn=pymysql.connect(host=config['host'],port=int(config['port']),user=config['user'],passwd=config['passwd'],db=config['db']) self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor) def auth(self): self.username = input('请输入用户名：') pwd = input('请输入密码：') # cursor.execute 执行SQL，并返回受影响行数 effect_row = self.cursor.execute("select * from userinfo where name=%s and password = %s", (self.username, pwd,)) if effect_row >0: # 提交，不然无法保存新建或者修改的数据 self.conn.commit() # 获取返回信息 ret = self.cursor.fetchall() self.user_id=ret[0]['user_id'] print(ret) # 关闭游标 # self.cursor.close() return self.user_id def show_host(self): if self.user_id: row=self.cursor.execute("select host_id,ip,port,username,pwd_type from user_to_host left join host on user_to_host.host_to_id = host.host_id where user_to_id =%s",(int(self.user_id))) if row >0: self.all_host=self.cursor.fetchall() print('用户:%s 可以管理下列主机:'%self.username) for line in self.all_host: print(''' ID:{host_id} IP:{ip} Port:{port} pwd_type:{pwd_type} '''.format(host_id=line['host_id'],ip=line['ip'],port=line['port'],pwd_type=line['pwd_type'])) else: print('用户:%s没有可以管理的主机,请联系管理员！') print('\t共计%s台机器'%len(self.all_host)) def main(self): self.user_id=self.auth() if self.user_id: self.show_host() self.command() # def show_res(self,res): # result=res.result() # if result: # print('\033[32;1m%s\033[0m' % result.decode()) # self.ssh.close() def excute(self,data): #time.sleep(2) cmd_str, id_num=data[0],data[1] cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.execute("select ip,port,username,pwd_type,password from host where host_id=%s", (int(id_num))) host_data = cursor.fetchall() #print(host_data) # print('正在执行：',id_num) for host in host_data: ssh = paramiko.SSHClient() if host['pwd_type'] == 'pwd': # print('使用密码') ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy()) ssh.connect(hostname=host['ip'], port=int(host['port']), username=host['username'], password=host['password']) stdin, stdout, stderr = ssh.exec_command(cmd_str) result = stdout.read() ssh.close() elif host['pwd_type']=='private_key': # print('使用私钥') private_key = paramiko.RSAKey(file_obj=StringIO(host['password'])) transport = paramiko.Transport((host['ip'], int(host['port']))) transport.connect(username=host['username'], pkey=private_key) ssh._transport = transport stdin, stdout, stderr = ssh.exec_command(cmd_str) result = stdout.read() ssh.close() print('\n\033[34;1m主机:%s 执行结果：\033[0m' % host['ip']) if result: print('\033[32;1m%s\033[0m' % result.decode()) # return result def command(self): while True: id_str=input('请输入想要操作的主机id,[批量操作,分割]：').strip(',') if not id_str:continue id_list=id_str.split(',') #print(self.all_host) for id_tt in id_list: flag = 0 # if not isinstance(id, int):exit() for host in self.all_host: try: if int(id_tt) == host['host_id']: flag+=1 except ValueError as e:pass if flag==0: print('\033[31;1m ID 错误:%s\033[0m'%id) break if flag!=0: cmd_str=input('请输入要操作的命令：') for id_num in id_list: print('开始执行:',id_num) self.pool.submit(self.excute,(cmd_str,id_num)) # print('\033[32;1m%s\033[0m' % fu.result().decode()) def run(): a=myconn() a.main()
/conf/setting.py
3.配置文件
#!/usr/bin/env python # -*- coding:utf-8 -*- #liuhao config={ 'host':'192.168.0.106', 'port':'3306', 'user':'root', 'passwd':'123456', 'db':'db', }
/bin/run.py
4.程序启动
#!/usr/bin/env python # -*- coding:utf-8 -*- #liuhao config={ 'host':'192.168.0.106', 'port':'3306', 'user':'root', 'passwd':'123456', 'db':'db', }
