def test_sql_json_has_access(self):
main_db = self.get_main_database(db.session)
security_manager.add_permission_view_menu('database_access', main_db.perm)
db.session.commit()
main_db_permission_view = (
db.session.query(ab_models.PermissionView)
.join(ab_models.ViewMenu)
.join(ab_models.Permission)
.filter(ab_models.ViewMenu.name == '[main].(id:1)')
.filter(ab_models.Permission.name == 'database_access')
.first()
)
astronaut = security_manager.add_role('Astronaut')
security_manager.add_permission_role(astronaut, main_db_permission_view)
# Astronaut role is Gamma + sqllab + main db permissions
for perm in security_manager.find_role('Gamma').permissions:
security_manager.add_permission_role(astronaut, perm)
for perm in security_manager.find_role('sql_lab').permissions:
security_manager.add_permission_role(astronaut, perm)
gagarin = security_manager.find_user('gagarin')
if not gagarin:
security_manager.add_user(
'gagarin', 'Iurii', 'Gagarin', '*****@*****.**',
astronaut,
password='******')
data = self.run_sql('SELECT * FROM ab_user', '3', user_name='gagarin')
db.session.query(Query).delete()
db.session.commit()
self.assertLess(0, len(data['data']))
def test_clean_requests_after_db_grant(self):
session = db.session
# Case 3. Two access requests from gamma and gamma2
# Gamma gets database access, gamma2 access request granted
# Check if request by gamma has been deleted
gamma_user = security_manager.find_user(username='******')
access_request1 = create_access_request(
session, 'table', 'energy_usage', TEST_ROLE_1, 'gamma')
create_access_request(
session, 'table', 'energy_usage', TEST_ROLE_2, 'gamma2')
ds_1_id = access_request1.datasource_id
# gamma gets granted database access
database = session.query(models.Database).first()
security_manager.add_permission_view_menu('database_access', database.perm)
ds_perm_view = security_manager.find_permission_view_menu(
'database_access', database.perm)
security_manager.add_permission_role(
security_manager.find_role(DB_ACCESS_ROLE), ds_perm_view)
gamma_user.roles.append(security_manager.find_role(DB_ACCESS_ROLE))
session.commit()
access_requests = self.get_access_requests('gamma', 'table', ds_1_id)
self.assertTrue(access_requests)
# gamma2 request gets fulfilled
self.client.get(EXTEND_ROLE_REQUEST.format(
'table', ds_1_id, 'gamma2', TEST_ROLE_2))
access_requests = self.get_access_requests('gamma', 'table', ds_1_id)
self.assertFalse(access_requests)
gamma_user = security_manager.find_user(username='******')
gamma_user.roles.remove(security_manager.find_role(DB_ACCESS_ROLE))
session.commit()
def post_add(self, item: "DruidDatasourceModelView") -> None:
item.refresh_metrics()
security_manager.add_permission_view_menu("datasource_access",
item.get_perm())
if item.schema:
security_manager.add_permission_view_menu("schema_access",
item.schema_perm)
def post_add(self, datasource):
datasource.refresh_metrics()
security_manager.add_permission_view_menu("datasource_access",
datasource.get_perm())
if datasource.schema:
security_manager.add_permission_view_menu("schema_access",
datasource.schema_perm)
def test_sql_json_has_access(self):
main_db = get_main_database()
security_manager.add_permission_view_menu("database_access",
main_db.perm)
db.session.commit()
main_db_permission_view = (db.session.query(
ab_models.PermissionView).join(ab_models.ViewMenu).join(
ab_models.Permission
).filter(ab_models.ViewMenu.name == "[main].(id:1)").filter(
ab_models.Permission.name == "database_access").first())
astronaut = security_manager.add_role("Astronaut")
security_manager.add_permission_role(astronaut,
main_db_permission_view)
# Astronaut role is Gamma + sqllab + main db permissions
for perm in security_manager.find_role("Gamma").permissions:
security_manager.add_permission_role(astronaut, perm)
for perm in security_manager.find_role("sql_lab").permissions:
security_manager.add_permission_role(astronaut, perm)
gagarin = security_manager.find_user("gagarin")
if not gagarin:
security_manager.add_user(
"gagarin",
"Iurii",
"Gagarin",
"*****@*****.**",
astronaut,
password="******",
)
data = self.run_sql("SELECT * FROM ab_user", "3", user_name="gagarin")
db.session.query(Query).delete()
db.session.commit()
self.assertLess(0, len(data["data"]))
def test_sql_json_has_access(self):
main_db = get_main_database(db.session)
security_manager.add_permission_view_menu('database_access',
main_db.perm)
db.session.commit()
main_db_permission_view = (db.session.query(
ab_models.PermissionView).join(ab_models.ViewMenu).join(
ab_models.Permission
).filter(ab_models.ViewMenu.name == '[main].(id:1)').filter(
ab_models.Permission.name == 'database_access').first())
astronaut = security_manager.add_role('Astronaut')
security_manager.add_permission_role(astronaut,
main_db_permission_view)
# Astronaut role is Gamma + sqllab + main db permissions
for perm in security_manager.find_role('Gamma').permissions:
security_manager.add_permission_role(astronaut, perm)
for perm in security_manager.find_role('sql_lab').permissions:
security_manager.add_permission_role(astronaut, perm)
gagarin = security_manager.find_user('gagarin')
if not gagarin:
security_manager.add_user('gagarin',
'Iurii',
'Gagarin',
'*****@*****.**',
astronaut,
password='******')
data = self.run_sql('SELECT * FROM ab_user', '3', user_name='gagarin')
db.session.query(Query).delete()
db.session.commit()
self.assertLess(0, len(data['data']))
def post_add(self, item: models.SqlaTable) -> None:
item.fetch_metadata()
security_manager.add_permission_view_menu("datasource_access",
item.get_perm())
if item.schema:
security_manager.add_permission_view_menu("schema_access",
item.schema_perm)
def test_filter_druid_datasource(self):
CLUSTER_NAME = "new_druid"
cluster = self.get_or_create(
DruidCluster, {"cluster_name": CLUSTER_NAME}, db.session
)
db.session.merge(cluster)
gamma_ds = self.get_or_create(
DruidDatasource, {"datasource_name": "datasource_for_gamma"}, db.session
)
gamma_ds.cluster = cluster
db.session.merge(gamma_ds)
no_gamma_ds = self.get_or_create(
DruidDatasource, {"datasource_name": "datasource_not_for_gamma"}, db.session
)
no_gamma_ds.cluster = cluster
db.session.merge(no_gamma_ds)
db.session.commit()
security_manager.add_permission_view_menu("datasource_access", gamma_ds.perm)
security_manager.add_permission_view_menu("datasource_access", no_gamma_ds.perm)
perm = security_manager.find_permission_view_menu(
"datasource_access", gamma_ds.get_perm()
)
security_manager.add_permission_role(security_manager.find_role("Gamma"), perm)
security_manager.get_session.commit()
self.login(username="******")
url = "/druiddatasourcemodelview/list/"
resp = self.get_resp(url)
self.assertIn("datasource_for_gamma", resp)
self.assertNotIn("datasource_not_for_gamma", resp)
def create_schema_perm(view_menu_name: str) -> None:
permission = "schema_access"
security_manager.add_permission_view_menu(permission, view_menu_name)
perm_view = security_manager.find_permission_view_menu(
permission, view_menu_name)
security_manager.add_permission_role(
security_manager.find_role(SCHEMA_ACCESS_ROLE), perm_view)
return None
def pre_add(self, db):
self.check_extra(db)
db.set_sqlalchemy_uri(db.sqlalchemy_uri)
security_manager.add_permission_view_menu("database_access", db.perm)
# adding a new database we always want to force refresh schema list
for schema in db.get_all_schema_names():
security_manager.add_permission_view_menu(
"schema_access", security_manager.get_schema_perm(db, schema))
def _pre_add_update(self, database):
self.check_extra(database)
self.check_encrypted_extra(database)
database.set_sqlalchemy_uri(database.sqlalchemy_uri)
security_manager.add_permission_view_menu("database_access", database.perm)
# adding a new database we always want to force refresh schema list
for schema in database.get_all_schema_names():
security_manager.add_permission_view_menu(
"schema_access", security_manager.get_schema_perm(database, schema)
)
def post_add(self, table, flash_message=True):
table.fetch_metadata()
security_manager.add_permission_view_menu('datasource_access', table.get_perm())
if table.schema:
security_manager.add_permission_view_menu('schema_access', table.schema_perm)
if flash_message:
flash(_(
'The table was created. '
'As part of this two-phase configuration '
'process, you should now click the edit button by '
'the new table to configure it.'), 'info')
def _pre_add_update(self, database: Database) -> None:
if app.config["PREVENT_UNSAFE_DB_CONNECTIONS"]:
check_sqlalchemy_uri(database.sqlalchemy_uri)
self.check_extra(database)
self.check_encrypted_extra(database)
if database.server_cert:
utils.parse_ssl_cert(database.server_cert)
database.set_sqlalchemy_uri(database.sqlalchemy_uri)
security_manager.add_permission_view_menu("database_access", database.perm)
# adding a new database we always want to force refresh schema list
for schema in database.get_all_schema_names():
security_manager.add_permission_view_menu(
"schema_access", security_manager.get_schema_perm(database, schema)
)
def test_clean_requests_after_schema_grant(self):
session = db.session
# Case 4. Two access requests from gamma and gamma2
# Gamma gets schema access, gamma2 access request granted
# Check if request by gamma has been deleted
gamma_user = security_manager.find_user(username="******")
access_request1 = create_access_request(
session, "table", "wb_health_population", TEST_ROLE_1, "gamma"
)
create_access_request(
session, "table", "wb_health_population", TEST_ROLE_2, "gamma2"
)
ds_1_id = access_request1.datasource_id
ds = (
session.query(SqlaTable)
.filter_by(table_name="wb_health_population")
.first()
)
ds.schema = "temp_schema"
security_manager.add_permission_view_menu("schema_access", ds.schema_perm)
schema_perm_view = security_manager.find_permission_view_menu(
"schema_access", ds.schema_perm
)
security_manager.add_permission_role(
security_manager.find_role(SCHEMA_ACCESS_ROLE), schema_perm_view
)
gamma_user.roles.append(security_manager.find_role(SCHEMA_ACCESS_ROLE))
session.commit()
# gamma2 request gets fulfilled
self.client.get(
EXTEND_ROLE_REQUEST.format("table", ds_1_id, "gamma2", TEST_ROLE_2)
)
access_requests = self.get_access_requests("gamma", "table", ds_1_id)
self.assertFalse(access_requests)
gamma_user = security_manager.find_user(username="******")
gamma_user.roles.remove(security_manager.find_role(SCHEMA_ACCESS_ROLE))
ds = (
session.query(SqlaTable)
.filter_by(table_name="wb_health_population")
.first()
)
ds.schema = None
session.commit()
def test_sql_json_has_access(self):
examples_db = get_example_database()
examples_db_permission_view = security_manager.add_permission_view_menu(
"database_access", examples_db.perm)
astronaut = security_manager.add_role("Astronaut")
security_manager.add_permission_role(astronaut,
examples_db_permission_view)
# Astronaut role is Gamma + sqllab + db permissions
for perm in security_manager.find_role("Gamma").permissions:
security_manager.add_permission_role(astronaut, perm)
for perm in security_manager.find_role("sql_lab").permissions:
security_manager.add_permission_role(astronaut, perm)
gagarin = security_manager.find_user("gagarin")
if not gagarin:
security_manager.add_user(
"gagarin",
"Iurii",
"Gagarin",
"*****@*****.**",
astronaut,
password="******",
)
data = self.run_sql(QUERY_1, "3", user_name="gagarin")
db.session.query(Query).delete()
db.session.commit()
self.assertLess(0, len(data["data"]))
def post_add(self, table, flash_message=True):
table.fetch_metadata()
security_manager.add_permission_view_menu("datasource_access",
table.get_perm())
if table.schema:
security_manager.add_permission_view_menu("schema_access",
table.schema_perm)
if flash_message:
flash(
_("The table was created. "
"As part of this two-phase configuration "
"process, you should now click the edit button by "
"the new table to configure it."),
"info",
)
def test_sql_json_schema_access(self):
examples_db = get_example_database()
db_backend = examples_db.backend
if db_backend == "sqlite":
# sqlite doesn't support database creation
return
sqllab_test_db_schema_permission_view = (
security_manager.add_permission_view_menu(
"schema_access", f"[{examples_db.name}].[{CTAS_SCHEMA_NAME}]"
)
)
schema_perm_role = security_manager.add_role("SchemaPermission")
security_manager.add_permission_role(
schema_perm_role, sqllab_test_db_schema_permission_view
)
self.create_user_with_roles(
"SchemaUser", ["SchemaPermission", "Gamma", "sql_lab"]
)
examples_db.get_sqla_engine().execute(
f"CREATE TABLE IF NOT EXISTS {CTAS_SCHEMA_NAME}.test_table AS SELECT 1 as c1, 2 as c2"
)
data = self.run_sql(
f"SELECT * FROM {CTAS_SCHEMA_NAME}.test_table", "3", username="******"
)
self.assertEqual(1, len(data["data"]))
data = self.run_sql(
f"SELECT * FROM {CTAS_SCHEMA_NAME}.test_table",
"4",
username="******",
schema=CTAS_SCHEMA_NAME,
)
self.assertEqual(1, len(data["data"]))
# postgres needs a schema as a part of the table name.
if db_backend == "mysql":
data = self.run_sql(
"SELECT * FROM test_table",
"5",
username="******",
schema=CTAS_SCHEMA_NAME,
)
self.assertEqual(1, len(data["data"]))
db.session.query(Query).delete()
get_example_database().get_sqla_engine().execute(
f"DROP TABLE IF EXISTS {CTAS_SCHEMA_NAME}.test_table"
)
db.session.commit()
def test_sql_json_has_access(self):
examples_db = get_example_database()
examples_db_permission_view = security_manager.add_permission_view_menu(
"database_access", examples_db.perm
)
astronaut = security_manager.add_role("ExampleDBAccess")
security_manager.add_permission_role(astronaut, examples_db_permission_view)
# Gamma user, with sqllab and db permission
self.create_user_with_roles("Gagarin", ["ExampleDBAccess", "Gamma", "sql_lab"])
data = self.run_sql(QUERY_1, "1", user_name="Gagarin")
db.session.query(Query).delete()
db.session.commit()
self.assertLess(0, len(data["data"]))
def test_filter_druid_datasource(self):
CLUSTER_NAME = 'new_druid'
cluster = self.get_or_create(DruidCluster,
{'cluster_name': CLUSTER_NAME},
db.session)
db.session.merge(cluster)
gamma_ds = self.get_or_create(
DruidDatasource, {'datasource_name': 'datasource_for_gamma'},
db.session)
gamma_ds.cluster = cluster
db.session.merge(gamma_ds)
no_gamma_ds = self.get_or_create(
DruidDatasource, {'datasource_name': 'datasource_not_for_gamma'},
db.session)
no_gamma_ds.cluster = cluster
db.session.merge(no_gamma_ds)
db.session.commit()
security_manager.add_permission_view_menu('datasource_access',
gamma_ds.perm)
security_manager.add_permission_view_menu('datasource_access',
no_gamma_ds.perm)
perm = security_manager.find_permission_view_menu(
'datasource_access', gamma_ds.get_perm())
security_manager.add_permission_role(
security_manager.find_role('Gamma'), perm)
security_manager.get_session.commit()
self.login(username='******')
url = '/druiddatasourcemodelview/list/'
resp = self.get_resp(url)
self.assertIn('datasource_for_gamma', resp)
self.assertNotIn('datasource_not_for_gamma', resp)
def pre_add(self, item: "DruidClusterModelView") -> None:
security_manager.add_permission_view_menu("database_access", item.perm)
def create_table_permissions(table: models.SqlaTable) -> None:
security_manager.add_permission_view_menu("datasource_access",
table.get_perm())
if table.schema:
security_manager.add_permission_view_menu("schema_access",
table.schema_perm)
def post_update(self, metric):
if metric.is_restricted:
security_manager.add_permission_view_menu('metric_access',
metric.get_perm())
def load_test_users_run():
"""
Loads admin, alpha, and gamma user for testing purposes
Syncs permissions for those users/roles
"""
if config.get('TESTING'):
security_manager.sync_role_definitions()
gamma_sqllab_role = security_manager.add_role('gamma_sqllab')
for perm in security_manager.find_role('Gamma').permissions:
security_manager.add_permission_role(gamma_sqllab_role, perm)
utils.get_or_create_main_db()
db_perm = utils.get_main_database(security_manager.get_session).perm
security_manager.add_permission_view_menu('database_access', db_perm)
db_pvm = security_manager.find_permission_view_menu(
view_menu_name=db_perm, permission_name='database_access')
gamma_sqllab_role.permissions.append(db_pvm)
for perm in security_manager.find_role('sql_lab').permissions:
security_manager.add_permission_role(gamma_sqllab_role, perm)
admin = security_manager.find_user('admin')
if not admin:
security_manager.add_user('admin',
'admin',
' user',
'*****@*****.**',
security_manager.find_role('Admin'),
password='******')
gamma = security_manager.find_user('gamma')
if not gamma:
security_manager.add_user('gamma',
'gamma',
'user',
'*****@*****.**',
security_manager.find_role('Gamma'),
password='******')
gamma2 = security_manager.find_user('gamma2')
if not gamma2:
security_manager.add_user('gamma2',
'gamma2',
'user',
'*****@*****.**',
security_manager.find_role('Gamma'),
password='******')
gamma_sqllab_user = security_manager.find_user('gamma_sqllab')
if not gamma_sqllab_user:
security_manager.add_user('gamma_sqllab',
'gamma_sqllab',
'user',
'*****@*****.**',
gamma_sqllab_role,
password='******')
alpha = security_manager.find_user('alpha')
if not alpha:
security_manager.add_user('alpha',
'alpha',
'user',
'*****@*****.**',
security_manager.find_role('Alpha'),
password='******')
security_manager.get_session.commit()
def pre_add(self, cluster):
security_manager.add_permission_view_menu("database_access",
cluster.perm)
def load_test_users_run():
"""
Loads admin, alpha, and gamma user for testing purposes
Syncs permissions for those users/roles
"""
if config.get("TESTING"):
security_manager.sync_role_definitions()
gamma_sqllab_role = security_manager.add_role("gamma_sqllab")
for perm in security_manager.find_role("Gamma").permissions:
security_manager.add_permission_role(gamma_sqllab_role, perm)
utils.get_or_create_main_db()
db_perm = utils.get_main_database().perm
security_manager.add_permission_view_menu("database_access", db_perm)
db_pvm = security_manager.find_permission_view_menu(
view_menu_name=db_perm, permission_name="database_access"
)
gamma_sqllab_role.permissions.append(db_pvm)
for perm in security_manager.find_role("sql_lab").permissions:
security_manager.add_permission_role(gamma_sqllab_role, perm)
admin = security_manager.find_user("admin")
if not admin:
security_manager.add_user(
"admin",
"admin",
" user",
"*****@*****.**",
security_manager.find_role("Admin"),
password="******",
)
gamma = security_manager.find_user("gamma")
if not gamma:
security_manager.add_user(
"gamma",
"gamma",
"user",
"*****@*****.**",
security_manager.find_role("Gamma"),
password="******",
)
gamma2 = security_manager.find_user("gamma2")
if not gamma2:
security_manager.add_user(
"gamma2",
"gamma2",
"user",
"*****@*****.**",
security_manager.find_role("Gamma"),
password="******",
)
gamma_sqllab_user = security_manager.find_user("gamma_sqllab")
if not gamma_sqllab_user:
security_manager.add_user(
"gamma_sqllab",
"gamma_sqllab",
"user",
"*****@*****.**",
gamma_sqllab_role,
password="******",
)
alpha = security_manager.find_user("alpha")
if not alpha:
security_manager.add_user(
"alpha",
"alpha",
"user",
"*****@*****.**",
security_manager.find_role("Alpha"),
password="******",
)
security_manager.get_session.commit()
def post_add(self, metric):
if metric.is_restricted:
security_manager.add_permission_view_menu("metric_access",
metric.get_perm())
