how would it break the EULA? Is dblink an option for you?
From what I've read in the Tableau Server Database All-Access Pass, it seems like Tableau Software does not support the tblwgadmin command to grant select on all tables in the schema. I'm open to any options for pulling the data. Do you have more documentation or examples of dblink working with tableau?
oh, I wouldn't grant access to all tables. And if you open up readonly access which is permitted via tabadmin dbpass, then this should give you access to the tables you need. tabadmin commands
You can find descriptions of the table here: Workgroups Database
Here's an example of setting up a dblink, though I did it with a stand-alone Postgres instance. The syntax may be different for Oracle.
CREATE EXTENSION dblink;
CREATE SERVER tableau FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '***your_ip_address', dbname 'workgroup', port '8060');
CREATE USER MAPPING FOR postgres SERVER tableau OPTIONS (user 'readonly', password '***your_password');
--SELECT dblink_connect('myconn2', 'tableau');
--Retrieve some rows
--SELECT * FROM dblink('tableau','SELECT name FROM sites') AS t (name varchar);
Why do you want to do this? nextgen_permissions should be able to provide tracking of permissions.
Thank you for the info. I am trying to do this to Audit changes in permissions over time. The next_gen_permissions table along with capabilities, roles, etc - give me permissions right now, but I want to track permissions yesterday vs. today vs. tomorrow. So I was looking to pull the tables into Oracle to append a date column that time stamps permissions every day.
Have you seen this done before? Do you have any idea of the best way to do this?
To do this, you can either build an ETL process against the active PostgreSQL repository database (or the passive repository, which is a better plan, if you're using High Availability), and export the data to your Oracle DB. Numerous tools can accomplish that. Alteryx, Talend, Pentaho, Python, etc...there are hundreds of ways to do it. If you want to pull data as safely as possible so that you don't impact the live database, you can take a nightly backup, unzip the .tsbak file, extract the .pgdump file, and then use pg_restore to restore the database backup to a completely independent PostgreSQL server. That way you don't impact Tableau Server performance any more than you might with a standard backup (which you should be taking nightly anyway! )
Matt, thank you for your help!
When connecting to the workgroup database (active or passive) should I be using the readonly or tableau username and password that I set up with tabadmin dbpass? Or is it a different connection with tblwgadmin workgroup?
Do you have reference material for taking a nightly backup, unzipping .tsbak, extracting .pgdump, and restoring?
You will need to use the readonly user, not the tableau user, as the tableau user will not have access to the next_gen_permissions table. I don't have any reference material for the backup-extract dump-restore process, no. But the process can be summed up with links...something to this effect:
In a script that runs nightly:
Run tabadmin backup
Extract the pgdump file using 7zip
Copy the pgdump file to whatever host you're running your separate PostgreSQL server on
Use the pg_restore command to actually run the restore process on that host
Use your ETL process, or set of SQL queries, to diff the next_gen_permissions tables between the restored database copy and the snapshot from the previous day in some other database you're maintaining.
Thank you very much, I really appreciate your help!