def test_schema_permission(context):
dble_conn = get_dble_conn(context)
for row in context.table:
# prepare table
test_conn = DBUtil(context.cfg_dble['dble']['ip'], row['user'],
row['password'], row['schema'],
context.cfg_dble['client_port'], context)
sql = "drop table if exists {0}".format(row['table'])
test_conn.query(sql)
sql = "create table {0}(id int, data varchar(10))".format(row['table'])
test_conn.query(sql)
sql = "insert into {0} values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd')".format(
row['table'])
dble_conn.query(sql)
# prepare sql
value = row['dml']
LOGGER.info("dml:{0}".format(value))
del_sql = "delete from {0} where id = 1".format(row['table'])
sel_sql = "select * from {0}".format(row['table'])
upd_sql = "update {0} set data='AAA' where id = 1".format(row['table'])
ins_sql = "insert into {0} values (5, 'eee')".format(row['table'])
lack_priv_msg = "The statement DML privilege check is not passed"
sqls_in_priv_order = [ins_sql, upd_sql, sel_sql, del_sql]
# do test
for priv, sql in zip(value, sqls_in_priv_order):
res, errs = test_conn.query(sql)
if priv == "1":
assert_that(errs, is_(None))
else:
assert_that(errs[1], contains_string(lack_priv_msg))
test_conn.close()
dble_conn.close()
def step_impl(context, mysqlID, host):
id = getattr(context, mysqlID)
node = get_node(host)
ip = node._ip
user = node.mysql_user
passwd = node.mysql_password
port = node.mysql_port
db = ''
sql = "kill {0}".format(id)
conn = DBUtil(ip, user, passwd, db, port, context)
res, err = conn.query(sql)
assert err is None, "kill connection is failed: {0}".format(err)
def step_impl(context, cmd, result, host):
node = get_node(host)
ip = node._ip
port = node._mysql_port
user = "******"
passwd = "111111"
db = ""
conn = DBUtil(ip, user, passwd, db, port, context)
if hasattr(context, result):
for r in getattr(context, result):
adminsql = "{0} {1}".format(cmd, r[3])
conn.query(adminsql)
def test_readonly_schema(context):
text = json.loads(context.text)
dble_conn = get_dble_conn(context)
for item in text:
test_conn = DBUtil(context.cfg_dble['dble']['ip'], item['user'],
item['password'], item['schema'],
context.cfg_dble['client_port'], context)
sql = "drop table if exists {0}".format(item['table'])
dble_conn.query(sql)
sql = "create table {0}(id int, data varchar(10))".format(
item['table'])
dble_conn.query(sql)
sql = "insert into {0} values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd')".format(
item['table'])
dble_conn.query(sql)
permission = []
value = int(item['dml'])
LOGGER.info("dml: {0}".format(value / 1))
for i in range(4):
if value % 10 == 1:
if i == 0:
permission.append("DELETE")
if i == 1:
permission.append("SELECT")
if i == 2:
permission.append("UPDATE")
if i == 3:
permission.append("INSERT")
value = value / 10
LOGGER.info("permission: {0}".format(permission))
del_sql = "delete from {0} where id = 1".format(item['table'])
sel_sql = "select * from {0}".format(item['table'])
upd_sql = "update {0} set data='AAA' where id = 1".format(
item['table'])
ins_sql = "insert into {0} values (5, 'eee')".format(item['table'])
if "DELETE" in permission:
res, errs = test_conn.query(del_sql)
assert_that(errs[1], contains_string('User READ ONLY'))
if "SELECT" in permission:
res, errs = test_conn.query(sel_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(sel_sql)
assert_that(
errs[1],
contains_string(
'The statement DML privilege check is not passed'))
if "UPDATE" in permission:
res, errs = test_conn.query(upd_sql)
assert_that(errs[1], contains_string('User READ ONLY'))
if "INSERT" in permission:
res, errs = test_conn.query(ins_sql)
assert_that(errs[1], contains_string('User READ ONLY'))
test_conn.close()
dble_conn.close()
def get_dble_conn(context, default_db="schema1", node=None):
if node is None:
node = get_node(context.dbles, "dble-1")
conn = DBUtil(node.ip, context.cfg_dble['client_user'],
context.cfg_dble['client_password'], default_db,
context.cfg_dble['client_port'], context)
return conn
def get_compare_conn(context, default_db="schema1"):
m_ip = context.cfg_mysql['compare_mysql']['master1']['ip']
m_port = context.cfg_mysql['compare_mysql']['master1']['port']
m_user = context.cfg_mysql['user']
m_passwd = context.cfg_mysql['password']
conn_mysql = DBUtil(m_ip, m_user, m_passwd, default_db, m_port, context)
conn_dble = get_dble_conn(context, default_db)
return conn_mysql, conn_dble
def get_compare_conn(context, default_db="schema1"):
node = get_node("mysql")
m_ip = node.ip
m_port = node.mysql_port
m_user = node.mysql_user
m_passwd = node.mysql_password
conn_mysql = DBUtil(m_ip, m_user, m_passwd, default_db, m_port, context)
conn_dble = get_dble_conn(context, default_db)
return conn_mysql, conn_dble
def get_admin_conn(context, user="", passwd=""):
node = get_node("dble-1")
if len(user.strip()) == 0:
user = node.manager_user
if len(passwd.strip()) == 0:
passwd = str(node.manager_password)
conn = None
try:
conn = DBUtil(node.ip, user, passwd, "", node.manager_port, context)
except MySQLdb.Error, e:
assert False, "create manager conn meets error:{0}".format(e.args)
def get_admin_conn(context, user="", passwd=""):
if len(user.strip()) == 0:
user = context.cfg_dble['manager_user']
if len(passwd.strip()) == 0:
passwd = str(context.cfg_dble['manager_password'])
conn = None
try:
conn = DBUtil(context.cfg_dble['dble']['ip'], user, passwd, "",
context.cfg_dble['manager_port'], context)
except MySQLdb.Error, e:
assert False, "create manager conn meets error:{0}".format(e.args)
def dble_mng_connect_test(context, ip):
user = context.cfg_dble['manager_user']
passwd = str(context.cfg_dble['manager_password'])
port = context.cfg_dble['manager_port']
conn = None
isSuccess = False
max_try = 5
while conn is None:
try:
conn = DBUtil(ip, user, passwd, '', port, context)
except MySQLdb.Error, e:
context.logger.info("connect to {0} err:{1}".format(ip, e))
conn = None
finally:
def dble_mng_connect_test(context, ip):
node = get_node("dble-1")
user = node.manager_user
passwd = str(node.manager_password)
port = node.manager_port
conn = None
isSuccess = False
max_try = 5
while conn is None:
try:
conn = DBUtil(ip, user, passwd, '', port, context)
except MySQLdb.Error, e:
context.logger.info("connect to {0} err:{1}".format(ip, e))
conn = None
finally:
def create_node_conn(context):
context.manager_conn = get_admin_conn(context)
sql = "show @@datanode"
result, error = context.manager_conn.query(sql)
context.manager_conn.close()
datanode = {}
if type(result) == tuple:
for i in range(len(result)):
datanode[result[i][0]] = result[i][1]
port = 3306
node_conn = {}
for node in datanode.keys():
user = context.cfg_mysql['user']
password = context.cfg_mysql['password']
host = datanode[node].split('/')[0]
db = datanode[node].split('/')[1]
LOGGER.info("{0} create, host:{1}, db:{2}".format(node, host, db))
conn = DBUtil(host, user, password, db, port, context)
node_conn[node] = conn
return node_conn
def get_dble_connect(context, host_name, default_db="schema1"):
node = get_node(host_name)
conn = DBUtil(node.ip, node.client_user, node.client_password, default_db,
node.client_port, context)
return conn
def get_dble_conn(context, default_db="schema1", node=None):
if node is None:
node = get_node("dble-1")
conn = DBUtil(node.ip, node.client_user, node.client_password, default_db,
node.client_port, context)
return conn
def test_schema_table(context):
text = json.loads(context.text)
dble_conn = get_dble_conn(context)
node = get_node("dble-1")
for item in text:
test_conn = DBUtil(node.ip, item['user'], item['password'], item['schema'],
node.client_port, context)
sql = "drop table if exists {0}".format(item['single_table'])
test_conn.query(sql)
sql = "create table {0}(id int, data varchar(10))".format(item['single_table'])
test_conn.query(sql)
sql = "insert into {0} values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd')".format(item['single_table'])
dble_conn.query(sql)
schema_permission = []
value = int(item['schema_dml'])
LOGGER.info("dml: {0}".format(value / 1))
for i in range(4):
if value % 10 == 1:
if i == 0:
schema_permission.append("DELETE")
if i == 1:
schema_permission.append("SELECT")
if i == 2:
schema_permission.append("UPDATE")
if i == 3:
schema_permission.append("INSERT")
value = value / 10
LOGGER.info("permission: {0}".format(schema_permission))
del_sql = "delete from {0} where id = 1".format(item['single_table'])
sel_sql = "select * from {0}".format(item['single_table'])
upd_sql = "update {0} set data='AAA' where id = 1".format(item['single_table'])
ins_sql = "insert into {0} values (5, 'eee')".format(item['single_table'])
if "DELETE" in schema_permission:
res, errs = test_conn.query(del_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(del_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
if "SELECT" in schema_permission:
res, errs = test_conn.query(sel_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(sel_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
if "UPDATE" in schema_permission:
res, errs = test_conn.query(upd_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(upd_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
if "INSERT" in schema_permission:
res, errs = test_conn.query(ins_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(ins_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
for conf_table in item['tables_config']['tables']:
sql = "drop table if exists {0}".format(conf_table['table'])
test_conn.query(sql)
sql = "create table {0}(id int, data varchar(10))".format(conf_table['table'])
test_conn.query(sql)
sql = "insert into {0} values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd')".format(conf_table['table'])
dble_conn.query(sql)
table_permission = []
value = int(conf_table['dml'])
for i in range(4):
if value % 10 == 1:
if i == 0:
table_permission.append("DELETE")
if i == 1:
table_permission.append("SELECT")
if i == 2:
table_permission.append("UPDATE")
if i == 3:
table_permission.append("INSERT")
value = value / 10
del_sql = "delete from {0} where id = 1".format(conf_table['table'])
sel_sql = "select * from {0}".format(conf_table['table'])
upd_sql = "update {0} set data='AAA' where id = 1".format(conf_table['table'])
ins_sql = "insert into {0} values (5, 'eee')".format(conf_table['table'])
join_sql = "select a.*,b.* from {0} a,{1} b where a.id = b.id".format(item['single_table'], conf_table['table'])
if "DELETE" in table_permission:
res, errs = test_conn.query(del_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(del_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
if "SELECT" in table_permission:
res, errs = test_conn.query(sel_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(sel_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
if "SELECT" not in table_permission or "SELECT" not in schema_permission:
res, errs = test_conn.query(join_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
else:
res, errs = test_conn.query(join_sql)
assert_that(errs, is_(None))
if "UPDATE" in table_permission:
res, errs = test_conn.query(upd_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(upd_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
if "INSERT" in table_permission:
res, errs = test_conn.query(ins_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(ins_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
sql = "drop table if exists {0}".format(conf_table['table'])
test_conn.query(sql)
test_conn.close()
dble_conn.close()