Does adding row level security only effect who can make reports.

Deannah shared this question 3 months ago
Answered

Does adding row level security only effect who can make reports. Will it also limit what you can see in reports?

Comments (1)

photo
1

Hey Deannah,

I hope you are well!

I see you had left a comment on a post almost 2 weeks back regarding a similar question - is this related to what you are asking now?

Here is the post, where Mike gives a detailed response on how to create the access filters :)

In regards to your first question - when adding access filters (if this is what you are referring to) that have been defined in the View that you are creating this report from, the user will have the option to apply the access filter to the report.


You can set it up so that certain users, such as administrators, are able to override access filters to view all results. This is done by defining an overriding code, which can then be added to a specific user’s source filter entry. Yellowfin will then not apply the access filter for such users, providing them with the full results.

Note that this type of setup to switch the access filters on or off can only be done by administrative users. The users for whom this is applied, will not be able to choose whether or not to apply the access filters themselves.


So, yes, to answer your second question, Access Filters limit the data that users can see when running a report - The data they see all depends on what you have allowed them access to.

Please refer back to the linked post here where Mike has detailed more instructions on how to do this - I hope this has helped!

Though if this is not what you were referring to, please correct me!

Let me know :)

Best Wishes,

Lesley

photo
1

HI Lesley,


I'm great thankyou how are you?

So.. ive been stuck on this problem for a while now. I have actually tried to follow Mikes instructions but i still dont fully understand how to get this to work for us. I actually have another ticket open with similar questions in.

Should i be using Reference codes to sort out my access levels? - Chris has been looking at this one

This is the question...

I have attached a copy of the file i have loaded that doesnt seem to work in that ticket. Am i doing this right?

photo
1

Hey Deannah,

I am well thank you :)

Ok, I have gone through both the wiki and Mikes steps and there seems to be a few things missing so I will explain here, but yes, you will need to be using your reference codes as the reference codes creates the 'values' you enter when creating what each user (or group) can see


For my example I am using a Gender access filter

1. we want to ensure we have a reference codes - here is mine -

b6e5ec3c12043a7683c9c31acdad49f8


2. you then go to Data Sources under the Admin Console > click the data source you wish to add an access filter to > Click 'Access Filters' > Click 'Configure Reference Type' > type in a Name (I have typed in 'gender' ) and then Click 'Add' -

0dbab4a49d3a6c3d9bd2ab44b8d2099d


3. Now that We have Clicked that Add button, we are still viewing the data source access filter section - Now we want to click the 'Add' button that is next to the 'Configure Reference type' button > Click the Manual Icon and continue > Now give your access filter a name (I went With GenderFilter) > then select the drop box and select the newly created Reference Type you created (in my case 'gender').

94c323e84b5181dd768313b31c86f39b


> Then select 'input Values'

4. You will now be able to add users that you wish to restrict data from. So in my case, I am going to restrict the System Administrator to only see Female records - I do this by selecting the user and then inputting the value that matches the Reference code Values.

ca5c916b7f68ac252bb82bb01a015f9c


I then click submit.


5. Now you will want to go to the View you wish to restrict the data in the reports from. > Go to the Prepare Tab > select the Second tab across the top for the View settings > Find the Field where you want to add the Filter to > add the access filter (in my case 'gender')

Note - this does restrict all data to that access filter, so for example, if I add athlete ID to my report, It will only show me the Female records for Athlete ID

e4fa81bd615efd0d1c5087d514fc003f


Then publish this View to Active it.


6. Now when creating a report based off of this view you have have a section in the report builder that allows you to turn on or off this access filter via report level.

Here is my access filter turned on -


f6c4180a7399450d90c4dcdba596ecd0


As you can see there is now an 'Access Filters' Section for my report builder and I have enabled the 'Gender' access filter.


Here is my report with this Disabled -

eaa0eade2f1fad3b4a8504c37ad3320e


As you can see I can now see all records (3228 rows) whereas previously with the access filter on, I could only see 1692 data records.


