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