topic Re: One Role to back up data with arcpy across multiple Schema in Oracle in Geodatabase Questions

One Role to back up data with arcpy across multiple Schema in Oracle

JamesMorrison1 — Wed, 03 Apr 2024 13:33:05 GMT

Hello Everyone.

Oracle 19c / ArcGIS Pro 3.2 / eGDB 11.1

I wish to have one sde connection file to have read-only access to data across multiple schema to export them via arcpy to a fGDB. With the aim to make a nightly export.

I have read the documentation: Grant and revoke dataset privileges in databases and enterprise geodatabases—ArcGIS Pro | Documentation

Basically I want one role to rule them all 🙂

Note: Not our real database structure.

AREA1 - user / schema
FC1
TAB1

AREA2 - user / schema
FC1
FC2
TAB1

AREA3- user / schema
FC1
FC2
TAB1
TAB2

Question 1

Can I just create a User BACKUP_DB, create a Role backup_data_read_role. Then assign that Role to each Feature Class (FC) or Table (TAB) in each Schema. Then have just one .sde connection file connecting as BACKUP_DB / PW to be used in my arcpy script?

Question 2

I assume I have to login to each Schema separately AREA1, AREA2, AREA3 and assign the Feature Classes and Tables to the Role backup_data_read_role? Can you in the ESRI world assign a Role to data in different Schema?

Question 3

Is there a better approach to achieve my results?

Feedback appreciated. Before I make too many mistakes 🙂

Re: One Role to back up data with arcpy across multiple Schema in Oracle

MarceloMarques — Wed, 03 Apr 2024 16:31:48 GMT

@JamesMorrison1 - see this example.

CONNECT /AS SYSDBA --a. create the role DROP ROLE "RLDATAREADER"; CREATE ROLE "RLDATAREADER" NOT IDENTIFIED;

If "not" registered as traditional versioning.

--b. grant privileges --b1. If featureclasses and tables --are "not" registered as traditional versioning. --AREA1 data owner GRANT SELECT ON AREA1.FC1 TO RLDATAREADER; GRANT SELECT ON AREA2.Sxxxx_ID$ TO RLDATAREADER; --FC1 Spatial Index Table GRANT SELECT ON AREA1.TAB1 TO RLDATAREADER; --AREA2 data owner GRANT SELECT ON AREA2.FC1 TO RLDATAREADER; GRANT SELECT ON AREA2.Sxxxx_ID$ TO RLDATAREADER; --FC1 Spatial Index Table GRANT SELECT ON AREA2.FC2 TO RLDATAREADER; GRANT SELECT ON AREA2.Sxxxx_ID$ TO RLDATAREADER; --FC2 Spatial Index Table GRANT SELECT ON AREA2.TAB1 TO RLDATAREADER; --AREA3 data owner GRANT SELECT ON AREA3.FC1 TO RLDATAREADER; GRANT SELECT ON AREA2.Sxxxx_ID$ TO RLDATAREADER; --FC1 Spatial Index Table GRANT SELECT ON AREA3.FC2 TO RLDATAREADER; GRANT SELECT ON AREA2.Sxxxx_ID$ TO RLDATAREADER; --FC2 Spatial Index Table GRANT SELECT ON AREA3.TAB1 TO RLDATAREADER; GRANT SELECT ON AREA3.TAB2 TO RLDATAREADER;

How to determine the Featureclass ST_Geometry Spatial Index Table

If registered as traditional versioning.

--b2. If featureclasses and tables are --registered as traditional versioning then --use the ArcGIS Privileges Tool to grant --the proper permission to the Roles. https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/change-privileges.htm /* Note: a featureclass is in fact many tables in the database and when registered as traditional versioning the delta tables for the featureclass are created, the delta tables are the A table for Adds and the D table for Deletes, other geodatabase operations can also create more tables in the geodatabase, e.g. M:N geodatabase relationship class, Geometry Network, Utility Network, Topology, etc. */

ArcGIS Privileges Tool example.

