Find 'Public Access' Permissions

Hello,

I am trying to write a LiveReport that will show me all subfolders (under one top level folder) that still have 'Public Access' set to at least SEE permissions. I need to check which of these folders still have this permission set as all of these folders should not have any Public Access at all.

Would I need to use the DTreeACL table to get this information?

Any help would be appriciated.

Thanks, Lee.

Find more posts tagged with

Comments

Colin_Schmidt

The DTREEACL is required because in it you'll find the PublicAccess record. Type = 3 and see > 0 means the PublicAccess exists and has SEE rights, at the least. SEE = 0 means "non access/all access removed. There is a document that lists these. The 9.7.1 Schema Compnaion and Reference Guide has this.

Colin_Schmidt

In ORACLE, the following SQL will summarize the objects with various Public Access. Using the DTREE, you can break it down further by the two types: Folders & other objects.

select
case
when dta.see = 0 then 'No Access'
when dta.see = 1 then 'See'
when dta.see = 2 then 'See Contents'
when dta.see = 3 then 'Modify'
when dta.see = 4 then 'Delete'
else 'Other ['||to_char(dta.see)||']' end Rights, count(*)
from livelink.dtreeacl dta
where dta.acltype = 3
group by case
when dta.see = 0 then 'No Access'
when dta.see = 1 then 'See'
when dta.see = 2 then 'See Contents'
when dta.see = 3 then 'Modify'
when dta.see = 4 then 'Delete'
else 'Other ['||to_char(dta.see)||']' end

Lee_Henstock

Hi,

Thank you for your reply.

I have just tried the following SQL : select * from dtreeACL where parentid=1249153 and acltype=3 and see >0 and this works as expected but for only one level. But how would I then get this to work for the 3^rd, 4^th etc levels down without changing the parentid each time?

Thanks,

Lee.

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 30 November 2011 13:36
To: eLink Recipient
Subject: Find 'Public Access' Permissions

Find 'Public Access' Permissions

Posted bycolin.schmidt@almacgroup.com (Schmidt, Colin J) On 11-30-2011 08:34

[To post a comment, use the normal reply function]
Topic:	Find 'Public Access' Permissions
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Think before you print! Save energy and paper.Do you really need to print this email?

Derbyshire County Council works to improve the lives of local people by delivering high quality services.You can find out more about us by visiting 'www.derbyshire.gov.uk'. If you want to work for us go to our job pages on 'www.derbyshire.gov.uk/jobs'. You can register for e-mail alerts, download job packs and apply on-line.

Please Note
This email is confidential, may be legally privileged and may contain personal views that are not the views of Derbyshire County Council. It is intended solely for the addressee. If this email was sent to you in error please notify us by replying to the email. Once you have done this please delete the email and do not disclose, copy, distribute, or rely on it.
Under the Data Protection Act 1998 and the Freedom of Information Act 2000 the contents of this email may be disclosed.

Derbyshire County Council reserves the right to monitor both sent and received emails.

Colin_Schmidt

That's a skill beyond me because there could be any varying amount of sub-folders. You could list all including the ParentID and sort it by that. You could also construct a partial URL to the Parent Folder or if you only want folders...directly to the one listed. Since you can change all sub-items all you really need is the parent, but this process depending upon how large your EWS is, could engulf the system doing it from EWS down.

You may be able to see a pattern by showing as much as you can and sorting it different ways to see if it's all over the place or only in certain areas.

If your count for this access by folder, is not too large, the list shouldn't be long and you could probably use the highest level parent and change all below, but you'd have to be careful how many objects are contained within and below before deciding.

It's a picture you'll have to decide on for your own actions. I'm not OT Support, but I can provide some Oracle for some queries.

Colin_Schmidt

One other option is to use that DataID to get the ParentID and then go down that chain and report anything within it. I'm sure there's a way to loop until none can be found, but that sounds like a processing cycle I wouldn't recommend.

This will display the Access to all folders immediately below and provide a count of any SubFolders:

select dt.parentid, dt.dataid, dt.name,
case
when dta.see = 0 then 'No Access'
when dta.see = 1 then 'See'
when dta.see = 2 then 'See Contents'
when dta.see = 3 then 'Modify'
when dta.see = 4 then 'Delete'
else 'Other ['||to_char(dta.see)||']' end Rights,
(select count(*) from livelink.dtree dt2 where dt.dataid = dt2.parentid and dt2.subtype = 0) "SubFolders"
from livelink.dtree dt
join livelink.dtreeacl dta on dt.dataid = dta.dataid and dta.acltype = 3
where dt.ownerid = -2000 and dt.subtype = 0 and dt.parentid = <ID>

To go deeper until all trees are exhausted could be an exhausting process. A DBA might know.

Lee_Henstock

Hi,

Thank you for all your advice and comments.

I have been playing about with the SQL and have come up with the following that seems to work as expected.

SELECT dtreeancestors.dataid, dtreeacl.acltype, dtreeacl.see FROM dtreeancestors , dtreeacl
WHERE dtreeancestors.dataid = dtreeacl.dataid and ancestorid=%1
and acltype=3 and see >0

I will be further testing this but hopefully this should give me the expcted results.

Again, thanks for you help.

Lee.

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 30 November 2011 18:58
To: eLink Recipient
Subject: RE Find 'Public Access' Permissions

RE Find 'Public Access' Permissions

Posted bycolin.schmidt@almacgroup.com (Schmidt, Colin J) On 11-30-2011 13:53

This will display the Access to all folders immediately below and provide a count of any SubFolders:

To go deeper until all trees are exhausted could be an exhausting process. A DBA might know.

[To post a comment, use the normal reply function]
Topic:	Find 'Public Access' Permissions
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center