So to summaries -

  • Have reference codes that define your values
  • when creating reference type, ensure users are given values of data they should see
  • Enable your access filter in the View under 'Field settings'
  • Enable your access filter in the report builder


I hope this helps some! Let me know if not and which part you are getting stuck on! :)

Best Wishes,

Lesley

photo
1

Thankyou so much!

This has taken me a step closer to what i am looking for, my next question is regards loading these in using a report/sql or CSV file. So for example if i was using CSV:

Identifier Type - i would select Email Address

Identifier - deannah@company.com

Filter Type - (Would this be gender?)

Reference id - Male?

Thank you so much again for helping me out with this, you're incredibly patient.

photo
1

Not a problem, Deannah! It's my pleasure :)

(Excuse all my typos, apparently I don't proof read)


Ok so I did have to play around with this as I was not entirely sure, since I had never created an access filter via CSV before - but I got it working so here were my steps.

Again, I am using gender as my example and we want to only see female records.

NOTE - WE DO NOT USE A REFERENCE CODE FOR THIS - I tested both with and without a reference code for the CSV example and it works just fine without (I deleted my gender reference code to test this)

I created an example CSV file (based on one of the example files provided in yellowfin) to make it more clear where we want to put what values :)

8bb96b0d7d5b1fa2b7b46c5bb8ebe0fe


So as you can see above, my CSV file that I will be using when creating a access filter. This is my users email address and I only want the access filter to show me Female records, so we have the Reference ID as Female.


To begin, we will roughly want to go through the same steps as before except there is a difference between when clicking the Manual Icon and the CSV icon.

Step 1. Similar to step one in my previous response, we want to configure a reference type first - except we must call it 'Gender' to match the 'Reference Type' in the csv file.

dff051c3d4d90a7b55430c2c8cc69c19


Step 2. Once we have added these, we again, then go back to the 'Add' button that is next to the 'configure reference type' button and instead this time select the CSV icon and continue > Give the access filter a name > drag and drop your CSV file -

c1c595e18e023d58b53091f8f4bde173


Then click Submit.


Step 3. Go to the View > Prepare Tab > field Settings and then select the field that corresponds with your access filter (in my case this is gender) > select Access filters > select the drop down box and select your filter (this will be the same as your reference Type), So in my case this is 'Gender'

702e31c6f877f2045a869c6c53058656


Step 4. Now go to the next tab along for 'View Security' the little padlock icon - and you should now have an option for 'Access Filters' here too > select it and then tick the box - Again, in my case this is 'Gender'

98fc420ac32f782923dab7ab919a20a0


Now publish the View to Activate it


Go to your report and you will see your access filter section and tick the box

6631306a9773d87a95ecb0683afa6f95


and then these are the results when disabling the filter -

fb714f6cf16d60e98fe35d02392698d8


Again, this shows all the records, 3228, whereas with the access filter enabled, this only showed 1692 records for my user :)


I hope this has helped and gets you the results you desire! Let me know :)

Best Wishes,

Lesley

photo
1

Hey Deannah,

I hope you are keeping well!

I just wanted to check in with you on this, to see if my previous response, including steps to create an access filter via CSV helped at all?

Let me know - I will be happy to continue assisting if you need :)

Best Wishes,

Lesley

photo
1

Hello,

Thankyou so much, this has worked perfectly for us.

A separate question. Say if you have a list of users and only the users that have a gender next to them for example if we take our two names as 'users' and we all have female next to us because we shouldnt be able to see any male records but Goggin isnt included because we want them to be able to see all records. Is that possible?

Deannah Female

Gill Female

Lesley Female


Let me know if that doesnt make any sense. I understand its kind of a complicated way of explaining it.


I should note we have now moved to YF 9

Many thanks,

Deannah

photo
1

Hey Deannah!

Im glad it worked for you! :)

ok....um, let me try to understand


3 users with Female next to them to restrict male data

a 4th user with no gender code next to their name to provide all results for them?


Best Wishes,

Lesley

photo
1

Yes, thats correct.


Sorry to be a pain!

