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