def execute_sql():
if not request.is_json:
return jsonify(msg="Missing JSON in request", success=0), 400
requester = get_jwt_claims()
request_data = request.get_json()
query_id = request_data.get('query_id', None)
query = helpers.get_record_from_id(SqlQuery, query_id)
connection_id = request_data.get('connection_id', None)
connection = helpers.get_record_from_id(Connection, connection_id)
raw_sql = query.raw_sql or request_data.get('raw_sql')
# viewer users cannot execute arbitrary sql
if not helpers.requester_has_write_privileges(requester):
return jsonify(msg='Current user does not have permission to execute query.', success=0), 401
if raw_sql:
try:
results = cm.execute_select_statement(conn=connection, raw_sql=raw_sql)
return jsonify(msg='Results provided.', results=results, success=1), 200
except AssertionError as e:
return jsonify(msg='Error: {}. No results'.format(e), success=0), 400
except exc.OperationalError as e:
return jsonify(msg='Error: {}. No results'.format(e), success=0), 400
else:
return jsonify(msg='No SQL provided.', success=0), 400
def test_post_to_delete_user_with_valid_data(self):
user_id = 42
username = '******'
user = test_utils.create_user(user_id=user_id, username=username)
personal_usergroup = user.get_personal_usergroup()
usergroup_id = personal_usergroup.id
response = self.post_to_delete_user(user_id=user_id)
response_dict = json.loads(response.data)
user = helpers.get_record_from_id(User, user_id)
usergroup = helpers.get_record_from_id(Usergroup, usergroup_id)
assert not user
assert not usergroup
def validate_sql_query_id(self, key, sql_query_id):
if not sql_query_id:
raise AssertionError('sql_query_id not provided')
if not helpers.get_record_from_id(SqlQuery, sql_query_id):
raise AssertionError('sql_query_id not recognized')
return sql_query_id
def edit_query():
if not request.is_json:
return jsonify(msg="Missing JSON in request", success=0), 400
request_data = request.get_json()
query_id = request_data.get('query_id')
requester = get_jwt_claims()
requester_is_active = requester['is_active']
query = helpers.get_record_from_id(SqlQuery, query_id)
if not query:
return jsonify(msg='Provided query_id not found.', success=0), 400
if not requester_is_active:
return jsonify(msg="Your account is no longer active.", success=0), 401
if not helpers.requester_has_write_privileges(requester):
return jsonify(msg="User must have write privileges to edit querys.", success=0), 401
try:
query = helpers.edit_query_from_dict(request_data)
return jsonify(msg='SqlQuery successfully edited.', query=query.get_dict(), success=1), 200
except AssertionError as exception_message:
return jsonify(msg='Error: {}. SqlQuery not edited'.format(exception_message), success=0), 400
def edit_publication():
if not request.is_json:
return jsonify(msg="Missing JSON in request", success=0), 400
request_data = request.get_json()
publication_id = request_data.get('publication_id')
requester = get_jwt_claims()
requester_is_active = requester['is_active']
publication = helpers.get_record_from_id(Publication, publication_id)
if not publication:
return jsonify(msg='Provided publication_id not found.', success=0), 400
if not requester_is_active:
return jsonify(msg="Your account is no longer active.", success=0), 401
if not helpers.requester_has_write_privileges(requester):
return jsonify(msg="User must have write privileges to edit publications.", success=0), 401
try:
publication = helpers.edit_publication_from_dict(request_data)
return jsonify(msg='Publication successfully edited.', publication=publication.get_dict(), success=1), 200
except AssertionError as exception_message:
return jsonify(msg='Error: {}. Publication not edited'.format(exception_message), success=0), 400
def edit_usergroup():
if not request.is_json:
return jsonify(msg="Missing JSON in request", success=0), 400
request_data = request.get_json()
usergroup_id = request_data.get('usergroup_id')
requester = get_jwt_claims()
requester_is_active = requester['is_active']
usergroup = helpers.get_record_from_id(Usergroup, usergroup_id)
if not usergroup:
return jsonify(msg='Provided usergroup_id not found.', success=0), 400
if usergroup.personal_group:
return jsonify(msg='Personal usergroups cannot be edited', success=0), 401
if not requester_is_active:
return jsonify(msg="Your account is no longer active.", success=0), 401
if not helpers.requester_has_write_privileges(requester):
return jsonify(msg="User must have write privileges to edit other usergroups.", success=0), 401
try:
usergroup = helpers.edit_usergroup_from_dict(request_data)
return jsonify(msg='Usergroup successfully edited.', usergroup=usergroup.get_dict(), success=1), 200
except AssertionError as exception_message:
return jsonify(msg='Error: {}. Usergroup not edited'.format(exception_message), success=0), 400
def delete_user():
if not request.is_json:
return jsonify(msg="Missing JSON in request", success=0), 400
request_data = request.get_json()
user_id = request_data.get('user_id', None)
user = helpers.get_record_from_id(User, user_id)
requester = get_jwt_claims()
if not helpers.requester_has_admin_privileges(requester):
return jsonify(msg="User must have admin privileges to delete a user.", success=0), 401
if not user:
return jsonify(msg='User not found', success=0), 400
if user.role == 'superuser' and requester['role'] != 'superuser':
return jsonify(msg="User must have superuser privileges to delete a superuser.", success=0), 401
if requester['user_id'] == user_id:
return jsonify(msg="User cannot delete self.", success=0), 401
usergroup = user.get_personal_usergroup()
db.session.delete(user)
db.session.delete(usergroup)
db.session.commit()
return jsonify(msg='User deleted.', success=1), 200
def edit_user():
if not request.is_json:
return jsonify(msg="Missing JSON in request", success=0), 400
request_data = request.get_json()
user_id = request_data.get('user_id')
requester = get_jwt_claims()
requester_is_editing_self = requester['user_id'] == user_id
requester_is_active = requester['is_active']
user = helpers.get_record_from_id(User, user_id)
if not user:
return jsonify(msg='Provided user_id not found.', success=0), 400
if not requester_is_active:
return jsonify(msg="Your account is no longer active.", success=0), 401
# non-admin can only edit themselves
if not helpers.requester_has_admin_privileges(requester) and not requester_is_editing_self:
return jsonify(msg="User must have admin privileges to edit other users.", success=0), 401
# only admin can edit user roles
if not helpers.requester_has_admin_privileges(requester) and request_data.get('role') != user.role:
return jsonify(msg="User must have admin privileges to edit a user's role.", success=0), 401
try:
user = helpers.edit_user_from_dict(request_data)
return jsonify(msg='User successfully edited.', user=user.get_dict(), success=1), 200
except AssertionError as exception_message:
return jsonify(msg='Error: {}. User not edited'.format(exception_message), success=0), 400
def edit_contact():
if not request.is_json:
return jsonify(msg="Missing JSON in request", success=0), 400
request_data = request.get_json()
contact_id = request_data.get('contact_id')
requester = get_jwt_claims()
requester_is_active = requester['is_active']
contact = helpers.get_record_from_id(Contact, contact_id)
if not contact:
return jsonify(msg='Provided contact_id not found.', success=0), 400
if not requester_is_active:
return jsonify(msg="Your account is no longer active.", success=0), 401
if not helpers.requester_has_write_privileges(requester):
return jsonify(msg="User must have write privileges to edit contacts.", success=0), 401
try:
contact = helpers.edit_contact_from_dict(request_data)
return jsonify(msg='Contact successfully edited.', contact=contact.get_dict(), success=1), 200
except AssertionError as exception_message:
return jsonify(msg='Error: {}. Contact not edited'.format(exception_message), success=0), 400
def validate_connection_id(self, key, connection_id):
if not connection_id:
raise AssertionError('connection_id not provided')
if not helpers.get_record_from_id(Connection, connection_id):
raise AssertionError('connection_id not recognized')
return connection_id
def test_edit_label_with_bad_connection_id(self):
conn_id = 999999
response = self.patch_to_edit_connection(connection_id=conn_id)
connection = helpers.get_record_from_id(Connection, conn_id)
assert response.status_code == 400
assert not connection
def test_edit_label_with_bad_publication_id(self):
conn_id = 999999
response = self.patch_to_edit_publication(publication_id=conn_id)
publication = helpers.get_record_from_id(Publication, conn_id)
assert response.status_code == 400
assert not publication
def test_edit_label_with_bad_query_id(self):
conn_id = 999999
response = self.patch_to_edit_queries(query_id=conn_id)
query = helpers.get_record_from_id(SqlQuery, conn_id)
assert response.status_code == 400
assert not query
def test_edit_label_with_bad_chart_id(self):
conn_id = 999999
response = self.patch_to_edit_charts(chart_id=conn_id)
chart = helpers.get_record_from_id(Chart, conn_id)
assert response.status_code == 400
assert not chart
def test_edit_label_with_bad_usergroup_id(self):
group_id = 999999
response = self.patch_to_edit_usergroups(usergroup_id=group_id)
usergroup = helpers.get_record_from_id(Usergroup, group_id)
assert response.status_code == 400
assert not usergroup
def test_edit_contact_with_bad_contact_id(self):
conn_id = 999999
response = self.patch_to_edit_contacts(contact_id=conn_id)
contact = helpers.get_record_from_id(Contact, conn_id)
assert response.status_code == 400
assert not contact
def test_delete_contact_removes_contact(self):
contact_id = 1234
test_utils.create_contact(contact_id=contact_id)
self.post_to_delete_contacts(contact_id=contact_id)
contact = helpers.get_record_from_id(Contact, contact_id)
assert not contact
def test_delete_usergroup_remove_usergroup(self):
usergroup_id = 42
test_utils.create_usergroup(usergroup_id=usergroup_id)
self.post_to_delete_usergroup(usergroup_id=usergroup_id)
usergroup = helpers.get_record_from_id(Usergroup, usergroup_id)
assert not usergroup
def test_delete_connection_removes_connection(self):
connection_id = 1234
test_utils.create_connection(connection_id=connection_id)
self.post_to_delete_connection(connection_id=connection_id)
connection = helpers.get_record_from_id(Connection, connection_id)
assert not connection
def test_delete_report_removes_report(self):
report_id = 1234
test_utils.create_report(report_id=report_id)
self.post_to_delete_reports(report_id=report_id)
report = helpers.get_record_from_id(Report, report_id)
assert not report
def test_delete_publication_removes_publication(self):
publication_id = 1234
test_utils.create_publication(publication_id=publication_id)
self.post_to_delete_publications(publication_id=publication_id)
publication = helpers.get_record_from_id(Publication, publication_id)
assert not publication
def test_delete_contact_requires_writer_privileges(self):
contact_id = 1234
test_utils.create_contact(contact_id=contact_id)
self.post_to_delete_contacts(contact_id=contact_id, token_type='viewer')
contact = helpers.get_record_from_id(Contact, contact_id)
assert contact
def test_delete_query_requires_writer_privileges(self):
query_id = 1234
test_utils.create_query(query_id=query_id)
self.post_to_delete_queries(query_id=query_id, token_type='viewer')
query = helpers.get_record_from_id(SqlQuery, query_id)
assert query
def test_delete_query_removes_query(self):
query_id = 1234
test_utils.create_query(query_id=query_id)
self.post_to_delete_queries(query_id=query_id)
query = helpers.get_record_from_id(SqlQuery, query_id)
assert not query
def test_delete_report_requires_writer_privileges(self):
report_id = 1234
test_utils.create_report(report_id=report_id)
self.post_to_delete_reports(report_id=report_id, token_type='viewer')
report = helpers.get_record_from_id(Report, report_id)
assert report
def test_delete_chart_requires_writer_privileges(self):
chart_id = 1234
test_utils.create_chart(chart_id=chart_id)
self.post_to_delete_charts(chart_id=chart_id, token_type='viewer')
chart = helpers.get_record_from_id(Chart, chart_id)
assert chart
def test_delete_chart_removes_chart(self):
chart_id = 1234
test_utils.create_chart(chart_id=chart_id)
self.post_to_delete_charts(chart_id=chart_id)
chart = helpers.get_record_from_id(Chart, chart_id)
assert not chart
def test_delete_publication_requires_writer_privileges(self):
publication_id = 1234
test_utils.create_publication(publication_id=publication_id)
self.post_to_delete_publications(publication_id=publication_id,
token_type='viewer')
publication = helpers.get_record_from_id(Publication, publication_id)
assert publication
def test_delete_usergroup_requires_admin_privileges(self):
usergroup_id = 42
test_utils.create_usergroup(usergroup_id=usergroup_id)
self.post_to_delete_usergroup(usergroup_id=usergroup_id,
token_type='writer')
usergroup = helpers.get_record_from_id(Usergroup, usergroup_id)
assert usergroup
def test_add_usergroup_to_query_with_bad_user_id(self):
query_id = 1234
test_utils.create_query(query_id=query_id)
response = self.patch_to_edit_queries(query_id=query_id,
usergroup_ids=[99999])
query = helpers.get_record_from_id(SqlQuery, query_id)
assert response.status_code == 400
assert not query.usergroups
