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 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 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', '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 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 get_users(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
dbtp = get_db().table_prefix
s = SQL(get_db())
s.select_from('user', [
'id', 'type', 'auth_type', 'username', 'surname', 'role_id', 'state',
'email', 'last_login', 'valid_from', 'valid_to'
],
alt_name='u')
s.left_join('role', ['name', 'privilege'],
join_on='r.id=u.role_id',
alt_name='r',
out_map={'name': 'role'})
_where = list()
if len(sql_restrict) > 0:
for k in sql_restrict:
if k == 'group_id':
_sql = 'u.id IN (SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'
_where.append(
_sql.format(dbtp=dbtp,
gtype=TP_GROUP_USER,
gid=sql_restrict[k]))
else:
log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
if k == 'group_id':
_where.append(
'u.id NOT IN ('
'SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'
''.format(dbtp=dbtp,
gtype=TP_GROUP_USER,
gid=sql_exclude[k]))
elif k == 'ops_policy_id':
_where.append(
'u.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'
''.format(dbtp=dbtp, pid=sql_exclude[k], rtype=TP_USER))
elif k == 'auditor_policy_id':
_where.append(
'u.id NOT IN ('
'SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} '
'AND `type`={ptype} AND rtype={rtype}'
')'.format(dbtp=dbtp,
pid=sql_exclude[k],
ptype=TP_POLICY_OPERATOR,
rtype=TP_USER))
elif k == 'auditee_policy_id':
_where.append(
'u.id NOT IN ('
'SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} '
'AND `type`={ptype} AND rtype={rtype}'
')'.format(dbtp=dbtp,
pid=sql_exclude[k],
ptype=TP_POLICY_ASSET,
rtype=TP_USER))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'role':
_where.append(
'u.role_id={filter}'.format(filter=sql_filter[k]))
elif k == 'type':
_where.append('u.type={filter}'.format(filter=sql_filter[k]))
elif k == 'state':
_where.append('u.state={filter}'.format(filter=sql_filter[k]))
elif k == 'search':
_where.append('('
'u.username LIKE "%{filter}%" '
'OR u.surname LIKE "%{filter}%" '
'OR u.email LIKE "%{filter}%" '
'OR u.desc LIKE "%{filter}%"'
')'.format(filter=sql_filter[k]))
if len(_where) > 0:
s.where('( {} )'.format(' AND '.join(_where)))
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'username' == sql_order['name']:
s.order_by('u.username', _sort)
elif 'surname' == sql_order['name']:
s.order_by('u.surname', _sort)
elif 'role_id' == sql_order['name']:
s.order_by('u.role_id', _sort)
elif 'state' == sql_order['name']:
s.order_by('u.state', _sort)
elif 'type' == sql_order['name']:
s.order_by('u.type', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, 0, 0, {}
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_users(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
dbtp = get_db().table_prefix
s = SQL(get_db())
s.select_from('user', ['id', 'type', 'auth_type', 'username', 'surname', 'role_id', 'state', 'email', 'last_login'],
alt_name='u')
s.left_join('role', ['name', 'privilege'], join_on='r.id=u.role_id', alt_name='r', out_map={'name': 'role'})
_where = list()
if len(sql_restrict) > 0:
for k in sql_restrict:
if k == 'group_id':
_sql = 'u.id IN (SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'
_where.append(_sql.format(dbtp=dbtp, gtype=TP_GROUP_USER, gid=sql_restrict[k]))
else:
log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
if k == 'group_id':
_where.append(
'u.id NOT IN ('
'SELECT mid FROM {dbtp}group_map WHERE type={gtype} AND gid={gid})'
''.format(dbtp=dbtp, gtype=TP_GROUP_USER, gid=sql_exclude[k]))
elif k == 'ops_policy_id':
_where.append(
'u.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'
''.format(dbtp=dbtp, pid=sql_exclude[k], rtype=TP_USER))
elif k == 'auditor_policy_id':
_where.append(
'u.id NOT IN ('
'SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} '
'AND `type`={ptype} AND rtype={rtype}'
')'.format(dbtp=dbtp, pid=sql_exclude[k], ptype=TP_POLICY_OPERATOR, rtype=TP_USER))
elif k == 'auditee_policy_id':
_where.append(
'u.id NOT IN ('
'SELECT rid FROM {dbtp}audit_auz WHERE policy_id={pid} '
'AND `type`={ptype} AND rtype={rtype}'
')'.format(dbtp=dbtp, pid=sql_exclude[k], ptype=TP_POLICY_ASSET, rtype=TP_USER))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'role':
_where.append('u.role_id={filter}'.format(filter=sql_filter[k]))
elif k == 'type':
_where.append('u.type={filter}'.format(filter=sql_filter[k]))
elif k == 'state':
_where.append('u.state={filter}'.format(filter=sql_filter[k]))
elif k == 'search':
_where.append('('
'u.username LIKE "%{filter}%" '
'OR u.surname LIKE "%{filter}%" '
'OR u.email LIKE "%{filter}%" '
'OR u.desc LIKE "%{filter}%"'
')'.format(filter=sql_filter[k]))
if len(_where) > 0:
s.where('( {} )'.format(' AND '.join(_where)))
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'username' == sql_order['name']:
s.order_by('u.username', _sort)
elif 'surname' == sql_order['name']:
s.order_by('u.surname', _sort)
elif 'role_id' == sql_order['name']:
s.order_by('u.role_id', _sort)
elif 'state' == sql_order['name']:
s.order_by('u.state', _sort)
elif 'type' == sql_order['name']:
s.order_by('u.type', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, 0, 0, {}
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_accounts(sql_filter, sql_order, sql_limit, sql_restrict, sql_exclude):
db = get_db()
dbtp = db.table_prefix
s = SQL(db)
# s.select_from('acc', ['id', 'host_id', 'host_ip', 'router_ip', 'router_port', 'username', 'protocol_type', 'auth_type', 'state'], alt_name='a')
s.select_from('acc', [
'id', 'host_id', 'username', 'protocol_type', 'auth_type', 'state',
'username_prompt', 'password_prompt'
],
alt_name='a')
s.left_join('host', ['name', 'desc'],
join_on='h.id=a.host_id',
alt_name='h',
out_map={'name': 'host_name'})
str_where = ''
_where = list()
if len(sql_restrict) > 0:
for k in sql_restrict:
if k == 'group_id':
_where.append(
'a.id IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'
.format(dbtp, TP_GROUP_ACCOUNT, sql_restrict[k]))
else:
log.w('unknown restrict field: {}\n'.format(k))
if len(sql_exclude) > 0:
for k in sql_exclude:
if k == 'group_id':
_where.append(
'a.id NOT IN (SELECT mid FROM {}group_map WHERE type={} AND gid={})'
.format(dbtp, TP_GROUP_ACCOUNT, sql_exclude[k]))
elif k == 'ops_policy_id':
_where.append(
'a.id NOT IN (SELECT rid FROM {dbtp}ops_auz WHERE policy_id={pid} AND rtype={rtype})'
.format(dbtp=dbtp, pid=sql_exclude[k], rtype=TP_ACCOUNT))
else:
log.w('unknown exclude field: {}\n'.format(k))
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append(
'(a.username LIKE "%{filter}%" OR a.host_ip LIKE "%{filter}%" OR a.router_ip LIKE "%{filter}%" OR h.name LIKE "%{filter}%" OR h.desc LIKE "%{filter}%")'
.format(filter=sql_filter[k]))
# _where.append('(a.username LIKE "%{filter}%")'.format(filter=sql_filter[k]))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'username' == sql_order['name']:
s.order_by('a.username', _sort)
elif 'protocol_type' == sql_order['name']:
s.order_by('a.protocol_type', _sort)
elif 'state' == sql_order['name']:
s.order_by('a.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, 1, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
if err != TPE_OK:
return err, 0, 1, None
# 得到主机id列表，然后查询相关主机的详细信息
host_ids = []
for _acc in s.recorder:
if _acc.host_id not in host_ids:
host_ids.append(_acc.host_id)
if len(host_ids) == 0:
return TPE_OK, 0, 1, None
s_host = SQL(db)
s_host.select_from(
'host', ['id', 'name', 'ip', 'router_ip', 'router_port', 'state'],
alt_name='h')
str_host_ids = ','.join([str(i) for i in host_ids])
s_host.where('h.id IN ({ids})'.format(ids=str_host_ids))
err = s_host.query()
if err != TPE_OK:
return err, 0, None
hosts = {}
for _host in s_host.recorder:
if _host.id not in hosts:
hosts[_host.id] = _host
for _acc in s.recorder:
_acc['_host'] = hosts[_acc.host_id]
return err, s.total_count, s.page_index, s.recorder