Select to view content in your preferred language

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

476
5
Jump to solution
04-03-2024 06:06 AM
JamesMorrison1
Occasional Contributor

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 🙂

0 Kudos
1 Solution

Accepted Solutions
MarceloMarques
Esri Regular Contributor

@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

MarceloMarques_2-1712161061450.png

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.

MarceloMarques_1-1712160495000.png

MarceloMarques_0-1712160437768.png

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

MarceloMarques_3-1712161577965.png

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.

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov

View solution in original post

5 Replies
MarceloMarques
Esri Regular Contributor

@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

MarceloMarques_2-1712161061450.png

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.

MarceloMarques_1-1712160495000.png

MarceloMarques_0-1712160437768.png

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

MarceloMarques_3-1712161577965.png

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.

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
MarceloMarques
Esri Regular Contributor

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

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
JamesMorrison1
Occasional Contributor

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?

 

 

0 Kudos
MarceloMarques
Esri Regular Contributor

@JamesMorrison1 - you got it.  : )

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
MarceloMarques
Esri Regular Contributor

@JamesMorrison1 - adding as a reference this other discussion.

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

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
0 Kudos