# Tableau Server - Which Users Have Access? Tableau PostgreSQL

## Метаданные

- **Канал:** Data Analytics
- **YouTube:** https://www.youtube.com/watch?v=x_LwyS93Yc0

## Содержание

### [0:00](https://www.youtube.com/watch?v=x_LwyS93Yc0) Segment 1 (00:00 - 05:00)

hi and welcome today we're going to be answering the question which users have access to what in tableau database now if you're a tablet server administrator you probably get this question quite frequently and be honest i so i wanted to build something where users that are not tablet administrators can actually go in and look for a person and see okay what group are they in and what access do they have so can they see the workbook that i just built that's essentially the question that people have and that's the question we want to solve we're going to give a little bit more data so they can start to understand exactly how tableau works in the background so in order to do this we are going to be using utilizing tableau's postgres database so you will need to be able to access that in order to do this we can get with your tableau admin if you are not a tablet admin user to kind of walk them through what you want but i'm going to show you a demo of what it looks like and so let's actually hop into the demo first and then we'll rewind back and walk through the mechanics of how to build it so here we have what the final product is going to look like again this is not a design or anything like that this is just for users to go in there and kind of search what they're looking for you can obviously jazz it up and here i just made it super quick and easy for someone to go in there and access it so i'm going to go look for myself so i typed in my name right here and so now i can see which group i'm a part of and all the projects that i have and i'm a part of uh multiple groups in here so i'm also a tableau server administrator you can see the multiple groups and what projects that group actually has access to so you can have users in multiple groups and it'll show you know all the groups they're associated with and the project are folders you know depending on how you your organization calls it they're called projects but those top layer folders it's going to show you exactly what they have access to and then on this side is actually going to drill down a little bit more and give you the project folder and the workbook so say for instance i know i'm a part of this particular group when i click on this it's going to show me every single project folder that i'm able to access as a member of that group but if i click on one particular report then it'll drill down to that report and show me the workbook names i actually have access to so this is super helpful as you started to build things out so let's go back out and let's just off click of that you can also search by workbook so say someone has a workbook they just created and they want to know if someone has access to it and so this is a pretty popular one that people are asking me about so if i go in here and i want to click know who has access to this particular folder i can click here and now i immediately get a name of all the people that have it and then how they have access to this particular folder and so it's only going to show the groupings of people that actually have access and in the groups they are associated with that happen to be in there so for instance this person gonzales has access to this particular suds folder because they're part of the coding and risk management they're part an ofp group and they're part of a pharmacy group so this person has access because they're part of this group name so it makes that association so user can go and search either way and if they want to come in here and then they want to say okay well i want to search this particular project but i want to know if this particular person jared has access they can search that you know with two different queries and again it's going to show up here and it's going to say yes that person does have access so there's no problem they can go ahead and access this and that just solves that question so that's ultimately what we want to build something that's pretty robust and you know pretty simple to use like that so let me go into the back end and show you how this is done so there's going to be about four steps in order to create this less stress environment where users can go in and see who has access to what groups they're part of and what projects and workbooks can they access and even the views that they can access so the first thing we're going to do is we're going to work on querying the data and this it actually took the most amount of time trying to figure out which tables tableau was actually using to be able to create all these different rules and try to get everything so it was something that was human readable so i'm going to walk you through that that's what's going to take you the most amount of time if you don't have a shortcut and i'm going to show you how to do that next thing we're going to do is we're going to build a view so the dashboard you just saw we had views in there so we're going to build that we're going to actually build up the dashboard i'm going to show you how to do some of the action filters and it's pretty quick and then lastly we're going to publish it so we can share with other people so let's dive in into querying the data first okay so here we are in pg admin and i'm just using pg admin because it's easy for me to write the queries in here and kind of work with this but you can also build all this in tableau you don't have to do it in here just made it easier for me to write the query out and kind of build it out the way i wanted it to so i'm going to go to my databases right here and so the name of the actual postgres database that tableau uses call work group so when you actually log into your database again credentials and everything like that you want to watch the postgres tablet's postgres database tutorial but it's called work group and we're going to go to our schemas down here and so the default schema for tableau is going to be this public so good to know especially if you watch our hyper training hyper api it defaults to this public thing just because it wants to be postgres compliant um so yeah you go to this public folder and so you have different things so you

### [5:00](https://www.youtube.com/watch?v=x_LwyS93Yc0&t=300s) Segment 2 (05:00 - 10:00)

