Centralised row-level security: New in Tableau 2021.4

0:00Hey, Tim here. In this video, I'm going to

0:02talk you through centralized row level

0:04security in Tablo 21.4.

0:06This is a huge feature. It's going to take

0:09a lot more than just this one video to

0:11cover it.

0:12But nonetheless, let's get stuck in. Now in

0:14the previous video, I created a virtual

0:17connection.

0:18I'm not going to cover how to do that here.

0:20I've already covered it in that video. So

0:21check that out before you watch this one.

0:23Nevertheless, we are in the same virtual

0:24connection that we created here. You can

0:26see that I've got invoices, employees and

0:28orders available here.

0:30And what I'm now going to do is essentially

0:32start creating the centralized row level

0:35security or the data policies in essence.

0:38Now there's three ways I'm going to show

0:39you how to do this here.

0:41And the first two are simple and the last

0:44one is using an entitlement table of sort,

0:47which is going to be based on our employees

0:49table here.

0:50Now what I did in this particular data set

0:52is I changed one of the employees names to

0:54match my name on Tablo service.

0:56If you're trying this out, you need to

0:58remember that whatever entitlement table

1:00you're using, it's going to need to match

1:01to something on Tablo server.

1:03So Tablo can actually match those two

1:05things up. So let's get stuck into this and

1:07let's find out how this works.

1:09Now the first thing I'm going to do is go

1:10over here to the data policies window. This

1:13is in the virtual connection setup.

1:15And if I go over to the data policies

1:16windows, you'll see that I have no data

1:18policies available.

1:20If I actually scroll this down, you can see

1:22that I can create a new policy here and I

1:24get this window, which essentially shows me

1:26what's going on.

1:27Now I'm going to uncheck this with policy

1:29applied setup.

1:31So we just see the table as it's applied

1:33right now with no data policies connected

1:35for the first one.

1:37So the first one, what I'm going to do is I

1:39'm going to add the orders table and I'm

1:41going to drag it just into this sort of

1:44dotted area that says add as a policy table

1:47.

1:47Apply this policy condition to this table.

1:49So when you do that, the orders table

1:50appears here on the left hand side.

1:53And there's this sort of stepped guide,

1:54essentially step one, you add the tables

1:57and columns that you want to map.

1:59And then on step two, you write the policy

2:00conditions.

2:01So in this particular case, what I'm going

2:03to do is add a column and in this case it's

2:05going to be ship city.

2:07Now, one thing that sort of confused me is

2:09that whatever you add here, ship city doesn

2:12't actually have to be what you use over

2:15here.

2:16Let me explain that a little bit. You see

2:17this term here saying policy one.

2:20If you rename that, you can actually use

2:22that over here to represent this ship city.

2:26So let me just show you how that works. In

2:27here, I'm going to double click and I'm

2:29going to type in city.

2:31And now that I've done that, I can go over

2:34here to the left and I can just type city

2:37equals.

2:38And I'm just going to check one specific

2:41city here, Lyon.

2:43OK. And I put a little sort of piece of

2:45punctuation there. And now you can see that

2:47's been applied there.

2:49And now if I go over to the bottom here and

2:51I tick this box with the policy applied, we

2:54should see that with me looking at this

2:58particular table, I should only see rows

3:00from the city, Lyon.

3:02So let that just load and we should get a

3:04preview of that very soon. So, yeah, you

3:06can see that's worked.

3:07We've got ten of eight hundred and thirty

3:09rows and you can see that the only city I'm

3:11seeing here is Lyon.

3:13So that's our very first simple policy set

3:15up. You can see it's working.

3:18Now, one of the nice things I like about

3:19this is that you've actually got the full

3:21capability of the Tableau calculation

3:24window here on the right hand side.

3:25So you could write some absolutely mad, mad

3:29policies with this.

3:31I don't know, sort of like I've never used

3:33any of these in a row level security setup,

3:37but you could you've got date functions

3:38here.

3:39You've got full Tableau server user

3:41functions. I've done videos on most of

3:42these functions as well.

3:44So date functions, all of that, I've done

3:45videos on all of them, so I pretty much go

3:47and check them out.

3:48So that's really sort of nice to see

3:49because you can build logic statements into

3:52these policies and that's going to be sort

