Information on Auditing sensitive data
Answered
We have a process in place to mask and audit sensitive information in datawarehouse.
However we need some help in auditing in yellowfin repo DB.Note that we have data security in place for user level, this is for auditing developers.
For example, HR sensitive data can be accessed by a BI developer in yellowfin using the data source by creating a SQL based report. We want to audit the username and related info whenever developer tries to fetch sensitive data.
Basically we need to know the table names in yellowfin REPO DB which will have info on the user , the table-column(query) he/she tried to access while creating a SQL based report. NOTE: We have postrges report DB nad yellowfin 7.4.12
The same question
Hi Sajith,
Thanks for reaching out. For the first part of your question on where user details are stored, these details are primarily found in the IpClass, IpContact and Person tables. There are other User references in other tables, but they'll just be linked on Id's. These three tables are where you'd actually see names and email addresses.
In terms of the other aspect, probably the best way to accomplish this would be by looking at SQL queries that may have been executed, the ReportInstance table stores historical records of report runs and edits. In this table you can see the SQLText column, which stores the actual SQL query run on a particular report run, as well as the IpRequestor, which indicates who ran the report. By comparing the IpRequestor (whose value is stored in the IpClass table as IpId), with the SQLText (where one of the user tables/columns specified above was referenced) and/or ReportId, if you already know which report sensitive data was accessed from, you should be able to tell who ran a report where user data was returned.
I should also point out we do have an Audit Content package that can be downloaded from the Yellowfin Marketplace. I'm not sure it has exactly what you're looking for here, but it may be worth taking a look through this content as well. This audit content contains 5 views, 3 dashboards and a set of reports to explore your system usage. It allows you to analyse your most active and least active users, roles, groups and client organisations along with the most used reports, dashboards, storyboards, views and data sources. It can also be used to analyse broadcasts, scheduling and monitor your migration patterns.
Hopefully this helps! Please let me know if you have any additional questions or concerns on any of this.
Regards,
Mike
Regards,
Mike
Hi Sajith,
Thanks for reaching out. For the first part of your question on where user details are stored, these details are primarily found in the IpClass, IpContact and Person tables. There are other User references in other tables, but they'll just be linked on Id's. These three tables are where you'd actually see names and email addresses.
In terms of the other aspect, probably the best way to accomplish this would be by looking at SQL queries that may have been executed, the ReportInstance table stores historical records of report runs and edits. In this table you can see the SQLText column, which stores the actual SQL query run on a particular report run, as well as the IpRequestor, which indicates who ran the report. By comparing the IpRequestor (whose value is stored in the IpClass table as IpId), with the SQLText (where one of the user tables/columns specified above was referenced) and/or ReportId, if you already know which report sensitive data was accessed from, you should be able to tell who ran a report where user data was returned.
I should also point out we do have an Audit Content package that can be downloaded from the Yellowfin Marketplace. I'm not sure it has exactly what you're looking for here, but it may be worth taking a look through this content as well. This audit content contains 5 views, 3 dashboards and a set of reports to explore your system usage. It allows you to analyse your most active and least active users, roles, groups and client organisations along with the most used reports, dashboards, storyboards, views and data sources. It can also be used to analyse broadcasts, scheduling and monitor your migration patterns.
Hopefully this helps! Please let me know if you have any additional questions or concerns on any of this.
Regards,
Mike
Regards,
Mike
Hi Sajith,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hi Sajith,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hi Mike,
Thanks for your time and suggestion. You can close this as solved!
Hi Mike,
Thanks for your time and suggestion. You can close this as solved!
Hi Sajith,
Great! Thanks for confirming.
Please don't hesitate to reach out with any other questions or concerns.
Regards,
Mike
Hi Sajith,
Great! Thanks for confirming.
Please don't hesitate to reach out with any other questions or concerns.
Regards,
Mike
Hi Mike,
The iprequestor column in reportinstance is coming as "1" and which does not have related record in ipclass table. Can you please help here!
Thanks,
Sajith P Shetty
Hi Mike,
The iprequestor column in reportinstance is coming as "1" and which does not have related record in ipclass table. Can you please help here!
Thanks,
Sajith P Shetty
Hi Sajith,
The IpRequestor value isn't always a user, it can be an Organization. '1' is the Default org. You can see possible IpOrg values in the Organisation table:
Please let me know if you have any other questions on this.
Regards,
Mike
Hi Sajith,
The IpRequestor value isn't always a user, it can be an Organization. '1' is the Default org. You can see possible IpOrg values in the Organisation table:
Please let me know if you have any other questions on this.
Regards,
Mike
Replies have been locked on this page!