Users to Roles by Environment

Tim2632000

Active Member
Does anyone know if there is a report in E1 that can output Users, their Roles as well as the Environment?

We are: AS/400, 8.11 SP1 and Tools Release 8.96.H1
 
Nothing vanilla…

Here is what you need along with a little creative logic UBE… If XE, you will need groups not roles.

Users: F0092, F0101
Environments: F0093
Roles: F95921

Good Luck.
 
Tim,
=A0
Will this work?
=A0
Run the Security Audit Report by Role Version
(R009502, XJDE0002)
Access the Work With Batch Versions - Available Versions form. To do so, en ter P98305W in the Fast Path.
1. In the Batch Application field, enter R009502 and click the Find button.
2. Select the Security Audit Report by Role version.
3. To define processing options for the report, select Processing Options f rom the Row menu, and then
complete the processing options as appropriate:
=95 Role (optional)
Do not use this option for this report. Instead, enter the role in the Data Selection form.
Copyright =A9 2007, Oracle. All rights reserved. 107
Using Security Workbench Chapter 7
=95 Report on Application Security
Leave blank if you want the report to include application security records. Enter 1 to exclude
application security records.
=95 Report on Row Security
Leave blank if you want the report to include row security records. Enter 1 to exclude row security
records.
=95 Report on Column Security
Leave blank if you want the report to list application security records. En ter 1 to exclude application
security records.
=95 Report on Published BSSV Security
Leave blank if you want the report to list published business service secur ity records. Enter 1 to
exclude published business service security records.
4. On the Work With Batch Versions - Available Versions form, click Select.
5. On the Versions Detail form, select the Data Selection check box and cli ck the Submit button.
6. On the Data Selection form, use the User ID left operand to define the r ole that you want the report
to list security records for.
7. Click OK on the Data Selection form.
8. On the Printer Selection form, define the location for the output of the report and then click OK to
submit it.
 
I wrote this SQL below to give me most of the info. Just modify it to suit your purposes (ie add another join to the F0093).

--SHOW USER ACCESS FOR ENABLED USERS

select A.RLTOROLE, B.AUROLEDESC, A.RLFRROLE, B.AUSEQNO from SY812.F95921 AS A LEFT JOIN SY812.F00926 AS B
ON A.RLFRROLE = B.AUUSER
where A.RLTOROLE IN (select SCUSER from SY812.F98OWSEC where SCEUSER='01')
ORDER BY A.RLTOROLE, B.AUSEQNO, A.RLFRROLE


Colin
 
I've created two DB views (F5595921 & F550092) & used them in some APPL (makes it easier to output your grid to Excel, if necessary).
It was the only way to add multiple instances of the same table (F0101), and join multiple tables from multiple databases.
CREATE VIEW PRODDTA.F5595921
AS
SELECT PRODDTA.F550092.USFRROLE AS RLFRROLE, PRODDTA.F550092.USEUSER AS RLEUSER, PRODDTA.F550092.USAN8 AS RLAN8,
F0101_1.ABALPH AS RLALPH, F0101_1.ABAC27 AS RLAC27, F0101_1.ABAC28 AS RLAC28, PRODDTA.F550092.USTOROLE AS RLTOROLE, PRODDTA.F550092.USAN80 AS RLAN80, F0101_2.ABALPH AS RLMLNM, F0101_2.ABAC01 AS RLAC01
FROM PRODDTA.F550092 LEFT OUTER JOIN
PRODDTA.F0101 F0101_2 ON PRODDTA.F550092.USAN80 = F0101_2.ABAN8 LEFT OUTER JOIN
PRODDTA.F0101 F0101_1 ON PRODDTA.F550092.USAN8 = F0101_1.ABAN8
and:
CREATE VIEW PRODDTA.F550092
AS
SELECT F95921_1.RLFRROLE AS USFRROLE, F98OWSEC_1.SCEUSER AS USEUSER, F0092_1.ULAN8 AS USAN8, F0092_2.ULUSER AS USTOROLE,
F0092_2.ULAN8 AS USAN80
FROM PS810.SY810.F98OWSEC F98OWSEC_1 RIGHT OUTER JOIN
PS810.SY810.F0092 F0092_2 ON F98OWSEC_1.SCUSER = F0092_2.ULUSER LEFT OUTER JOIN
PS810.SY810.F95921 F95921_1 LEFT OUTER JOIN
PS810.SY810.F0092 F0092_1 ON F95921_1.RLFRROLE = F0092_1.ULUSER ON F0092_2.ULUSER = F95921_1.RLTOROLE
 
Sorry for asking again:

Is there a way (in E1)to see which users belong to a role (8.12)
(outside of DB lookup?)
 
Back
Top