topic Re: Privileges required for System Tables/ST_Geometry Access for Normal Read/Write Access in Geodatabase Questions

Privileges required for System Tables/ST_Geometry Access for Normal Read/Write Access

ChrisDupont — Wed, 11 May 2016 10:49:44 GMT

Hi All,

Here’s the environment I have:

Oracle 12c, configured with ST_Geometry spatial type and containing versioned data
ArcGIS 10.2.1 (patched up to UT4)
ArcFM 10.2.1b

I’m in the process of cleaning up the roles on a project database that has been handed to me. The group that was previously working with it did not have a great deal of GIS experience and did not provision roles properly. To make things easier, the DBA role was liberally provided to many accounts, and those accounts that didn’t have it were provided massive blanket roles (provisioned through Oracle at a schema level, and not via ArcGIS) and what appeared to be every single system privilege you could provide.

This is not an acceptable setup going forward, so I’ve spent a bit of time designing proper roles and getting those roles created and provisioned properly (i.e. via ArcGIS interfaces for spatial/versioned data) and ensuring that at least the minimum amount of Oracle permissions are there (as per http://resources.arcgis.com/en/help/main/10.2/index.html#//002n0000002v000000) for the test account. After creating the test account, provisioning it with the necessary oracle privileges and my new roles, I took it out for a test run – and quickly found out that while I can connect to the database with it, see all the objects and see all the records in the objects – I could not view the data spatially, either in ArcCatalog or ArcMap.

It took a little while, but we found out that the reason that things were failing was that the user did not have EXECUTE access to a large group (I think around 80) ST packages/procedures. Granting the user EXECUTE rights to those packages resolved that issue. Then I encountered a problem that the user could not edit versioned data (and actually did not even consider the versioned material to be versioned, even though other accounts were fine) – so now I’m in the process of trying to figure out the provisioning for the backend SDE/System tables to allow this functionality to work....which is both time consuming and aggravating.

So my questions are:

Is there a list of the minimum level of permissions required for packages and system tables that would need to be applied for user access? I can’t see any documentation that covers this, and I don’t recall ever hearing about this problem for other setups. Which leads to my next question:
Are these rights and roles set in the backend via the ‘Enable Enterprise’ and ‘Create Spatial Type’ tasks – or another system process that gets run during the setup?
1. If there are system processes that do this, can they be re-run on a database without causing issues?

If there is any information that someone could provide on either of these questions, that would be greatly appreciated.

Thanks in advance.

Chris

Re: Privileges required for System Tables/ST_Geometry Access for Normal Read/Write Access

PanagiotisPapadopoulos — Wed, 11 May 2016 12:03:16 GMT

During the install and configuration of an ArcSDE geodatabase, grants are made to the PUBLIC role on tables for DELETE, INSERT, SELECT AND UPDATE permissions. Additionally, there are grants given to the PUBLIC role to add EXECUTE to various SDE and SYS objects.

These permissions are needed to provide access to geodatabase functionality, such as viewing certain geometry types and performing administration tasks.

If these permissions cannot be granted to the PUBLIC role they must be explicitly granted to the GIS users.

the following TEchnical Artile provide all the the necessary info you may needed to know

40020 - Repair permissions on ArcSDE repository tables and objects for the public role in ArcSDE for Oracle geodatabases

also helpful is the following article

35408 - Can the execute privilege be removed from public on Oracle packages?

Re: Privileges required for System Tables/ST_Geometry Access for Normal Read/Write Access

ChrisDupont — Thu, 26 May 2016 12:21:56 GMT

Hi Panagiotis,

Thanks for the response and my apologies for the delay in responding. The '40020' article you provided set me on the right path, though as all the databases I currently had were in the same state. I ended up having to rebuild a DB from scratch and copy the objects back in using ESRI tools. Once I did that, the privilege issue was resolved.

For future reference for anyone else with this issue, the following objects were found to be granted to PUBLIC role after I rebuilt the database.

ALL_ST_GEOMETRY_COLUMNS_V DELETE

ALL_ST_GEOMETRY_COLUMNS_V INSERT

ALL_ST_GEOMETRY_COLUMNS_V SELECT

ALL_ST_GEOMETRY_COLUMNS_V UPDATE

ARCHIVE_UTIL EXECUTE

BLOB_ARRAY_TAB EXECUTE

BND_ROWID_TAB EXECUTE

COLUMN_REGISTRY SELECT

CONNECTION_ID_GENERATOR SELECT

DBTUNE SELECT

DBTUNE_UTIL EXECUTE

FLT_ARRAY_TAB EXECUTE

GDB_ITEMRELATIONSHIPS DELETE

GDB_ITEMRELATIONSHIPS INSERT

GDB_ITEMRELATIONSHIPS SELECT

GDB_ITEMRELATIONSHIPS UPDATE

GDB_ITEMRELATIONSHIPS_VW DELETE

GDB_ITEMRELATIONSHIPS_VW INSERT

GDB_ITEMRELATIONSHIPS_VW SELECT

GDB_ITEMRELATIONSHIPS_VW UPDATE

GDB_ITEMRELATIONSHIPTYPES DELETE

GDB_ITEMRELATIONSHIPTYPES INSERT

GDB_ITEMRELATIONSHIPTYPES SELECT

GDB_ITEMRELATIONSHIPTYPES UPDATE

GDB_ITEMS DELETE

GDB_ITEMS INSERT

GDB_ITEMS SELECT

GDB_ITEMS UPDATE

GDB_ITEMS_VW DELETE

GDB_ITEMS_VW INSERT

GDB_ITEMS_VW SELECT

GDB_ITEMS_VW UPDATE

GDB_ITEMTYPES DELETE

GDB_ITEMTYPES INSERT

GDB_ITEMTYPES SELECT

GDB_ITEMTYPES UPDATE

GDB_REPLICALOG DELETE

GDB_REPLICALOG INSERT

GDB_REPLICALOG SELECT

GDB_REPLICALOG UPDATE

GDB_TABLES_LAST_MODIFIED DELETE

GDB_TABLES_LAST_MODIFIED INSERT

GDB_TABLES_LAST_MODIFIED SELECT

GDB_TABLES_LAST_MODIFIED UPDATE

GDB_UTIL EXECUTE

GEOMETRY_COLUMNS SELECT

INSTANCES DELETE

INSTANCES INSERT

INSTANCES SELECT

INSTANCES UPDATE

INSTANCES_UTIL EXECUTE

INSTANCE_ID_GENERATOR SELECT

INT_ARRAY_TAB EXECUTE

KEYSET_UTIL EXECUTE

LAYERS SELECT

LAYERS_UTIL EXECUTE

LAYER_ID_GENERATOR SELECT

LAYER_LOCKS SELECT

LAYER_STATS_UTIL EXECUTE

LINEAGES_MODIFIED SELECT

LOCATORS SELECT

LOCATOR_ID_GENERATOR SELECT

LOCATOR_UTIL EXECUTE

LOCK_UTIL EXECUTE

LOGFILE_UTIL EXECUTE

METADATA SELECT

METADATA_ID_GENERATOR SELECT

METADATA_UTIL EXECUTE

MVTABLES_MODIFIED SELECT

OBJECT_LOCKS SELECT

PINFO_UTIL EXECUTE

PROCESS_INFORMATION SELECT

R4 SELECT

R5 SELECT

R6 SELECT

R7 SELECT

R8 SELECT

RASTERCOLUMNS_UTIL EXECUTE

RASTERCOLUMN_ID_GENERATOR SELECT

RASTER_COLUMNS SELECT

REGISTRY_UTIL EXECUTE

SDENUMTAB EXECUTE

SDEXMLTOTEXT EXECUTE

SDE_ARCHIVES SELECT

SDE_LAYER_STATS SELECT

SDE_LOGFILE_POOL SELECT

SDE_TABLES_MODIFIED SELECT

SDE_TABLES_MODIFIED UPDATE

SDE_UTIL EXECUTE

SDE_XML_COLUMNS SELECT

SDE_XML_DOC1 DELETE

SDE_XML_DOC1 INSERT

SDE_XML_DOC1 SELECT

SDE_XML_DOC1 UPDATE

SDE_XML_DOC2 DELETE

SDE_XML_DOC2 INSERT

SDE_XML_DOC2 SELECT

Re: Privileges required for System Tables/ST_Geometry Access for Normal Read/Write Access

PanagiotisPapadopoulos — Fri, 27 May 2016 10:06:41 GMT

thanks Chris for the response.