Finding User to Environment Mapping in JDE

If you are familiar with JDE, you must be aware that environments are added to roles, forming a role environment pair. Roles are assigned to users which indirectly provide access (to the user) to particular environments. Following is an example of procedure that is followed in JDE :

• Admin creates a new user (P0092)
• Admin creates a role (P0092)
• Environment(s) is/are assigned to role (P0092 -> Row Exit)
• Role(s) is/are assigned to user (P95921)
• User has access to those environments that are attached to roles assigned to him/her.

Now, when we have to find whether a user has access to particular environment or not, we generally follow this procedure :

• Find roles assigned to user (P95921)
• For each role, check environments attached to the role (P0092 -> Row Exit)

Consider a situation where we have a user id NIMISH (say).

There are total 4 environments, say, DV910, PY910, QA910 and PD910.

User ID “NIMISH” has 20 different roles assigned to him.

Only one of these roles has access to QA910 (say).

If we were to be asked whether user ID NIMISH has access to QA910 environment or not? It would take quite a long time (and efforts) to go through each role, searching whether it has the required environment attached to it or not.

Moreover, if the required user ID doesn’t have access to that particular environment, then we would require to search each and every role, only to answer “No”.

Considering the job of CNC, we get such kind of requests every now and then. So I spent some time thinking over finding an alternative to this process.

What if you could find out the answer in less than 10sec?

Yes, you heard it right. But this requires you to have access to SY910 database.

Just run the following query to display the environments that a user has access to :

SELECT DISTINCT LLLL FROM sy910.F0093 WHERE LLUSER IN (SELECT RLFRROLE FROM sy910.F95921 WHERE RLTOROLE=’NIMISH’);

Replace “NIMISH” in the above example with the user ID.

The query does all the hard work for you.

Explanation :

In the above query “RLFRROLE” finds all the roles assigned to the user id (RLTOROLE) from table F95921.

Then we do a search in F0093 table to find the “DISTINCT” environments assigned to these roles and display them.

Hope this saves a lot of time and efforts for you (as it did for me).