Information on Auditing sensitive data

Sajith P Shetty shared this question 55 days ago
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

Comments (6)

photo
1

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

photo
1

Hi Sajith,

I just wanted to check in and see how things are going with this.

Regards,

Mike

photo
1

Hi Mike,


Thanks for your time and suggestion. You can close this as solved!

photo
1

Hi Sajith,

Great! Thanks for confirming.

Please don't hesitate to reach out with any other questions or concerns.

Regards,

Mike

photo
1

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

photo
1

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:

/2f22c404f7b5d4960c2266989ac73822

Please let me know if you have any other questions on this.

Regards,

Mike

photo
1

Hi Mike,

In that case my initial question still unanswered.

How do I get user details from reportinstance table.

photo
1

Hi Sajith,

In cases where the requestor has a RequestTypeCode of 'ORGANISATION' rather than 'PERSON', you'll have to then refer to results from the 'Event' table.

Here's an example from my ReportInstance table:

/3cc7822923e6415a7c6472e13fbeca9b

I can then find this ReportId ('90847') in the Event table by querying something like:

SELECT * FROM yf92.event WHERE EventData LIKE '%90847%';
/8f7b172afba96bb9b9619bf332a401f6

As you can see, this gives several indications - the date and datetime of the event, which in this case the EventCode indicates this report was run on a Dashboard, and in the EventData column you can see the requestor=5, and the requestortype=PERSON, so of course this value you can see in the IpClass table, and '5' by default is the Admin user.

Combining results from these two tables should hopefully give you what you're looking for, but please let me know if you have any additional questions or concerns on this.

Regards,

Mike

photo
1

Hi Sajith,

I just wanted to check in and see if you require anything else, or if we're okay to close this case out.

Regards,

Mike

photo
1

Hi Mike,


All ok now!

Thanks for your quick and swift response.

-Sajith P Shetty

photo
1

Hi Sajith,

You’re welcome! Thanks for confirming.

I’ll go ahead and close this out then, but please don’t hesitate to reach out with any other questions or concerns.

Regards,

Mike

photo