Python SQL.reset方法代碼示例

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import reset [as 別名]
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)

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import reset [as 別名]
def create_user(handler, user):
    """
    創建一個用戶賬號
    """
    db = get_db()
    _time_now = tp_timestamp_utc_now()
    operator = handler.get_current_user()

    if 'type' not in user:
        user['type'] = TP_USER_TYPE_LOCAL
    if 'ldap_dn' not in user:
        user['ldap_dn'] = ''

    # 1. 判斷此賬號是否已經存在了
    s = SQL(db)
    err = s.reset().select_from('user', ['id']).where('user.username="{}"'.format(user['username'])).query()
    if err != TPE_OK:
        return err, 0
    if len(s.recorder) > 0:
        return TPE_EXISTS, 0

    # _password = tp_password_generate_secret(user['password'])
    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 (' \
          '{role}, "{username}", "{surname}", {user_type}, "{ldap_dn}", {auth_type}, "{password}", {state}, ' \
          '"{email}", {creator_id}, {create_time}, {last_login}, {last_chpass}, "{desc}");' \
          ''.format(db.table_prefix, role=user['role'], username=user['username'], surname=user['surname'],
                    user_type=user['type'], ldap_dn=user['ldap_dn'], auth_type=user['auth_type'], 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:
        return TPE_DATABASE, 0

    _id = db.last_insert_id()

    syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "創建用戶：{}".format(user['username']))

    # calc count of users.
    err, cnt = s.reset().count('user')
    if err == TPE_OK:
        tp_stats().user_counter_change(cnt)

    return TPE_OK, _id

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import reset [as 別名]
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="{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

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import reset [as 別名]
def generate_reset_password_token(handler, user_id):
    db = get_db()
    operator = handler.get_current_user()
    s = SQL(db)
    _time_now = tp_timestamp_utc_now()

    # 0. query user's email by user_id
    err = s.select_from('user', ['email'], alt_name='u').where('u.id={user_id}'.format(user_id=user_id)).query()
    if err != TPE_OK:
        return err, None, None
    if len(s.recorder) == 0:
        return TPE_DATABASE, None, None

    email = s.recorder[0].email

    # 1. clean all timed out tokens.
    s.reset().delete_from('user_rpt').where('create_time<{}'.format(_time_now - 24 * 60 * 60)).exec()

    # 2. find out if this user already have a token.
    err = s.reset().select_from('user_rpt', ['id'], alt_name='u').where('u.user_id={}'.format(user_id)).query()
    if err != TPE_OK:
        return err, None, None

    token = tp_generate_random(16)

    if len(s.recorder) == 0:
        sql = 'INSERT INTO `{dbtp}user_rpt` (user_id, token, create_time) VALUES ' \
              '({user_id}, "{token}", {create_time});' \
              ''.format(dbtp=db.table_prefix, user_id=user_id, token=token, create_time=_time_now)
        db_ret = db.exec(sql)
        if not db_ret:
            return TPE_DATABASE, None, None
    else:
        sql = 'UPDATE `{dbtp}user_rpt` SET token="{token}", create_time={create_time} WHERE user_id={user_id};' \
              ''.format(dbtp=db.table_prefix, token=token, create_time=_time_now, user_id=user_id)
        db_ret = db.exec(sql)
        if not db_ret:
            return TPE_DATABASE, None, None

    # syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "為用戶 {} 手動重置了密碼".format(name))

    return TPE_OK, email, token

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import reset [as 別名]
def remove_members(handler, policy_id, policy_type, ids):
    s = SQL(get_db())

    auz_ids = [str(i) for i in ids]

    # 將用戶從所在組中移除
    where = 'policy_id={} AND type={} AND id IN ({})'.format(policy_id, policy_type, ','.join(auz_ids))
    err = s.reset().delete_from('ops_auz').where(where).exec()
    if err != TPE_OK:
        return err

    #return TPE_OK
    return policy.rebuild_ops_auz_map()

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import reset [as 別名]
def remove_users(handler, users):
    db = get_db()
    s = SQL(db)

    str_users = ','.join([str(i) for i in users])

    # 1. 獲取用戶名稱，用於記錄係統日誌
    where = 'u.id IN ({})'.format(str_users)
    err = s.select_from('user', ['username'], alt_name='u').where(where).query()
    if err != TPE_OK:
        return err
    if len(s.recorder) == 0:
        return TPE_NOT_EXISTS

    str_names = '，'.join([n['username'] for n in s.recorder])

    sql_list = []

    # 將用戶從所在組中移除
    sql = 'DELETE FROM `{tpdp}group_map` WHERE type={t} AND mid IN ({ids});' \
          ''.format(tpdp=db.table_prefix, t=TP_GROUP_USER, ids=str_users)
    sql_list.append(sql)
    # 刪除用戶
    sql = 'DELETE FROM `{tpdp}user` WHERE id IN ({ids});'.format(tpdp=db.table_prefix, ids=str_users)
    sql_list.append(sql)
    # 將用戶從運維授權中移除
    sql = 'DELETE FROM `{}ops_auz` WHERE rtype={rtype} AND rid IN ({ids});' \
          ''.format(db.table_prefix, rtype=TP_USER, ids=str_users)
    sql_list.append(sql)
    sql = 'DELETE FROM `{}ops_map` WHERE u_id IN ({ids});'.format(db.table_prefix, ids=str_users)
    sql_list.append(sql)
    # 將用戶從審計授權中移除
    sql = 'DELETE FROM `{}audit_auz` WHERE rtype={rtype} AND rid IN ({ids});' \
          ''.format(db.table_prefix, rtype=TP_USER, ids=str_users)
    sql_list.append(sql)
    sql = 'DELETE FROM `{}audit_map` WHERE u_id IN ({ids});'.format(db.table_prefix, ids=str_users)
    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(str_names))

    # calc count of users.
    err, cnt = s.reset().count('user')
    if err == TPE_OK:
        tp_stats().user_counter_change(cnt)

    return TPE_OK

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import reset [as 別名]
def update_policy(handler, args):
    db = get_db()

    # 1. 判斷此賬號是否已經存在
    s = SQL(db)
    err = s.reset().select_from('ops_policy', ['id']).where('ops_policy.id={}'.format(args['id'])).query()
    if err != TPE_OK:
        return err
    if len(s.recorder) == 0:
        return TPE_NOT_EXISTS

    sql = 'UPDATE `{}ops_policy` SET `name`="{name}", `desc`="{desc}" WHERE `id`={p_id};' \
          ''.format(db.table_prefix,
                    name=args['name'], desc=args['desc'], p_id=args['id']
                    )
    db_ret = db.exec(sql)
    if not db_ret:
        return TPE_DATABASE

    return TPE_OK

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import reset [as 別名]
def create_policy(handler, args):
    """
    創建一個授權策略
    """
    db = get_db()
    _time_now = tp_timestamp_utc_now()

    # 1. 判斷此賬號是否已經存在了
    s = SQL(db)
    err = s.reset().select_from('ops_policy', ['id']).where('ops_policy.name="{}"'.format(args['name'])).query()
    if err != TPE_OK:
        return err, 0
    if len(s.recorder) > 0:
        return TPE_EXISTS, 0

    # 2. get total count
    sql = 'SELECT COUNT(*) FROM {}ops_policy'.format(db.table_prefix)
    db_ret = db.query(sql)
    if not db_ret or len(db_ret) == 0:
        return TPE_DATABASE, 0
    rank = db_ret[0][0] + 1

    sql = 'INSERT INTO `{}ops_policy` (`rank`, `name`, `desc`, `creator_id`, `create_time`) VALUES ' \
          '({rank}, "{name}", "{desc}", {creator_id}, {create_time});' \
          ''.format(db.table_prefix,
                    rank=rank, name=args['name'], desc=args['desc'],
                    creator_id=handler.get_current_user()['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(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "創建運維授權策略：{}".format(args['name']))

    return TPE_OK, _id

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import reset [as 別名]
def cleanup_storage(handler):
    # storage config
    sto = tp_cfg().sys.storage

    db = get_db()
    _now = tp_timestamp_utc_now()
    msg = []
    have_error = False

    s = SQL(db)
    chk_time = _now - sto.keep_log * 24 * 60 * 60

    if sto.keep_log > 0:
        # find out all sys-log to be remove
        s.select_from('syslog', ['id'], alt_name='s')
        s.where('s.log_time<{chk_time}'.format(chk_time=chk_time))
        err = s.query()
        if err != TPE_OK:
            have_error = True
            msg.append('清理係統日誌時發生錯誤：無法獲取係統日誌信息！')
            # return err, msg
        else:
            removed_log = len(s.recorder)
            if 0 == removed_log:
                msg.append('沒有滿足條件的係統日誌需要清除！')
            else:
                s.reset().delete_from('syslog').where('log_time<{chk_time}'.format(chk_time=chk_time))
                err = s.exec()
                if err != TPE_OK:
                    have_error = True
                    msg.append('清理係統日誌時發生錯誤：無法清除指定的係統日誌！')
                else:
                    msg.append('{} 條係統日誌已清除！'.format(removed_log))

    if sto.keep_record > 0:
        core_cfg = tp_cfg().core
        if not core_cfg.detected:
            have_error = True
            msg.append('清除指定會話錄像失敗：未能檢測到核心服務！')
        else:
            replay_path = core_cfg.replay_path
            if not os.path.exists(replay_path):
                have_error = True
                msg.append('清除指定會話錄像失敗：會話錄像路徑不存在（{}）！'.format(replay_path))
            else:
                # find out all record to be remove
                s.reset().select_from('record', ['id', 'protocol_type'], alt_name='r')
                s.where('r.time_begin<{chk_time}'.format(chk_time=chk_time))
                err = s.query()
                if err != TPE_OK:
                    have_error = True
                    msg.append('清除指定會話錄像失敗：無法獲取會話錄像信息！')
                elif len(s.recorder) == 0:
                    msg.append('沒有滿足條件的會話錄像需要清除！')
                else:
                    record_removed = 0
                    for r in s.recorder:
                        if r.protocol_type == TP_PROTOCOL_TYPE_RDP:
                            path_remove = os.path.join(replay_path, 'rdp', '{:09d}'.format(r.id))
                        elif r.protocol_type == TP_PROTOCOL_TYPE_SSH:
                            path_remove = os.path.join(replay_path, 'ssh', '{:09d}'.format(r.id))
                        elif r.protocol_type == TP_PROTOCOL_TYPE_TELNET:
                            path_remove = os.path.join(replay_path, 'telnet', '{:09d}'.format(r.id))
                        else:
                            have_error = True
                            msg.append('會話錄像記錄編號 {}，未知遠程訪問協議！'.format(r.id))
                            continue

                        if os.path.exists(path_remove):
                            # print('remove path', path_remove)
                            try:
                                shutil.rmtree(path_remove)
                            except:
                                have_error = True
                                msg.append('會話錄像記錄 {} 清除失敗，無法刪除目錄 {}！'.format(r.id, path_remove))

                        ss = SQL(db)
                        ss.delete_from('record').where('id={rid}'.format(rid=r.id))
                        ss.exec()

                        record_removed += 1

                    msg.append('{} 條會話錄像數據已清除！'.format(record_removed))

    if have_error:
        return TPE_FAILED, msg
    else:
        return TPE_OK, msg

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import reset [as 別名]
def remove_accounts(handler, host_id, acc_ids):
    """
    刪除遠程賬號
    """
    db = get_db()
    acc_count = len(acc_ids)
    acc_ids = ','.join([str(uid) for uid in acc_ids])

    s = SQL(db)
    # 1. 判斷是否存在
    s.select_from('host', ['name', 'ip', 'router_ip', 'router_port', 'acc_count'], alt_name='a')
    s.where('a.id={h_id}'.format(h_id=host_id, ids=acc_ids))
    err = s.query()
    if err != TPE_OK:
        return err
    if len(s.recorder) == 0:
        return TPE_NOT_EXISTS
    _h_name = s.recorder[0].name
    _h_ip = s.recorder[0].ip
    _h_router_ip = s.recorder[0].router_ip
    _h_router_port = s.recorder[0].router_port

    s.reset().select_from('acc', ['username'], alt_name='a')
    s.where('a.host_id={h_id} AND a.id IN ({ids}) '.format(h_id=host_id, ids=acc_ids))
    err = s.query()
    if err != TPE_OK:
        return err
    if len(s.recorder) == 0:
        return TPE_NOT_EXISTS

    acc_names = []
    for a in s.recorder:
        acc_name = '{}@{}'.format(a.username, _h_ip)
        if len(_h_router_ip) > 0:
            acc_name += '（由{}:{}路由）'.format(_h_router_ip, _h_router_port)
        acc_names.append(acc_name)

    sql_list = []

    sql = 'DELETE FROM `{}acc` WHERE host_id={} AND id IN ({});'.format(db.table_prefix, host_id, acc_ids)
    sql_list.append(sql)

    sql = 'DELETE FROM `{}group_map` WHERE type={} AND mid IN ({});'.format(db.table_prefix, TP_GROUP_ACCOUNT, acc_ids)
    sql_list.append(sql)

    # 更新主機相關賬號數量
    sql = 'UPDATE `{}host` SET acc_count=acc_count-{acc_count} WHERE id={host_id};'.format(db.table_prefix, acc_count=acc_count, host_id=host_id)
    sql_list.append(sql)

    sql = 'DELETE FROM `{}ops_auz` WHERE rtype={rtype} AND rid IN ({rid});'.format(db.table_prefix, rtype=TP_ACCOUNT, rid=acc_ids)
    sql_list.append(sql)

    sql = 'DELETE FROM `{}ops_map` WHERE a_id IN ({acc_id});'.format(db.table_prefix, acc_id=acc_ids)
    sql_list.append(sql)

    if not db.transaction(sql_list):
        return TPE_DATABASE

    # s.reset().select_from('host', ['acc_count'], alt_name='a')
    # s.where('a.id={h_id}'.format(h_id=host_id, ids=acc_ids))
    # err = s.query()
    # if err != TPE_OK:
    #     return err
    # if len(s.recorder) == 0:
    #     return TPE_NOT_EXISTS

    syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "刪除賬號：{}".format('，'.join(acc_names)))

    tp_stats().acc_counter_change(-1)

    return TPE_OK

# 需要導入模塊: from app.base.db import SQL [as 別名]
# 或者: from app.base.db.SQL import reset [as 別名]
def rebuild_ops_auz_map():
    _users = {}
    _hosts = {}
    _accs = {}
    _gusers = {}
    _ghosts = {}
    _gaccs = {}
    _groups = {}
    _policies = {}

    _p_users = {}
    _p_assets = {}

    _map = []

    db = get_db()
    dbtp = db.table_prefix
    db.exec('DELETE FROM {}ops_map'.format(dbtp))

    s = SQL(get_db())

    # 加載所有策略
    err = s.reset().select_from('ops_policy', ['id', 'rank', 'state'], alt_name='p').query()
    if err != TPE_OK:
        return err
    if 0 == len(s.recorder):
        return TPE_OK
    for i in s.recorder:
        _policies[i.id] = i

    # 加載所有的用戶
    err = s.reset().select_from('user', ['id', 'username', 'surname', 'state'], alt_name='u').query()
    if err != TPE_OK:
        return err
    if 0 == len(s.recorder):
        return TPE_OK
    for i in s.recorder:
        _users[i.id] = i

    # 加載所有的主機
    err = s.reset().select_from('host', ['id', 'name', 'ip', 'router_ip', 'router_port', 'state'], alt_name='h').query()
    if err != TPE_OK:
        return err
    if 0 == len(s.recorder):
        return TPE_OK
    for i in s.recorder:
        _hosts[i.id] = i

    # 加載所有的賬號
    err = s.reset().select_from('acc', ['id', 'host_id', 'username', 'protocol_type', 'protocol_port', 'auth_type', 'state'], alt_name='a').query()
    if err != TPE_OK:
        return err
    if 0 == len(s.recorder):
        return TPE_OK
    for i in s.recorder:
        _accs[i.id] = i

    # 加載所有的組
    err = s.reset().select_from('group', ['id', 'type', 'state'], alt_name='g').query()
    if err != TPE_OK:
        return err
    for i in s.recorder:
        _groups[i.id] = i
        if i.type == TP_GROUP_USER:
            _gusers[i.id] = []
        elif i.type == TP_GROUP_HOST:
            _ghosts[i.id] = []
        elif i.type == TP_GROUP_ACCOUNT:
            _gaccs[i.id] = []

    # 加載所有的組
    err = s.reset().select_from('group_map', ['id', 'type', 'gid', 'mid'], alt_name='g').query()
    if err != TPE_OK:
        return err
    for g in s.recorder:
        if g.type == TP_GROUP_USER:
            # if g.gid not in _gusers:
            #     _gusers[g.gid] = []
            _gusers[g.gid].append(_users[g.mid])
        elif g.type == TP_GROUP_HOST:
            # if g.gid not in _ghosts:
            #     _ghosts[g.gid] = []
            _ghosts[g.gid].append(_hosts[g.mid])
        elif g.type == TP_GROUP_ACCOUNT:
            # if g.gid not in _gaccs:
            #     _gaccs[g.gid] = []
            _gaccs[g.gid].append(_accs[g.mid])

    # 加載所有策略明細
    err = s.reset().select_from('ops_auz', ['id', 'policy_id', 'type', 'rtype', 'rid'], alt_name='o').query()
    if err != TPE_OK:
        return err
    if 0 == len(s.recorder):
        return TPE_OK

    # 分解各個策略中操作者和被操作資產的信息
    for i in s.recorder:
        if i.type == TP_POLICY_OPERATOR:

            if i.policy_id not in _p_users:
#.........這裏部分代碼省略.........