I have a report that provides me a listing of what groups ha...

[Thomas Stockwell]

I have a report that provides me a listing of what groups have permissions to what items in the WebFocus repository. The most detail I can get is DomainBasicuser. What table in the repository lists the List and Read permission for a given folder/report for a given user/group

Based on several past forum posts, this is the code that I have for my report currently:

WITH resources_CTE (id, name, ownerid, parentid,prt_path,full_path) AS (

SELECT

id,

name,

ownerid,

parentid,

'' prt_path,

'' full_path

FROM webfocrep.uoa_resources

WHERE parentid=-1

UNION ALL

SELECT

uoa_resources.id,

uoa_resources.name,

uoa_resources.ownerid,

uoa_resources.parentid,

rc.full_path,

rc.full_path||'/'||uoa_resources.name full_path

FROM webfocrep.uoa_resources

JOIN resources_cte rc

ON uoa_resources.parentid=rc.id

WHERE uoa_resources.parentid!=-1

)

select

uoa_groups.name,

uoa_groups.extgroupname,

uoa_groupsrules.verb,

uoa_groupsrules.applyto,

uoa_psets.name permission_class,

resources_CTE.name,

resources_CTE.prt_path,

resources_CTE.full_path,

wf_reposobj.classname,

wf_reposobj.objname,

wf_reposobj.prt_path,

wf_item.datatype,

wf_nlsobj.objdesc,

wf_nlsobj.summary

FROM webfocrep.uoa_groups

JOIN webfocrep.uoa_groupsrules

ON uoa_groups.id = uoa_groupsrules.groupid

JOIN resources_CTE

ON resources_CTE.id = uoa_groupsrules.resourceid

LEFT OUTER JOIN webfocrep.wf_reposobj

ON wf_reposobj.objname = resources_CTE.name

AND wf_reposobj.prt_path = resources_CTE.prt_path

LEFT OUTER JOIN webfocrep.wf_item

ON wf_item.handle = wf_reposobj.handle

LEFT OUTER JOIN webfocrep.wf_nlsobj

ON wf_nlsobj.obj_handle = wf_reposobj.handle

LEFT OUTER JOIN webfocrep.uoa_psets

ON uoa_psets.id = uoa_groupsrules.psetid;

[Toby Mills]

Hi Tom

While I dont think I will have the answer right off, I thought maybe if I asked more questions, it might spark and idea from someone else.

What release are you on

I wanted to see if I understand the request. I think you mean that you want to essentially do an Effective Policy Report to show all users who have the List and Read Role (locked factory role) for a certain resource.

The List and Read Role has these privileges:

Access Resource (opList)

Access Resource Properties (opViewProps)

Open Resource (opOpen)

So for a given item, like /wfc/repository/test/myrpt.fex (just making that up), I think youd like to know which people have all three of those privileges on that item.

And youd like this to be a webfocus report you can run right

All I can say at the moment is that the list of privileges used to not be listed anywhere in the repository. The full list of privs was an internal thing and not really stored for the world to see. They are listed in Appendix B of the security manual (at least in 8105m).

Let us know your version and if I understand the request correctly. When I turned on the adapter for the WF client in 8204+, I got prompted to build a bunch of masters/acx files that represent lots of calls to the repository. So maybe - if youre on a newer release of WF, you might be able to leverage those masters/acx files to get what you want.

Just throwing ideas out in case it rings a bell for anybody.

Sounds like a useful report for auditing

Later

Toby

[Thomas Stockwell]

Im on 8.2.04.

If I cant find an explicit entry for List and Read role, then you are correct, I would be looking for users with those privileges for a given folder object.

Yes, runnable as a WF report.

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

Through testing, I was able to determine what the applyto column signified:

CASE

WHEN uoa_groupsrules.applyto = 1 THEN Folder & Children

WHEN uoa_groupsrules.applyto = 2 THEN Folder Only

WHEN uoa_groupsrules.applyto = 3 THEN Children Only

END applyto,

Im in essence finding List and Read with my above code by searching for records without a PERMISSION_CLASS, with a PERMIT verb and an ApplyTo record. However, this may need to be more complex for institutions who use a larger plethora of permissions classes in WF.

[Toby Mills]

Morning Tom,

Thanks for the extra info.

I have an idea to get started with.

Lets first look at something to see if Im thinking down the right path. Or even generally in the right area.

Open Security Center.

Click on the Roles tab

Go down to the List and Read Role with the little lock picture.

Right click and choose Show Rules using this Role.

See if that report lists the things youd want. Its going to show Groups probably instead of individuals.

If thats right, well talk about the WebFOCUS Client Rest adapter. This is pretty new to me, so I dont know what all we can do with it. I do know that it connects to the Repository and makes a bunch of canned masters and acx files for you to use. There is also a master named LIST_RULESFORROLE. My guess is - that might show you the same info as the report I had you run (list rules for Role) above.

Id like to figure out more about how to use these masters/acx files but finding docs on what they all do - or require for input - is hard to come by.

Let me know what you think.

Chuck - if youre reading this, maybe this thread should be in the admin / internals Category

Later!

Toby

[Manoj Chaurasia]

I was able to move this under the administration and internals category. Also please keep in mind that the WebServices enablement is a separately licensed function that Univ of Michigan may not have purchased.

[Thomas Stockwell]

I dont think we are licensed for the WebFOCUS Client Rest adapter, so that likely isnt an option. I believe the solution I came up with will work for us. It may just not be as robust enough for larger institutions.

[Don Garland]

Here is a similar query I use to see group security up to 5 levels of domain folders. Anyone successfully converted this type of query to a cluster join/business view

WITH cl (ID, DESCRIPTION, PARENTID, EXTGROUPNAME)

as

(

select ID, DESCRIPTION, PARENTID, EXTGROUPNAME

from WebFOCUS_82_Int.dbo.UOA_GROUPS

where PARENTID < 0

union all

select grp.ID, grp.DESCRIPTION, grp.PARENTID, grp.EXTGROUPNAME

from WebFOCUS_82_Int.dbo.UOA_GROUPS AS grp

inner join WebFOCUS_82_Int.dbo.UOA_GROUPS grp1 on grp.PARENTID = grp1.ID

)

select a.ID AS Domain_ID

, a.DESCRIPTION AS Domain_Name

, a.EXTGROUPNAME AS Domain_LDAP

, b.ID AS L2_ID

, b.DESCRIPTION AS L2_Name

, b.EXTGROUPNAME AS L2_LDAP

, c.ID AS L3_ID

, c.DESCRIPTION AS L3_Name

, c.EXTGROUPNAME AS L3_LDAP

, d.ID AS L4_ID

, d.DESCRIPTION AS L4_Name

, d.EXTGROUPNAME AS L4_LDAP

, e.ID AS L5_ID

, e.DESCRIPTION AS L5_Name

, e.EXTGROUPNAME AS L5_LDAP

from cl a

left outer join cl b on a.ID = b.PARENTID

left outer join cl c on b.ID = c.PARENTID

left outer join cl d on c.ID = d.PARENTID

left outer join cl e on d.ID = e.PARENTID

where a.PARENTID < 0