mfeinman62 asked a question.
Hi all ...
One of the items I am looking into is the reassignment of private views or reports from a Windows Authentication id (Domain/Username) to a SAML-based id (E-mail Address). In other words, after the conversion from Windows Authentication to SAML Authentication, I want MyDomain/JohnDoe to still access his private reports and views after his persona becomes JohnDoe@MyDomain.com.
After doing some research, I see that I can reassign the reports two ways:
- I can modify the FNMSCompliance.dbo.ComplianceSavedSearch table and set the private report to public (set RestrictedAccessTypeID = 1) but that makes it available to everyone. Not an issue if I get permission to do this.
- I can modify the FNMSCompliance.dbo.ComplianceSavedSearch table and update the ownership id (set CreatedByOperatorID to the ComplianceOperatorID of the new owner).
[I may have to make a private folder public (or change the folder to one that already exists).]
While writing these queries and the SQL update statement, I saw that there are private reports and views that exist for employees who no longer work at my agency. Some date back many years.
I could reassign them to someone else or make them public, but that got me thinking - is there a way to clean up private reports/views owned by ex-users?
--Mark
There is no way in the web UI to delete private reports & views owned by somebody else. This would need do be done by deleting records from the ComplianceSavedSearch view directly in the database.
With that said, I'm not sure if there is any real reason to need to do this. Report definitions generally don't contain sensitive or personal data that might require removal, so it is probably easiest just to leave them there and not worry about them.
You are right, @ChrisG (Flexera Software)​ . I don't plan on doing anything with these report definitions, other than making sure that private ones can still be seen by users after the SAML conversion takes place. I was just surprised to see some from ex-users. Since the report definitions could be useful to other team members, I can reassign ownership using a SQL update statement. My question was based on curiosity more than anything else.
--Mark
I believe it would be safe to compare ComplianceOperator table with ActiveDirectory users and get the ComplianceOperatorID's of users that are gone in AD. Then clean up the ComplianceSavedSearch table deleting the reports created by those users and that are not public.
Is that your idea, Mark? Or am I missing something?
Hi @ppyrzynski​ ... The question was more out of curiosity rather than looking to actually do something.
--Mark
The field CreatedByOperatorID in ComplianceSavedSearch is actually a foreign key to ComplianceOperator. I couldn't find any example for reports with limited access and a CreatedByOperatorID missing, I assume the removal of an operator also removes personal reports.
There might still be disable operators with private views and/or reports.
SELECT
css.ComplianceSavedSearchID
,css.SearchName
,co.OperatorLogin
,IsEnabled
FROM ComplianceSavedSearch css
LEFT JOIN ComplianceOperator co
ON css.CreatedByOperatorID = co.ComplianceOperatorID
WHERE css.RestrictedAccessTypeID = 2
AND co.IsEnabled = 0
Why would you ever remove an operator from ComplianceOperator table? I would say the operators are never removed from the DB. OperatorID is used in many places, history being one. WebUI doesn't give you that option, does it? All you can do is to disable operator. In our environment users request access to the tool via IMDL. Once a role is granted then I create and enable operator in FNMS. But the IMDL roles expire at some point and if user doesn't extend it I disable the account in the tool. A user can then request the role again, and I reenable the account. So removing reports that belong to disabled operator would cause that such returning operator would loose his previously created reports. This being said, I think it's better to base the report removal on account's presence in AD. If it's not there then the user is no longer with the company and such report can be safely removed. That's how I see it.
Of course, it makes sense to tie this process to other processes & data sources, like Offboarding or AD.