Report permissions by Groups or Users

Has anyone developped a report giving what a group or person has access to and the kind of access. Say you put someone in a certain group, it would be nice to know exactly what you are giving access to.ThanksMichel

Find more posts tagged with

Comments

Marcia_Gillespie-Norder_(tokeleuser7_-_(deleted))

Message from <mnorder@aus.telusa.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">RE: Report permissions by Groups or Users

We have something close to what you're asking for. We're on Oracle, by the way, so you may need to adapt the queries here if you're on MS SQL Server.

The SQL below (in a LiveReport with a user input parameter of "User") will retrieve all of the objects that that user has permissions to. For most users, this is going to be a very long list! We have it limited to 1000 results for that reason. Your mileage may vary on usefulness.

---------------------------
SELECT DTree.*,KUAF.Name "Group", DTree.Name "Object", DTree.SubType, DTree1.Name "Parent", DTreeACL.Permissions FROM DTreeACL,KUAF,DTree,DTree DTree1 WHERE DTree.ParentID = DTree1.DataID AND DTreeACL.DataID = DTree.DataID AND DTreeACL.RightID = KUAF.ID AND DTreeACL.RightID = %1

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

Something else that we have that I LOVE that you might be interested in is a report that shows permissions by object ID. We don't allow our users to have permissions privileges, so this allows them to check permissions without having access to the permissions function. The SQL looks heinous, but all of the 'S/SC/M/EA' stuff is just showing all of the different combinations of permissions someone can have on a folder.

We've made this report accessible in every folder by sticking a little graphic on the top bar of the UI--so if you're in a folder, you can click the little graphic to see the permissions on that folder. Our users love this--and we (as admins) do, too, because they can double-check us on stuff without us giving them permissions access.

---------------------------
SELECT DTREE.DataID,DTREE.Name, DECODE(DTREEACL.RIGHTID, -1,'Public Access',KUAF.FIRSTNAME || ' ' || KUAF.LASTNAME || ' (' || KUAF.NAME || ')' ) "UserGroup", DECODE(DTREEACL.PERMISSIONS, '128','No Rights','130','S','36995','S/SC','62666','S/M','102531','S/SC/M','65670','S/M/AI','102547','S/SC/M/EP','233603','S/SC/M/EA','102535','S/SC/M/AI','118915','S/SC/M/DV','233619','S/SC/M/EP/EA','102551','S/SC/M/EP/AI','118931','S/SC/M/EP/DV','233607','S/SC/M/EA/AI','249987','S/SC/M/EA/DV','118919','S/SC/M/AI/DV','233623','S/SC/M/EP/EA/AI','250003','S/SC/M/EP/EA/DV','118935','S/SC/M/EP/AI/DV','249991','S/SC/M/EA/AI/DV','250007','S/SC/M/EP/EA/AI/DV','118923','S/SC/M/DV/D','127107','S/SC/M/DV/R','127115','S/SC/M/DV/D/R','118939','S/SC/M/EP/DV/D','127123','S/SC/M/EP/DV/R','127131','S/SC/M/EP/DV/D/R','249995','S/SC/M/EA/DV/D','258179','S/SC/M/EA/DV/R','258187','S/SC/M/EA/DV/D/R','118927','S/SC/M/AI/DV/D','127111','S/SC/M/AI/DV/R','127119','S/SC/M/AI/DV/D/R','250011','S/SC/M/EP/EA/DV/D','258195','S/SC/M/EP/EA/DV/R','258203','S/SC/M/EP/EA/DV/D/R','118943','S/SC/M/EP/AI/DV/D','127127','S/SC/M/EP/AI/DV/R','127135','S/SC/M/EP/AI/DV/D/R','249999','S/SC/M/EA/AI/DV/D','258183','S/SC/M/EA/AI/DV/R','258191','S/SC/M/EA/AI/DV/D/R','250015','S/SC/M/EP/EA/AI/DV/D','258199','S/SC/M/EP/EA/AI/DV/R','258207','Full Perm','16777215','Full Perm','Unknown') "Rights" FROM DTREE, KUAF, DTREEACL WHERE DTREEACL.DATAID = DTREE.DATAID AND DTREEACL.RIGHTID = KUAF.ID(+) AND DTREE.DATAID = %1 and DTREEACL.ACLTYPE <> 1 and DTREEACL.ACLTYPE <> 2 and DTREEACL.ACLTYPE <> 4

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

Hope this helps you some?

