Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] WF 8.0.08 - Security Center: How to create a report of users in a group?
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] WF 8.0.08 - Security Center: How to create a report of users in a group?
 Login/Join
 
Expert
posted
I would like to create a report of the users in a Security Center Group. I cannot find this option - such a simple request for a reporting tool like WebFOCUS.

This message has been edited. Last edited by: Francis Mariani,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Hi Francis,
For this you need access to repository and join
UOA_GROUPS, UOA_GROUPUSERS, UOA_USERS tables together by ID-s.

Regards,


Release: WebFOCUS 8104, AppStudio: 8105
OS: Windows
Output: HTML,Excel,Active Reports
 
Posts: 89 | Registered: November 19, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
SSander, thanks very much.

By the way, is this documented or did you explore and discover?

Cheers,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
As far as I know its not documented... it was explore and discover.
It is the easy part compared to the BIP_ITEM_* logic where the fex-es are held.

Regards,


Release: WebFOCUS 8104, AppStudio: 8105
OS: Windows
Output: HTML,Excel,Active Reports
 
Posts: 89 | Registered: November 19, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
JOIN GROUPID IN WF_REPOS_UOA_GROUPSUSERS TO ID IN WF_REPOS_UOA_GROUPS AS J1
JOIN USERID  IN WF_REPOS_UOA_GROUPSUSERS TO ID IN WF_REPOS_UOA_USERS  AS J2

TABLE FILE WF_REPOS_UOA_GROUPSUSERS
PRINT 
WF_REPOS_UOA_GROUPS.DESCRIPTION AS 'Group Description'
WF_REPOS_UOA_GROUPS.NAME        AS 'Group Name'

WF_REPOS_UOA_USERS.DESCRIPTION  AS 'User Description'
WF_REPOS_UOA_USERS.NAME         AS 'User Name'

BY WF_REPOS_UOA_USERS.DESCRIPTION NOPRINT

WHERE WF_REPOS_UOA_GROUPS.NAME EQ 'Treasury_Power_Users'
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Thanks very much SSander.

Now, it would have been nice if IBI had put a button on the web interface for Security Center.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
This is pretty slick. Thanks!


webFOCUS 8203
WindowsServer 2012 R2,
Excel, PDF, HTML, ActiveReports
 
Posts: 124 | Location: Minnesota | Registered: August 26, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
A quick note to mention that "WF_REPOS_" is a prefix I added when generating the metadata for the repository tables.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Master
posted Hide Post
Francis,

Your query is actually incomplete. Please consider this SQL I created not to long ago.

 select x.id
      ,x.parentid
      ,x.name
      ,t2.groupid
      ,t2.userid
      ,t3.name