3:54of like a mind maze to get your head into.

3:57But it's really exciting that that option's

3:58there. Now, I've created this policy for

4:01this table.

4:02You can see that this is the orders table.

4:04Unfortunately, policies can't be applied to

4:08, well, more than one table.

4:10So in this case, let me rephrase this.

4:14Tables can't be part of more than one

4:15policy.

4:16So one table can only have one policy. And

4:19you can see here that I have a little sort

4:21of shield, which means that this table

4:23already has a policy, which means we can't

4:24add it to another one.

4:26We have to go and create a new policy to

4:27add the other tables.

4:29So let's go ahead to our invoices table

4:31here. And what I want to do is actually

4:32create a new policy for this one.

4:34So I can actually click on this and add it

4:38to this policy. If I do do that, let me

4:40just go in and add it there.

4:42It's going to ask me for the invoices,

4:44which field represents the city.

4:46So what I'll then have to do is go into inv

4:48oices, select ship, ship city. That's a bit

4:50of a tongue twister.

4:52Got to be careful with that one. And for

4:53this one, if I click on invoices here, you

4:56'll see that my invoices table here is now

4:58only showing Leon.

5:00And if I click on my orders table here, my

5:02orders table is only showing Leon there.

5:05So this interface sort of adapts to what

5:07you're doing.

5:09And you have to sort of bear in mind that

5:10you can add multiple tables here.

5:13And the city column is just simply defining

5:15a standard field across all of these tables

5:17.

5:18And then when this policy is applied to

5:19this connection, everyone who sits behind

5:22this policy is going to need to use this

5:24policy because this is a column level

5:27policy.

5:28It's only going to return data from Leon in

5:30this particular case to absolutely everyone

5:32.

5:33And the more I add to this table, the more

5:35it's going to affect all the other users

5:37who are using it.

5:39So that's one simple use case. It's really,

5:41really nice to use and nice to see.

5:44For the next one, I'm going to use the

5:46sales person in a particular table. This

5:49time it's going to match my own name.

5:51So how do we remove these policies? Well,

5:53if I go back to the policy list, let's just

5:55go back here.

5:57We can actually go to this little three dot

5:58name and remove it. And we're back to

6:00square one with no policies applied.

6:03Now, if I start by creating a new policy

6:06again, you'll see that we start with a

6:08blank canvas and our preview starts to load

6:10again.

6:11And I'll untick this with policy applied

6:13tick box here. And we basically just start

6:15again.

6:16We're basically back to square one. And for

6:18the record here, I'm selecting the tables

6:20on the left, which is what is generating

6:22this preview.

6:23If I then select them here, then that will

6:25generate the preview as well. So the

6:27preview can be driven from two different

6:29areas.

6:30It's a bit of a sort of an interesting user

6:32interface to get used to. But once you get

6:34used to it, it's actually a real delight to

6:35use.

6:36I think I think this is one of the best

6:37implementations of the connection window,

6:41bringing in the best parts of Tableau prep

6:43that we've seen already into this

6:44experience to really make it nice and rich.

6:47Now for the next one, what I'm going to do

6:49is bring in the invoices table. And what I

6:51'll do is I'll add it as a policy in hand.

6:54You'll see that it makes its way in here.

6:56And what I'll do is I'll add a column to

6:57map. And for this particular one, I'm going

6:59to look for the salesperson.

7:01And so for this salesperson column, what I

7:03'm just going to do is I'm just going to

7:04call this sales just to keep this simple

7:06and easy to type.

7:07But then now that I've added that in, I'm

7:09just going to go in here to sales and I'm

7:11just going to say sales equal username.

7:14So I think this is the username function in

7:16Tableau server. So essentially what I'm

7:18saying is that this sales column, which is

7:23referring to the salesperson, is going to

7:26basically go and grab that salesperson's

7:28name.

7:29Where is this here? So you'll see it here

7:31in this column. And in this particular case

7:34, I'm looking to match my own full name

7:37against the Tableau server username, which

7:41in this case is Tim Neguena.

7:43I purposely cooked this data for this demo

7:46so that it would match in this particular

7:50case.

7:51So that's essentially sort of the loop that

7:52's happening here. So if I go ahead and look

7:54at this with the policy applied, I am Tim

