ArcGIS Desktop 10.5 problems with role permissions in Oracle

4625
12
Jump to solution
03-23-2017 03:00 AM
Highlighted
New Contributor III

In ArcGIS Desktop 10.5 (ArcCatalog or ArcMap) I get the following error when attempting to add a feature class to a map from an Oracle 11g (11.2.0.4) Enterprise Geodatabase (10.2.1) which I have been granted select permission through a role:

Error opening feature class

Insufficient permissions [insufficient permissions[OWNER.TABLE_NAME]]

And then immediately after it another error pops up:

Could not add the specified data object to the map.

Insufficient permissions [insufficient permissions[OWNER.TABLE_NAME]]

Sometimes the "Insufficient permissions" line is repeated 2 or 3 times.

Right clicking on a layer and doing Properties gives an error that only includes the "Insufficient permissions" line the first time.  Trying it again does nothing (no error message).

In ArcCatalog If I modify the permissions (through a separate connection as admin) in any way while my user connection is open then I am able to access all layers granted through roles.  If I disconnect and reconnect then the error returns.  I can't reproduce this in ArcMap, only Catalog.

If I am granted permission on the layer directly to my user account (not through the role) then I can access it.

If I have been granted the SELECT ANY TABLE system privilege then I am able to access all layers.

The role is a default role for my account.

The error only occurs on feature classes, plain tables work fine.

I only get the error when connecting directly to the geodatabase, going through the ArcSDE Server 10.2.1 works correctly.

I do not get this error using ArcMap 10.4 or lower.

The same problem occurs when trying access the layer with arcpy.MakeFeatureLayer_management:

ExecuteError: Failed to execute. Parameters are not valid. ERROR 000732: Input Features: Dataset C:/db_connection.sde/OWNER.TABLE_NAME does not exist or is not supported Failed to execute (MakeFeatureLayer)

1 Solution

Accepted Solutions
Highlighted
Occasional Contributor

The  BUG-000104457 is addressed in the patch:

http://support.esri.com/download/7504

This patch is for ArcMap 10.5.

View solution in original post

12 Replies
Highlighted
Esri Frequent Contributor
Highlighted
Occasional Contributor II

Hello Ryan-

 Thank you for providing some in-depth information and background on this issue. I would encourage you to reach out to Esri Technical Support (Geodata) if possible regarding this matter for further analysis. 

Reply
0 Kudos
Highlighted
New Contributor II

We are experiencing the same issue.  10.3 and 10.4 connect to the databases fine, but in testing 10.5 we get the insufficient permissions errors.  Connecting via the schema owner does not yield these errors, presumably because the schema owner has the select any table privilege granted to it.

We are digging to find a proper role/privilege configuration that will stop this, we obviously do not want to grant the select any table privilege to all users.

Reply
0 Kudos
Highlighted
New Contributor II

We also ran into this error, but with the schema owner. It is very strange that a schema owner cannot preview of show the properties of a table. We noticed that some tables worked whereas other tables gave the error. There seemed no difference in registration, so we looked at the Oracle code behind the table and noticed that the one table had one extra role permission. We gave the schema owner the role permission and suddenly the table was accessible. So now you guess it will be straightforward to figure out which permission was missing...if it was always that simple we would get bored, so we had to dig deeper.

After revoking ALL permissions from the schema owner and giving the schema owner just enough to make a connection, we could also 'see' all the tables, so a role GRANTED to the schema owner caused the issue. Our roles and permissions structure is fairly complex, so it took a hundred times of granting and revoking permissions and disconnecting/connecting to the geodatabase to find out that the issue was:

granting a role with editing permissions which is granted to another role directly to a user caused the issue. So:

CREATE ROLE LEVEL_ONE;
CREATE ROLE LEVEL_TWO;

GRANT LEVEL_TWO TO LEVEL_ONE;

GRANT DELETE, INSERT, SELECT, UPDATE ON GEODATA.LOOKUP_VALUES TO LEVEL_TWO;

GRANT LEVEL_TWO TO GEODATA;

-- this last grant causes the GEODATA user to not be able to access the LOOKUP_VALUES table in his own schema.

REVOKE LEVEL_TWO FROM GEODATA;

-- just to make sure: revoking this role makes the dataset accessible again.  

Highlighted
Occasional Contributor

Hello, 

A Bug is logged on this issue.

BUG: BUG-000104457 - Error: “Opening feature class. Insufficient permissions” is encountered while adding data to ArcMap 10.5 session using Data Owner.

For further information on this issue please reach out to ESRI support services.

Highlighted
Esteemed Contributor

I believe this is the same issue I talked about in the following post:

Desktop Client Issues in 10.5 When Using Oracle SDE Database 

The issue would appear to prevent data editing in ArcMap 10.5 (very big issue).

Does anyone have the ability to see if this bug exists when using ArcMap 10.5 against an Oracle 12c SDE database?

Highlighted
Occasional Contributor

Unfortunately, this issue is reproducible with oracle 12C data in ArcMap 10.5.

Reply
0 Kudos
Highlighted
Occasional Contributor

Hello, 

The BUG-000104457 ( Error: “Opening feature class. Insufficient permissions” is encountered while adding data to ArcMap 10.5 session using Data Owner.) is fixed in ArcMap 10.5.1.

Highlighted
Esteemed Contributor

Did ESRI already release 10.5.1 to the general public (I do not see it as an available download at myesri.com when I sign in)?  Are you a beta tester for new releases so you have been able to test this yourself?

Reply
0 Kudos