photo
1

Hey Deanna,

Not a pain at all, I enjoy helping! :)

Yes this is possible, but I dont think you actually need to remove the gender value next to their name. You just need to 'exclude' the specific user from the access filter....

Let me run through the steps quickly and then I will update you on how to do this!

Best Wishes,

Lesley

photo
1

Ahh yeah that makes sense, i think i recall seeing that option somewhere. Instructions would be incredible thankyou.


Best Wishes,

Deannah

photo
1

Hey Deannah,

I checked this out and I think you do just remove the 'reference id' that is next to their name - You can overwrite the CSV with an edited copy where you have removed the value from the 'Reference' ID' column of your CSV to test and let me know if this works?

I tested by just removing 'Female' from my reference id column and and this seemed to work just fine...

Let me know if this works for you also :)

Best Wishes,

Lesley

photo
1

Hey,


I've just tried that and it doesn't seem to have worked for me. It just doesnt include that user when you load the CSV?


Thanks,

Deannnah

photo
1

Hey Deannah, Thanks for letting me know - Ok instead, run the below query against your config db

INSERT INTO Configuration VALUES (1,'SYSTEM','SOURCEFILTERWILDCARD','%');
Then in the CSV file for the Reference ID value enter '%' for that user
Restart yellowfin and upload the csv again and see if that works
The user with the % value should then see all results :)
Let me know the results! 
Best Wishes,
Lesley

photo
1

Hey Lesley,


Would you be able to explain where to insert that config?

I cant see that on the configuration tab.


Many thanks,

Deannah

photo
1

Good Morning, Deanna!

My apologies for not explaining properly

You just need to run the query against your configuration database, you don't need to specify any tables -

Like so -

This is my Yellowfin Configuration DB called 'json8183' and we just want to run the whole insert query against the DB -

9e0f567fc3ceab3f548bae2988edf587


What this query does, is turn on the 'wildcard' functionality in yellowfin. Using '%' as the next value states that this percentage symbol will be the referenceID value that allows for a user to view all data regardless of the access filters set :)

Once you have done this, restart yellowfin, update the CSV and see if that works!

Let me know if that made any sense or not and how it goes :)

Best Wishes,

Lesley

photo
1

Hey Deannah,

I hope you are well!

Just wanted to check in on this one to confirm if running the query against your database enabled the wildcard feature as we hoped it would?

Let me know :)

Best Wishes,

Lesley

photo
1

I still don't have the access yet. I've actually opened another ticket which is similar to this one.

Here is the ticket number, i'm still waiting for a reply on it. Any help would be amazing thanks!

#18593-07-29

photo
1

Hey Deannah,

Do you mean you don't have access to the db?

In regards to the other ticket for SQL Access Filters - Have you got a reference code for the values/codes?

from what we know so far, manual requires reference codes - CSV does not require reference codes - But I have not tested this with SQL - Let me know if yours does or does not have reference codes and if you don't, would it be possible to test it with one?

Best Wishes,

Lesley

photo
1

Hey Lesley,

Yeah I'm still waiting on DB access.

So i had the same thought as you and add in reference codes to check and see if that worked. It didn't sadly. Not sure if you have any other ideas?


Many thanks,

Deannah

photo
1

Hey Deannah,

Ok thanks - let me know how it goes once you do have access to the DB :)

In regards to your other ticket...have you assigned the access filter to a view yet?

Best Wishes,

Lesley

photo
1

OK i've just tried assigning it, and the security doesn't work. Its annoying because the query pulls through the data you can see that its correct when its loaded but then it never appears in the Records table.

None of this ever makes sense to me.


Thank you so much for helping me out.

photo
1

Hey Deannah - I will discuss with Jared about the other ticket (18593) as to keep communication about each issue in each ticket - I hope that's ok!

I hope you have a great weekend! :)

Best Wishes,

Lesley

photo
1

Thank you so much Lesley!

Hope you have a great weekend :)

photo
1

Closing as per requested via ticket 17998

Best Wishes,

Lesley

photo