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