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

[ChristopheAndrey1]

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?

[George_Thompson · Accepted solution]

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

Geodatabase‌

[ChristopheAndrey1]

Yes, that was it!

Thank for your quick answer!

[ChristopheAndrey1]

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?

[George_Thompson]

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.

[ChristopheAndrey1]

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. 

[George_Thompson]

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.

[MichaelVolz]

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.

[ChristopheAndrey1]

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. 

[MichaelVolz]

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.