List Subitems Where Permissions on SubItems Differ From Parent Node

Hi big-brains! I'm hoping that someone more savvy than I am can solve this problem. I'm stumped on making a particular LiveReport more efficient. The goal is to find all folders with a certain naming convention (say: "Special"), then list all items (folders and docs) underneath those folders where said items' permissions differ from the root node's.

I've tried two different approaches but neither scale. The two approaches I tried were:

SELECT DISTINCT %1, b.Name as Parent, d.Name AS Login, c.Permissions AS "gPermissions"
FROM DTree DTree, DTree b, DTreeACL c, KUAF d
WHERE DTree.ParentID=b.DataID
AND c.DataID=DTree.DataID
AND d.ID=c.RightID
AND (c.RightID, c.Permissions) NOT IN (SELECT RightID, Permissions FROM DTreeACL WHERE DataID=b.DataID)
START WITH b.Name LIKE '%%(Special)%%' CONNECT BY NOCYCLE PRIOR b.DataID=b.ParentID
ORDER BY b.Name

And also:

WITH ParList (ItemID, RootID) AS
( SELECT a.DataID AS ItemID, CONNECT_BY_ROOT b.DataID AS RootID from DTree a, DTree b
WHERE a.ParentID=b.DataID
START WITH b.Name LIKE '%%(Special)%%' CONNECT BY NOCYCLE PRIOR b.DataID=b.ParentID
)
SELECT %1, b.Name AS ParentName, c.Name AS RootName, a.Permissions AS "gPermissions", d.Name AS UserName FROM DTree DTree, DTree b, DTree c,
KUAF d,
((
SELECT DISTINCT b.ItemID, b.RootID, a.RightID, a.Permissions AS Permissions FROM DTreeACL a, ParList b WHERE a.DataID=b.ItemID
MINUS
SELECT DISTINCT b.ItemID, b.RootID, a.RightID, a.Permissions AS Permissions FROM DTreeACL a, ParList b WHERE a.DataID=b.RootID )
UNION
( SELECT DISTINCT b.ItemID, b.RootID, a.RightID, -1 FROM DTreeACL a, ParList b WHERE a.DataID=b.RootID
MINUS
SELECT DISTINCT b.ItemID, b.RootID, a.RightID, -1 FROM DTreeACL a, ParList b WHERE a.DataID=b.ItemID
)) a
WHERE DTree.DataID=a.ItemID
AND c.DataID=a.RootID
AND b.DataID=DTree.ParentID
AND d.ID=a.RightID
ORDER BY RootName, ParentName, Name

Thanks for any help. It's a tricky one.

Ben

Find more posts tagged with

Comments

Benjamin Shapiro

OK, looks like no takers. I added a "distinct" clause at the beginning, but still no luck.

If anyone can think of a solution, I'd love to hear it.

Thanks,

Ben

John W. Simon, Jr.

Ben,

I think you scared everyone by asking for help from big brains! I'm definitely not in that category...but have you tinkered with replacing your "connect by" on dTree with a call to dTreeAncestors instead?

Other than that I would ask Oracle for help. Replace the sqlid below with your sqlid and execute this script. Oracle should come back with recommendations.

JWS

--------------------------------------

set linesize 150
set long 2000000000
set longchunk 1000
set pagesize 50000
set serveroutput on size unlimited

variable task varchar2(64);

execute :task := dbms_sqltune.create_tuning_task(sql_id => '9fprxq5zscrh4');
execute dbms_sqltune.execute_tuning_task(:task);
select dbms_sqltune.report_tuning_task(:task,'TEXT','ALL','ALL') from dual;
select dbms_sqltune.script_tuning_task(:task,'ALL') from dual;

Benjamin Shapiro

Thanks John. But I disagree with your non-big-brain self-assessment.

For those interested in knowing, Colin Schmidt provided me a solution that uses joins and worked perfectly well. I modified it for the specific requirements, and here it is:

Report to list all items under a defined collection of roots where the permissions are different from said root node(s):
---
WITH FullList (Anc, Root, ChildID, Child, Perms) AS
(SELECT DISTINCT dtan.AncestorID Anc, dtp.Name Root, dtan.DataID ChildID, dtc.Name Child,
CASE
WHEN EXISTS (SELECT dtac.Permissions FROM DTreeACL dtac WHERE dtan.DataID = dtac.DataID AND dtap.RightID = dtac.RightID AND dtap.Permissions <> dtac.Permissions) THEN 'Different Perms'
WHEN (SELECT COUNT(*) FROM DTreeACL dtaz1 WHERE dtan.AncestorID = dtaz1.DataID) <> (SELECT COUNT(*) FROM DTreeACL dtaz2 WHERE dtan.DataID = dtaz2.DataID) THEN 'Different Users'
ELSE 'Matching' END Perms
FROM DTreeAncestors dtan
JOIN DTree dtp ON dtan.AncestorID = dtp.DataID
JOIN DTree dtc ON dtan.DataID = dtc.DataID
JOIN DTreeACL dtap ON dtan.AncestorID = dtap.DataID
LEFT OUTER JOIN KUAF kc ON dtap.RightID = kc.ID
WHERE dtan.AncestorID IN (SELECT DataID FROM DTree WHERE ::Whatever your criterion is::) )

SELECT %1, FullList.Root, FullList.Perms, DTName.Name PName
FROM FullList, DTree DTName, DTree DTree
WHERE FullList.Perms != 'Matching'
AND DTree.DataID=FullList.ChildID
AND DTName.DataID = DTree.ParentID
ORDER BY FullList.Root, DTree.Name
---
Use "Report Fields" for %1, and obviously "LiveReport:" format.

It can certainly be tweaked more to provide the specific permissions changes, etc. but this provides an excellent high-level view.

Cheers,
Ben

On 2015-01-28 8:23 AM, eLink Entry: Content Server LiveReports Forum wrote:

eLink : List Subitems Where Permissions on SubItems Differ From Parent Node
List Subitems Where Permissions on SubItems Differ From Parent Node Posted by john.w.simon@conocophillips.com (Simon, Jr., John W.) On 01/28/2015 08:19 AM

Ben, I think you scared everyone by asking for help from big brains! I'm definitely not in that category...but have you tinkered with replacing your "connect by" on dTree with a call to dTreeAncestors instead? Other than that I would ask Oracle for help. Replace the sqlid below with your sqlid and execute this script. Oracle should come back with recommendations. JWS -------------------------------------- set linesize 150
set long 2000000000
set longchunk 1000
set pagesize 50000
set serveroutput on size unlimited variable task varchar2(64); execute :task := dbms_sqltune.create_tuning_task(sql_id => '9fprxq5zscrh4');
execute dbms_sqltune.execute_tuning_task(:task);
select dbms_sqltune.report_tuning_task(:task,'TEXT','ALL','ALL') from dual;
select dbms_sqltune.script_tuning_task(:task,'ALL') from dual;