[SOLVED]Getting A User's Groups That They Have Access To
Hi,
We are running webfocus 8 and what I am trying to do is get a view of all the groups that a user has access to in webfocus. In webfocus 7, there was the mr extract utility but it seems that webfocus 8 doesn't have that anymore. After some searching through the forums, I was able to locate the webfocus repository and run this report:
JOIN
UOA_USERS.UOA_USERS.ID IN UOA_USERS TO MULTIPLE
UOA_GROUPSUSERS.UOA_GROUPSUSERS.USERID IN UOA_GROUPSUSERS TAG J0 AS J0
END
JOIN
J0.UOA_GROUPSUSERS.GROUPID IN UOA_USERS TO MULTIPLE UOA_GROUPS.UOA_GROUPS.ID
IN UOA_GROUPS TAG J1 AS J1
END
TABLE FILE UOA_USERS
PRINT
NAME
EMAIL
ID
J0.GROUPID
J1.DESCRIPTION
J1.NAME
WHERE NAME = 'username';
END
This almost provides me all the information that I need but it doesn't seems to show me the groups that a user is an advanced user in.
My intention is to join this report with all the group information to a custom lookup table that we have.
Can anyone please help?
Thanks, PrinceThis message has been edited. Last edited by: Prince Joseph,
Webfocus 8, All Outputs
October 14, 2015, 02:27 PM
Francis Mariani
I think you have to join to UOA_PSETS. The Description column in UOA_PSETS have the info "Advanced users who can also develop domain reporting applications and access all scheduling features"...
I'm not sure yet which columns to join with. I thought it might be UOA_USERSROLES.PSETID but that doesn't seem to work...
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
October 14, 2015, 02:38 PM
Francis Mariani
I think you also have to join to UOA_GROUPSRULES - the group rules probably come first, then the user rules override?.?.?.
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
October 14, 2015, 02:59 PM
Francis Mariani
I have no clue if this is helpful (or correct):
JOIN ID IN UOA_USERS TO MULTIPLE USERID IN UOA_GROUPSUSERS AS J1
JOIN ID IN UOA_USERS TO MULTIPLE USERID IN UOA_USERSROLES AS J2
JOIN PSETID IN UOA_USERS TO MULTIPLE ID IN UOA_PSETS TAG J3 AS J3
JOIN GROUPID IN UOA_USERS TO MULTIPLE ID IN UOA_GROUPS AS J4
JOIN GROUPID IN UOA_USERS TO MULTIPLE GROUPID IN UOA_GROUPSRULES AS J5
JOIN UOA_GROUPSRULES.PSETID IN UOA_USERS TO MULTIPLE ID IN UOA_PSETS TAG J6 AS J6
TABLE FILE UOA_USERS
WHERE NAME EQ 'fmariani';
PRINT
COMPUTE T0/A20 = 'UOA_USERS';
UOA_USERS.ID
UOA_USERS.DESCRIPTION
UOA_USERS.NAME
COMPUTE T3/A20 = 'UOA_PSETS';
J3.UOA_PSETS.DESCRIPTION
J3.UOA_PSETS.NAME
COMPUTE T4/A20 = 'UOA_GROUPS';
UOA_GROUPS.DESCRIPTION
UOA_GROUPS.EXTGROUPNAME
UOA_GROUPS.NAME
COMPUTE T6/A20 = 'UOA_PSETS';
J6.UOA_PSETS.DESCRIPTION
J6.UOA_PSETS.NAME
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
October 14, 2015, 03:01 PM
Francis Mariani
It look like some work needs to be done to correct the joins:
FOC2506 - INTERFACE-MANAGED NATIVE JOIN SELECTED FOR THE FOLLOWING REASON:
FOC2516 - MULTIPLICATIVE EFFECT WOULD ENSUE
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2592 - RDBMS-MANAGED JOIN HAS BEEN DISABLED
SELECT
T1."ID",
T1."DESCRIPTION",
T1."NAME",
T2."ID",
T2."PSETID",
T4."ID",
T4."DESCRIPTION",
T4."NAME",
T5."ID",
T5."GROUPID",
T6."ID",
T6."PSETID",
T7."ID",
T7."DESCRIPTION",
T7."NAME",
T8."ID",
T8."DESCRIPTION",
T8."EXTGROUPNAME",
T8."NAME"
FROM
WF_REPOS.UOA_USERS T1,
WF_REPOS.UOA_USERSROLES T2,
WF_REPOS.UOA_PSETS T4,
WF_REPOS.UOA_GROUPSUSERS T5,
WF_REPOS.UOA_GROUPSRULES T6,
WF_REPOS.UOA_PSETS T7,
WF_REPOS.UOA_GROUPS T8
WHERE
(T2."USERID" = T1."ID") AND
(T4."ID" = T2."PSETID") AND
(T5."USERID" = T1."ID") AND
(T6."GROUPID" = T5."GROUPID") AND
(T7."ID" = T6."PSETID") AND
(T8."ID" = T5."GROUPID") AND
(T1."NAME" = 'fmariani')
ORDER BY
T1."ID",T2."ID",T4."ID",T5."ID",T6."ID",T7."ID",T8."ID";
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
October 14, 2015, 03:07 PM
Prince Joseph
Thank you so much for looking into this. I will investigate with your suggestions and let you know what I find.
Webfocus 8, All Outputs
October 14, 2015, 05:54 PM
Waz
There are three & variables what we use.
&FOCSECUSER &FOCSECGROUP &FOCSECGROUPS
Not sure if your setup will have them, but the groups one has the all users groups
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
October 15, 2015, 07:50 AM
<PeterA>
You can try this. In site.wfs export the member of to an amper variable as
IBIMR_memberof(pass)
Then run the following
-* Code shamelessly stolen from focalpoint -* Sept 2015 -IF &IBIMR_memberof OMITS ';' GOTO ASO_LOOP; -SET &ASSO_CD0=GETTOK(&IBIMR_memberof,&IBIMR_memberof.LENGTH,1,';',60,'A60'); -SET &ASO_TOK=2; -SET &ASO_CNT=1; -SET &ASO_IND=&ASSO_CD0; -REPEAT ASO_LOOP WHILE &ASO_CNT LE 90; -*REPEAT ASO_LOOP WHILE &ASO_CNT LE &ASSO_CD0; -SET &ASSO_CD&ASO_CNT.EVAL=GETTOK(&IBIMR_memberof,&IBIMR_memberof.LENGTH,&ASO_TOK,';',60,'A60'); -IF &ASSO_CD&ASO_CNT.EVAL EQ ' ' THEN GOTO ASO_SKIP; -SET &ASO_TOK=&ASO_TOK + 1; -SET &ASO_CNT=&ASO_CNT + 1; -ASO_LOOP -ASO_SKIP -SET &ASO_CNT=&ASO_CNT - 1; -SET &OUTCNT = 1; -REPEAT OUTLOOP WHILE &OUTCNT LE &ASO_CNT; -TYPE &ASSO_CD&OUTCNT.EVAL -* &FOCSECUSER -SET &OUTCNT=&OUTCNT + 1; -OUTLOOP -EXIT
October 15, 2015, 07:51 AM
<PeterA>
" IBIMR_memberof(pass)"
October 19, 2015, 07:57 AM
Tony A
quote:
&FOCSECUSER&FOCSECGROUP&FOCSECGROUPS
To get these variables as mentioned by Waz, add them to your site.wfs in the manner to which Peter was trying to show -
@ Peter, the forum s/w interprets your carats as HTML code, so you need to use < and > for < and >
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
October 19, 2015, 05:01 PM
Waz
Interesting,
We didn't have to pass these, but our set up has a trusted relationship with the WF server.This message has been edited. Last edited by: Waz,
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
October 21, 2015, 09:03 AM
GavinL
Here is my list.. Maybe someone can use some of it.
# --- Documentation: http://techsupport.information...cAdmin/wf8104sec.pdf
# --- WebFOCUS Security and Administration > Manipulating WebFOCUS Variables > Managed Reporting Internal Variables
# Managed Reporting Internal Variables
# The folder where the processed report is stored
<SET> IBIMR_folder (pass)
# The complete path for a procedure, including file name and extension
<SET> IBIMR_fullpath (pass)
# The domain HREF where the report being processed is stored
<SET> IBIMR_domain (pass)
# The description given to the report by the developer
<SET> MR_FULL_FEXNAME (pass)
# The file name (also known as the IBFS name) assigned to the report when it was created
<SET> MR_ITEM_HANDLE (pass)
# The groups in which the user is a part of.
<SET> IBIMR_memberof(pass)
# The user name of who is logged in.
<SET> IBIMR_user(pass)
This I have an example fex:
SET ASNAMES = MIXED
-DEFAULT &Username = _FOC_NULL:
-DEFAULT &Fullname = _FOC_NULL:
TABLE FILE gavin_test/uoa_users
BY UOA_USERS.UOA_USERS.NAME AS 'Username'
BY UOA_USERS.UOA_USERS.DESCRIPTION AS 'Fullname'
WHERE UOA_USERS.UOA_USERS.NAME EQ '&IBIMR_user'
ON TABLE HOLD AS USERHOLD FORMAT ALPHA
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN
-READFILE USERHOLD
-DEFAULT &IBIMR_memberof = _FOC_NULL;
-DEFAULT &IBIMR_folder = _FOC_NULL;
-DEFAULT &IBIMR_fullpath = _FOC_NULL;
-DEFAULT &IBIMR_domain = _FOC_NULL;
-DEFAULT &MR_FULL_FEXNAME = _FOC_NULL;
-DEFAULT &MR_ITEM_HANDLE = _FOC_NULL;
-*These variables were setup via Admin Console->Config->Custom Settings
-TYPE IBIMR_memberof = &IBIMR_memberof
-TYPE IBIMR_folder = &IBIMR_folder
-TYPE IBIMR_fullpath = &IBIMR_fullpath
-TYPE IBIMR_domain = &IBIMR_domain
-TYPE MR_FULL_FEXNAME = &MR_FULL_FEXNAME
-TYPE MR_ITEM_HANDLE = &MR_ITEM_HANDLE
-TYPE FOCSECGROUPS = &FOCSECGROUPS
-TYPE FOCSECGROUP = &FOCSECGROUP
-TYPE FOCSECUSER = &FOCSECUSER
-*EO These variables were setup via Admin Console->Config->Custom Settings
-SET &USER = GETUSER('A20');
-TYPE GETUSER() = &USER
-SET &LIST = GRPLIST(300, 'A300');
-TYPE GRPLIST() = &LIST
-TYPE FOCMODE = &FOCMODE
-TYPE IBIMR_user = &IBIMR_user
-SET &Username = TRUNCATE(&Username);
-TYPE Username = &Username
-SET &Fullname = TRUNCATE(&Fullname);
-TYPE Fullname = &Fullname
The results look like this:
0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
IBIMR_memberof = Administrators;EVERYONE;
IBIMR_folder = Public
IBIMR_fullpath = IBFS:/WFC/Repository/Public/GetUsersandGroupInformation.fex
IBIMR_domain = Public/
MR_FULL_FEXNAME = Get Users and Group Information
MR_ITEM_HANDLE = GetUsersandGroupInformation
FOCSECGROUPS = Administrators;EVERYONE
FOCSECGROUP = Administrators
FOCSECUSER = PTH\admin
GETUSER() = SYSTEM
GRPLIST() = Administrators;EVERYONE
FOCMODE = WINNT
IBIMR_user = admin
Username = admin
Fullname = Administrator
- FOCUS Man, just FOCUS! ----------------------------- Product: WebFOCUS Version: 8.1.04 Server: Windows 2008 Server
October 29, 2015, 02:35 PM
Prince Joseph
Thanks everyone for your suggestions! I ended up getting the information from /ibi_apps/rs/ibfs/SSYS/GROUPS?IBIRS_action=get via an ajax request.