def post():
form, error = JsonParser('user_id', 'db_user', 'db_password',
'db_database', 'db_priv',
Argument('desc', nullable=True,
required=False)).parse()
if error is None:
if not User.query.filter_by(id=form.user_id).first():
return json_response(message="管理员账号错误")
if UserInfo.query.filter_by(user_id=form.user_id,
db_user=form.db_user).first():
return json_response(message="账号已存在，不能重复创建")
cli = User.query.get_or_404(form.user_id)
try:
with MysqlClient(ip=cli.db_host,
user=cli.db_user,
password=cli.db_password,
port=cli.db_port,
db=form.db_database) as f:
f.grant_priv(form.db_user, form.db_password, form.db_database,
form.db_priv)
except Exception as e:
return json_response(message='连接失败')
host = UserInfo(user_id=form.user_id,
db_user=form.db_user,
db_password=form.db_password,
desc=form.desc)
host.save()
userpriv = UserPriv(account_id=host.id,
db_database=form.db_database,
db_priv=form.db_priv)
userpriv.save()
return json_response(host)
return json_response(message=error)
def changeprivileges(priv_id):
form, error = JsonParser('db_priv', 'user_id', 'account_id').parse()
if form.db_priv == '只读':
form.db_priv = '0'
elif form.db_priv == '读写':
form.db_priv = '1'
else:
return json_response(message='权限传递错误')
if error is None:
host = UserPriv.query.get_or_404(priv_id)
account = UserInfo.query.get_or_404(form.account_id)
cli = User.query.get_or_404(form.user_id)
try:
with MysqlClient(ip=cli.db_host,
user=cli.db_user,
password=cli.db_password,
port=cli.db_port) as f:
f.revoke_priv(account.db_user, host.db_database, host.db_priv)
f.grant_priv(account.db_user, account.db_password,
host.db_database, form.db_priv, 0)
host.update(db_priv=form.db_priv)
return json_response()
except Exception as e:
return json_response(message='连接失败')
return json_response(message=error)
def adddatabase(account_id):
form, error = JsonParser('db_database', 'db_priv').parse()
account = UserInfo.query.get_or_404(account_id)
cli = User.query.get_or_404(account.user_id)
if error is None:
try:
with MysqlClient(ip=cli.db_host,
user=cli.db_user,
password=cli.db_password,
port=cli.db_port) as f:
res = f.basename()
sql = 'create database ' + form.db_database
if form.db_database in res:
return json_response(message='数据库已存在不能重复创建')
f.execute(sql)
f.grant_priv(account.db_user, '', form.db_database,
form.db_priv, 0)
userpriv = UserPriv(account_id=account_id,
db_database=form.db_database,
db_priv=form.db_priv)
userpriv.save()
return json_response()
except Exception as e:
return json_response(message='连接失败')
return json_response(message=error)
def get_valid(user_id):
cli = User.query.get_or_404(user_id)
if cli.type.lower() == 'mysql':
try:
with MysqlClient(ip=cli.db_host,
user=cli.db_user,
password=cli.db_password,
port=cli.db_port):
return json_response()
except Exception as e:
return json_response(message='连接失败')
elif cli.type.lower() == 'mongodb':
try:
f = MongoDBClient(ip=cli.db_host,
user=cli.db_user,
password=cli.db_password,
port=cli.db_port,
db='admin')
if f.list_collections('admin'):
return json_response()
except Exception as e:
json_response(message='mongodb暂时无法验证')
# return json_response(message='mongodb暂时无法验证')
elif cli.type.lower() == 'redis':
return json_response(message='redis暂时无法验证')
else:
return json_response(message='数据类型错误')
def get_valid(user_id):
cli = User.query.get_or_404(user_id)
try:
with MysqlClient(ip=cli.db_host, user=cli.db_user, password=cli.db_password, port=cli.db_port):
#mysql.mysql_valid(ip=cli.db_host, user=cli.db_user, password=cli.db_password, port=cli.db_port):
return json_response()
except Exception as e:
return json_response(message='连接失败')
def mysql_priv(ip,user,password,port,db,priv):
try:
with MysqlClient(ip=ip, user=user, password=password, port=port) as f:
if priv == 0:
sql1 = "create user '"+form.db_user+"'@'%' "+"identified by '"+form.db_password+"';"
sql2 = "grant select on "+form.db_database+".* to '"+form.db_user+"'@'%';"
except Exception as e:
return False
def get_db(user_id):
cli = User.query.get_or_404(user_id)
try:
with MysqlClient(ip=cli.db_host,
user=cli.db_user,
password=cli.db_password,
port=cli.db_port) as f:
res = f.basename()
return json_response(res)
except Exception as e:
return json_response(message='连接失败')
def mysql_valid(ip,user,password,port):
try:
ip = ip
user = user
password = password
port = port
except Exception as e:
return 'KeyError'
else:
try:
with MysqlClient(ip=ip, user=user, password=password, port=port):
return True
except Exception as e:
return False
def resetpassword(account_id):
form, error = JsonParser('new_password').parse()
if error is None:
user_info = UserInfo.query.get_or_404(account_id)
cli = User.query.get_or_404(user_info.user_id)
try:
with MysqlClient(ip=cli.db_host,
user=cli.db_user,
password=cli.db_password,
port=cli.db_port) as f:
res = f.reset_password(user_info.db_user, form.new_password)
#return json_response(res)
except Exception as e:
return json_response(message='连接失败')
data = user_info.update(db_password=form.new_password)
return json_response(data)
return json_response(message=error)
def delete_db(user_id, db_database):
# form, error = JsonParser('db_database').parse()
cli = User.query.get_or_404(user_id)
db_data = UserPriv.query.filter_by(db_database=db_database).count()
if db_data > 0:
return json_response(message='数据库关联的有账号，请先解绑用户，再删除')
# if error is None:
try:
with MysqlClient(ip=cli.db_host,
user=cli.db_user,
password=cli.db_password,
port=cli.db_port) as f:
res = f.basename()
if db_database in res:
sql = 'drop database ' + db_database
f.execute(sql)
return json_response()
return json_response(message='数据库不存在')
except Exception as e:
return json_response(message='连接失败')
def get_valid_db(user_id):
account = UserInfo.query.get_or_404(user_id)
cli = User.query.get_or_404(account.user_id)
priv = UserPriv.query.filter_by(account_id=account.id)
data_record = [x.to_json() for x in priv.all()]
try:
with MysqlClient(ip=cli.db_host,
user=cli.db_user,
password=cli.db_password,
port=cli.db_port) as f:
res = list_only_to_json(f.basename())
item = []
for i in data_record:
item.append(i['db_database'])
for i in res:
for j in item:
if i['name'] == j:
i['status'] = '1'
return json_response(res)
except Exception as e:
return json_response(message='连接失败')
def delete(user_id):
host = UserPriv.query.get_or_404(user_id)
host_info = UserPriv.query.filter_by(account_id=host.account_id)
account = UserInfo.query.get_or_404(host.account_id)
cli = User.query.get_or_404(account.user_id)
is_delete = 0
try:
with MysqlClient(ip=cli.db_host,
user=cli.db_user,
password=cli.db_password,
port=cli.db_port) as f:
if host_info.count() == 1:
is_delete = 1
host.delete()
account.delete()
else:
host.delete()
f.revoke_priv(account.db_user, host.db_database, host.db_priv,
is_delete)
return json_response()
except Exception as e:
return json_response(message='连接失败')
def mysql_conn(ip,user,password,port):
try:
with MysqlClient(ip=ip, user=user, password=password, port=port) as f:
return f
except Exception as e:
return False