Permissions Matrix of a Folder

Upinder_Minhas

I found this query here on KC which works great but its in Oracle can someone help changing this to MS SQL

SELECT d . rn "RowNum" , d . dataid "DataID" ,

LPAD ( '+' , lvl - 1 , '+' ) || ' ' || d . NAME "[+Hierarchy] Name" ,

d . path ,

DECODE ( d . subtype ,

0 , 'Folder' ,

1 , 'Shortcut' ,

2 , 'Generation' ,

130 , 'Topic' ,

131 , 'Category' ,

134 , 'Response' ,

136 , 'Compound doc' ,

138 , 'Release' ,

140 , 'URL' ,

144 , 'Document' ,

146 , 'Customview' ,

202 , 'Projet' ,

215 , 'Discussion' ,

258 , 'Request' ,

299 , 'LiveReport' ,

557 , 'E-mail composite' ,

751 , 'E-mail folder' ,

'Other' ) "Object type" ,

lvl - 1 "Level" ,

decode ( da . acltype , 1 , 'Owner' ,

2 , case when subtype = 202 THEN 'Coordonator'

when da . ownerid > 0 THEN 'Coordonator'

ELSE 'Owner Group' END ,

3 , 'Public' ,

0 , case when subtype = 202 and k . name = 'Members' THEN 'Members'

when subtype = 202 THEN 'Guest'

when da . ownerid > 0 and k . name = 'Members' THEN 'Members'

when da . ownerid > 0 THEN 'Guest'

ELSE 'Assigned' END ,

' ' ) "Access type" ,

coalesce ( case when kk . type = 0 then kk . firstname || ' ' || kk . lastname || ' (' || kk . name || ')'

else kk . name end ,

case when k . type = 0 then k . firstname || ' ' || k . lastname || ' (' || k . name || ')'

else k . name end ,

' ' ) "User/Group" ,

decode ( bitand ( da . PERMISSIONS , 2 ), 2 , 'See' , ' ' ) || ', ' ||

decode ( bitand ( da . PERMISSIONS , 36865 ), 36865 , 'SeeContent' , ' ' ) || ', ' ||

decode ( bitand ( da . PERMISSIONS , 65536 ), 65536 , 'Modif' , ' ' ) || ', ' ||

decode ( bitand ( da . PERMISSIONS , 131072 ), 131072 , 'ModifAttrib' , ' ' ) || ', ' ||

decode ( bitand ( da . PERMISSIONS , 4 ), 4 , 'Add' , ' ' ) || ', ' ||

decode ( bitand ( da . PERMISSIONS , 8192 ), 8192 , 'Reserve' , ' ' ) || ', ' ||

decode ( bitand ( da . PERMISSIONS , 16384 ), 16384 , 'DelVersions' , ' ' ) || ', ' ||

decode ( bitand ( da . PERMISSIONS , 8 ), 8 , 'Delete' , ' ' ) || ', ' ||

decode ( bitand ( da . PERMISSIONS , 16 ), 16 , 'ModifPerm' , ' ' ) "Rights"

FROM ( SELECT c . dataid , c . SUBTYPE , c . NAME ,

livelink . GETLLPATH ( d . ParentID ) as path ,

LEVEL lvl , ROWNUM AS rn

FROM dtree c

WHERE c . SUBTYPE IN ( 0 , 136 , 202 , 751 )

and level <= 99 CONNECT BY PRIOR c . dataid = ABS ( c . parentid )

START WITH c . dataid = 10587503 ) d

INNER JOIN dtreeacl da

ON da . dataid = d . dataid

left JOIN kuaf k

ON da . rightid = k . id

left join ( select * from kuafchildren

where exists

( select * from kuaf where name in ( 'Coordinators' , 'Members' , 'Guests' ) and kuaf . id = kuafchildren . id )

) kc

on k . id = kc . id

left JOIN kuaf kk

ON kc . childid = kk . id

where da . rightid <> - 2

and not (( d . subtype = 202 or da . ownerid > 0 ) and da . acltype = 1 )

and not exists

( select * from kuaf where name = 'Coordinators' and kuaf . id = kc . CHILDID )

and not( k . name = 'Guests' and kc . id is null and da . acltype <> 3 )

order by d . rn , decode ( da . acltype , 0 , 99 , da . acltype ), da . rightid

eLink User

Not really what you want but hopefully will help you with your endeavour.I don't have a SQLserver handy to try and make it all the way for you:)
I am sure Nicole Bride will give you something to help iterate for the oracle connect by ,but connect by is a oracle thing.IN sqlserver you have to write a CTE for this.Actually to crawl nodes underneath you can use dtreeancestors so here's what I do

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objaction=overview&objid=39405404

Note in my audit I am looking for any folder that has inadvertently excess perms for PA

this is how in SQLserver BitWise is done They teach you that in LR class

select getdate();

  

PRINT N'EDIT PERMS SET  FOR PUBLIC ACCESS ITEMS CAN USE HIERARCHY SEE SCRIPT '

  

SELECT DTREE.DATAID,DTREE.NAME FROM DTREE ,DTREEACL

