def verify_user(name, password): cfg = app_cfg() db = get_db() sql = 'SELECT `account_id`, `account_type`, `account_name`, `account_pwd`, `account_lock` FROM `{}account` WHERE `account_name`="{}";'.format(db.table_prefix, name) db_ret = db.query(sql) if db_ret is None: # 特别地,如果无法取得数据库连接,有可能是新安装的系统,尚未建立数据库,此时应该处于维护模式 # 因此可以特别地处理用户验证:用户名admin,密码admin可以登录为管理员 if cfg.app_mode == APP_MODE_MAINTENANCE: if name == 'admin' and password == 'admin': return 1, 100, 'admin', 0 return 0, 0, '', 0 if len(db_ret) != 1: return 0, 0, '', 0 user_id = db_ret[0][0] account_type = db_ret[0][1] name = db_ret[0][2] locked = db_ret[0][4] if locked == 1: return 0, 0, '', locked if not sec_verify_password(password, db_ret[0][3]): # 按新方法验证密码失败,可能是旧版本的密码散列格式,再尝试一下 if db_ret[0][3] != hashlib.sha256(password.encode()).hexdigest(): return 0, 0, '', locked else: # 发现此用户的密码散列格式还是旧的,更新成新的吧! _new_sec_password = sec_generate_password(password) sql = 'UPDATE `{}account` SET `account_pwd`="{}" WHERE `account_id`={}'.format(db.table_prefix, _new_sec_password, int(user_id)) db.exec(sql) return user_id, account_type, name, locked
def add_user(user_name, user_pwd, user_desc): db = get_db() sql = 'SELECT `account_id` FROM `{}account` WHERE `account_name`="{}";'.format(db.table_prefix, user_name) db_ret = db.query(sql) if db_ret is None or len(db_ret) != 0: return -100 sec_password = sec_generate_password(user_pwd) sql = 'INSERT INTO `{}account` (`account_type`, `account_name`, `account_pwd`, `account_status`,' \ '`account_lock`,`account_desc`) VALUES (1,"{}","{}",0,0,"{}")'.format(db.table_prefix, user_name, sec_password, user_desc) ret = db.exec(sql) if ret: return 0 return -101
def modify_pwd(old_pwd, new_pwd, user_id): db = get_db() sql = 'SELECT `account_pwd` FROM `{}account` WHERE `account_id`={};'.format(db.table_prefix, int(user_id)) db_ret = db.query(sql) if db_ret is None or len(db_ret) != 1: return -100 if not sec_verify_password(old_pwd, db_ret[0][0]): # 按新方法验证密码失败,可能是旧版本的密码散列格式,再尝试一下 if db_ret[0][0] != hashlib.sha256(old_pwd.encode()).hexdigest(): return -101 _new_sec_password = sec_generate_password(new_pwd) sql = 'UPDATE `{}account` SET `account_pwd`="{}" WHERE `account_id`={}'.format(db.table_prefix, _new_sec_password, int(user_id)) db_ret = db.exec(sql) if db_ret: return 0 else: return -102
def reset_user(user_id): db = get_db() _new_sec_password = sec_generate_password('123456') sql = 'UPDATE `{}account` SET `account_pwd`="{}" WHERE `account_id`={};'.format(db.table_prefix, _new_sec_password, int(user_id)) return db.exec(sql)
def create_and_init(db, step_begin, step_end): try: _db_exec( db, step_begin, step_end, '创建表 account', """CREATE TABLE `{}account` ( `account_id` integer PRIMARY KEY {}, `account_type` int(11) DEFAULT 0, `account_name` varchar(32) DEFAULT NULL, `account_pwd` varchar(128) DEFAULT NULL, `account_status` int(11) DEFAULT 0, `account_lock` int(11) DEFAULT 0, `account_desc` varchar(255), `oath_secret` varchar(64), );""".format(db.table_prefix, db.auto_increment)) _db_exec( db, step_begin, step_end, '创建表 auth', """CREATE TABLE `{}auth`( `auth_id` INTEGER PRIMARY KEY {}, `account_name` varchar(255), `host_id` INTEGER, `host_auth_id` int(11) NOT NULL );""".format(db.table_prefix, db.auto_increment)) # 注意,这个key表原名为cert,考虑到其中存放的是ssh密钥对,与证书无关,因此改名为key # 这也是升级到数据库版本5的标志! _db_exec( db, step_begin, step_end, '创建表 key', """CREATE TABLE `{}key` ( `cert_id` integer PRIMARY KEY {}, `cert_name` varchar(255), `cert_pub` varchar(2048) DEFAULT '', `cert_pri` varchar(4096) DEFAULT '', `cert_desc` varchar(255) ); """.format(db.table_prefix, db.auto_increment)) _db_exec( db, step_begin, step_end, '创建表 config', """CREATE TABLE `{}config` ( `name` varchar(128) NOT NULL, `value` varchar(255), PRIMARY KEY (`name` ASC) );""".format(db.table_prefix)) _db_exec( db, step_begin, step_end, '创建表 group', """CREATE TABLE `{}group` ( `group_id` integer PRIMARY KEY {}, `group_name` varchar(255) DEFAULT '' );""".format(db.table_prefix, db.auto_increment)) _db_exec( db, step_begin, step_end, '创建表 host_info', """CREATE TABLE `{}host_info`( `host_id` integer PRIMARY KEY {}, `group_id` int(11) DEFAULT 0, `host_sys_type` int(11) DEFAULT 1, `host_ip` varchar(32) DEFAULT '', `host_port` int(11) DEFAULT 0, `protocol` int(11) DEFAULT 0, `host_lock` int(11) DEFAULT 0, `host_desc` varchar(255) DEFAULT '' );""".format(db.table_prefix, db.auto_increment)) _db_exec( db, step_begin, step_end, '创建表 auth_info', """CREATE TABLE `{}auth_info`( `id` INTEGER PRIMARY KEY {}, `host_id` INTEGER, `auth_mode` INTEGER, `user_name` varchar(255), `user_pswd` varchar(255), `user_param` varchar(255), `cert_id` INTEGER, `encrypt` INTEGER, `log_time` varchar(60) );""".format(db.table_prefix, db.auto_increment)) _db_exec( db, step_begin, step_end, '创建表 key', """CREATE TABLE `{}log` ( `id` INTEGER PRIMARY KEY {}, `session_id` varchar(32), `account_name` varchar(64), `host_ip` varchar(32), `host_port` INTEGER, `sys_type` INTEGER DEFAULT 0, `auth_type` INTEGER, `protocol` INTEGER, `user_name` varchar(64), `ret_code` INTEGER, `begin_time` INTEGER, `end_time` INTEGER, `log_time` varchar(64) );""".format(db.table_prefix, db.auto_increment)) _admin_sec_password = sec_generate_password('admin') _db_exec( db, step_begin, step_end, '建立管理员账号', 'INSERT INTO `{}account` VALUES (1, 100, "admin", "{}", 0, 0, "超级管理员", "");' .format(db.table_prefix, _admin_sec_password)) _db_exec( db, step_begin, step_end, '设定数据库版本', 'INSERT INTO `{}config` VALUES ("db_ver", "{}");'.format( db.table_prefix, db.DB_VERSION)) return True except: log.e('ERROR\n') return False