1

I have versioned tables in an Oracle geodatabase (versioned with the option to move edits to base).

I would like to prevent users from deleting rows from the tables when using ArcGIS Desktop.

(With that said, the users still need to be able to select, update, and insert rows into the tables.)

The reason for preventing the deletion of rows is:

The asset records in the tables need to be preserved/decommissioned so that they can be integrated to a work order management system. We cannot allow rows to be deleted from the GIS, because the GIS records would become out-of-sync with the records in the work order management system (in which deletes are not possible, and historical records are preserved).


ArcGIS functionality:

It is my understanding that preventing deletes is possible with out-of-the-box ArcGIS functionality on unversioned tables:

Grant and revoke dataset privileges:

If the dataset is not registered as versioned, you can grant and revoke the update, insert, and delete privileges individually using the Privileges dialog box. For example, you can grant a user select and update privileges, which allows the user to connect to the dataset and alter existing features but does not allow the user to add new features or delete existing features.

If the dataset is registered as versioned, the privileges that allow a user to modify a dataset (update, insert, and delete) must be granted and revoked as a group.


Question:

I understand that preventing deletes is not possible on versioned tables using out-of-the-box functionality in ArcGIS.

However, is there a reason why I shouldn't prevent deletes via database privileges?

REVOKE DELETE ON ROADS FROM ROLE_EDITORS_GROUP_A
  • Other than it would cause both UPDATEs and DELETEs to fail? Yes. – Vince Oct 3 at 15:49
  • @Vince I can confirm that you are correct. When trying to INSERT a new record with DELETE revoked, I get this error: Unable to save edits. Underlying DBMS error [ORA-01031: insufficient privileges]. – User1973 Oct 3 at 15:52
  • 1
    That means the code is testing for S/I/U/D privs before doing anything, even though D isn't required for insert. – Vince Oct 3 at 16:01
  • 1
    It is sometimes easier to delete versioned-DB records and replace them with records from another source--essentially an update that lists DELETES and INSERTS in a version-to-version compare. For example, when shapes change a lot, many attributes change, or tiny topology edits were made. Not sure you want to limit that activity. And, what if someone accidentally adds a record, how would they take it out? – danak Oct 4 at 21:42
3

Unfortunately, as you know you can only grant or revoke INSERT/UPDATE/DELETE privileges as a group when working with versions inside of a geodatabase. If you revoke the DELETE privilege at the RDBMS-level you will certainly create future issues. This is a result of how versioning was designed and is handled inside of the geodatabase; you don't just have a single table, you have a base table (e.g. Roads), a view (e.g. Roads_evw), an adds table (e.g. A10078), and a deletes table (D10078), in addition to the sde tables. These tables operate together and require broad privileges in order to provide the versioning functionality. In theory, you can create the functionality you desire, but it would be extremely difficult to manage as you upgrade your software/geodatabase/RDBMS. Esri would be unlikely to support your geodatabase moving forward should you introduce any issues.

The supported and recommended solution is to protect the DEFAULT version of the geodatabase and have users make all edits in their own versions or in a designated editing version. The version(s) should be Q/A'd before being posted to the DEFAULT version by the geodatabase admistrator.

  • Thanks for your answer! Regarding the second paragraph: I understand that manual QC in named versions is a typical approach to this sort of problem. With that said, I think we should be able to prevent errors from happening in the first place, instead of trying to catch them after-the-fact. Rule enforcement is one of the things that computers are good at (and it's one of the things that humans are bad at). I think there is an appealing opportunity here to systematically prevent errors, rather than prevent errors with manual QC. – User1973 Oct 3 at 16:17
  • As a human who is quite good at rule enforcement (with data at least), I don't get how it is hard to check the number of records in the version vs. in DEFAULT. It is nothing like having to deal with editing conflicts. – danak Oct 4 at 21:49
0

I tested REVOKE DELETE ON ROADS FROM ROLE_EDITORS_GROUP_A in ArcGIS Desktop 10.3.

It failed; when trying to INSERT a new record with DELETE revoked, I get this error:

Unable to save edits. Underlying DBMS error [ORA-01031: insufficient privileges].

Explanation: (adapted from @Vince's comment)

ArcObjects is testing for all of the privileges (SELECT, INSERT, UPDATE, DELETE) before doing anything, even though DELETE isn't required for INSERT:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.