Best regards,
Marcia A. Norder
Tokyo Electron America.

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion
[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, February 23, 2004 1:38 PM
To: eLink Recipient
Subject: Report permissions by Groups or Users

Report permissions by Groups or Users
Posted by Quesnel, Roger on 02/23/2004 02:36 PM

Thanks
Michel

[To reply to this thread, use your normal E-mail reply function.]

============================================================

Discussion: Livelink LiveReports Discussion
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=view

Livelink Server:
https://knowledge.opentext.com/knowledge/livelink.exe

eLink User

Message from Anne Callanan <a.callanan@londonmet.ac.uk> via eLink

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> Dear Marcia

These are two fantastic reports - congratulations

Best regards
Anne

eLink Discussion: Livelink LiveReports Discussion wrote:

eLink

REReport permissions by Groups or Users Posted by TokEleUser7 (Norder, Marcia)on 02/24/2004 03:44 PM
In reply to: Reportpermissions by Groups or Users Posted by HYDQUE01User1 (Quesnel, Roger)on 02/23/2004 02:36 PM

Message from <mnorder@aus.telusa.com>via eLink

We have something close to what you're askingfor. We're on Oracle, by the way, so you may need to adapt the querieshere if you're on MS SQL Server. The SQL below (in a LiveReport with a userinput parameter of "User") will retrieve all of the objects that thatuser has permissions to. For most users, this is going to be a verylong list! We have it limited to 1000 results for that reason. Yourmileage may vary on usefulness. ---------------------------
SELECT DTree.*,KUAF.Name "Group", DTree.Name"Object", DTree.SubType, DTree1.Name "Parent", DTreeACL.PermissionsFROM DTreeACL,KUAF,DTree,DTree DTree1 WHERE DTree.ParentID =DTree1.DataID AND DTreeACL.DataID = DTree.DataID AND DTreeACL.RightID =KUAF.ID AND DTreeACL.RightID = %1 --------------------------- Something else that we have that I LOVE thatyou might be interested in is a report that shows permissions by objectID. We don't allow our users to have permissions privileges, so thisallows them to check permissions without having access to thepermissions function. The SQL looks heinous, but all of the'S/SC/M/EA' stuff is just showing all of the different combinations ofpermissions someone can have on a folder. We've made this report accessible in everyfolder by sticking a little graphic on the top bar of the UI--so ifyou're in a folder, you can click the little graphic to see thepermissions on that folder. Our users love this--and we (as admins)do, too, because they can double-check us on stuff without us givingthem permissions access. ---------------------------
SELECT DTREE.DataID,DTREE.Name,DECODE(DTREEACL.RIGHTID, -1,'Public Access',KUAF.FIRSTNAME || ' ' ||KUAF.LASTNAME || ' (' || KUAF.NAME || ')' ) "UserGroup",DECODE(DTREEACL.PERMISSIONS, '128','NoRights','130','S','36995','S/SC','62666','S/M','102531','S/SC/M','65670','S/M/AI','102547','S/SC/M/EP','233603','S/SC/M/EA','102535','S/SC/M/AI','118915','S/SC/M/DV','233619','S/SC/M/EP/EA','102551','S/SC/M/EP/AI','118931','S/SC/M/EP/DV','233607','S/SC/M/EA/AI','249987','S/SC/M/EA/DV','118919','S/SC/M/AI/DV','233623','S/SC/M/EP/EA/AI','250003','S/SC/M/EP/EA/DV','118935','S/SC/M/EP/AI/DV','249991','S/SC/M/EA/AI/DV','250007','S/SC/M/EP/EA/AI/DV','118923','S/SC/M/DV/D','127107','S/SC/M/DV/R','127115','S/SC/M/DV/D/R','118939','S/SC/M/EP/DV/D','127123','S/SC/M/EP/DV/R','127131','S/SC/M/EP/DV/D/R','249995','S/SC/M/EA/DV/D','258179','S/SC/M/EA/DV/R','258187','S/SC/M/EA/DV/D/R','118927','S/SC/M/AI/DV/D','127111','S/SC/M/AI/DV/R','127119','S/SC/M/AI/DV/D/R','250011','S/SC/M/EP/EA/DV/D','258195','S/SC/M/EP/EA/DV/R','258203','S/SC/M/EP/EA/DV/D/R','118943','S/SC/M/EP/AI/DV/D','127127','S/SC/M/EP/AI/DV/R','127135','S/SC/M/EP/AI/DV/D/R','249999','S/SC/M/EA/AI/DV/D','258183','S/SC/M/EA/AI/DV/R','258191','S/SC/M/EA/AI/DV/D/R','250015','S/SC/M/EP/EA/AI/DV/D','258199','S/SC/M/EP/EA/AI/DV/R','258207','FullPerm','16777215','Full Perm','Unknown') "Rights" FROM DTREE, KUAF,DTREEACL WHERE DTREEACL.DATAID = DTREE.DATAID AND DTREEACL.RIGHTID =KUAF.ID(+) AND DTREE.DATAID = %1 and DTREEACL.ACLTYPE <> 1 andDTREEACL.ACLTYPE <> 2 and DTREEACL.ACLTYPE <> 4 --------------------------- Hope this helps you some? Best regards,
Marcia A. Norder
Tokyo Electron America.
-----Original Message-----
From: eLink Discussion: Livelink LiveReportsDiscussion
[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, February 23, 2004 1:38 PM
To: eLink Recipient
Subject: Report permissions by Groups or Users
Report permissions by Groups or Users
Posted by Quesnel, Roger on 02/23/2004 02:36 PM Has anyone developped a report giving what agroup or person has access to and the kind of access. Say you putsomeone in a certain group, it would be nice to know exactly what youare giving access to. Thanks
Michel [To reply to this thread, use your normalE-mail reply function.] ============================================================ Discussion: Livelink LiveReports Discussion
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=view Livelink Server:
https://knowledge.opentext.com/knowledge/livelink.exe

-- Anne CallananLivelink and Webboard Systems AdministratorIntranet Services TeamLondon Metropolitan University166 - 220 Holloway RoadLondon N7 8DBSwitchboard	: 020 7423 0000 Extension: 2389Direct		: 020 7314 4325

x-teleca01user3_-_(deleted)

Thank you Madame Norder. A question: The first LiveReport looks for the username in the Permissions area. But that does not mean that this particuliar user does not have access to other objects. The LiveReport does not check for UserGroups. I have found the following LiveReport but am having problem to fuse it to yours. Can anybody help me please with the code?Sql giving usergroups of a user:select b.name from kuafchildren a, kuaf b where a.id = b.id and a.childid = %1

Cheryl_Henry_(nswcdahladmin_-_(deleted))

I am also looking for the same type of report. Basically I would like to find out where a group has been given permission. If I removed all the users from a group, how would I find where the group still have permissions, since there are no users associated with the group?Thanks for your assistance,Marjorie Parish

ray_chance

Does anyone have a report like this? I have some convoluted information from a post 3 years ago...------------------Add item permissions=A<1,?,'actions'={},'conf_names'='','conf_values'='','displaytype'='report','fields'={},'headers'={},'inputs'={A<1,?,'inputType'='User','label'='inputLabel1','prompt'='User'>},'oldDataId'=86062,'params'={'USERINPUT1','#FilterObject:0',?,?,?,?,?,?,?,?},'recordlimit'=5000,'sql'='Select RightId, Name from dtreeacl, dtree where dtreeacl.dataid=dtree.dataid and rightid=%1 and permissions=102535 and %2','subreport_id'=?,'subreport_parms'={},'subreport_path'='','title'='Add item permissions'>-----------------------------------------Not really sure how to interpret it.OT says to hire Global Support to write report. :-(Thanks!

Marcia_Gillespie-Norder_(tokeleuser7_-_(deleted))

Message from <mnorder@aus.telusa.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">RE: What about usergroups?

A group is a user in KUAF just like a person is. So--for the SQL that I posted earlier in this thread, you can give it either the KUAF ID of a person or a group, and it will still tell you where that user (group/person) has permissions.

It is a fairly intense report, though, so beware! The output is a huge list of every object that the user has permissions on. Obviously, for your people or groups that have permissions on a lot of stuff, this is going to be a VERY large list.

Best regards,
Marcia A. Norder

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, March 25, 2004 7:32 AM
To: eLink Recipient
Subject: What about usergroups?

What about usergroups?
Posted by Parish, Marjorie on 03/25/2004 08:29 AM

I am also looking for the same type of report. Basically I would like to find out where a group has been given permission.

If I removed all the users from a group, how would I find where the group still have permissions, since there are no users associated with the group?

Thanks for your assistance,
Marjorie Parish

[To reply to this thread, use your normal E-mail reply function.]

============================================================

Topic: Report permissions by Groups or Users
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3363329&objAction=view

Discussion: Livelink LiveReports Discussion https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=view

Livelink Server:
https://knowledge.opentext.com/knowledge/livelink.exe

Cheryl_Henry_(nswcdahladmin_-_(deleted))

Marcia, thanks for your reply. The SQL works wonderful for a users, but for a group I do not get any results. As my test I am using a group that has access to several locations. Have you tried the SQL for a group with no member that has access to a project or folder? Does it work for you? Any clues on what I might be missing?Thanks your your help.Marjorie Parish

ray_chance

I changed the sql to prompt for a number (groupid) and tried with several groupids, but kept getting timed out by the firewall. I went to the database server and tried it directly using sqlplus, and it just hung for a very long time...no error message.Thanks,Michelle

Ben_Shapiro

Message from Benjamin Shapiro via eLink Looks like an exported LiveReport. Try saving the text as a .rpt file and do a LiveReport import of it intoLivelink. Benjamin--Benjamin J. Shapiro, BMathSenior Enterprise Consultant--Open Text CorporationSuite 300, 515 Legget DriveOttawa, ON, Canada, K2K 3G4Telephone: +1 (613) 599-9335 x3238Toll Free: +1 (800) 267-9975Cellular: +1 (613) 797 9609Facsimile: +1 (613) 599-9177mailto:bshapiro@opentext.com----- Original Message ----- From: "eLink Discussion: Livelink LiveReports Discussion"To: "eLink Recipient" Sent: Thursday, March 25, 2004 9:14 AMSubject: "WHAT DOES THIS GROUP HAVE ACCESS TO?" Report needed> eLink"WHAT DOES THIS GROUP HAVE ACCESS TO?" Report needed Posted byNSSCAdmin (Newton, Russ) on 03/25/2004 09:11 AM> In reply to: What about usergroups? Posted by NSWCDahlAdmin(Parish, Marjorie) on 03/25/2004 08:29 AM>> Does anyone have a report like this?>> I have some convoluted information from a post 3 years ago...> ------------------> Add itempermissions=A<1,?,'actions'={},'conf_names'='','conf_values'='','displaytype'='report','fields'={},'headers'={},'inputs'={A<1,?,'inputType'='User','label'='inputLabel1','prompt'='User'>},'oldDataId'=86062,'params'={'USERINPUT1','#FilterObject:0',?,?,?,?,?,?,?,?},'recordlimit'=5000,'sql'='Select RightId,Name from dtreeacl, dtree where dtreeacl.dataid=dtree.dataid and rightid=%1and permissions=102535 and%2','subreport_id'=?,'subreport_parms'={},'subreport_path'='','title'='Additem permissions'>> -----------------------------------------> Not really sure how to interpret it.>> OT says to hire Global Support to write report. :-(>> Thanks!>> --------------------------------------------------------------------------------> [To reply to this thread, use your normal E-mail reply function.]> Topic: Report permissions by Groups or Users> Discussion: Livelink LiveReports Discussion> Livelink Server: Knowledge Center>

Cheryl_Henry_(nswcdahladmin_-_(deleted))

I recieved some help from Sibel Godinho at Open Text Technical Support. Below are a couple of queries that seem to work, one for project (coordinator, member, guest) access given to a group and another for folders access given to a group. The 252945 is the id for the group. After running the reports I can search on the data id and find the item in Livelink. I was able to use these queries to verify that a group was not being used and therefore removed the group from the system.[Folders a group has access too]SELECT d.ParentID, d.DataID, d.Name, d.SubType, da.rightid, da.permissions, kc.id, kc.childid FROM DTREE d, DTREEACL da, KUAFChildren kc WHERE da.DATAID = d.DATAID AND da.RIGHTID= kc.id AND kc.childid = 252945 and da.ACLTYPE not in (1,2,4) and d.subtype = 0[Projects a group has access too]SELECT k.Name "Group", d.Name "LL Item", d.Dataid, kc.id, kc.childid FROM KUAF k, DTree d, KUAFChildren kc WHERE k.ID=252945 and k.ID = kc.ChildID AND (kc.ID = d.DataID+3 or kc.ID = d.DataID+2 or kc.ID = d.DataID+1) ORDER BY k.NameIf anyone have any other suggestions, corrections or helpful hints, please post them. Thanks,Marjorie Parish

ray_chance

Thanks Marjorie! These are great!!!

Benoit_Robidoux

You said "We've made this report accessible in every folder by sticking a little graphic on the top bar of the UI--so if you're in a folder, you can click the little graphic to see the permissions on that folder."We want to implement this function here but we don't know in witch html file add the little graphic of the top bar ? Are you using the LAPI "Run Report" function to execute the LiveReports ?Thank you very much for you helpBenoit RobidouxHydro-Quebec - Research Institute

Valerie_Jeanperin

Hey Benoit, it's been a while regarding this post,anyway, if you still online, could you let me know wether you manage to implement this or not,many thanksValérie JeanperinAXA France

Mary_Slane_Corona

MarciaGreat reports--especially the 2nd one. Three things:1. Is it possible to have this report (and how) to show all of its subfolders and permissions.2. How do you get it to show the permissions (and who it is) of the user owner and the group owner.3. How did you put the little graphic that runs the report on all of the pages?