def get_policies(sql_filter, sql_order, sql_limit):
dbtp = get_db().table_prefix
s = SQL(get_db())
s.select_from('audit_policy', ['id', 'rank', 'name', 'desc', 'state'],
alt_name='p')
str_where = ''
_where = list()
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append(
'(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.
format(filter=sql_filter[k]))
if k == 'state':
_where.append('p.state={}'.format(sql_filter[k]))
else:
log.e('unknown filter field: {}\n'.format(k))
return TPE_PARAM, s.total_count, 0, s.recorder
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
s.order_by('p.rank', True)
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
def add_members(handler, policy_id, policy_type, ref_type, members):
# step 1: select exists rid.
s = SQL(get_db())
s.select_from('ops_auz', ['rid'], alt_name='p')
_where = list()
_where.append('p.policy_id={}'.format(policy_id))
_where.append('p.type={}'.format(policy_type))
_where.append('p.rtype={}'.format(ref_type))
s.where('( {} )'.format(' AND '.join(_where)))
err = s.query()
if err != TPE_OK:
return err
exists_ids = [r['rid'] for r in s.recorder]
operator = handler.get_current_user()
db = get_db()
_time_now = tp_timestamp_sec()
sql = []
for m in members:
if m['id'] in exists_ids:
continue
sql_s = 'INSERT INTO `{tp}ops_auz` (`policy_id`,`type`,`rtype`,`rid`,`name`,`creator_id`,`create_time`) VALUES ' \
'({ph}, {ph}, {ph}, {ph}, {ph}, {ph}, {ph});' \
''.format(tp=db.table_prefix, ph=db.place_holder)
sql_v = (policy_id, policy_type, ref_type, m['id'], m['name'],
operator['id'], _time_now)
sql.append({'s': sql_s, 'v': sql_v})
if db.transaction(sql):
# return TPE_OK
return policy.rebuild_ops_auz_map()
else:
return TPE_DATABASE
def get_policies(sql_filter, sql_order, sql_limit):
dbtp = get_db().table_prefix
s = SQL(get_db())
s.select_from('audit_policy', ['id', 'rank', 'name', 'desc', 'state'], alt_name='p')
str_where = ''
_where = list()
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append('(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.format(filter=sql_filter[k]))
if k == 'state':
_where.append('p.state={}'.format(sql_filter[k]))
else:
log.e('unknown filter field: {}\n'.format(k))
return TPE_PARAM, s.total_count, 0, s.recorder
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
s.order_by('p.rank', True)
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
def get(self):
ret = self.check_privilege(TP_PRIVILEGE_SYS_CONFIG)
if ret != TPE_OK:
return
if get_db().need_create:
cfg.reload()
_db = get_db()
_db.init()
db = {'type': _db.db_type}
if _db.db_type == _db.DB_TYPE_SQLITE:
db['sqlite_file'] = _db.sqlite_file
elif _db.db_type == _db.DB_TYPE_MYSQL:
db['mysql_host'] = _db.mysql_host
db['mysql_port'] = _db.mysql_port
db['mysql_user'] = _db.mysql_user
db['mysql_db'] = _db.mysql_db
param = {'db': db}
self.render('maintenance/install.mako',
page_param=json.dumps(param))
elif get_db().need_upgrade:
return self.redirect('/maintenance/upgrade')
else:
self.redirect('/')
def get(self):
ret = self.check_privilege(TP_PRIVILEGE_SYS_CONFIG)
if ret != TPE_OK:
return
if get_db().need_create:
cfg.reload()
_db = get_db()
_db.init()
db = {'type': _db.db_type}
if _db.db_type == _db.DB_TYPE_SQLITE:
db['sqlite_file'] = _db.sqlite_file
elif _db.db_type == _db.DB_TYPE_MYSQL:
db['mysql_host'] = _db.mysql_host
db['mysql_port'] = _db.mysql_port
db['mysql_user'] = _db.mysql_user
db['mysql_db'] = _db.mysql_db
param = {'db': db}
self.render('maintenance/install.mako', page_param=json.dumps(param))
elif get_db().need_upgrade:
return self.redirect('/maintenance/upgrade')
else:
self.redirect('/')
def get_account_info(acc_id):
s = SQL(get_db())
# s.select_from('acc', ['id', 'password', 'pri_key', 'state', 'host_ip', 'router_ip', 'router_port', 'protocol_type', 'protocol_port', 'auth_type', 'username'], alt_name='a')
s.select_from('acc', [
'id', 'password', 'pri_key', 'state', 'host_id', 'protocol_type',
'protocol_port', 'auth_type', 'username', 'username_prompt',
'password_prompt'
],
alt_name='a')
s.where('a.id={}'.format(acc_id))
err = s.query()
if err != TPE_OK:
return err, None
if len(s.recorder) != 1:
return TPE_DATABASE, None
sh = SQL(get_db())
sh.select_from('host',
['id', 'name', 'ip', 'router_ip', 'router_port', 'state'],
alt_name='h')
sh.where('h.id={}'.format(s.recorder[0].host_id))
err = sh.query()
if err != TPE_OK:
return err, None
if len(s.recorder) != 1:
return TPE_DATABASE, None
s.recorder[0]['_host'] = sh.recorder[0]
return TPE_OK, s.recorder[0]
def get(self):
ret = self.check_privilege(TP_PRIVILEGE_SYS_CONFIG)
if ret != TPE_OK:
return
if get_db().need_create:
return self.redirect('/maintenance/install')
elif get_db().need_upgrade:
self.render('maintenance/upgrade.mako')
else:
self.redirect('/')
def get(self):
ret = self.check_privilege(TP_PRIVILEGE_SYS_CONFIG)
if ret != TPE_OK:
return
if get_db().need_create:
return self.redirect('/maintenance/install')
elif get_db().need_upgrade:
self.render('maintenance/upgrade.mako')
else:
self.redirect('/')
def get(self):
from app.base.db import get_db
if tp_cfg().app_mode == APP_MODE_MAINTENANCE and get_db().need_create:
_user = {
'id': 0,
'username': '******',
'surname': '系统维护-安装',
'role_id': 0,
'role': '',
'privilege': TP_PRIVILEGE_SYS_CONFIG,
'_is_login': True
}
self.set_session('user', _user)
self.redirect('/maintenance/install')
return
if tp_cfg().app_mode == APP_MODE_MAINTENANCE and get_db().need_upgrade:
_user = {
'id': 0,
'username': '******',
'surname': '系统维护-升级',
'role_id': 0,
'role': '',
'privilege': TP_PRIVILEGE_SYS_CONFIG,
'_is_login': True
}
self.set_session('user', _user)
self.redirect('/maintenance/upgrade')
return
_user = self.get_current_user()
_ref = quote(self.get_argument('ref', '/'))
if _user['_is_login']:
self.redirect(_ref)
return
if _user['id'] == 0:
username = self.get_cookie('username')
if username is None:
username = ''
else:
username = _user['username']
default_auth_type = tp_cfg().sys.login.auth
param = {
'ref': _ref,
'username': username,
'default_auth': default_auth_type
}
self.render('auth/login.mako', page_param=json.dumps(param))
def get_host_groups_for_user(user_id, user_privilege):
# get all host-groups for current logged in user.
db = get_db()
# step 0. return all host-groups if user have all host-group access privilege
if (user_privilege & (TP_PRIVILEGE_ASSET_CREATE | TP_PRIVILEGE_ASSET_DELETE
| TP_PRIVILEGE_ASSET_GROUP)) != 0:
s = SQL(get_db())
s.select_from('group', ['id', 'name'], alt_name='g')
s.where('g.type={}'.format(TP_GROUP_HOST))
s.order_by('g.name')
err = s.query()
return err, s.recorder
# step 1. get all hosts which could be access by this user.
sql = 'SELECT `h_id` FROM `{dbtp}ops_map` WHERE `u_id`={dbph} GROUP BY `h_id`;'.format(
dbtp=db.table_prefix, dbph=db.place_holder)
db_ret = db.query(sql, (user_id, ))
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS, None
hosts = []
for db_item in db_ret:
hosts.append(str(db_item[0]))
if len(hosts) == 0:
return TPE_NOT_EXISTS, None
# step 2. get groups which include those hosts.
sql = 'SELECT `gid` FROM `{dbtp}group_map` WHERE (`type`={gtype} AND `mid` IN ({hids})) GROUP BY `gid`;'.format(
dbtp=db.table_prefix, gtype=TP_GROUP_HOST, hids=','.join(hosts))
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS, None
groups = []
for db_item in db_ret:
groups.append(str(db_item[0]))
# step 3. get those groups id and name.
s = SQL(get_db())
s.select_from('group', ['id', 'name'], alt_name='g')
s.where('g.id IN ({})'.format(','.join(groups)))
s.order_by('g.name')
err = s.query()
return err, s.recorder
def post(self):
ret = self.check_privilege(TP_PRIVILEGE_SYS_CONFIG)
if ret != TPE_OK:
return
args = self.get_argument('args', None)
if args is not None:
try:
args = json.loads(args)
except:
return self.write_json(TPE_JSON_FORMAT)
else:
return self.write_json(TPE_PARAM)
if 'cmd' not in args:
return self.write_json(TPE_PARAM)
cmd = args['cmd']
# if cmd == 'enter_maintenance_mode':
# cfg.app_mode = APP_MODE_MAINTENANCE
# return self.write_json(0)
if cmd == 'install':
if not get_db().need_create:
return self.write_json(TPE_FAILED, '数据库已存在，无需创建！')
if 'sysadmin' not in args or 'email' not in args or 'password' not in args:
return self.write_json(TPE_PARAM)
task_id = thread_mgr.create_db(args['sysadmin'], args['email'],
args['password'])
return self.write_json(0, data={"task_id": task_id})
if cmd == 'upgrade_db':
if not get_db().need_upgrade:
return self.write_json(-1, '无需升级')
task_id = thread_mgr.upgrade_db()
return self.write_json(0, data={"task_id": task_id})
elif cmd == 'get_task_ret':
r = thread_mgr.get_task(args['tid'])
if r is None:
return self.write_json(0, data={'running': False, 'steps': []})
else:
return self.write_json(0, data=r)
else:
self.write_json(-1, '未知命令 `{}`！'.format(cmd))
def update_accounts_state(handler, host_id, acc_ids, state):
db = get_db()
acc_ids = ','.join([str(uid) for uid in acc_ids])
# 1. 判断是否存在
sql = 'SELECT id FROM {}acc WHERE host_id={host_id} AND id IN ({ids});'.format(
db.table_prefix, host_id=host_id, ids=acc_ids)
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS
sql_list = list()
sql = 'UPDATE `{tp}acc` SET `state`={ph} WHERE `id` IN ({ids});' \
''.format(tp=db.table_prefix, ph=db.place_holder, ids=acc_ids)
sql_list.append({'s': sql, 'v': (state, )})
# sync to update the ops-audit table.
sql = 'UPDATE `{tp}ops_auz` SET `state`={ph} WHERE `rtype`={ph} AND `rid` IN ({rid});' \
''.format(tp=db.table_prefix, ph=db.place_holder, rid=acc_ids)
sql_list.append({'s': sql, 'v': (state, TP_ACCOUNT)})
sql = 'UPDATE `{tp}ops_map` SET `a_state`={ph} WHERE `a_id` IN ({acc_id});' \
''.format(tp=db.table_prefix, ph=db.place_holder, acc_id=acc_ids)
sql_list.append({'s': sql, 'v': (state, )})
if db.transaction(sql_list):
return TPE_OK
else:
return TPE_DATABASE
def session_begin(sid, user_id, host_id, acc_id, user_username, acc_username,
host_ip, conn_ip, conn_port, client_ip, auth_type,
protocol_type, protocol_sub_type):
db = get_db()
sql = 'SELECT surname FROM `{tp}user` WHERE id={ph};'.format(
tp=db.table_prefix, ph=db.place_holder)
db_ret = db.query(sql, (user_id, ))
if db_ret is None or len(db_ret) == 0:
user_surname = user_username
else:
user_surname = db_ret[0][0]
sql = 'INSERT INTO `{}record` (sid,user_id,host_id,acc_id,state,user_username,user_surname,host_ip,conn_ip,conn_port,client_ip,acc_username,auth_type,protocol_type,protocol_sub_type,time_begin,time_end) ' \
'VALUES ("{sid}",{user_id},{host_id},{acc_id},0,"{user_username}","{user_surname}","{host_ip}","{conn_ip}",{conn_port},"{client_ip}","{acc_username}",{auth_type},{protocol_type},{protocol_sub_type},{time_begin},0)' \
';'.format(db.table_prefix,
sid=sid, user_id=user_id, host_id=host_id, acc_id=acc_id, user_username=user_username, user_surname=user_surname, host_ip=host_ip, conn_ip=conn_ip, conn_port=conn_port,
client_ip=client_ip, acc_username=acc_username, auth_type=auth_type, protocol_type=protocol_type, protocol_sub_type=protocol_sub_type,
time_begin=tp_timestamp_sec())
ret = db.exec(sql)
if not ret:
return TPE_DATABASE, 0
record_id = db.last_insert_id()
if record_id == -1:
return TPE_DATABASE, 0
else:
return TPE_OK, record_id
def delete_log(log_list):
try:
where = list()
for item in log_list:
where.append(' `id`={}'.format(item))
db = get_db()
sql = 'DELETE FROM `{}log` WHERE{};'.format(db.table_prefix,
' OR'.join(where))
ret = db.exec(sql)
if not ret:
return False
# TODO: 此处应该通过json-rpc接口通知core服务来删除重放文件。
for item in log_list:
log_id = int(item)
try:
record_path = os.path.join(tp_cfg().core.replay_path, 'ssh',
'{:06d}'.format(log_id))
if os.path.exists(record_path):
shutil.rmtree(record_path)
record_path = os.path.join(tp_cfg().core.replay_path, 'rdp',
'{:06d}'.format(log_id))
if os.path.exists(record_path):
shutil.rmtree(record_path)
except Exception:
pass
return True
except:
return False
def add_host(handler, args):
"""
添加一个远程主机
"""
db = get_db()
_time_now = tp_timestamp_utc_now()
# 1. 判断此主机是否已经存在了
if len(args['router_ip']) > 0:
sql = 'SELECT id FROM {}host WHERE ip="{}" OR (router_ip="{}" AND router_port={});'.format(db.table_prefix, args['ip'], args['router_ip'], args['router_port'])
else:
sql = 'SELECT id FROM {}host WHERE ip="{}";'.format(db.table_prefix, args['ip'])
db_ret = db.query(sql)
if db_ret is not None and len(db_ret) > 0:
return TPE_EXISTS, 0
sql = 'INSERT INTO `{}host` (`type`, `os_type`, `name`, `ip`, `router_ip`, `router_port`, `state`, `creator_id`, `create_time`, `cid`, `desc`) VALUES ' \
'(1, {os_type}, "{name}", "{ip}", "{router_ip}", {router_port}, {state}, {creator_id}, {create_time}, "{cid}", "{desc}");' \
''.format(db.table_prefix,
os_type=args['os_type'], name=args['name'], ip=args['ip'], router_ip=args['router_ip'], router_port=args['router_port'],
state=TP_STATE_NORMAL, creator_id=handler.get_current_user()['id'], create_time=_time_now,
cid=args['cid'], desc=args['desc'])
db_ret = db.exec(sql)
if not db_ret:
return TPE_DATABASE, 0
_id = db.last_insert_id()
h_name = args['ip']
if len(args['router_ip']) > 0:
h_name += '（由{}:{}路由）'.format(args['router_ip'], args['router_port'])
syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "创建主机：{}".format(h_name))
tp_stats().host_counter_change(1)
return TPE_OK, _id
def remove_members(gtype, gid, members):
db = get_db()
if gtype == TP_GROUP_USER:
name = 'u'
gname = 'gu'
elif gtype == TP_GROUP_HOST:
name = 'h'
gname = 'gh'
elif gtype == TP_GROUP_ACCOUNT:
name = 'a'
gname = 'ga'
else:
return TPE_PARAM
mids = ','.join([str(uid) for uid in members])
sql_list = []
_where = 'WHERE (type={gtype} AND gid={gid} AND mid IN ({mid}))'.format(
gtype=gtype, gid=gid, mid=mids)
sql = 'DELETE FROM `{dbtp}group_map` {where};'.format(dbtp=db.table_prefix,
where=_where)
sql_list.append(sql)
sql = 'DELETE FROM `{}ops_map` WHERE {gname}_id={gid} AND {name}_id IN ({ids});'.format(
db.table_prefix, gname=gname, name=name, gid=gid, ids=mids)
sql_list.append(sql)
sql = 'DELETE FROM `{}audit_map` WHERE {gname}_id={gid} AND {name}_id IN ({ids});'.format(
db.table_prefix, gname=gname, name=name, gid=gid, ids=mids)
sql_list.append(sql)
if db.transaction(sql_list):
return TPE_OK
else:
return TPE_DATABASE
def add_role(handler, role_name, privilege):
db = get_db()
_time_now = tp_timestamp_sec()
operator = handler.get_current_user()
# 1. 判断是否已经存在了
sql = 'SELECT id FROM {}role WHERE name="{name}";'.format(db.table_prefix,
name=role_name)
db_ret = db.query(sql)
if db_ret is not None and len(db_ret) > 0:
return TPE_EXISTS, 0
sql = 'INSERT INTO `{}role` (name, privilege, creator_id, create_time) VALUES ' \
'("{name}", {privilege}, {creator_id}, {create_time});' \
''.format(db.table_prefix, name=role_name, privilege=privilege, creator_id=operator['id'], create_time=_time_now)
db_ret = db.exec(sql)
if not db_ret:
return TPE_DATABASE, 0
_id = db.last_insert_id()
syslog.sys_log(operator, handler.request.remote_ip, TPE_OK,
"创建角色：{}".format(role_name))
return TPE_OK, _id
def update_users_state(handler, user_ids, state):
db = get_db()
user_ids = ','.join([str(i) for i in user_ids])
sql_list = []
sql = 'UPDATE `{}user` SET state={state} WHERE id IN ({ids});' \
''.format(db.table_prefix, state=state, ids=user_ids)
sql_list.append(sql)
sql = 'UPDATE `{}ops_auz` SET state={state} WHERE rtype={rtype} AND rid IN ({rid});' \
''.format(db.table_prefix, state=state, rtype=TP_USER, rid=user_ids)
sql_list.append(sql)
sql = 'UPDATE `{}ops_map` SET u_state={state} WHERE u_id IN ({ids});' \
''.format(db.table_prefix, state=state, ids=user_ids)
sql_list.append(sql)
sql = 'UPDATE `{}audit_auz` SET state={state} WHERE rtype={rtype} AND rid IN ({rid});' \
''.format(db.table_prefix, state=state, rtype=TP_USER, rid=user_ids)
sql_list.append(sql)
sql = 'UPDATE `{}audit_map` SET u_state={state} WHERE u_id IN ({ids});' \
''.format(db.table_prefix, state=state, ids=user_ids)
sql_list.append(sql)
if db.transaction(sql_list):
return TPE_OK
else:
return TPE_DATABASE
def update_users_state(handler, user_ids, state):
db = get_db()
user_ids = ','.join([str(i) for i in user_ids])
sql_list = []
sql = 'UPDATE `{}user` SET state={state} WHERE id IN ({ids});' \
''.format(db.table_prefix, state=state, ids=user_ids)
sql_list.append(sql)
sql = 'UPDATE `{}ops_auz` SET state={state} WHERE rtype={rtype} AND rid IN ({rid});' \
''.format(db.table_prefix, state=state, rtype=TP_USER, rid=user_ids)
sql_list.append(sql)
sql = 'UPDATE `{}ops_map` SET u_state={state} WHERE u_id IN ({ids});' \
''.format(db.table_prefix, state=state, ids=user_ids)
sql_list.append(sql)
sql = 'UPDATE `{}audit_auz` SET state={state} WHERE rtype={rtype} AND rid IN ({rid});' \
''.format(db.table_prefix, state=state, rtype=TP_USER, rid=user_ids)
sql_list.append(sql)
sql = 'UPDATE `{}audit_map` SET u_state={state} WHERE u_id IN ({ids});' \
''.format(db.table_prefix, state=state, ids=user_ids)
sql_list.append(sql)
if db.transaction(sql_list):
return TPE_OK
else:
return TPE_DATABASE
def set_password(handler, user_id, password):
db = get_db()
operator = handler.get_current_user()
# print('----------', operator)
# 1. get user info (user name)
s = SQL(db)
err = s.reset().select_from('user', ['username', 'surname']).where('user.id={}'.format(user_id)).query()
if err != TPE_OK:
return err
if len(s.recorder) == 0:
return TPE_NOT_EXISTS
name = s.recorder[0]['username']
surname = s.recorder[0]['surname']
if len(surname) == 0:
surname = name
sql = 'UPDATE `{}user` SET password="******" WHERE id={user_id};' \
''.format(db.table_prefix, password=password, user_id=user_id)
db_ret = db.exec(sql)
if not db_ret:
return TPE_DATABASE
if operator['id'] == 0:
syslog.sys_log({'username': name, 'surname': surname}, handler.request.remote_ip, TPE_OK,
"用户 {} 通过邮件方式重置了密码".format(name))
else:
syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "为用户 {} 手动重置了密码".format(name))
return TPE_OK
def remove_role(handler, role_id):
db = get_db()
s = SQL(db)
# 1. 判断是否存在
s.select_from('role', ['name'], alt_name='r')
s.where('r.id={rid}'.format(rid=role_id))
err = s.query()
if err != TPE_OK:
return err
if len(s.recorder) == 0:
return TPE_NOT_EXISTS
role_name = s.recorder[0].name
sql_list = list()
sql = 'DELETE FROM `{tp}role` WHERE `id`={ph};'.format(tp=db.table_prefix,
ph=db.place_holder)
sql_list.append({'s': sql, 'v': (role_id, )})
# 更新此角色相关的用户信息
sql = 'UPDATE `{tp}user` SET `role_id`=0 WHERE `role_id`={ph};'.format(
tp=db.table_prefix, ph=db.place_holder)
sql_list.append({'s': sql, 'v': (role_id, )})
if not db.transaction(sql_list):
return TPE_DATABASE
syslog.sys_log(handler.get_current_user(), handler.request.remote_ip,
TPE_OK, "删除角色：{}".format(role_name))
return TPE_OK
def update(handler, gid, name, desc):
db = get_db()
# 1. 判断是否已经存在
sql = 'SELECT `id`, `type` FROM `{}group` WHERE `id`={};'.format(
db.table_prefix, gid)
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS
gtype = db_ret[0][1]
sql_list = []
# 2. 更新记录
sql = 'UPDATE `{}group` SET `name`="{name}", `desc`="{desc}" WHERE id={gid};' \
''.format(db.table_prefix, name=name, desc=desc, gid=gid)
sql_list.append(sql)
# 3. 同步更新授权表和权限映射表
# 运维授权
sql = 'UPDATE `{}ops_auz` SET `name`="{name}" WHERE (`rtype`={rtype} AND `rid`={rid});'.format(
db.table_prefix, name=name, rtype=gtype, rid=gid)
sql_list.append(sql)
# 审计授权
sql = 'UPDATE `{}audit_auz` SET `name`="{name}" WHERE (`rtype`={rtype} AND `rid`={rid});'.format(
db.table_prefix, name=name, rtype=gtype, rid=gid)
sql_list.append(sql)
if not db.transaction(sql_list):
return TPE_DATABASE
return TPE_OK
def remove_members(gtype, gid, members):
db = get_db()
if gtype == TP_GROUP_USER:
name = 'u'
gname = 'gu'
elif gtype == TP_GROUP_HOST:
name = 'h'
gname = 'gh'
elif gtype == TP_GROUP_ACCOUNT:
name = 'a'
gname = 'ga'
else:
return TPE_PARAM
mids = ','.join([str(uid) for uid in members])
sql_list = []
_where = 'WHERE (type={gtype} AND gid={gid} AND mid IN ({mid}))'.format(gtype=gtype, gid=gid, mid=mids)
sql = 'DELETE FROM `{dbtp}group_map` {where};'.format(dbtp=db.table_prefix, where=_where)
sql_list.append(sql)
sql = 'DELETE FROM `{}ops_map` WHERE {gname}_id={gid} AND {name}_id IN ({ids});'.format(db.table_prefix, gname=gname, name=name, gid=gid, ids=mids)
sql_list.append(sql)
if gtype != TP_GROUP_ACCOUNT:
sql = 'DELETE FROM `{}audit_map` WHERE {gname}_id={gid} AND {name}_id IN ({ids});'.format(db.table_prefix, gname=gname, name=name, gid=gid, ids=mids)
sql_list.append(sql)
if db.transaction(sql_list):
return TPE_OK
else:
return TPE_DATABASE
def update_account(handler, host_id, acc_id, args):
"""
更新一个远程账号
"""
db = get_db()
# 1. 判断是否存在
sql = 'SELECT `id`, `host_ip`, `router_ip`, `router_port` FROM `{}acc` WHERE `host_id`={host_id} AND `id`={acc_id};'.format(db.table_prefix, host_id=host_id, acc_id=acc_id)
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS
_host_ip = db_ret[0][1]
_router_ip = db_ret[0][2]
_router_port = db_ret[0][3]
sql_list = []
sql = list()
sql.append('UPDATE `{}acc` SET'.format(db.table_prefix))
_set = list()
_set.append('`protocol_type`={}'.format(args['protocol_type']))
_set.append('`protocol_port`={}'.format(args['protocol_port']))
_set.append('`auth_type`={}'.format(args['auth_type']))
_set.append('`username`="{}"'.format(args['username']))
_set.append('`username_prompt`="{}"'.format(args['username_prompt']))
_set.append('`password_prompt`="{}"'.format(args['password_prompt']))
if args['auth_type'] == TP_AUTH_TYPE_PASSWORD and len(args['password']) > 0:
_set.append('`password`="{}"'.format(args['password']))
elif args['auth_type'] == TP_AUTH_TYPE_PRIVATE_KEY and len(args['pri_key']) > 0:
_set.append('`pri_key`="{}"'.format(args['pri_key']))
sql.append(','.join(_set))
sql.append('WHERE `id`={};'.format(acc_id))
# db_ret = db.exec(' '.join(sql))
# if not db_ret:
# return TPE_DATABASE
sql_list.append(' '.join(sql))
if len(_router_ip) == 0:
_name = '{}@{}'.format(args['username'], _host_ip)
else:
_name = '{}@{} （由{}:{}路由）'.format(args['username'], _host_ip, _router_ip, _router_port)
# 运维授权
sql = 'UPDATE `{}ops_auz` SET `name`="{name}" WHERE (`rtype`={rtype} AND `rid`={rid});'.format(db.table_prefix, name=_name, rtype=TP_ACCOUNT, rid=acc_id)
sql_list.append(sql)
sql = 'UPDATE `{}ops_map` SET `a_name`="{name}", `protocol_type`={protocol_type}, `protocol_port`={protocol_port} ' \
'WHERE (a_id={aid});'.format(db.table_prefix,
name=args['username'], protocol_type=args['protocol_type'], protocol_port=args['protocol_port'],
aid=acc_id)
sql_list.append(sql)
if not db.transaction(sql_list):
return TPE_DATABASE
return TPE_OK
def get_by_username(username):
s = SQL(get_db())
s.select_from('user', [
'id', 'type', 'auth_type', 'username', 'surname', 'ldap_dn',
'password', 'oath_secret', 'role_id', 'state', 'fail_count',
'lock_time', 'email', 'create_time', 'last_login', 'last_ip',
'last_chpass', 'mobile', 'qq', 'wechat', 'valid_from', 'valid_to',
'desc'
],
alt_name='u')
s.left_join('role', ['name', 'privilege'],
join_on='r.id=u.role_id',
alt_name='r',
out_map={'name': 'role'})
s.where('u.username="******"'.format(username))
err = s.query()
if err != TPE_OK:
return err
if len(s.recorder) == 0:
return TPE_NOT_EXISTS, {}
if s.recorder[0]['privilege'] is None:
s.recorder[0]['privilege'] = 0
return TPE_OK, s.recorder[0]
def remove_role(handler, role_id):
db = get_db()
s = SQL(db)
# 1. 判断是否存在
s.select_from('role', ['name'], alt_name='r')
s.where('r.id={rid}'.format(rid=role_id))
err = s.query()
if err != TPE_OK:
return err
if len(s.recorder) == 0:
return TPE_NOT_EXISTS
role_name = s.recorder[0].name
sql_list = []
sql = 'DELETE FROM `{}role` WHERE id={};'.format(db.table_prefix, role_id)
sql_list.append(sql)
# 更新此角色相关的用户信息
sql = 'UPDATE `{}user` SET role_id=0 WHERE role_id={rid};'.format(db.table_prefix, rid=role_id)
sql_list.append(sql)
if not db.transaction(sql_list):
return TPE_DATABASE
syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "删除角色：{}".format(role_name))
return TPE_OK
def update(handler, gid, name, desc):
db = get_db()
# 1. 判断是否已经存在
sql = 'SELECT `id`, `type` FROM `{}group` WHERE `id`={};'.format(db.table_prefix, gid)
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS
gtype = db_ret[0][1]
sql_list = []
# 2. 更新记录
sql = 'UPDATE `{}group` SET `name`="{name}", `desc`="{desc}" WHERE id={gid};' \
''.format(db.table_prefix, name=name, desc=desc, gid=gid)
sql_list.append(sql)
# 3. 同步更新授权表和权限映射表
# 运维授权
sql = 'UPDATE `{}ops_auz` SET `name`="{name}" WHERE (`rtype`={rtype} AND `rid`={rid});'.format(db.table_prefix, name=name, rtype=gtype, rid=gid)
sql_list.append(sql)
# 审计授权
sql = 'UPDATE `{}audit_auz` SET `name`="{name}" WHERE (`rtype`={rtype} AND `rid`={rid});'.format(db.table_prefix, name=name, rtype=gtype, rid=gid)
sql_list.append(sql)
if not db.transaction(sql_list):
return TPE_DATABASE
return TPE_OK
def update_groups_state(handler, gtype, glist, state):
if gtype not in TP_GROUP_TYPES:
return TPE_PARAM
if gtype == TP_GROUP_USER:
gname = 'gu'
elif gtype == TP_GROUP_HOST:
gname = 'gh'
elif gtype == TP_GROUP_ACCOUNT:
gname = 'ga'
else:
return TPE_PARAM
group_list = ','.join([str(i) for i in glist])
db = get_db()
sql_list = []
# 2. 更新记录
sql = 'UPDATE `{}ops_auz` SET state={state} WHERE rtype={rtype} AND rid={rid};' \
''.format(db.table_prefix, state=state, rtype=gtype, rid=group_list)
sql_list.append(sql)
sql = 'UPDATE `{}ops_map` SET {gname}_state={state} WHERE {gname}_id IN ({gids});' \
''.format(db.table_prefix, state=state, gname=gname, gids=group_list)
sql_list.append(sql)
sql = 'UPDATE `{dbtp}group` SET state={state} WHERE id IN ({gids});' \
''.format(dbtp=db.table_prefix, state=state, gids=group_list)
sql_list.append(sql)
if db.transaction(sql_list):
return TPE_OK
else:
return TPE_DATABASE
def get_list(gtype):
s = SQL(get_db())
s.select_from('group', ['id', 'name'], alt_name='g')
s.where('g.type={}'.format(gtype))
err = s.query()
return err, s.recorder
def delete_log(log_list):
try:
where = list()
for item in log_list:
where.append(' `id`={}'.format(item))
db = get_db()
sql = 'DELETE FROM `{}log` WHERE{};'.format(db.table_prefix, ' OR'.join(where))
ret = db.exec(sql)
if not ret:
return False
# TODO: 此处应该通过json-rpc接口通知core服务来删除重放文件。
for item in log_list:
log_id = int(item)
try:
record_path = os.path.join(tp_cfg().core.replay_path, 'ssh', '{:06d}'.format(log_id))
if os.path.exists(record_path):
shutil.rmtree(record_path)
record_path = os.path.join(tp_cfg().core.replay_path, 'rdp', '{:06d}'.format(log_id))
if os.path.exists(record_path):
shutil.rmtree(record_path)
except Exception:
pass
return True
except:
return False
def create(handler, gtype, name, desc):
if gtype not in TP_GROUP_TYPES:
return TPE_PARAM, 0
db = get_db()
_time_now = tp_timestamp_utc_now()
# 1. 判断是否已经存在了
sql = 'SELECT id FROM {dbtp}group WHERE type={gtype} AND name="{gname}";'.format(dbtp=db.table_prefix, gtype=gtype, gname=name)
db_ret = db.query(sql)
if db_ret is not None and len(db_ret) > 0:
return TPE_EXISTS, 0
operator = handler.get_current_user()
# 2. 插入记录
sql = 'INSERT INTO `{dbtp}group` (`type`, `name`, `creator_id`, `create_time`, `desc`) VALUES ' \
'({gtype}, "{gname}", {creator_id}, {create_time}, "{desc}");' \
''.format(dbtp=db.table_prefix,
gtype=gtype, gname=name, creator_id=operator['id'],
create_time=_time_now, desc=desc)
db_ret = db.exec(sql)
if not db_ret:
return TPE_DATABASE, 0
_id = db.last_insert_id()
syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "创建{gtype}：{gname}".format(gtype=TP_GROUP_TYPES[gtype], gname=name))
return TPE_OK, _id
def update_accounts_state(handler, host_id, acc_ids, state):
db = get_db()
acc_ids = ','.join([str(uid) for uid in acc_ids])
# 1. 判断是否存在
sql = 'SELECT id FROM {}acc WHERE host_id={host_id} AND id IN ({ids});'.format(db.table_prefix, host_id=host_id, ids=acc_ids)
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS
sql_list = []
sql = 'UPDATE `{}acc` SET state={state} WHERE id IN ({ids});' \
''.format(db.table_prefix, state=state, ids=acc_ids)
sql_list.append(sql)
# sync to update the ops-audit table.
sql = 'UPDATE `{}ops_auz` SET state={state} WHERE rtype={rtype} AND rid IN ({rid});' \
''.format(db.table_prefix, state=state, rtype=TP_ACCOUNT, rid=acc_ids)
sql_list.append(sql)
sql = 'UPDATE `{}ops_map` SET a_state={state} WHERE a_id IN ({acc_id});' \
''.format(db.table_prefix, state=state, acc_id=acc_ids)
sql_list.append(sql)
if db.transaction(sql_list):
return TPE_OK
else:
return TPE_DATABASE
def update_groups_state(handler, gtype, glist, state):
if gtype not in TP_GROUP_TYPES:
return TPE_PARAM
if gtype == TP_GROUP_USER:
gname = 'gu'
elif gtype == TP_GROUP_HOST:
gname = 'gh'
elif gtype == TP_GROUP_ACCOUNT:
gname = 'ga'
else:
return TPE_PARAM
group_list = ','.join([str(i) for i in glist])
db = get_db()
sql_list = list()
# 2. 更新记录
sql = 'UPDATE `{tp}ops_auz` SET `state`={ph} WHERE `rtype`={ph} AND `rid` IN ({rid});' \
''.format(tp=db.table_prefix, ph=db.place_holder, rid=group_list)
sql_list.append({'s': sql, 'v': (state, gtype)})
sql = 'UPDATE `{tp}ops_map` SET `{gname}_state`={ph} WHERE `{gname}_id` IN ({gids});' \
''.format(tp=db.table_prefix, ph=db.place_holder, gname=gname, gids=group_list)
sql_list.append({'s': sql, 'v': (state, )})
sql = 'UPDATE `{tp}group` SET `state`={ph} WHERE `id` IN ({gids});' \
''.format(tp=db.table_prefix, ph=db.place_holder, gids=group_list)
sql_list.append({'s': sql, 'v': (state, )})
if db.transaction(sql_list):
return TPE_OK
else:
return TPE_DATABASE
def update_accounts_state(handler, host_id, acc_ids, state):
db = get_db()
acc_ids = ','.join([str(uid) for uid in acc_ids])
# 1. 判断是否存在
sql = 'SELECT id FROM {}acc WHERE host_id={host_id} AND id IN ({ids});'.format(
db.table_prefix, host_id=host_id, ids=acc_ids)
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS
sql_list = []
sql = 'UPDATE `{}acc` SET state={state} WHERE id IN ({ids});' \
''.format(db.table_prefix, state=state, ids=acc_ids)
sql_list.append(sql)
# sync to update the ops-audit table.
sql = 'UPDATE `{}ops_auz` SET state={state} WHERE rtype={rtype} AND rid IN ({rid});' \
''.format(db.table_prefix, state=state, rtype=TP_ACCOUNT, rid=acc_ids)
sql_list.append(sql)
sql = 'UPDATE `{}ops_map` SET a_state={state} WHERE a_id IN ({acc_id});' \
''.format(db.table_prefix, state=state, acc_id=acc_ids)
sql_list.append(sql)
if db.transaction(sql_list):
return TPE_OK
else:
return TPE_DATABASE
def update_oath_secret(handler, user_id, oath_secret):
db = get_db()
s = SQL(db)
err = s.select_from('user', ['username', 'surname'], alt_name='u').where(
'u.id={user_id}'.format(user_id=user_id)).query()
if err != TPE_OK:
return err
if len(s.recorder) == 0:
return TPE_NOT_EXISTS
username = s.recorder[0].username
surname = s.recorder[0].surname
sql = 'UPDATE `{dbtp}user` SET oath_secret="{secret}" WHERE id={user_id}' \
''.format(dbtp=db.table_prefix, secret=oath_secret, user_id=user_id)
if db.exec(sql):
if len(oath_secret) > 0:
syslog.sys_log({
'username': username,
'surname': surname
}, handler.request.remote_ip, TPE_OK,
"用户 {} 更新了身份认证器绑定信息".format(username))
else:
syslog.sys_log({
'username': username,
'surname': surname
}, handler.request.remote_ip, TPE_OK,
"用户 {} 清除了身份认证器绑定信息".format(username))
return TPE_OK
else:
return TPE_DATABASE
def get_user_info(user_id):
"""
获取一个指定的用户的详细信息，包括关联的角色的详细信息、所属组的详细信息等等
"""
s = SQL(get_db())
s.select_from('user', [
'id', 'type', 'auth_type', 'username', 'surname', 'ldap_dn',
'password', 'oath_secret', 'role_id', 'state', 'fail_count',
'lock_time', 'email', 'create_time', 'last_login', 'last_ip',
'last_chpass', 'mobile', 'qq', 'wechat', 'desc'
],
alt_name='u')
s.left_join('role', ['name', 'privilege'],
join_on='r.id=u.role_id',
alt_name='r',
out_map={'name': 'role'})
s.where('u.id="{}"'.format(user_id))
err = s.query()
if err != TPE_OK:
return err, {}
if len(s.recorder) == 0:
return TPE_NOT_EXISTS, {}
return TPE_OK, s.recorder[0]
def create(handler, gtype, name, desc):
if gtype not in TP_GROUP_TYPES:
return TPE_PARAM, 0
db = get_db()
_time_now = tp_timestamp_utc_now()
# 1. 判断是否已经存在了
sql = 'SELECT id FROM {dbtp}group WHERE type={gtype} AND name="{gname}";'.format(
dbtp=db.table_prefix, gtype=gtype, gname=name)
db_ret = db.query(sql)
if db_ret is not None and len(db_ret) > 0:
return TPE_EXISTS, 0
operator = handler.get_current_user()
# 2. 插入记录
sql = 'INSERT INTO `{dbtp}group` (`type`, `name`, `creator_id`, `create_time`, `desc`) VALUES ' \
'({gtype}, "{gname}", {creator_id}, {create_time}, "{desc}");' \
''.format(dbtp=db.table_prefix,
gtype=gtype, gname=name, creator_id=operator['id'],
create_time=_time_now, desc=desc)
db_ret = db.exec(sql)
if not db_ret:
return TPE_DATABASE, 0
_id = db.last_insert_id()
syslog.sys_log(
operator, handler.request.remote_ip, TPE_OK,
"创建{gtype}：{gname}".format(gtype=TP_GROUP_TYPES[gtype], gname=name))
return TPE_OK, _id
def get_list(gtype):
s = SQL(get_db())
s.select_from('group', ['id', 'name'], alt_name='g')
s.where('g.type={}'.format(gtype))
err = s.query()
return err, s.recorder
def make_groups(handler, gtype, glist, failed):
"""
根据传入的组列表，查询每个组的名称对应的id，如果没有，则创建之
"""
db = get_db()
_time_now = tp_timestamp_utc_now()
operator = handler.get_current_user()
name_list = list()
for g in glist:
sql = 'SELECT id FROM {dbtp}group WHERE type={gtype} AND name="{gname}";'.format(dbtp=db.table_prefix, gtype=gtype, gname=g)
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
# need create group.
sql = 'INSERT INTO `{dbtp}group` (`type`, `name`, `creator_id`, `create_time`) VALUES ' \
'({gtype}, "{name}", {creator_id}, {create_time});' \
''.format(dbtp=db.table_prefix,
gtype=gtype, name=g, creator_id=operator['id'], create_time=_time_now)
db_ret = db.exec(sql)
if not db_ret:
failed.append({'line': 0, 'error': '创建{gtype} `{gname}` 失败，写入数据库时发生错误'.format(gtype=TP_GROUP_TYPES[gtype], gname=g)})
continue
glist[g] = db.last_insert_id()
name_list.append(g)
else:
glist[g] = db_ret[0][0]
syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "创建{gtype}：{gname}".format(gtype=TP_GROUP_TYPES[gtype], gname='，'.join(name_list)))
return TPE_OK
def update_groups_state(handler, gtype, glist, state):
if gtype not in TP_GROUP_TYPES:
return TPE_PARAM
if gtype == TP_GROUP_USER:
gname = 'gu'
elif gtype == TP_GROUP_HOST:
gname = 'gh'
elif gtype == TP_GROUP_ACCOUNT:
gname = 'ga'
else:
return TPE_PARAM
group_list = ','.join([str(i) for i in glist])
db = get_db()
sql_list = []
# 2. 更新记录
sql = 'UPDATE `{}ops_auz` SET state={state} WHERE rtype={rtype} AND rid={rid};' \
''.format(db.table_prefix, state=state, rtype=gtype, rid=group_list)
sql_list.append(sql)
sql = 'UPDATE `{}ops_map` SET {gname}_state={state} WHERE {gname}_id IN ({gids});' \
''.format(db.table_prefix, state=state, gname=gname, gids=group_list)
sql_list.append(sql)
sql = 'UPDATE `{dbtp}group` SET state={state} WHERE id IN ({gids});' \
''.format(dbtp=db.table_prefix, state=state, gids=group_list)
sql_list.append(sql)
if db.transaction(sql_list):
return TPE_OK
else:
return TPE_DATABASE
def update(handler, gid, name, desc):
db = get_db()
# 1. 判断是否已经存在
sql = 'SELECT `id`, `type` FROM `{}group` WHERE `id`={};'.format(
db.table_prefix, gid)
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS
gtype = db_ret[0][1]
sql_list = list()
# 2. 更新记录
sql = 'UPDATE `{tp}group` SET `name`={ph}, `desc`={ph} WHERE `id`={ph};'.format(
tp=db.table_prefix, ph=db.place_holder)
sql_list.append({'s': sql, 'v': (name, desc, gid)})
# 3. 同步更新授权表和权限映射表
# 运维授权
sql = 'UPDATE `{tp}ops_auz` SET `name`={ph} WHERE (`rtype`={ph} AND `rid`={ph});'.format(
tp=db.table_prefix, ph=db.place_holder)
sql_list.append({'s': sql, 'v': (name, gtype, gid)})
# 审计授权
sql = 'UPDATE `{tp}audit_auz` SET `name`={ph} WHERE (`rtype`={ph} AND `rid`={ph});'.format(
tp=db.table_prefix, ph=db.place_holder)
sql_list.append({'s': sql, 'v': (name, gtype, gid)})
if not db.transaction(sql_list):
return TPE_DATABASE
return TPE_OK
def get_host_groups_for_user(user_id, user_privilege):
# get all host-groups for current logged in user.
db = get_db()
# step 0. return all host-groups if user have all host-group access privilege
if (user_privilege & (TP_PRIVILEGE_ASSET_CREATE | TP_PRIVILEGE_ASSET_DELETE | TP_PRIVILEGE_ASSET_GROUP)) != 0:
s = SQL(get_db())
s.select_from('group', ['id', 'name'], alt_name='g')
s.where('g.type={}'.format(TP_GROUP_HOST))
s.order_by('g.name')
err = s.query()
return err, s.recorder
# step 1. get all hosts which could be access by this user.
sql = 'SELECT `h_id` FROM `{dbtp}ops_map` WHERE `u_id`={dbph} GROUP BY `h_id`;'.format(dbtp=db.table_prefix, dbph=db.place_holder)
db_ret = db.query(sql, (user_id, ))
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS, None
hosts = []
for db_item in db_ret:
hosts.append(str(db_item[0]))
if len(hosts) == 0:
return TPE_NOT_EXISTS, None
# step 2. get groups which include those hosts.
sql = 'SELECT `gid` FROM `{dbtp}group_map` WHERE (`type`={gtype} AND `mid` IN ({hids})) GROUP BY `gid`;'.format(dbtp=db.table_prefix, gtype=TP_GROUP_HOST, hids=','.join(hosts))
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS, None
groups = []
for db_item in db_ret:
groups.append(str(db_item[0]))
# step 3. get those groups id and name.
s = SQL(get_db())
s.select_from('group', ['id', 'name'], alt_name='g')
s.where('g.id IN ({})'.format(','.join(groups)))
s.order_by('g.name')
err = s.query()
return err, s.recorder
def post(self):
ret = self.check_privilege(TP_PRIVILEGE_SYS_CONFIG)
if ret != TPE_OK:
return
args = self.get_argument('args', None)
if args is not None:
try:
args = json.loads(args)
except:
return self.write_json(TPE_JSON_FORMAT)
else:
return self.write_json(TPE_PARAM)
if 'cmd' not in args:
return self.write_json(TPE_PARAM)
cmd = args['cmd']
# if cmd == 'enter_maintenance_mode':
# cfg.app_mode = APP_MODE_MAINTENANCE
# return self.write_json(0)
if cmd == 'install':
if not get_db().need_create:
return self.write_json(TPE_FAILED, '数据库已存在，无需创建！')
if 'sysadmin' not in args or 'email' not in args or 'password' not in args:
return self.write_json(TPE_PARAM)
task_id = thread_mgr.create_db(args['sysadmin'], args['email'], args['password'])
return self.write_json(0, data={"task_id": task_id})
if cmd == 'upgrade_db':
if not get_db().need_upgrade:
return self.write_json(-1, '无需升级')
task_id = thread_mgr.upgrade_db()
return self.write_json(0, data={"task_id": task_id})
elif cmd == 'get_task_ret':
r = thread_mgr.get_task(args['tid'])
if r is None:
return self.write_json(0, data={'running': False, 'steps': []})
else:
return self.write_json(0, data=r)
else:
self.write_json(-1, '未知命令 `{}`！'.format(cmd))
def update_host(handler, args):
"""
更新一个远程主机
"""
db = get_db()
# 1. 判断是否存在
sql = 'SELECT `id` FROM `{}host` WHERE `id`={};'.format(
db.table_prefix, args['id'])
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS
sql_list = []
sql = 'UPDATE `{}host` SET `os_type`={os_type}, `name`="{name}", `ip`="{ip}", `router_ip`="{router_ip}", ' \
'`router_port`={router_port}, `cid`="{cid}", `desc`="{desc}" WHERE `id`={host_id};' \
''.format(db.table_prefix,
os_type=args['os_type'], name=args['name'], ip=args['ip'], router_ip=args['router_ip'], router_port=args['router_port'],
cid=args['cid'], desc=args['desc'], host_id=args['id'])
sql_list.append(sql)
# 更新所有此主机相关的账号
sql = 'UPDATE `{}acc` SET `host_ip`="{ip}", `router_ip`="{router_ip}", `router_port`={router_port} WHERE `host_id`={id};' \
''.format(db.table_prefix,
ip=args['ip'], router_ip=args['router_ip'], router_port=args['router_port'], id=args['id'])
sql_list.append(sql)
# 同步更新授权表和权限映射表
_name = args['ip']
if len(args['name']) > 0:
_name = '{} [{}]'.format(args['name'], args['ip'])
# 运维授权
sql = 'UPDATE `{}ops_auz` SET `name`="{name}" WHERE (`rtype`={rtype} AND `rid`={rid});' \
''.format(db.table_prefix, name=_name, rtype=TP_HOST, rid=args['id'])
sql_list.append(sql)
sql = 'UPDATE `{}ops_map` SET `h_name`="{hname}", `ip`="{ip}", `router_ip`="{router_ip}", `router_port`={router_port} ' \
'WHERE (h_id={hid});'.format(db.table_prefix,
hname=args['name'], ip=args['ip'], hid=args['id'],
router_ip=args['router_ip'], router_port=args['router_port'])
sql_list.append(sql)
# 审计授权
sql = 'UPDATE `{}audit_auz` SET `name`="{name}" WHERE (`rtype`={rtype} AND `rid`={rid});'.format(
db.table_prefix, name=_name, rtype=TP_HOST, rid=args['id'])
sql_list.append(sql)
sql = 'UPDATE `{}audit_map` SET `h_name`="{hname}", `ip`="{ip}", `router_ip`="{router_ip}", `router_port`={router_port} ' \
'WHERE (h_id={hid});'.format(db.table_prefix,
hname=args['name'], ip=args['ip'], hid=args['id'],
router_ip=args['router_ip'], router_port=args['router_port'])
sql_list.append(sql)
if not db.transaction(sql_list):
return TPE_DATABASE
operator = handler.get_current_user()
syslog.sys_log(operator, handler.request.remote_ip, TPE_OK,
"更新主机信息：{}".format(_name))
return TPE_OK
def get_users_by_type(_type):
s = SQL(get_db())
err = s.select_from('user', ['id', 'type', 'ldap_dn'], alt_name='u').where('u.type={}'.format(_type)).query()
if err != TPE_OK:
return None
if len(s.recorder) == 0:
return None
return s.recorder
def create_users(handler, user_list, success, failed):
"""
批量创建用户
"""
db = get_db()
_time_now = tp_timestamp_utc_now()
operator = handler.get_current_user()
name_list = list()
s = SQL(db)
for i in range(len(user_list)):
user = user_list[i]
if 'type' not in user:
user['type'] = TP_USER_TYPE_LOCAL
if 'ldap_dn' not in user:
user['ldap_dn'] = ''
err = s.reset().select_from('user', ['id']).where('user.username="******"'.format(user['username'])).query()
if err != TPE_OK:
failed.append({'line': user['_line'], 'error': '数据库查询失败'})
if len(s.recorder) > 0:
failed.append({'line': user['_line'], 'error': '账号 `{}` 已经存在'.format(user['username'])})
continue
if user['type'] == TP_USER_TYPE_LOCAL:
_password = tp_password_generate_secret(user['password'])
else:
_password = ''
sql = 'INSERT INTO `{}user` (' \
'`role_id`, `username`, `surname`, `type`, `ldap_dn`, `auth_type`, `password`, ' \
'`state`, `email`, `creator_id`, `create_time`, `last_login`, `last_chpass`, `desc`' \
') VALUES (' \
'0, "{username}", "{surname}", {user_type}, "{ldap_dn}", 0, "{password}", ' \
'{state}, "{email}", {creator_id}, {create_time}, {last_login}, {last_chpass}, "{desc}");' \
''.format(db.table_prefix, username=user['username'], surname=user['surname'], user_type=user['type'],
ldap_dn=user['ldap_dn'], password=_password, state=TP_STATE_NORMAL, email=user['email'],
creator_id=operator['id'], create_time=_time_now, last_login=0, last_chpass=_time_now,
desc=user['desc'])
db_ret = db.exec(sql)
if not db_ret:
failed.append({'line': user['_line'], 'error': '写入数据库时发生错误'})
continue
success.append(user['username'])
name_list.append(user['username'])
user['_id'] = db.last_insert_id()
if len(name_list) > 0:
syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "批量导入方式创建用户：{}".format('，'.join(name_list)))
# tp_stats().user_counter_change(len(name_list))
# calc count of users.
err, cnt = s.reset().count('user')
if err == TPE_OK:
tp_stats().user_counter_change(cnt)
def get_all_hosts_for_check_state():
"""查询所有主机"""
s = SQL(get_db())
s.select_from('host', ['ip', 'router_ip'], alt_name='h')
err = s.query()
if err != TPE_OK:
return None
return s.recorder
def get_users_by_type(_type):
s = SQL(get_db())
err = s.select_from('user', ['id', 'type', 'ldap_dn'],
alt_name='u').where('u.type={}'.format(_type)).query()
if err != TPE_OK:
return None
if len(s.recorder) == 0:
return None
return s.recorder
def update_host(handler, args):
"""
更新一个远程主机
"""
db = get_db()
# 1. 判断是否存在
sql = 'SELECT `id` FROM `{}host` WHERE `id`={};'.format(db.table_prefix, args['id'])
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
return TPE_NOT_EXISTS
sql_list = []
sql = 'UPDATE `{}host` SET `os_type`={os_type}, `name`="{name}", `ip`="{ip}", `router_ip`="{router_ip}", ' \
'`router_port`={router_port}, `cid`="{cid}", `desc`="{desc}" WHERE `id`={host_id};' \
''.format(db.table_prefix,
os_type=args['os_type'], name=args['name'], ip=args['ip'], router_ip=args['router_ip'], router_port=args['router_port'],
cid=args['cid'], desc=args['desc'], host_id=args['id'])
sql_list.append(sql)
# 更新所有此主机相关的账号
sql = 'UPDATE `{}acc` SET `host_ip`="{ip}", `router_ip`="{router_ip}", `router_port`={router_port} WHERE `host_id`={id};' \
''.format(db.table_prefix,
ip=args['ip'], router_ip=args['router_ip'], router_port=args['router_port'], id=args['id'])
sql_list.append(sql)
# 同步更新授权表和权限映射表
_name = args['ip']
if len(args['name']) > 0:
_name = '{} [{}]'.format(args['name'], args['ip'])
# 运维授权
sql = 'UPDATE `{}ops_auz` SET `name`="{name}" WHERE (`rtype`={rtype} AND `rid`={rid});' \
''.format(db.table_prefix, name=_name, rtype=TP_HOST, rid=args['id'])
sql_list.append(sql)
sql = 'UPDATE `{}ops_map` SET `h_name`="{hname}", `ip`="{ip}", `router_ip`="{router_ip}", `router_port`={router_port} ' \
'WHERE (h_id={hid});'.format(db.table_prefix,
hname=args['name'], ip=args['ip'], hid=args['id'],
router_ip=args['router_ip'], router_port=args['router_port'])
sql_list.append(sql)
# 审计授权
sql = 'UPDATE `{}audit_auz` SET `name`="{name}" WHERE (`rtype`={rtype} AND `rid`={rid});'.format(db.table_prefix, name=_name, rtype=TP_HOST, rid=args['id'])
sql_list.append(sql)
sql = 'UPDATE `{}audit_map` SET `h_name`="{hname}", `ip`="{ip}", `router_ip`="{router_ip}", `router_port`={router_port} ' \
'WHERE (h_id={hid});'.format(db.table_prefix,
hname=args['name'], ip=args['ip'], hid=args['id'],
router_ip=args['router_ip'], router_port=args['router_port'])
sql_list.append(sql)
if not db.transaction(sql_list):
return TPE_DATABASE
operator = handler.get_current_user()
syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "更新主机信息：{}".format(_name))
return TPE_OK
def add_account(handler, host_id, args):
"""
添加一个远程账号
"""
db = get_db()
_time_now = tp_timestamp_sec()
operator = handler.get_current_user()
# 1. 判断是否已经存在了
sql = 'SELECT `id` FROM `{tp}acc` WHERE `host_id`={ph} AND `protocol_port`={ph} AND `username`={ph} AND `auth_type`={ph};'.format(
tp=db.table_prefix, ph=db.place_holder)
db_ret = db.query(
sql,
(host_id, args['protocol_port'], args['username'], args['auth_type']))
if db_ret is not None and len(db_ret) > 0:
return TPE_EXISTS, 0
sql_s = 'INSERT INTO `{tp}acc` (`host_id`,`host_ip`,`router_ip`,`router_port`,`protocol_type`,`protocol_port`,' \
'`state`,`auth_type`,`username`,`username_prompt`,`password_prompt`,`password`,`pri_key`,`creator_id`,`create_time`) VALUES ' \
'({ph}, {ph}, {ph}, {ph}, {ph}, {ph}, {ph}, {ph}, {ph}, {ph}, {ph}, {ph}, {ph}, {ph}, {ph});' \
''.format(tp=db.table_prefix, ph=db.place_holder)
sql_v = (host_id, args['host_ip'], args['router_ip'], args['router_port'],
args['protocol_type'], args['protocol_port'], TP_STATE_NORMAL,
args['auth_type'], args['username'], args['username_prompt'],
args['password_prompt'], args['password'], args['pri_key'],
operator['id'], _time_now)
# sql = 'INSERT INTO `{}acc` (host_id, protocol_type, protocol_port, state, auth_type, username, password, pri_key, creator_id, create_time) VALUES ' \
# '({host_id}, {protocol_type}, {protocol_port}, {state}, {auth_type}, "{username}", "{password}", "{pri_key}", {creator_id}, {create_time});' \
# ''.format(db.table_prefix,
# host_id=host_id,
# protocol_type=args['protocol_type'], protocol_port=args['protocol_port'], state=TP_STATE_NORMAL,
# auth_type=args['auth_type'], username=args['username'], password=args['password'], pri_key=args['pri_key'],
# creator_id=operator['id'], create_time=_time_now)
db_ret = db.exec(sql_s, sql_v)
if not db_ret:
return TPE_DATABASE, 0
_id = db.last_insert_id()
acc_name = '{}@{}'.format(args['username'], args['host_ip'])
if len(args['router_ip']) > 0:
acc_name += '（由{}:{}路由）'.format(args['router_ip'], args['router_port'])
syslog.sys_log(operator, handler.request.remote_ip, TPE_OK,
"创建账号：{}".format(acc_name))
# 更新主机相关账号数量
sql = 'UPDATE `{tp}host` SET `acc_count`=`acc_count`+1 WHERE `id`={ph};' \
''.format(tp=db.table_prefix, ph=db.place_holder)
db.exec(sql, (host_id, ))
# if not db_ret:
# return TPE_DATABASE, 0
tp_stats().acc_counter_change(1)
return TPE_OK, _id
def get_host_accounts(host_id):
# 获取指定主机的所有账号
s = SQL(get_db())
# s.select_from('acc', ['id', 'state', 'host_ip', 'router_ip', 'router_port', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'pri_key'], alt_name='a')
s.select_from('acc', ['id', 'state', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'username_prompt', 'password_prompt'], alt_name='a')
s.where('a.host_id={}'.format(host_id))
s.order_by('a.username', True)
err = s.query()
return err, s.recorder
def get_host_info(host_id):
s = SQL(get_db())
s.select_from('host', ['id', 'type', 'ip', 'router_ip', 'router_port', 'state'], alt_name='h')
s.where('h.id={}'.format(host_id))
err = s.query()
if err != TPE_OK:
return err, None
if len(s.recorder) != 1:
return TPE_DATABASE, None
return TPE_OK, s.recorder[0]
def get_by_id(gtype, gid):
# 获取要查询的组的信息
s = SQL(get_db())
s.select_from('group', ['id', 'state', 'name', 'desc'], alt_name='g')
s.where('g.type={} AND g.id={}'.format(gtype, gid))
err = s.query()
if err != TPE_OK:
return err, {}
if len(s.recorder) == 0:
return TPE_NOT_EXISTS, {}
return TPE_OK, s.recorder[0]
def get_by_id(gtype, gid):
# 获取要查询的组的信息
s = SQL(get_db())
s.select_from('group', ['id', 'state', 'name', 'desc'], alt_name='g')
s.where('g.type={} AND g.id={}'.format(gtype, gid))
err = s.query()
if err != TPE_OK:
return err, {}
if len(s.recorder) == 0:
return TPE_NOT_EXISTS, {}
return TPE_OK, s.recorder[0]
def add_members(gtype, gid, members):
# 向指定组中增加成员，同时根据授权策略，更新授权映射表
db = get_db()
sql = []
for uid in members:
sql.append('INSERT INTO `{}group_map` (`type`, `gid`, `mid`) VALUES ({}, {}, {});'.format(db.table_prefix, gtype, gid, uid))
if db.transaction(sql):
return policy.rebuild_auz_map()
else:
return TPE_DATABASE
def make_group_map(gtype, gm):
db = get_db()
for item in gm:
# 检查如果不存在，则插入
sql = 'SELECT id FROM `{dbtp}group_map` WHERE type={gtype} AND gid={gid} AND mid={mid};'.format(dbtp=db.table_prefix, gtype=gtype, gid=item['gid'], mid=item['mid'])
db_ret = db.query(sql)
if db_ret is None or len(db_ret) == 0:
sql = 'INSERT INTO `{dbtp}group_map` (`type`, `gid`, `mid`) VALUES ' \
'({gtype}, {gid}, {mid});' \
''.format(dbtp=db.table_prefix, gtype=gtype, gid=item['gid'], mid=item['mid'])
db_ret = db.exec(sql)
def get_by_id(pid):
s = SQL(get_db())
s.select_from('audit_policy', ['id', 'name', 'desc'], alt_name='p')
s.where('p.id={}'.format(pid))
err = s.query()
if err != TPE_OK:
return err, {}
if len(s.recorder) == 0:
return TPE_NOT_EXISTS, {}
return TPE_OK, s.recorder[0]
def get_by_id(pid):
s = SQL(get_db())
s.select_from('ops_policy', ['id', 'name', 'desc', 'flag_record', 'flag_rdp', 'flag_ssh', 'flag_telnet'], alt_name='p')
s.where('p.id={}'.format(pid))
err = s.query()
if err != TPE_OK:
return err, {}
# if len(s.recorder) == 0:
# return TPE_NOT_EXISTS, {}
return TPE_OK, s.recorder[0]
def _upgrade_db(self, tid):
def _step_begin(msg):
return self._step_begin(tid, msg)
def _step_end(sid, code, msg=None):
self._step_end(tid, sid, code, msg)
if get_db().upgrade_database(_step_begin, _step_end):
cfg.app_mode = APP_MODE_NORMAL
# self._step_begin(tid, '操作已完成')
self._thread_end(tid)
def _create_db(self, tid, sysadmin, email, password):
def _step_begin(msg):
return self._step_begin(tid, msg)
def _step_end(sid, code, msg=None):
self._step_end(tid, sid, code, msg)
if get_db().create_and_init(_step_begin, _step_end, sysadmin, email, password):
cfg.app_mode = APP_MODE_NORMAL
# self._step_begin(tid, '操作已完成')
self._thread_end(tid)