WHERE DTREE.DATAID=DTREEACL.DATAID

AND DTREE.SUBTYPE=0

AND DTREEACL.ACLTYPE=3

 /*PUBLIC ACCESS PERMISSIONS */

AND FLOOR(DTREEACL.PERMISSIONS/POWER(2,4))%2=1

/*EP BIT 4 ON */

  

AND DTREE.DATAID IN (SELECT DT.DATAID FROM DTREEANCESTORS DA ,DTREE DT WHERE DT.DATAID=DA.DATAID AND DT.SUBTYPE=0 AND DA.ANCESTORID=539693)

ORDER BY DTREE.NAME



as you can see I am crawling for all nodes underneath 539693


which is a folder and children I needed audited

1 Use Share Point, 2 Use  Web Report, 3 Use  Spreadsheet

On Fri, Aug 30, 2013 at 10:54 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Permissions Matrix of a Folder

Posted byuminhas@nwrpartnership.com (Minhas, Upinder)On 08-30-2013 11:49

I found this query here on KC which works great but its in Oracle can someone help changing this to MS SQL SELECT d . rn "RowNum" , d . dataid "DataID" , LPAD ( '+' , lvl - 1 , '+' ) || ' ' || d . NAME "[+Hierarchy] Name" , d . path , DECODE ( d . subtype , 0 , 'Folder' , 1 , 'Shortcut' , 2 , 'Generation' , 130 , 'Topic' , 131 , 'Category' , 134 , 'Response' , 136 , 'Compound doc' , 138 , 'Release' , 140 , 'URL' , 144 , 'Document' , 146 , 'Customview' , 202 , 'Projet' , 215 , 'Discussion' , 258 , 'Request' , 299 , 'LiveReport' , 557 , 'E-mail composite' , 751 , 'E-mail folder' , 'Other' ) "Object type" , lvl - 1 "Level" , decode ( da . acltype , 1 , 'Owner' , 2 , case when subtype = 202 THEN 'Coordonator' when da . ownerid > 0 THEN 'Coordonator' ELSE 'Owner Group' END , 3 , 'Public' , 0 , case when subtype = 202 and k . name = 'Members' THEN 'Members' when subtype = 202 THEN 'Guest' when da . ownerid > 0 and k . name = 'Members' THEN 'Members' when da . ownerid > 0 THEN 'Guest' ELSE 'Assigned' END , ' ' ) "Access type" , coalesce ( case when kk . type = 0 then kk . firstname || ' ' || kk . lastname || ' (' || kk . name || ')' else kk . name end , case when k . type = 0 then k . firstname || <font size="2" /> ' ' || k . lastname || ' (' || k . name || ')' else k . name end , ' ' ) "User/Group" , decode ( bitand ( da . PERMISSIONS , 2 ), 2 , 'See' , ' ' ) || ', ' || decode ( bitand ( da . PERMISSIONS , 36865 ), 36865 , 'SeeContent' , ' ' ) || ', ' || decode ( bitand ( da . PERMISSIONS , 65536 ), 65536 , 'Modif' , ' ' ) || ', ' || decode ( bitand ( da . PERMISSIONS , 131072 ), 131072 , 'ModifAttrib' , ' ' ) || ', ' || decode ( bitand ( da . PERMISSIONS , 4 ), 4 , 'Add' , ' ' ) || ', ' || decode ( bitand ( da . PERMISSIONS , 8192 ), 8192 , 'Reserve' , ' ' ) || ', ' || decode ( bitand ( da . PERMISSIONS , 16384 ), 16384 , 'DelVersions' ,</font> ' ' ) || ', ' || decode ( bitand ( da . PERMISSIONS , 8 ), 8 , 'Delete' , ' ' ) || ', ' || decode ( bitand ( da . PERMISSIONS , 16 ), 16 , 'ModifPerm' , ' ' ) "Rights&quot; FROM ( SELECT c . dataid , c . SUBTYPE , c . NAME , livelink . GETLLPATH ( d . ParentID ) as path , LEVEL lvl , ROWNUM AS rn FROM dtree c WHERE c . SUBTYPE IN ( 0 , 136 , 202 , 751 ) and level <= 99 CONNECT BY PRIOR c . dataid = ABS ( c . parentid ) START WITH c . dataid = 10587503 ) d INNER JOIN dtreeacl da ON da . dataid = d . dataid left JOIN kuaf k ON da . rightid = k . id left join ( select * from kuafchildren where exists ( select * from kuaf where name in ( 'Coordinators' , 'Members' , 'Guests' ) and kuaf . id = kuafchildren . id ) ) kc on k . id = kc . id left JOIN kuaf kk ON kc . childid = kk . id where da . rightid <> - 2 and not (( d . subtype = 202 or da . ownerid > 0 ) and da . acltype = 1 ) and not exists ( select * from kuaf where name = 'Coordinators' and kuaf . id = kc . CHILDID ) and not( k . name = 'Guests' and kc . id is null and da . acltype <> 3 ) order by d . rn , decode ( da . acltype , 0 , 99 , da . acltype ), da . rightid

---

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