Forum Discussion
Helper IComplexity RLS - Help please!
Hi PedroModa,
For Sure, Here is Simple and clear solution Step by Step you can Do :
First Step is to Create Security Table called UserSecurity with all user permissions like this:UserEmail CostCenter RestrictedSupplier [email protected] 1-1.000010 [email protected] 2-1.000012 [email protected] 1-1.000010 0009789 Until you reach end (25) etc Second Step is to put RLS Rules for Each Table
- dCentrodeCusto (dimension):
[COLI-CODCUSTO] IN VALUES(UserSecurity[CostCenter])- dFornecedor (dimension):
NOT([CODCFO] IN VALUES(UserSecurity[RestrictedSupplier]))- dMedContratos (hide entire table):
[Contrato] IN {""} // Or use FALSE() to hide everything- fAjusteGerencial (fact table):
VAR TI = RELATED(dCentrodeCusto[NOME]) = "TECNOLOGIA DA INFORMACAO" VAR ValidAccount = NOT([Conta] IN { "Salários E Ordenados", "Hora Extra", "Encargos Trabalhistas", "Rescisões E Indenizações", "Prêmios E Bonificações" }) RETURN TI && ValidAccount- fBalancete (fact table):
VAR TI = RELATED(dCentrodeCusto[NOME]) = "INFORMATION TECHNOLOGY" VAR ValidAccount = NOT(RELATED('dC Account'[Management Accounts]) IN { "Salaries and Wages", "Overtime", "Labor Charges", "Terminations and Compensation", "Bonuses and Bonuses" }) VAR InitialExpense = NOT([COMPLEMENT2] = "JOAO DA SILVA") RETURN TI && ValidAccount && InitialExpense- fPartida and fpitempartida (hide everything):
FALSE() // Hides all rows- ftmovitens and fvalorcado (Use same logic as fAjusteGerencial but adapt column names)
Third and Last Step is to add this to UserSecurity table:
[UserEmail] = USERPRINCIPALNAME()I hope you find this useful cause it took too much time and if you need any help tell me and sorry for delay reply 🫡❤️
Helper IAhmed-Elfeel Thank you so much for your help, things are starting to become clearer... could you help me think about how I would create this rule?
How would I create this dynamic RLS... The person can only see these coli-codcusto (table dcentrodecusto (table diensao)) [COLI-CODCUSTO] == "1-1.000010" || [COLI-CODCUSTO] == "2-1.000012"
in the dfornecedor table, this rule [CODCFO] <> "0009789"
in the dMedContratos table (dimension table), this rule [Contrato] == "" (that is, I don't want anything from this table to appear)
in the fAjusteGerencial table (fact table), this rule VAR TI = RELATED(dCentrodeCusto[NOME]) IN {"TECNOLOGIA DA INFORMACAO"} VAR ContaValida = NOT ( [Conta] IN { "Salários E Ordenados", "Hora Extra", "Encargos Trabalhistas", "Rescisões E Indenizações", "Prêmios E Bonificações" } ) RETURN (TI && ContaValida)
in the fBalancete table (fact table), this rule VAR TI = RELATED(dCentrodeCusto[NOME]) IN {"INFORMATION TECHNOLOGY"} VAR ValidAccount = NOT ( RELATED('dC Account'[Management Accounts]) IN { "Salaries and Wages", "Overtime", "Labor Charges", "Terminations and Compensation", "Bonuses and Bonuses" } ) VAR InitialExpense = NOT ( fBalance Sheet[COMPLEMENT2] = {"JOAO DA SILVA"} ) RETURN (IT && ValidAccount && InitialExpense)
in the fMeasurements table (fact table), this rule [CODCCUSTO] <> "" in the fPartida table (fact table), this rule [IDPARTIDA] == "195X195X195" (that is, I don't want it to see anything)
in the fpitempartida table (fact table), this rule [CHAPA] == "195X195X195" (that is, I don't want it to see anything)
in In the `ftmovitens` table (fact table), this rule: `VAR TI = RELATED(dCentrodeCusto[NOME]) IN {"TECNOLOGIA DA INFORMACAO"} VAR ContaValida = NOT ( RELATED('dC Conta'[Contas Gerenciais]) IN { "Salários E Ordenados", "Hora Extra", "Encargos Trabalhistas", "Rescisões E Indenizações", "Prêmios E Bonificações" } ) RETURN (TI && ContaValida)` Bonuses" } ) RETURN (TI && ContaValida)
and in the table fvalorçado (fact table), this rule VAR TI = RELATED(dCentrodeCusto[NOME]) IN {"INFORMATION TECHNOLOGY"} VAR ContaValida = NOT ( [Conta] IN { "Salaries and Wages", "Overtime", "Labor Charges", "Terminations and Compensation", "Bonuses and Bonuses" } ) RETURN (TI && ContaValida)
- Ahmed-Elfeel
Super User
Hi PedroModa,
For Sure, Here is Simple and clear solution Step by Step you can Do :
First Step is to Create Security Table called UserSecurity with all user permissions like this:UserEmail CostCenter RestrictedSupplier [email protected] 1-1.000010 [email protected] 2-1.000012 [email protected] 1-1.000010 0009789 Until you reach end (25) etc Second Step is to put RLS Rules for Each Table
- dCentrodeCusto (dimension):
[COLI-CODCUSTO] IN VALUES(UserSecurity[CostCenter])- dFornecedor (dimension):
NOT([CODCFO] IN VALUES(UserSecurity[RestrictedSupplier]))- dMedContratos (hide entire table):
[Contrato] IN {""} // Or use FALSE() to hide everything- fAjusteGerencial (fact table):
VAR TI = RELATED(dCentrodeCusto[NOME]) = "TECNOLOGIA DA INFORMACAO" VAR ValidAccount = NOT([Conta] IN { "Salários E Ordenados", "Hora Extra", "Encargos Trabalhistas", "Rescisões E Indenizações", "Prêmios E Bonificações" }) RETURN TI && ValidAccount- fBalancete (fact table):
VAR TI = RELATED(dCentrodeCusto[NOME]) = "INFORMATION TECHNOLOGY" VAR ValidAccount = NOT(RELATED('dC Account'[Management Accounts]) IN { "Salaries and Wages", "Overtime", "Labor Charges", "Terminations and Compensation", "Bonuses and Bonuses" }) VAR InitialExpense = NOT([COMPLEMENT2] = "JOAO DA SILVA") RETURN TI && ValidAccount && InitialExpense- fPartida and fpitempartida (hide everything):
FALSE() // Hides all rows- ftmovitens and fvalorcado (Use same logic as fAjusteGerencial but adapt column names)
Third and Last Step is to add this to UserSecurity table:
[UserEmail] = USERPRINCIPALNAME()I hope you find this useful cause it took too much time and if you need any help tell me and sorry for delay reply 🫡❤️