have views right here and then you have tables so you can see there's over 255 different tables that are supporting all of the user actions that occur in tableau it's ridiculous there are a lot of tables in here let me just open this up real quick so you can see how confusing this could get very quickly and if your tableau admin you are already familiar with all these different tables in here they just they track everything so anything you need and it's not they but you have all of the different things that you need in here so i recommend if you're starting out mostly just use the views are updated it's the same data from here they just kind of made it a lot easier to use and in this particular project i use views because i didn't need any robust data but all these table do have bigger views so especially i'm using the users table um you would have to use the system users table and the regular users table in order to get kind of what we have done at just one table in here so very handy so the first thing i want to take a look at is the first query that i built here and so this is going to be a user access and so this query is going to be split into two parts one is going user access and the other one is called group access so in this table i just want to know which users are part of which group that was a question that i wanted to solve i bought in some extra data just so i could you use it for other reports and especially because i had to use some real level security so this came in handy but essentially i just want to know based on this tableau username i want to know which group they're part of because if i then i can you know let the users know okay hey this person is at least in this group and this group has access to this project so that's kind of the workflow that we use so i separated out my queries just so i could use the query separately but you can put them all together and so let's just go to the tables that were used because that's the most important part and if you wanted to build something similar to this you can just use this right here and also if you click on the link below i'll take you over to my website where you can download all the scripts everything that i'm using the workbook all the data you can go ahead and do it one thing we are connecting to a postgres database so you're going to have to know your user credentials in order to get in there but you can still have the workbook sent into your admin and say hey i'm trying to get this live can you just help me do this and they'll just put their user credentials in there and you'll be good to go so it's pretty easy so again the first thing i did was create this user access table again this could be leveraged for multiple things so that's why i separated it out because i had another project going on and right here we can see that we have the uh the tableau username the full name um just the last time we logged in and this group id using this structure right here and this group user table kind of brings everything together so it unites the user and the group table so you want to make sure you have that tableau user group there so that was the first thing i did then i went ahead and built this one which provides the group access which this one is where it gets complicated but you are going to need this table right here which is the next gen permissions this controls all the permissions for all the different groups and earlier i said you need to have some type of structure into how you implement your security and that's where this part comes in because the grantee type and the author is the authorizable type are kind of key components into making all this work and if you don't have a certain set where you actually use groups and projects then there's other ways in which you can view because there's different types for both of these columns but you need to understand the types in order to be able to build this out else it's going to return false data because it's just numerical data that it's returning because we're using the id and so we want to filter the state out so that is your query that you're going to need to use again in the link below you can click on it you can download this bring it in and i'm going to do a bigger training full training on this on that site as well where you can go in and kind of learn what these tables are and how to bring them together so that's what we have here so we finished our first step which is gathering the data so next we're going to head over to tableau and see exactly how to connect this and start building our view so here you can see we have all the different views that you can use again i would primarily just use the regular tables but you use it right here but again i just use custom sql to build this out so we have our users and it ends in a group id and then it connects right there and these are two tables are connected based on that group id and again this just returns the groups and everything that we want to see in there um and so i made that connection right there so the first thing we're going to do is and i actually still have this filter from my demo so let me clear this out so we can get all of the data so i created two uh workbooks one is going to be every worksheets i should say users and a workbooks table and this users just allows me to see all the users and kind of which project they're a part of and it's pretty much straightforward i just want a user to be able to click on a name and then get all the information like we saw and then here we have all of our different project folders that we have on tableau server and then the workbooks associated with them and then you know it can drill down into more details if you want to bring the views in we also have the views in that query so you can see the actual view name if you want to i have that set up over here again not really important for what the use case that i've needed and then lastly we bought it all together and that third step to build this dashboard out so you just drag in your worksheets build

### [10:00](https://www.youtube.com/watch?v=x_LwyS93Yc0&t=600s) Segment 3 (10:00 - 12:00)

it out kind of make it look the way you if you want to add these filters in there which is the search filter parameters all you have to do is go here to filters click on it and you can select the item that you want to bring on there make sure it's floating just so you can have it wherever you want it and then to get this kind of wildcard search look you just select that i didn't do anything fancy i didn't build a custom stream query i technically could have just created one search box and you can search by any one of these names but again i really wanted to make this super simple because it took so long just to get the data modeled together and figure out which tables to use but once you have that all together you have it in the view and for actions i actually didn't even create special action filters i just used the quick filter um actions that were built into here and it worked out perfectly so i didn't even create any custom actions you can always go in and create custom actions if you want to but yeah as you can see i didn't name them didn't do anything it just searches all fields so you click on this it filters everything there but you can make it a little you know less robust or more robust whatever you want to do by just creating your filters and filtering those views out but i just use the quick filters in this um just to get it done and you see it works better than anything i would have done so that is how you can build this type of view right here and all you have to do is just publish it and share it with your users and i mean i just published this i want to say last night it was probably around 11 and 7 o'clock this morning i got an email saying oh my god thank you this is amazing so this is really great and you can search users by first name last name and everything here you can search for any workbook or project or anything like that and it just makes your life so much easier here so that is how you build that and again if you want to click in the link below i will give you access to the actual workbook so you can build a workbook yourself as well as a sql query so if you want to build this in postgresql then you can build that out and make it robust and then also i have a training where we go really in depth we walk over the tables we show you everything that you need to know and understand about how to use the progress database in order to get user data all right so that's all i have for you thank you and i'll see you in the next video

---
*Источник: https://ekstraktznaniy.ru/video/44457*