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

[JamesMorrison1]

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

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 🙂

[MarceloMarques · Accepted solution]

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

[MarceloMarques]

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

[JamesMorrison1]

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?

[MarceloMarques]

@JamesMorrison1 - you got it.  : )

[MarceloMarques]

@JamesMorrison1 - adding as a reference this other discussion.

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