7:57Neguena.

7:58So I can go in here and check this and you

8:00'll see that nothing is working here. For

8:03whatever reason, this is not working.

8:05So let me untick this and make sure that

8:07this is actually doing what I think it

8:09should be doing.

8:11So here we are for the salesperson here and

8:14this is the full name Tim Neguena.

8:17Everything is working as expected.

8:20Now, if I just go to this function here and

8:23let's just do this. Let's just let's just

8:27cook this deliberately.

8:29Let's just get this to match using my

8:31actual text. That's something we can, of

8:33course, do.

8:34And now that that's working, there's no

8:36sort of issue with that. Let's just check

8:38what happens.

8:39And you see that does work. So what's

8:41clearly going on here is that I must I must

8:45have a different username to what I thought

8:49I had.

8:50So instead of username, let's see if I can

8:53grab my full name instead from server.

8:56So my username is clearly not working for

8:57this particular demo. So let's try my full

9:00name. And finally, that's working.

9:02So that's a good bit of debugging in real

9:04time there. We had to kind of figure that

9:05out as I'm recording a video,

9:08which is always a stressful thing. But

9:09there you go. I tried to use the username.

9:11In this case, it's actually my email, which

9:13isn't going to work.

9:14But if I try the full name, which is what

9:16server knows as my full name, Tim Neguena,

9:19then that works just fine.

9:21So there you go. That's another version of

9:22this row level security that we've set up.

9:25Now, when you apply these policies, you can

9:26see that it is applied here to the invoices

9:28table.

9:29You can actually go back to the tables list

9:31and see them applied there.

9:34So when I go back to the tables list, you

9:35'll see that it's called policy one over

9:37here.

9:38So if you want to make sure that things

9:39make sense, give these policies a good name

9:41.

9:42So let's go back to data policies. We can

9:44just double click into where it says policy

9:46one and give it a name and call this sales

9:49person match.

9:51I'm going to describe what the policy does.

9:52Hit enter.

9:54Now, if I go back to my tables, you'll see

9:56that this has a nice, clear name and it's

9:59easy to use.

10:00And we can start sort of working with this

10:02over when we start connecting to data.

10:05Now, if I go back to my data policies, you

10:07can see this one's work.

10:09We've now created two row level security

10:11examples.

10:12Let's go ahead and create the third one,

10:14which is a user entitlement table example.

10:17OK, for this next one, if you go back to

10:18data policies column and create a new

10:20policy, you'll see that we have the same

10:22window.

10:23And for this one, I'm going to drag the

10:25employees table not onto this one, but over

10:27here onto the entitlement table example.

10:29And when we do that, things get a little

10:31bit sort of crazy because essentially what

10:33it wants us now to do is to map everything

10:35together.

10:36So what we now need to do is add all of

10:39these into the table.

10:42So I'm actually just going to add all of

10:43these in.

10:44You can see that this is essentially going

10:46to allow us to get this to work.

10:49So you can see the employees table is there

10:51and you can see the other tables are just

10:53here.

10:54So what we can now do is go ahead and match

10:56these up and what we're essentially doing

10:59is almost creating a relationship between

11:01these two,

11:02telling it what the employee ID is in both

11:05data sets.

11:06And then that sort of relationship

11:08interface is actually what's going to be

11:10driving the centralized low level row level

11:13security.

11:14So there we go. We've got that matching.

11:16So now that's good. We can't rename this

11:18particular capability because this is just

11:21the way it is essentially.

11:24So now that that's set up, what we now have

11:26to do is go back and let's preview this

11:29data one more time.

11:31I think it doesn't want to load because I

11:32haven't done anything here.

11:34So let's go in here and just type in what's

11:36going on.

11:37So for this one, you can bring in various

11:39items from your entitlement table.

11:42So for this one, I'm going to select full

11:44name, which in this case is actually coming

11:46from my employees table.

11:48And what I'm going to do is I'm going to

11:50say that equals the server full name here,

11:54like so.

11:54And that should become a valid centralized

11:56row level security.

11:58You can see the calculation is indeed valid

11:59. No notifications to be heard there.

12:03I'm going to leave this window open. So if

12:04we do make a mistake, it tells us.

12:06And then what I can do with the policy

12:08applied, let's go ahead and see what

