cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jengwt Member
Member

Use Security to Block Access to Table or Column

Say that I have 3 tables:

Table A, "Users", contains data on the users of my report; names, emails, etc.

This table is the basis of my RLS. There is a security role that filters this table based on user email vs USERPRINCIPALNAME

 

Table B, "Accounts", contains basic account data.

Accounts have an identifier that links them to a given user.

 

Table C, "Profit", contains profitability data that is linked to certain accounts.

 

So, Table A is linked to Table B and Table B is linked to Table C.

 

My questions are: 

 

1) Is there a way I can set up a role to say, "users under this role are not allowed to see table C"?

 

2) Say the profitability data is in table B, and table C doesn't exist. Can I set up a role that says, "users under this role are not allowed to see the Profitability column(s) in Table B"?

 

Assume there is no way to distinguish between the two types of users in the Users table.

 

I have explored one option for this which would be to have table C, and to create a new column with a value like 0/1 or T/F. Then have two security roles where, in addition to the security in Table A, one filters Table C where the Column = T (of which there are none, so basically they can't see any of the records in Table C), and the other does not filter Table C or filters for where the Column = F.

However, this seems clunky and dependent on correct assignment of users to specific roles.

 

We have a lot of users, and would prefer to manage our report security with group codes. So we want to avoid manual assignment of specific users to roles. I am not sure if there are group codes which distinguish between users who should be allowed to see that data, and others who are not. (will update)

 

3/4) Now assume that we have our two type of users divided up into two tables. Table A has the folks who are allowed to see profit data, and Table D are those who are not. Questions 3 and 4 are, how would you answer questions 1 and 2 in this case, repspectively?

Would the answer to (3) be to simply put a filter on Table C that checks to make sure the USERPRINCIPALNAME occurs in Table A?

No, I tried this and it doesn't work. Security must interact with the data in the filtered table in some way. Even if it did work, it could cascade to your other tables, depending on your table relationship directions. But you can't just have a T/F toggle for a whole table.

Perhaps, this could be made to work if you had some clever LOOKUPVALUEs that could bridge the gap between your main security table(s) and the table you're wanting to filter. Like maybe:
'Profit'[ACCT_NUM] = LOOKUPVALUE('Acounts'[ACCT_NUM]

     , 'Accounts'[ACCT_NUM]

     , LOOKUPVALUE('Users'[ACCT_NUM]

          , 'Users'[EMAIL]

          , USERPRINCIPALNAME

          )

     )

That doesn't work either. The problem with this code is (I guess)  that since a given person could have multiple accounts under their name, and a given account could be tied to multiple profit records, the inner LOOKUPVALUEs in the code could yield multiple returns, confusing the outer LOOKUPVALUEs.

At least, that’s what I think that error code is saying:

“A single value for column ‘ACCT_NBR’ in table ‘Profit’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.”

 

Can you do this for a column (4)?

 

Unfortunately, simply blocking access to a viz or page is not an option:
https://community.powerbi.com/t5/Desktop/Use-Security-to-Hide-Pages-or-Visuals/m-p/446379

1 REPLY 1
jengwt Member
Member

Re: Use Security to Block Access to Table or Column

*Updated OP to reflect this.

 

I tried my suggestion for (3), and it doesn't work.

Security must interact with the data in the filtered table in some way. Even if it did work, it could cascade to your other tables, depending on your table relationship directions. But you can't just have a T/F toggle for a whole table.

 

Perhaps, this could be made to work if you had some clever LOOKUPVALUEs that could bridge the gap between your main security table(s) and the table you're wanting to filter. Like maybe:

 

'Profit'[ACCT_NUM] = LOOKUPVALUE('Acounts'[ACCT_NUM]

     , 'Accounts'[ACCT_NUM]

     , LOOKUPVALUE('Users'[ACCT_NUM]

          , 'Users'[EMAIL]

          , USERPRINCIPALNAME

          )

     )

 

That doesn't work either. The problem with this code is (I guess)  that since a given person could have multiple accounts under their name, and a given account could be tied to multiple profit records, the inner LOOKUPVALUEs in the code could yield multiple returns, confusing the outer LOOKUPVALUEs.

At least, that’s what I think that error code is saying:

“A single value for column ‘ACCT_NBR’ in table ‘Profit’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.”