Permission Groups that aren't applied in CS

Hello,

I'm looking for a livereport that displays Permission Groups that aren’t applied anywhere within the structure so we know which groups to remove.

I have created the below report below, however it may need to be expanded, I'm not sure if I'm missing some checks.

Select A.ID, A.Name from Kuaf A

Where A.Type = 1 And A.ID NOT IN (Select distinct RightId From DtreeACL) AND A.Deleted = 0

Order by A.Name;

Find more posts tagged with

Comments

Colin_Schmidt

That looks about right. You can use that “not in” or “and “A.Type = 1 and not exists (blah, etc.)”. I read somewhere that “not exists” can be more efficient, but check with others.

Colin Schmidt

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, November 27, 2017 1:47 PM
To: eLink Recipient
Subject: [EXT] Permission Groups that aren't applied in CS

Permission Groups that aren't applied in CS

Posted bywbrantley@cvrenergy.com (Brantley, Will) On 11/27/2017 01:46 PM

Hello,

I'm looking for a livereport that displays Permission Groups that aren’t applied anywhere within the structure so we know which groups to remove.

I have created the below report below, however it may need to be expanded, I'm not sure if I'm missing some checks.

Select A.ID, A.Namefrom Kuaf A

Where A.Type= 1 And A.IDNOT IN(Select distinct RightId From DtreeACL)AND A.Deleted = 0

Orderby A.Name;

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	My Support

The information contained in this message may be CONFIDENTIAL and is for the intended addressee only. Any unauthorized use, dissemination of the information, or copying of this message is prohibited. If you are not the intended addressee, please notify the sender immediately and delete this message.

Will_Brantley

I wanted to make sure this query is checking the parent group and potential subgroups to make sure nothing is left to chance. Does it capture everything?

Colin_Schmidt

If a nested group is in by its parent or any lineage above it, this won’t find it. It will only find the Group that is specifically added to the Perms.

It would involve checking with the “KUAFCHILDREN” and check each KUAFCHILDREN.ID that comes up when selecting KUAFCHILDREN.CHILDID. Though, it might be as simple as:

Where A.ID = 1

and (A.ID not in (select distinct dta1.rightid from dtreeacl dta1 where A.ID = dta1.rightid)

and A.ID not in (select distinct kc.id from kuafchildren kc join dtreeacl dta2 on dta2.rightid = kc.id where kc.childid = A.ID)

I’m unable to test this, but basically,

1). Check for an immediate hit in the DTREEACL (if hit, then it’s a good Group. If it doesn’t find it, it still could be in by a Parent.)

2). Check the GroupID in the KUAFCHILDREN as a “childid”, which would show all the Parents in the ID list. If any of those don’t hit,

I would think you’ve found an unassigned Group.

But you’d have to test this on a selected Object or tree by using the DTREEANCESTORS in the sub-queries to keep from it potentially reporting everything, should the code be wrong.

A.ID not in (select distinct DTA1.rightid from dtreeacl dta1 join dtreeancestors dtan on dtan.ancestorid = #### and dtan.dataid = dta1.dataid where ….etc.

Colin

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, November 27, 2017 3:52 PM
To: eLink Recipient
Subject: [EXT] [EXTERNAL] - RE Permission Groups that aren't applied in CS

[EXTERNAL] - RE Permission Groups that aren't applied in CS

Posted bywbrantley@cvrenergy.com (Brantley, Will) On 11/27/2017 03:47 PM

I wanted to make sure this query is checking the parent group and potential subgroups to make sure nothing is left to chance. Does it capture everything?

[To post a comment, use the normal reply function]
Topic:	Permission Groups that aren't applied in CS
Forum:	Content Server LiveReports Forum
Content Server:	My Support

John Simon

"...so we know which groups to remove"

Before you remove the groups, you might want to investigate if the groups are used elsewhere? For example: 1) we have groups that only exist because of workflow assignments as it is much easier to add/remove people to/from a group than it is to track down every map and change every step the user is assigned to (when the user needs to be replaced by another), 2) we have groups that only exist because of Object and Usage privileges (and other configuration settings).

Cheers...

Colin_Schmidt

That would involve querying the WMAPTASK table and/or the WSUBWORKTASK table.

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, November 28, 2017 9:18 AM
To: eLink Recipient
Subject: [EXT] Permission Groups that aren't applied in CS

Permission Groups that aren't applied in CS

Posted byjohn.simon@capspire.com (Simon, John) On 11/28/2017 09:16 AM

"...so we know which groups to remove"

Cheers...

[To post a comment, use the normal reply function]
Topic:	Permission Groups that aren't applied in CS
Forum:	Content Server LiveReports Forum
Content Server:	My Support

Will_Brantley

Thanks Colin and John. I got it working using the query below. You guy were very helpful! I appreciate it.

Select A.ID, A.Name from Kuaf A
Where A.Type = 1 And A.ID NOT Exists (Select distinct DTA1.RightId From DtreeACL DTA1 where A.ID = DTA1.RightId)
AND A.ID NOT Exists (Select distinct KC.Id from kuafchildren KC join DtreeACL DTA2 on DTA2.RightId = KC.Id Where KC.ChildId = A.Id)
AND A.Deleted = 0
Order by A.Name;

Will_Brantley

1. Would the inverse also be true if I was looking to find all groups where permissions are applied in CS (including, ancestors, parents & children)? I wasn't getting the results using the query below

            Select A.ID, A.Name from Kuaf A
            Where A.Type = 1 And A.ID IN (Select distinct DTA1.RightId From DtreeACL DTA1 where A.ID = DTA1.RightId)
            AND A.ID IN (Select distinct KC.Id from kuafchildren KC join DtreeACL DTA2 on DTA2.RightId = KC.Id Where KC.ChildId = A.Id)
            AND A.Deleted = 0
            Order by A.Name;

2. Where the groups are applied for all folders?