def setUp(self): session = db.session # Create roles security_manager.add_role(self.NAME_AB_ROLE) security_manager.add_role(self.NAME_Q_ROLE) gamma_user = security_manager.find_user(username="******") gamma_user.roles.append(security_manager.find_role(self.NAME_AB_ROLE)) gamma_user.roles.append(security_manager.find_role(self.NAME_Q_ROLE)) self.create_user_with_roles("NoRlsRoleUser", ["Gamma"]) session.commit() # Create regular RowLevelSecurityFilter (energy_usage, unicode_test) self.rls_entry1 = RowLevelSecurityFilter() self.rls_entry1.tables.extend( session.query(SqlaTable).filter( SqlaTable.table_name.in_(["energy_usage", "unicode_test"])).all()) self.rls_entry1.filter_type = "Regular" self.rls_entry1.clause = "value > {{ cache_key_wrapper(1) }}" self.rls_entry1.group_key = None self.rls_entry1.roles.append(security_manager.find_role("Gamma")) self.rls_entry1.roles.append(security_manager.find_role("Alpha")) db.session.add(self.rls_entry1) # Create regular RowLevelSecurityFilter (birth_names name starts with A or B) self.rls_entry2 = RowLevelSecurityFilter() self.rls_entry2.tables.extend( session.query(SqlaTable).filter( SqlaTable.table_name.in_(["birth_names"])).all()) self.rls_entry2.filter_type = "Regular" self.rls_entry2.clause = "name like 'A%' or name like 'B%'" self.rls_entry2.group_key = "name" self.rls_entry2.roles.append(security_manager.find_role("NameAB")) db.session.add(self.rls_entry2) # Create Regular RowLevelSecurityFilter (birth_names name starts with Q) self.rls_entry3 = RowLevelSecurityFilter() self.rls_entry3.tables.extend( session.query(SqlaTable).filter( SqlaTable.table_name.in_(["birth_names"])).all()) self.rls_entry3.filter_type = "Regular" self.rls_entry3.clause = "name like 'Q%'" self.rls_entry3.group_key = "name" self.rls_entry3.roles.append(security_manager.find_role("NameQ")) db.session.add(self.rls_entry3) # Create Base RowLevelSecurityFilter (birth_names boys) self.rls_entry4 = RowLevelSecurityFilter() self.rls_entry4.tables.extend( session.query(SqlaTable).filter( SqlaTable.table_name.in_(["birth_names"])).all()) self.rls_entry4.filter_type = "Base" self.rls_entry4.clause = "gender = 'boy'" self.rls_entry4.group_key = "gender" self.rls_entry4.roles.append(security_manager.find_role("Admin")) db.session.add(self.rls_entry4) db.session.commit()
def setUp(self): session = db.session # Create the RowLevelSecurityFilter self.rls_entry = RowLevelSecurityFilter() self.rls_entry.table = (session.query(SqlaTable).filter_by( table_name="birth_names").first()) self.rls_entry.clause = "gender = 'male'" self.rls_entry.roles.append( security_manager.find_role("Gamma") ) # db.session.query(Role).filter_by(name="Gamma").first()) db.session.add(self.rls_entry) db.session.commit()
def setUp(self): # Create the RowLevelSecurityFilter self.rls_entry = RowLevelSecurityFilter() self.rls_entry.tables.extend( db.session.query(SqlaTable).filter( SqlaTable.table_name.in_(["energy_usage", "unicode_test"])).all()) self.rls_entry.clause = "value > 1" self.rls_entry.roles.append( security_manager.find_role("Gamma") ) # db.session.query(Role).filter_by(name="Gamma").first()) self.rls_entry.roles.append(security_manager.find_role("Alpha")) db.session.add(self.rls_entry) db.session.commit()
def test_sql_lab_insert_rls( mocker: MockerFixture, session: Session, app_context: None, ) -> None: """ Integration test for `insert_rls`. """ from flask_appbuilder.security.sqla.models import Role, User from superset.connectors.sqla.models import RowLevelSecurityFilter, SqlaTable from superset.models.core import Database from superset.models.sql_lab import Query from superset.security.manager import SupersetSecurityManager from superset.sql_lab import execute_sql_statement from superset.utils.core import RowLevelSecurityFilterType engine = session.connection().engine Query.metadata.create_all(engine) # pylint: disable=no-member connection = engine.raw_connection() connection.execute("CREATE TABLE t (c INTEGER)") for i in range(10): connection.execute("INSERT INTO t VALUES (?)", (i, )) cursor = connection.cursor() query = Query( sql="SELECT c FROM t", client_id="abcde", database=Database(database_name="test_db", sqlalchemy_uri="sqlite://"), schema=None, limit=5, select_as_cta_used=False, ) session.add(query) session.commit() admin = User( first_name="Alice", last_name="Doe", email="*****@*****.**", username="******", roles=[Role(name="Admin")], ) # first without RLS with override_user(admin): superset_result_set = execute_sql_statement( sql_statement=query.sql, query=query, session=session, cursor=cursor, log_params=None, apply_ctas=False, ) assert (superset_result_set.to_pandas_df().to_markdown() == """ | | c | |---:|----:| | 0 | 0 | | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 |""".strip()) assert query.executed_sql == "SELECT c FROM t\nLIMIT 6" # now with RLS rls = RowLevelSecurityFilter( filter_type=RowLevelSecurityFilterType.REGULAR, tables=[SqlaTable(database_id=1, schema=None, table_name="t")], roles=[admin.roles[0]], group_key=None, clause="c > 5", ) session.add(rls) session.flush() mocker.patch.object(SupersetSecurityManager, "find_user", return_value=admin) mocker.patch("superset.sql_lab.is_feature_enabled", return_value=True) with override_user(admin): superset_result_set = execute_sql_statement( sql_statement=query.sql, query=query, session=session, cursor=cursor, log_params=None, apply_ctas=False, ) assert (superset_result_set.to_pandas_df().to_markdown() == """ | | c | |---:|----:| | 0 | 6 | | 1 | 7 | | 2 | 8 | | 3 | 9 |""".strip()) assert query.executed_sql == "SELECT c FROM t WHERE (t.c > 5)\nLIMIT 6"