Oracle Read-Only User 10.2

[JasonTipton]

I'm needing to create an Oracle Read Only User in a 10.2 Oracle GDB. I'm reading the documentation for Adding Users To Oracle​. I see that a user must be able to create the Log File tables​. About 1/4 the way down the Log file documentation (under heading "Permissions required for shared log files"), the help suggests that you do the following to create a read-only user:

1. Create "Read Only" User
2. Grant more than just read only
   • Need Create Table, Create Session, Create Sequence, Insert/Update/Delete
3. Have read-only user "make a selection that exceeds the selection threshold" (100 records)
   • The log tables/sequences are created
4. Revoke the extra permissions
   • Create Table, Create Sequence

This might be a fine way to "let ArcMap do what it needs to do" and then take back control, but in a large company with source control, change management and DBA environment promotion, my DBA's aren't going to like this approach. They would much rather do everything in a more precise, scriptable (documented) method.

This process seems to create 2 tables, 4 indexes, and 1 sequence. Can I not just copy the DDL from these objects in DEV to give to my DBA's to push up to production? This seems to work. If so, I'm not sure why this isn't documented.

The new steps would be:

1. Create Read Only User
   1. Need to be able to INSERT/UPDATE/DELETE tables in own schema.
   2. Need CREATE SESSION, of course
2. Create DB Objects
   • TABLES
     • SDE_LOGFILE_DATA
     • SDE_LOGFILES
   • INDEXES
     • SDE_LOGFILE_DATA_IDX1
     • SDE_LOGFILE_DATA_IDX2
     • SDE_LOGFILES_PK
     • SDE_LOGFILES_UK
   • SEQUENCES
     • SDE_LOGFILE_LID_GEN

<EDIT> Thanks to Vince Angelo​

Other alternatives to creating the logfile objects via SQL or having a user manually select 101+ features in Arcmap:

• arcpy.ConfigureGeodatabaseLogFileTables_management​
• sdeping​ utility with the -L option

New to 10.4

• Create Database User​ now creates the log file tables when the tool is ran

Haven't tested these methods.  All 3 methods would be scriptable/automated, but they are not as explicit as just creating the objects in SQL and still require the extra permissions to be added/revoked.

Message was edited by: Jason Tipton Added some alternatives to SQL creation. Edited Again to add 10.4 update

[VinceAngelo]

Try:

sdeping -i sde:oracle11g -u [username] -p [password]@[tnsname] -v

If that doesn't work, I recommend using Instant Client.

- V

[JasonTipton]

That was it!

[JasonTipton]

Just happened to find in the "What's New in ArcMap 10.4"​ that the Create Database User Tool now creates the log tables when the tool is ran. That certainly simplifies the workflow from an ArcGIS user standpoint.

Unfortunately, again, it doesn't help my cause because I need to do all of this from the Oracle side and you still have to come back after running that script and remove the Create table/sequence permissions.

I will add that to the list of alternatives.

[MelissaJarman]

There is an ideas post for this as well as an enhancement for this functionality. This would pair very nicely with the new functionality of the 'Create Database User' tool creating the log file tables for the user without having to make a selection set, etc.

ENH-000090374 - Add the option within the 'Create Database User' geoprocessing tool to toggle between a user with permission to connect or a user with permission to load data

http://ideas.arcgis.com/ideaView?id=087E0000000Cx8dIAC#sthash.sk5AnmOD