- Home
- :
- All Communities
- :
- Products
- :
- Data Management
- :
- Geodatabase Questions
- :
- Re: Privileges required for System Tables/ST_Geome...
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Privileges required for System Tables/ST_Geometry Access for Normal Read/Write Access
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- 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
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
also helpful is the following article
35408 - Can the execute privilege be removed from public on Oracle packages?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
also helpful is the following article
35408 - Can the execute privilege be removed from public on Oracle packages?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thanks Chris for the response.
