Is there a query which could be run in a live report that would list all the folders (no docs) a given user has access to that would include the path heirarchy?
We are on 10.5.2 and running MS SQL.
Thanks in advance!
Hi Matt,
I believe there is a WebReport included with the Report Pack for WebReports that provides a graphical way of doing this, to an extent.
Provided you have a valid WebReports license, you can use the Report Pack, if you install it. The Report Pack is free to use, I believe.
Is that something that interests you?
Thanks.
Oracle has a way of providing the path and you’d have to look up on the Internet if there is an equivalent way in MS SQL.
SYS_CONNECT_BY_PATH & START WITH + CONNECT BY.
Colin J. Schmidt
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Monday, September 11, 2017 3:23 PMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: user permissions report for folders
user permissions report for folders
Posted by Brammer, Matt On 09/11/2017 03:01 PM
[To post a comment, use the normal reply function]
Forum:
Content Server LiveReports Forum
Content Server:
Knowledge Center CS16
The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.
I suspect what you need is the Permissions Search function like this:
https://youtu.be/TP5Mx0nr_ws
It gives you what you're asking for and yes, it is a licenced product but from your question it gives you exactly what you're after. More information here:
http://www.fastman.com/fastman-management-suite/permissions-manager/
and you can get it direct from Fastman or through OpenText.
Try this.I guess almost similar with what Colin provided in MSSQL.
You need to have enough rights to create a custom function in your CS DB thou.
I've only answer part of your question. Remaining of what you need should be fairly straight forward, right?
Here is a thing I forgot I had, but is a function in MS SQL for just getting the full path to a specific item, which you could likely combine with a permissions check:
CREATE FUNCTION [GETLLPATH](@dataid bigint)
RETURNS varchar(max)
AS
BEGIN
declare @path varchar(max);
SET @path='';
WITH Child(ParentID,DataID,Name) AS
(SELECT Child.ParentID
,Child.DataID
,Child.Name
FROM DTreeCore Child
WHERE Child.DataID=@dataid
UNION ALL
SELECT Parent.ParentID
,Parent.DataID
,Parent.Name
FROM DTreeCore as Parent,Child
WHERE Child.ParentID = Parent.DataID
)SELECT @path=@path+name+':' from Child order by DataID
SET @path=SUBSTRING(@path,0,LEN(@path))
RETURN @path
END
However, there are ways to get the contents of a given volume with something like:
SELECT DTreeCore.DataID, Name FROM TreeCore INNER JOIN DBrowseAncestors ON DTreeCore.DataID = DBrowseAncestors.DataID WHERE DBrowseAncestors.AncestorID = %1
The above will give you everything under a given volume, where the volume ID is defined in %1. You could combine that with some permissions SQL to get all the perms for the items, within.
Typically, you’ll have Groups assigned to a Permissions list and there will be nested Groups. You’d have to use the DTREEACL.RIGHTID to determine this on a User-level. Then use the KUAFCHILDREN to see if the User ID is in a Group(DTREEACL.RIGHTID) that has access. In Oracle, we’d use the “START WITH” and “CONNECT BY PRIOR”. You’d have to determine the alternative way in MS SQL.
Reference the Champion Toolkit for the Schema Companion. It has some of the codes that you may need for the query. The internet and your companions-in-query here may have a way.
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Tuesday, September 12, 2017 8:09 AMTo: eLink Recipient <devnull@elinkkc.opentext.com>Subject: user permissions report for folders 2
user permissions report for folders 2
Posted by Ghazal, Nizar On 09/12/2017 08:06 AM
WHEREChild.DataID=@dataid
)SELECT @path=@path+;name+':' from Child order by DataID
Topic:
What Colin is referring to is 'Effective Permissions". Nested group access, likely following best practice but with unexpected outcomes. Nothing to do with folder heirarchy, can occur a couple of folders down in a tree and quickly propogate from there.
Hope is a wonderful thing, certainty lets you sleep at night.
I tried to create this Function in SQL Server 2016, but it kicked back syntax errors. Is this a SCALAR function? I replaced "DTREECORE" with "DTREE".
CREATE FUNCTION [<schema>].[GETLLPATH](@dataid bigint)RETURNS varchar(max)ASBEGINdeclare @path varchar(max);SET @path=''; WITH Child(ParentID,DataID,Name) AS(SELECT Child.ParentID,Child.DataID,Child.Name FROM DTree Child WHERE Child.DataID=@dataid UNION ALL SELECT Parent.ParentID,Parent.DataID,Parent.Name FROM DTree as Parent,Child WHERE Child.ParentID = Parent.DataID)SELECT @path=@path+name+':'from Childorder by DataIDSET @path=SUBSTRING(@path,0,LEN(@path))RETURN @pathEND
Msg 102, Level 15, State 1, Procedure GETLLPATH, Line 7 [Batch Start Line 154]Incorrect syntax near ' '.Msg 319, Level 15, State 1, Procedure GETLLPATH, Line 7 [Batch Start Line 154]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.Msg 102, Level 15, State 1, Procedure GETLLPATH, Line 9 [Batch Start Line 154]Incorrect syntax near ' '.Msg 102, Level 15, State 1, Procedure GETLLPATH, Line 13 [Batch Start Line 154]Incorrect syntax near ' '.
