nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

All comments

MMBR

@JoshBillings - the featureclass registration_id in the sde.table_registry table in the sde repository is used to name the Delta Tables, A<registration_id> Adds Table and D_<registration_id> for Deletes Table.

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

How to determine the Featureclass ST_Geometry Spatial Index Table

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

@MarceloMarques @ShannonShields Thank you for the replies. Would there happen to be a table in the geodatabase that shows all of these A & D tables and what feature class they belong to?

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

MMBR

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

MMBR

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

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

MMBR

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.