from (select t1.id
	    ,t1.name
	from uoa_groups t1
	start with t1.id in (select groupid
				from uoa_groupsusers gu
				group by groupid) x
	,uoa_groupsusers t2
	,uoa_users t3
where x.id = t2.groupid
  and t2.userid = t3.id
order by t3.name, x.id, x.name
group by t3.name, x.id, x.name
 


You actually need a recursive to join to understand the implicit relationships between users and groups.

Another thing you need to watch out for is that these tables do not contain users that are a part of a group do to an external group. For instance I use LDAP Groups for my external authentication. Any user that is added to a group through that external authentication will not be captured within the UOA Security tables. I opened a thread about what I was trying to do here: http://forums.informationbuild...137028376#2137028376

if you want more information.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
Eric,

Thanks for your input. I don't understand what your SQL is retrieving. Also, I was just told that retrieving stuff from these UOA tables isn't reliable as it won't provide data for users that have never logged in.

All I know is that I did get a report that represented the group I created in the Security Center.

I cannot tell you how annoyed I am about all this...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Master
posted Hide Post
Francis,

The query is a hierarchical query. In the new security setup you have nested groups. for Example:

Tenant A > Group 1 > sub-Group 1.

If you want to know all the users that are a part of Group 1 and you were to query it with your query, you would see no users from sub-Group 1, even though they have all the rights and access as Group 1. Here is a link to oracle's documentation on hierarchical queries: http://docs.oracle.com/cd/B193...14200/queries003.htm

Thats what my query will do. It will list all of the Groups that a User is a part of; implicitly (ie Tenant A and Group 1) or explicitly (sub-Group 1). If in your environment all of your authorizations are handled within WebFOCUS, this will allow you to create a full list of users with access to each group. If you use an External Authorization like I do (LDAP), then any users from that External Authorizationn will not be captured by these tables.

As for whether these tables will provide data for users that have not logged in, that depends on whether you use Internal or External Authentication. In my case I have it setup for External Authentication (LDAP) and to import users when they log in for the first time. These users will then only appear in my security tables at that point in time. If I were to use Internal Authentication, then I would have to provision my users ahead of time. These would then show up in the UOA* tables, as I would have manually added them.

Overall I actually am pretty happy about the new Security Model. It has taken a bit of time to get my head around it, but as an administrator I can much better control the Security of my system. I can also better delegate responsibility to my tenants. Overall, I find this to be leaps and bounds ahead of what we had before.

Do you know if you are using Internal or External Authentication / Authorization? This has a big impact on how these tables work and whether they will allow you to acheieve what it is you want to acheive.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
quote:

select x.id
      ,x.parentid
      ,x.name
      ,t2.groupid
      ,t2.userid
      ,t3.name
from (select t1.id
	    ,t1.name
	from uoa_groups t1
	start with t1.id in (select groupid
				from uoa_groupsusers gu
				group by groupid) x
	,uoa_groupsusers t2
	,uoa_users t3
where x.id = t2.groupid
  and t2.userid = t3.id
order by t3.name, x.id, x.name
group by t3.name, x.id, x.name

Eric, when I try to use this, I get an error saying I need a CONNECT BY clause. Even reading the oracle doc, I'm not sure what this is or what it should be for your query.



Prod: 8.1.0.4, Dev/App Studio: 8.1.0.4 Dev:App Studio, Dev Studio 8.1.0.4 OS:Windows 7 Output:AHTML, Excel 2007

The life of a designer is a life of fight against the ugliness.
 
Posts: 138 | Location: North Carolina | Registered: August 10, 2012Reply With QuoteReport This Post
Expert
posted Hide Post
I get this error as well:

(FOC1400) SQLCODE IS 1788 (HEX: 000006FC)
 : ORA-01788: CONNECT BY clause required in this query block
 L    (FOC1405) SQL PREPARE ERROR.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Here is my version of the report. Still trying to figure out how to get it into Excel, but this at lest shows the group hierarchy:

SQL SQLORA
select 
	  t3.name
	  ,t3.description
	  ,x.*
from (select id, name as "Group_Name", connect_by_root name as "Top_Level_Group", sys_connect_by_path(name,'/') as "Path"
	from uoa_groups
	start with parentid = -1
	connect by parentid = prior id
	order siblings by parentid) x
	,uoa_groupsusers t2
	,uoa_users t3
where x.id = t2.groupid
  and t2.userid = t3.id;
order by x.path, t3.description
TABLE FILE SQLOUT
HEADING
"User/group Recursive query"
PRINT *
ON TABLE SET LINES 99999
END  



Prod: 8.1.0.4, Dev/App Studio: 8.1.0.4 Dev:App Studio, Dev Studio 8.1.0.4 OS:Windows 7 Output:AHTML, Excel 2007

The life of a designer is a life of fight against the ugliness.
 
Posts: 138 | Location: North Carolina | Registered: August 10, 2012Reply With QuoteReport This Post
Expert
posted Hide Post
I noticed that there's a missing right parenthesis in the original code.

I tried to add a 'connect by' without understanding the code, which revealed the missing parenthesis, but I don't know where it goes:

SELECT 
  X.ID
, X.PARENTID
, X.NAME
, T2.GROUPID
, T2.USERID
, T3.NAME
FROM 
(
    SELECT 
      T1.ID
    , T1.NAME
    FROM 
    ENTRPT.UOA_GROUPS T1
    START WITH 
        parent is null
    connect by
        prior child=parent 
    and T1.ID IN 
    (
        SELECT 
        GROUPID
        FROM 
        ENTRPT.UOA_GROUPSUSERS GU
        GROUP BY 
        GROUPID
    ) X
    , ENTRPT.UOA_GROUPSUSERS T2
    , ENTRPT.UOA_USERS T3
    WHERE 
         X.ID = T2.GROUPID
    AND T2.USERID = T3.ID
    ORDER BY T3.NAME, X.ID, X.NAME
    GROUP BY T3.NAME, X.ID, X.NAME
)
;


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
I don't know how correct this is, but it does return results:

select 
  x.id
, parentid
, x.name "GroupName"
, t2.groupid
, t2.userid
, t3.DESCRIPTION "USER_NAME"
, t3.name "UserName"
, SYS_CONNECT_BY_PATH(x.name, '/') "Path"
, LEVEL
from 
  WF_REPOS.uoa_groups x
, WF_REPOS.uoa_groupsusers t2
, WF_REPOS.uoa_users t3
where 
    x.id = t2.groupid 
AND t2.userid = t3.ID 
START WITH x.name <> 'blah'
CONNECT BY PRIOR x.ID = x.parentid
ORDER BY 
  x.NAME
, t3.name
;


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Master
posted Hide Post
We use WebFOCUS RESTful Web Services security administration web service requests for this type of thing.

A call to list existing WF users would look something like this:
http://host:port/ibi_apps/rs/ibfs/SSYS/USERS?IBIRS_action=get

WF returns the results in XML format. We save the XML into a file, and then open in MS Excel to obtain our report.

There are similar calls for things like get all users in a group and so on.

See the 'WebFOCUS RESTful Web Services Developers Guide' for further info, and of course check that your site has this product installed/available to you, if you consider this type of call.




Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
 
Posts: 807 | Registered: April 23, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
WF returns the results in XML format. We save the XML into a file, and then open in MS Excel to obtain our report.

So one cannot use these WebFOCUS RESTful Web Services to create WebFOCUS reports?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Master
posted Hide Post
Apparently I missed a bit of syntax. I went back through the oracle documentation and added a couple things as well as the connect by.

 Select x.Path, x.Pathlen, x.ID AS Group_ID, x.CONNECT_BY_ROOTID, t1.name AS Group_Name, x.name AS MemeberOf, t2.userid
      ,t3.name AS User_Name
from (
select ID, connect_by_root ID, LEVEL-1 AS Pathlen, SYS_CONNECT_BY_PATH(ID, '/') AS Path, name 
from uoa_groups 
--where level > 0 
connect by prior parentid = id) x
,uoa_groups t1
,uoa_groupsusers t2
,uoa_users t3
where t1.id = x.connect_by_rootid
    and T2.GROUPID = x.ID
    and t2.USERID = t3.ID
group by x.Path, x.Pathlen, x.ID, x.CONNECT_BY_ROOTID, t1.name,x.name, t2.userid, t3.name
Order by x.Path, x.Pathlen, x.ID, x.CONNECT_BY_ROOTID, t1.name, x.name, t2.userid, t3.name; 


You still won't see any users from LDAP, but if you manage your users directly through WebFOCUS, that isn't an issue.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Reply With QuoteReport This Post
Master
posted Hide Post
quote:
So one cannot use these WebFOCUS RESTful Web Services to create WebFOCUS reports?

So far we developers, at our site, have only needed to create 'user/group' reports on a one-off/ad hoc basis, so running the URL in browser, and then opening the resultant XML in MS Excel has worked just fine.

I *guess* this could be automated by...calling the web service from a fex, grabbing the resultant XML, and reading it into a TABLE FILE command for reporting...?
 
Posts: 807 | Registered: April 23, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
Thanks for the updated code Eric.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] WF 8.0.08 - Security Center: How to create a report of users in a group?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.