12:10happens here.

12:11This should work. I think I need to

12:13actually go and choose a user.

12:15So let me let me make sure that I've

12:16selected myself in this particular use case

12:19and wait and see what's going on.

12:22So let's let that load a little bit and

12:24then we can we can sort of debug this if it

12:27's got a problem.

12:29So there you go. I've actually managed to

12:31load all of that.

12:32And if I go over to this database, you can

12:33see I'm actually looking at orders here.

12:36And the strange thing here is because we

12:38all this table doesn't have any salesperson

12:40name, it's just using employee ID.

12:43We can't actually see what's going on, but

12:45you can see here that it's got one employee

12:46ID, employee ID eight.

12:48And we can see one hundred and four of

12:50eight hundred and thirty rows.

12:52If I go over to the invoices table, this

12:55might take a little less time to load.

12:57And you can see that the only salesperson I

12:58can see here is Tim Neguena, which indeed

13:00matches my own server username.

13:03And again, this is being matched to the

13:05server full name and the full name that it

13:07's being matched to is coming from my

13:09employees table,

13:10which is the same as what we're sort of

13:12using to describe the entitlement for the

13:14user, essentially.

13:16So this is super cool. I really like this.

13:19This is a nice interface.

13:21It's actually it feels like it's been part

13:22of Tableau all along, which is really,

13:25really cool to see.

13:26It works in WebEdit only, of course, you

13:28have to go and set these up in the virtual

13:30connection setup.

13:31And now when you save this, if I go ahead

13:33and publish this, now this sort of data

13:36policy is now gone off and is now part of

13:38that virtual connections.

13:40Everyone who connects to this will only be

13:42able to work on their own rows that they

13:44have access to see.

13:46And so that's really, really cool. You

13:48could type in additional conditions, for

13:50example, full name equals full name or

13:53username equals Tim Neguena.

13:55And what that will do is it will

13:56essentially if you type the second one, it

13:58will essentially give you full access.

14:00If the username is Tim Neguena, then it

14:01will just give me access to everything.

14:04Otherwise, for everyone else, the full name

14:05is going to be what it's going to check

14:07again.

14:08So you can build in logic statements and

14:10checks to kind of create super access for

14:13specific people.

14:15Maybe they're part of groups. Don't forget,

14:17you've got the server based groups here so

14:21you can check if they're a member of a

14:22specific group and if they are, show them

14:24everything.

14:25And if not, then check their full name

14:26matches the entitlement table and off you

14:28go.

14:29So all of this is super, super powerful.

14:31You can sort of do a lot with this.

14:32And again, I really like that you've got

14:33the full calculation window interface here.

14:36So anything you've done in the past could

14:38now be ported into this particular window

14:40to do some really interesting things.

14:43So that centralized row level security.

14:45Once you've done this, it's applied to the

14:46virtual connection.

14:47And I've already shown you in the virtual

14:48connection video how to connect to your

14:50data set.

14:51And those connections will observe the

14:53policies that are set up here.

14:55I'd love to see column level policies. Of

14:57course, this has just come out. So

14:59immediately asking for the next thing.

15:00Column level policies will be great because

15:02it will be great for those instances where

15:04you're not having to build multiple work

15:06books for different groups of people who can

15:08't see certain metrics.

15:10You could build one workbook, one set of

15:12dashboards and just control what metrics

15:14people can and can't see.

15:16And of course, have some sort of capability

15:18to allow these to degrade gracefully, if

15:20that makes sense, inside of a dashboard.

15:23So that's it for this video. Thanks for

15:25watching. If you found this video useful,

15:26if you like the centralized row level

15:29security, then by all means, let me know in

15:31the comments.

15:33If you've got ideas or things you want to

15:34try, let me know in the comments and I'll

15:36try and do my best to have a go at this as

15:38well.

15:39For the virtual connections video and for

15:40this video, I'll try and put any resources

15:42that I've used in those descriptions as

15:44well.

15:45So check those descriptions for up-to-date

15:47information.

15:48And of course, the first comment is always

15:50pinned is from me and I'll update you there

15:52if there's an issue, if I've made a mistake

15:55or something's not quite right.

15:57So thanks for watching again and I'll catch

15:58you in the next video.

16:00Thank you.