Best Practices with Granting Privileges and Versioning Data

[JoshBillings]

Hey all,

I am working on getting a new Oracle enterprise geodatabase set up for our organization. Our named users are using account credentials that utilize our AD. The DBA has a process that he runs to grant privileges to roles and users in the geodatabase. Every time he runs this privilege process, I get a "ORA-00942 table or view does not exist" error when I try to open a table in a map. I have to un-version and re-version the tables in the geodatabase in order to access the tables again with any account other than the data owner. Is there any cause for concern in doing this?

We were handling privileges through ArcMap/Pro in our old database but we thought our DBA's process he created might be better for the new database. How does your organization typically handle granting privileges to users and roles?  

Thanks,

Josh

[MarceloMarques]

@JoshBillings - you only need to refresh the Oracle roles if the data owner user creates new featureclasses and tables in the geodatabase or if the data owner user performs any geodatabase operation that creates new tables behind the scenes, like register as traditional version. Then, the editor/viewer users must disconnect, close/open ArcMap & ArcGIS Pro, and then reconnect. 

"ORA-00942 table or view does not exist" - you probably just need to close/open ArcMap & ArcGIS Pro and reconnect, you do not need to un-version and re-version the tables in the geodatabase.

I typically handle the privileges in Oracle via SQL as well but when necessary I do use the ArcMap / ArcGIS Pro Privileges Tool.

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 and forward to your Oracle DBA.

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

I hope this helps.

[JoshBillings]

Thanks for the reply @MarceloMarques. Unfortunately, closing ArcGIS Pro and reopening it doesn't solve the issue. I also tried refreshing the SDE connection file and also creating a new SDE connection.

Below is the message I get in the Drawing Alert notification pane (I'm using ArcGIS Pro 3.2). Is there anything meaningful in this message? Maybe the [STATE_ID = 0] could lead to something useful?

Thanks,

Josh

[MarceloMarques]

@JoshBillings - in that case the issue might be how your Oracle DBA is refreshing the privileges using SQL, which might not be granting permissions to all the tables of the data owner user to the roles.

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]

@JoshBillings - Example, "PM" is the data owner user.

The Oracle DBA can use the SQL script below to refresh the permissions of the roles "RLPMVIEWER" and "RLPMEDITOR".

SET SERVEROUTPUT ON;
spool Roles_pm.sql;
DROP ROLE "RLPMEDITOR";
CREATE ROLE "RLPMEDITOR" NOT IDENTIFIED;
DROP ROLE "RLPMVIEWER";
CREATE ROLE "RLPMVIEWER" NOT IDENTIFIED;

select 'grant select on ' ||owner|| '.' || table_name || ' to RLPMVIEWER;'
from sys.dba_tables where lower(owner) = 'pm' order by table_name;

select 'grant select,insert,update,delete on ' ||owner|| '.' || table_name || ' to RLPMEDITOR;'
from sys.dba_tables where lower(owner) = 'pm' order by table_name;

spool off;
SET SERVEROUTPUT ON;
/
@Roles_pm.sql

Note, a featureclass is in fact many tables in the database, and when registered as traditional versioning the delta tables for that featureclass are created, delta tables = A adds table and D deletes table, other geodatabase operations also create tables in the database, for example, a M:N geodatabase relationship also has a table in the database behind the scenes.

Therefore, you need to use the ArcGIS Privileges Tool to refresh the permissions.

Or via SQL you need to grant the privileges to all the tables of the data owner user to the roles.

If you need to grant privileges to only one featureclass for example, then you should use the ArcGIS Privileges Tool, because the tool known all the tables of the featureclass. If you try to do this via SQL and you miss a table of the featureclass then you will have permission issues. We can determine the tables of a featureclass using the sde.table_registry and sde.layers, but it is always safe just to use the ArcGIS Privileges Tool in this case.

Then grant the role "RLPMEDITOR" to the editor users, it can be a Windows Authentication Login.
Then grant the role "RLPMVIEWER" to the viewer users, it can be a Windows Authentication Login.

If the Oracle Database is running on Windows OS, then we can create the Windows Authentication Login following these steps.

--HOW TO SETUP - WINDOWS AUTHENTICATION
http://download.oracle.com/docs/cd/B19306_01/win.102/b14304/external.htm

https://docs.oracle.com/en/database/oracle/oracle-database/19/ntqrf/administering-external-users-and-roles-on-windows.html#GUID-DC967330-BCB4-4737-AA50-BBBBF464F24C 

https://docs.oracle.com/en/database/oracle/oracle-database/21/ntqrf/administering-external-users-and-roles-on-windows.html#GUID-DC967330-BCB4-4737-AA50-BBBBF464F24C 

Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Administration Assistant for Windows.

