Databrowser Security – Part II – The 9.1 Remix

So, way back last year (okay, just a few weeks ago), Chris wrote Part 1 of this blog, and promised a Part 2 with a 9.1 workaround for the same problem. To recap – a client wanted to grant access via Databrowser to just a few tables for a role. We knew we couldn’t use object level security, as that would completely limit that user in all of E1 to just those few tables. In the first part of this blog – I’ll pause here in case you want to go re-read – Chris talked about how to accomplish this in 9.2 with UDO View security. But there are still E1 customers on 9.1, so how could they accomplish something similar without the cool UDO features you get in 9.2?

Longtime followers of the Cube will know that we always say that security is a team effort between Functional and CNC. So Chris and I combined our powers as we often do, and we came up with the following workaround.

Let’s use the same objective as in Part 1 – we want to give the role DBTROLE access to the F1201 table only. But we are on 9.1, and thus cannot use UDO View security. The following is a step by step guide to make this work in 9.1.

Step 1: Access Security Workbench (P00950).

Step 2: Select Form menu and choose Set Up Security, Databrowser

Step 3: Enter the role and check “Allow access to launch Data Browser” but UN-check “Allow access to Search and Select for Tables or Business View queries”, as shown below:

NOTE – There is a bug in Tools (fixed in that prevents users from launching Data Browser (even when the Allow access box is checked) if the Allow access to Search and Select is denied/unchecked. See doc ID 2034701.1.

For a workaround to THIS, you can secure P9860WS instead.

Step 4: Click OK.

The role DBTROLE now has access to use the databrowser application. It’s important to note that users with this role cannot search for tables or views, but if they know the table name, they can type it into the field and launch it, as long as their regular Object security permits it.

While this solution is not perfect, it can function as a possible workaround until you upgrade to 9.2 and can live the good life with UDO security.

But wait – if my users don’t know the table names, and can’t search for them, how can they use Databrowser effectively?

For most users, even power users, the JDE table names are not intuitive anyway, and trying to remember the difference between F4311, F43121, and F43199 can drive them nuts.

Users will take advantage of Databrowser more fully when you create and publish a few public queries, with meaningful names. Here’s what we mean:

  1. Access the Databrowser
  2. In the Query Selector window, select the By Table radio button, and enter F4311, then tab out.

  1. Click OK or hit enter to launch F4311.

  1. Now, don’t do *anything* else. No need to customize the grid or add query criteria (unless you want to). Just hit the Manage Queries funnel in the upper right.

  1. Click the Save icon, and enter a name that will identify the table, such as “Purchase Order Detail F4311”. Then click OK.

  1. When you return to the Query Selector window, select the radio button next to Personal Queries, and click the drop down. You will see your new query listed.

  1. To make this query available to others, go to P98950 – User Overrides (remember, we are back in 9.1 – no UDOs).
  2. Locate your newly saved query named “Purchase Order Detail F4311” and click the Copy button.

  1. In the Copy Overrides form, enter the user or role to which you would like to copy the query, and click OK to save.

  1. This effectively publishes the query to the user/role, and it can now be accessed via the Public Queries radio button/drop down in the Query Selector window.

Repeat these steps for your top 5-10 tables that users want to query, and encourage them to build their own Personal queries using these as a starting point. They won’t have to remember those crazy JDE table names, and they can begin to leverage the capabilities of Databrowser on their own.

NOTE – For customers who use HCM or have other sensitive data they wish to secure users from viewing, this solution will NOT prevent them from entering the table names if they know them and if the tables are not secured.

We suggest that, for 9.1 users who should not access sensitive data, that those tables are secured using regular Object security before granting Databrowser access.