How to determine the Delta Tables - Adds Table, Deletes Table of a Featureclass or Table registered as traditional versioning.

Create the Viewer User

--c. create viewer user DROP USER AREA_VIEWER CASCADE; CREATE USER AREA_VIEWER PROFILE "GIS_USERS" IDENTIFIED BY ***** DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK; ALTER USER "AREA_VIEWER" TEMPORARY TABLESPACE "GIS_TEMP_GROUP"; ALTER USER "AREA_VIEWER" PROFILE "GIS_USERS"; GRANT "CONNECT" TO "AREA_VIEWER"; GRANT "GIS_LOGIN" TO "AREA_VIEWER"; GRANT "RLAREAVIEWER" TO "AREA_VIEWER"; ALTER USER AREA_VIEWER QUOTA UNLIMITED ON "GIS_SDELOGFILE"; ALTER USER AREA_VIEWER QUOTA UNLIMITED ON "GIS_SDELOGFILEIDX";

Oracle Enterprise Geodatabases Best Practices

You can learn more best practices about privileges in my community.esri.com blog below, read the database guidebook for Production Mapping, the best practices can be applied to any industry, there you will also find the database template scripts for Oracle with further recommendations, you can download the guidebook and the database template script.

For a complete example on how to setup the data owner user, the editor and viewer users and how to grant permissions via roles see the Production Mapping Guide Book for Oracle.

Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

Best Practices Production Mapping 3.x Workspace in Oracle®

Oracle 19c Database Template Scripts (*)

 ArcGIS Pro 3.x & ArcMap 10.9.x June 21, 2022 *New

* has all the best practices from the guidebooks and more, including how to setup Oracle windows authentication logins and refresh privileges via roles.

I hope this helps.

Re: One Role to back up data with arcpy across multiple Schema in Oracle

MarceloMarques — Wed, 03 Apr 2024 16:32:25 GMT

@JamesMorrison1 - I made a few more updates to my reply above. I hope this clarifies and helps.

Re: One Role to back up data with arcpy across multiple Schema in Oracle

JamesMorrison1 — Thu, 04 Apr 2024 11:15:40 GMT

Hello Marcelo

As always great and in-depth replies. Much appreciated.

[1] So I create the Role in Oracle backup_data_read_role

[2] I then use three sde connection files for AREA1, AREA2 and AREA3 to give access to data owned in each separate Schema.

[3] In ArcGIS Pro / Arcpy for each separate sde connection file I can do the following:

AREA1.sde
arcpy.management.ChangePrivileges(FC1, " backup_data_read_role", "GRANT")
arcpy.management.ChangePrivileges(TAB1, " backup_data_read_role", "GRANT")

AREA2.sde
arcpy.management.ChangePrivileges(FC1, " backup_data_read_role", "GRANT")
arcpy.management.ChangePrivileges(FC2, " backup_data_read_role", "GRANT")
arcpy.management.ChangePrivileges(TAB1, " backup_data_read_role", "GRANT")

AREA3.sde
arcpy.management.ChangePrivileges(FC1, " backup_data_read_role", "GRANT")
arcpy.management.ChangePrivileges(FC2, " backup_data_read_role", "GRANT")
arcpy.management.ChangePrivileges(TAB1, " backup_data_read_role", "GRANT")
arcpy.management.ChangePrivileges(TAB2, " backup_data_read_role", "GRANT")

Finally assign Role backup_data_read_role to User BACKUP_DB in Oracle

[4] Create sde connection file for BACKUP_DB and this User should be able to see data in all three Schema AREA1, AREA2 and AREA3 via the backup_data_read_role?

Re: One Role to back up data with arcpy across multiple Schema in Oracle

MarceloMarques — Thu, 04 Apr 2024 15:20:38 GMT

@JamesMorrison1 - you got it. : )

Re: One Role to back up data with arcpy across multiple Schema in Oracle

MarceloMarques — Fri, 05 Apr 2024 21:40:39 GMT

@JamesMorrison1 - adding as a reference this other discussion.

Re: Best Practices with Granting Privileges and Ve... - Esri Community