topic Re: How to keep connection to Oracle DB working from ArcGIS Pro 2.0 to 2.2.0? in ArcGIS Pro Questions

How to keep connection to Oracle DB working from ArcGIS Pro 2.0 to 2.2.0?

ChristopheAndrey1 — Wed, 29 Aug 2018 12:48:16 GMT

Hello
I just switched from ArcGIS Pro 2.0 to 2.2.0.

I have an Oracle 12 DB with 4 schemas, i.e. 4 DB-users.

With ArcGIS Pro 2.0, I could connect to all four without problem, using TNS names.

With ArcGIS Pro 2.2.0, I can connect to 3 of them but not to the 4th one. Trying to connect with the 4th user provokes an ORA-933 error. Please note that the 4th user only allows limited SQL-statements (no DDL, just DML, and only read-only ones).

What changed with 2.2.0 in respect to DB connection?

How am I supposed to connect to the DB with 2.2.0 using a DB-user with somewhat restricted rights?

Re: How to keep connection to Oracle DB working from ArcGIS Pro 2.0 to 2.2.0?

George_Thompson — Wed, 29 Aug 2018 13:42:33 GMT

Can you try to grant 'RESOURCE' to that user?

Geodatabase‌

Re: How to keep connection to Oracle DB working from ArcGIS Pro 2.0 to 2.2.0?

ChristopheAndrey1 — Wed, 29 Aug 2018 13:54:03 GMT

Btw, what I wrote for 2.0 in my question also holds for 2.1.3, as I just tested. Therefore, the break occurs between 2.1.3 and 2.2.0.

Re: How to keep connection to Oracle DB working from ArcGIS Pro 2.0 to 2.2.0?

George_Thompson — Wed, 29 Aug 2018 13:57:48 GMT

There may have been a change required at 2.2.

Have you tried to GRANT RESOURCE to that user and see if it will work?

Otherwise, you may need to get a technical support case opened and have an analyst take a look.

Re: How to keep connection to Oracle DB working from ArcGIS Pro 2.0 to 2.2.0?

ChristopheAndrey1 — Wed, 29 Aug 2018 16:43:34 GMT

Yes, that was it!

Thank for your quick answer!

Re: How to keep connection to Oracle DB working from ArcGIS Pro 2.0 to 2.2.0?

ChristopheAndrey1 — Wed, 29 Aug 2018 16:57:36 GMT

This said, granting "resource" cannot be an option on a productive database. Luckily for me, I'm currently using ArcGIS Pro 2.2 only for prototyping purposes.

In my productive application, ArcGIS Pro 2.0's query layers connect to the DB using a read-only DB user. Granting resource would mean allowing every ArcGIS Pro user smart enough to decrypt the DB user password from the map file to wreak my productive DB havoc.

Has somebody an idea how query layers are supposed to work without having to grant "resource" to the DB user they're using?

Re: How to keep connection to Oracle DB working from ArcGIS Pro 2.0 to 2.2.0?

George_Thompson — Wed, 29 Aug 2018 17:12:51 GMT

I would recommend contacting Esri Technical Support and working with them on what the difference between Pro 2.1.x and Pro 2.2.x. This could prevent you from having to grant RESOURCE to the read-only user.

Re: How to keep connection to Oracle DB working from ArcGIS Pro 2.0 to 2.2.0?

MichaelVolz — Wed, 29 Aug 2018 17:37:13 GMT

I had the same type of issue and BUG-000115421 was logged with ESRI where RESOURCE was needed to be granted for db role that was not needed in ArcMap. I only really starting testing SDE against Pro 2.2.x so I'm not sure if the SDE connection would have worked in earlier versions of Pro without the RESOURCE grant.

"The issue was discovered, but requires a change in db permissions. I would like to discuss why this database permission change was needed when moving from ArcMap to Pro as this specific db permission was not needed in ArcMap where this datasource has been used from 10.3.1 to 10.5.1." - from Case with ESRI

I'm hoping ESRI developers will fix this issue before Pro becomes production software at my org.

Re: How to keep connection to Oracle DB working from ArcGIS Pro 2.0 to 2.2.0?

ChristopheAndrey1 — Thu, 30 Aug 2018 09:28:28 GMT

Hi Michael

Is there a way to view the description of BUG-000115421 and track its progress?

I searched for it on support.esri.com but found no match.

Re: How to keep connection to Oracle DB working from ArcGIS Pro 2.0 to 2.2.0?

MichaelVolz — Thu, 30 Aug 2018 11:06:54 GMT

Christopher:

The only way I know how to get info on a bug and track its progress is to create a case with ESRI tech support and ask for that case to be linked to this specific bug as we are encountering the same issue. This is Oracle specific and might only be associated with Query layers so there may only be a limited number of end users who will encounter this issue.

Re: How to keep connection to Oracle DB working from ArcGIS Pro 2.0 to 2.2.0?

MichaelVolz — Mon, 10 Sep 2018 12:15:26 GMT

Christopher:

BUG-000115421 is a duplicate of BUG-000115420 and according to ESRI a solution is implemented where you now need to Grant 'Resource' database role to the user from Oracle, so ESRI's solution would break your security protocol.

Re: How to keep connection to Oracle DB working from ArcGIS Pro 2.0 to 2.2.0?

Anonymous User — Tue, 02 Oct 2018 19:07:05 GMT

I currently have an open case with Esri support regarding this problem as well. At pro 2.1.3 I am able to easily connect to an Oracle database with a read-only user that has been created by our DBAs and is used to provide read-only access to the Oracle tables, including via our GIS tools. This user is also used in other departments by other applications that need this data.

Upgrading the ArcGIS Pro client to 2.2 broke this connection ability. Nothing had changed in the user credentials or any other settings in Oracle. While Esri support has been helpful, the most recent suggestion was to give this read-only user CREATE TABLE permissions in Oracle.

For those of us in larger organizations, and since we are using the Oracle tables to supplement our GIS data and create new datasets that we then make available as GIS data in our enterprise GDB (SQL server), we have no ability to alter permissions on the Oracle database--nor should we. We use SQL server as our storage RDBMS for our enterprise geodatabase--we can apply permissions here no problem. However, expecting GIS users to be able to alter permissions on citywide RDBMS system to make Pro able to connect just to see the data isn't realistic. In my opinion this should not be considered a viable workaround. I will follow up with whatever the final solution turns out to be--for now I have rolled back the version of Pro to 2.1.3.

Edit: (10/3/18) I was just informed by Esri tech support that this is a bug that will be fixed in next patch (2.2.3) for ArcGIS Pro.