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

3139
3
Jump to solution
05-11-2016 03:49 AM
Highlighted
New Contributor II

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:

  1. 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:
  2. 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

0 Kudos
1 Solution

Accepted Solutions
Highlighted
Esri Regular Contributor

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...

also helpful is the following article

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

View solution in original post

0 Kudos
3 Replies
Highlighted
Esri Regular Contributor

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...

also helpful is the following article

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

View solution in original post

0 Kudos
Highlighted
New Contributor II

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

Highlighted
Esri Regular Contributor

thanks Chris for the response.

0 Kudos