08-22-2012 03:04 PM
We typically do not add and remove users from the User/Groups under the application tab in DA client. But today, I took a look and found a user that should not be in there whatsoever. Now we are trying to track down how he got in there. Who put him there? When was he put there? Has he done any searches? What permissions do you need to even get into the client and add a user. Though he currently does not have a role assigned to him, I don't know that this has always been the case. Basically need to know if there is any tracking going on so we can see how this has come to be. Any assistance is much appreciated.
Solved! Go to Solution.
08-23-2012 02:17 PM
I get what you are trying to do. The above queries were in response to you wanting to see if they had ran any searches and to show you if they had been assigned any permissions on any cases.
As for who added him, who has the Discovery System Admin role? Also, you will need to look at any potential custom role you have created that has the permission to assign roles.
The Admin guide has this:
You must have the application permission Manage Administration Security to assign an application-wide role to a user. You require the case permission Role Assignment to assign a case-specific role. By default, Discovery System Admin users have the first permission, whereas Case Administrator users in a case have the second
I believe the ModifiedDate column from the query below will be when the user was added.
select * from tblPrincipal
Not real sure how to find out who added him, but it should be a short list based on the roles above.
08-23-2012 06:19 AM
For permissions try this:
Permissions are logged in the table tblHistCasePermission.
You could use a query like this to see what permissions were added then removed.
SELECT c.name as CaseName
, p.PrincipalLogin as DomainUser
, tp.name as PermissionName
, hc.startdate as StartDate
, hc.enddate as EndDate
FROM tblHistCasePermission hc
JOIN tblPrincipal p on p.PrincipalID = hc.PrincipalID
JOIN tblPermission tp on tp.PermissionID = hc.PermissionID
JOIN tblCase c on c.CaseID = hc.CaseID
--WHERE c.Name = 'CASENAME'
GROUP BY c.name, p.PrincipalLogin, tp.name, hc.StartDate, hc.EndDate
08-23-2012 06:32 AM
For if they ran any searches try this, find out the PrincipalID from tblPrincipal
SELECT * FROM tblPrincipal then edit the where clause below:
SELECT SearchID,
CaseID,
NumHits,
PrincipalName AS ModifiedBy,
tblIntSearches.Name AS SearchName,
tblIntSearches.ModifiedDate AS DateModified,
CreateDate,
tblStatus.[Name] AS SearchType,
CreationType.[Name] AS CreationType,
SampleResultSize,
NativeQuery,
NativeLegacyQuery,
XMLText
FROM tblIntSearches
LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[ModifiedByID] = tblPrincipal.[PrincipalID]
INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]
INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]
WHERE tblPrincipal.[PrincipalID] = 3
UNION ALL
SELECT TypeID AS SearchID,
tblAudit.CaseID,
NumHits,
PrincipalName AS ModifiedBy,
tblIntSearches.Name AS SearchName,
AuditDate AS DateModified,
CreateDate,
tblStatus.[Name] AS SearchType,
CreationType.[Name] AS CreationType,
SampleResultSize,
NativeQuery,
NativeLegacyQuery,
XMLText
FROM tblIntSearches
INNER JOIN tblAudit ON tblAudit.TypeID = tblintSearches.SearchID
AND tblIntSearches.StatusID = 858
AND tblAudit.AuditTypeID = 1052
LEFT OUTER JOIN [tblPrincipal] ON tblIntSearches.[CreatedByID] = tblPrincipal.[PrincipalID]
INNER JOIN [tblStatus] ON tblIntSearches.[Type] = tblStatus.[StatusID]
INNER JOIN [tblStatus] CreationType ON tblIntSearches.CreationType = CreationType.[StatusID]
ORDER BY CreateDate DESC
08-23-2012 01:44 PM
This is really helpful. Thank you. We are looking more on the application level. We need to know who and when did someone add a user to the user/group tab. There is no roles assigned to this user, but we need to know how he got there to begin with. Any assistance would be helpful and appreciated. Thanks.
08-23-2012 02:04 PM
To clarify, we are looking to see the info for the Users and Groups, Role Assignments under the Application Tab. It appears that there has been a user added that should not have been added. We would like to see if we can find out when it was added and by whom. Thank you.
08-23-2012 02:17 PM
I get what you are trying to do. The above queries were in response to you wanting to see if they had ran any searches and to show you if they had been assigned any permissions on any cases.
As for who added him, who has the Discovery System Admin role? Also, you will need to look at any potential custom role you have created that has the permission to assign roles.
The Admin guide has this:
You must have the application permission Manage Administration Security to assign an application-wide role to a user. You require the case permission Role Assignment to assign a case-specific role. By default, Discovery System Admin users have the first permission, whereas Case Administrator users in a case have the second
I believe the ModifiedDate column from the query below will be when the user was added.
select * from tblPrincipal
Not real sure how to find out who added him, but it should be a short list based on the roles above.
08-23-2012 04:09 PM
Thank you Tony. I was able to see when the person was added, and from there see who may have been doing anything at that time and had the permissions to add that user. All of the scripts came into play. I really appreciate your efforts to assist me. Thanks again.
08-23-2012 04:16 PM
Glad you were able to suss through it! :)
Cheers,