initialization parameter 
os_authent_prefix=OPS$
remote_os_authent=FALSE
os_roles=FALSE
remote_os_roles=FALSE

DROP USER "OPS$ACME\MARC3737" CASCADE;
CREATE USER "OPS$ACME\MARC3737" 
  PROFILE "DEFAULT" IDENTIFIED EXTERNALLY 
  DEFAULT TABLESPACE "USERS" 
  TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
ALTER USER "OPS$ACME\MARC3737" TEMPORARY TABLESPACE "GIS_TEMP_GROUP";
ALTER USER "OPS$ACME\MARC3737" PROFILE "GIS_USERS";
GRANT "CONNECT" TO "OPS$ACME\MARC3737";
GRANT "GIS_LOGIN" TO "OPS$ACME\MARC3737";
GRANT "PMEDITOR" TO "OPS$ACME\MARC3737";
ALTER USER "OPS$ACME\MARC3737" QUOTA UNLIMITED ON "GIS_SDELOGFILE";
ALTER USER "OPS$ACME\MARC3737" QUOTA UNLIMITED ON "GIS_SDELOGFILEIDX";

SQL> connect /@mcs
Connected.
SQL> show user
USER is "OPS$ACME\MARC3737"
SQL>

*/
--exit;

--Oracle Client Enable Windows Authentication Protocol
https://www.oracle.com/database/technologies/faq-instant-client.html
/*
How do I ensure that my Oracle Net files like "tnsnames.ora" and "sqlnet.ora" are being used in Instant Client?
Files like "tnsnames.ora", "sqlnet.ora" and "oraaccess.xml" will be located by Instant Client 
by setting the TNS_ADMIN environment variable or registry entry to the directory containing the files. 
Use the full directory path; do not include a file name. 
Alternatively create a subdirectory "network/admin" under the Instant Client directory for the Oracle Net files. 
This is the default location and so no TNS_ADMIN variable is required.
*/
--under network/admin create a file named sqlnet.ora and add this line to enable windows authentication and save the file

SQLNET.AUTHENTICATION_SERVICES = (NTS)

/*
--example:  sqlnet.ora created under oracle instant client 64-bits (Pro) and 32-bits (ArcMap)
C:\oracle\instantclient_19_6\x32\network\admin
C:\oracle\instantclient_19_6\x64\network\admin

--cmd window, verify PATH variable
echo %PATH%
... ;C:\oracle\instantclient_19_6\x64;C:\oracle\instantclient_19_6\x32; ...
*/

If the Oracle Database is on Linux or if we want to use Active Directory Groups to grant access via Windows Authentication for Oracle Databases that are running on Windows or on Linux then is necessary to perform the configuration described in the document below.

Configuring Centrally Managed Users with Microsoft Active Directory (oracle.com)

I hope this helps to clarify.

[MarceloMarques]

@JoshBillings - I made a few updates on my last reply above. I hope this clarifies now.

[MarceloMarques]

@JoshBillings - this other discussion that I provided some examples as well might help further.

Solved: One Role to back up data with arcpy across multipl... - Esri Community

[JoshBillings]

Hi @MarceloMarques,

I would like to open this thread back up and see if you could assist me once again. Thanks for all of your earlier replies, they were helpful.

We are using Oracle CMU which uses Active Directory Groups to grant access to the Oracle Database. 

Rather than having to take note of all of the A and D tables that get created, it may be simpler to use the ArcGIS Privileges Tool rather than trying to have a list of all of these tables to be included in our permission process.

Are there any downsides or considerations to think of when managing feature dataset/feature class/table permissions via the ArcGIS Privileges Tool? Could this introduce errors that I may not be thinking of?

[MarceloMarques]

@JoshBillings - You can refresh the permissions by using a SQL Script that grants the correct privileges to the roles, you do not need to track A and D tables, I show that in the database guidebook, you can achieve the same using the ArcGIS Privileges Tool, but avoid granting privileges directly to editor and viewer users, this can lead to confusion of what privilege is granted, instead grant only permissions on the data owner tables to the roles and then grant the roles to the editor and viewer users, as I show in the database guidebook, you can still use the ArcGIS Privileges Tool to refresh the role permissions, but I prefer to use a SQL Script. You can find examples in the Production Mapping database guidebook for Oracle at Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices (esri.com)
I hope this answers your question.

[ShannonShields]

Hi Josh,

traditional versioned feature classes & tables have additional side tables to manage the edits. Based on the screenshot you provided it looks like permissions have not been granted to those tables - A# and D#. Each versioned table will get one set, and the names are based on the registration Id of the business table. 

Using the ArcGIS tools to grant permission is going to ensure that any related object has permission granted as well. The downside to those tools is that permissions need to be granted by the user that created the tables. You can absolutely continue to have your DBA manage permissions, but they need to understand all the related objects that also need to have permissions applied.

-Shannon