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