AnsweredAssumed Answered

Oracle Read-Only User 10.2

Question asked by jason.tipton.gis on Feb 17, 2016
Latest reply on Mar 14, 2016 by mjarman-esristaff

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:

 

New to 10.4

 

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

Outcomes