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 · Accepted solution]

This will work now, but it isn't a guarantee that it will work in later software releases.  This happens every time you start nipping at the heels of reverse engineering.  The documented procedure will work, even if the indexes change.

- V

BTW: The sdeping utility with the -L option will create the logifile pair of tables, and arcpy scripting can do the same.

[JasonTipton]

Vince Angelo Nice. Really didn't expect for there to be a an arcpy tool for that. That's great! Still doesn't really help my situation too much, but definitely good to know.

Those DBA's like to control as much as the database as possible. Also, I'm assuming the python script requires sysdba access. Most of the GDB Admin tools seem to (ex:create user schema...). That's definitely a deal breaker. For security, we can't have remote sysdba access and the database is running on a Solaris box so it's not like you can (or want) to install ArcMap on the DB box. Plus, they wouldn't give us sysdba access anyway...

It would be nice/helpful if these tools were mentioned/linked to from the previously mentioned ESRI documentation. As for "reverse engineering", I get what you're saying. Right now it "currently works that way", but we can't say it will always work that way, or even at 10.4 it works that way. I'm only "reverse engineering" the solution because it is not openly documented. If it (and other topics) were fully documented by geodatabase release, an organization could ensure that the changes that they made were complying the geodatabase model. It really wouldn't be "reverse engineering" at that point, but complying with standards.

Will edit my question to include the alternate solutions.

[VinceAngelo]

The layout of the LOGFILES / LOGFILE_DATA tables are documented​, and have been with every release since SDE 3.0.

ArcPy doesn't require SYSDBA access to create a logfiles, just the normal user access with CREATE TABLE before it's revoked (I'm not aware of a tool, but scripting it probably wouldn't be more than 10-15 lines of code).  Even that isn't necessary if you choose to use session logfiles.

- V

[JasonTipton]

Yes, thanks. That is in the same set of documentation I was looking at. It just would have been helpful if the documentation would have gone ahead and provided a link to that page when it mentioned "logfile tables".

Also, the python tool is documented, but at the time of reading, I didn't necessarily gather from it that it would create the log tables, just switch the logfile type.

I'm not sure if I fully understand the session logfile options. If I switch to session logfiles, will that only affect that user schema and the others will continue to operate how they have been? I took it as being a global change which is why I wrote it off, but if it only affects the current user schema, that is an option for sure...

... although, looking at it more, it still looks to me like the user needs create table access which is actually worse, b/c they need it every time they connect instead of just once.

[JasonTipton]

Vince Angelo​ -- apparently I can't DM you and I didn't want to hijack the other thread, but luckily this applies here as well. I've never used the se_tools before. I tried

sdeping -i sde:oracle11g:[dbserver]:[port] -u [username] -p [password] -v 

attempting to get the connection time, but I get an ORA-00903 invalid table with no other output

[VinceAngelo]

With Direct Connect to Oracle, I find it easiest to use the Instant Client connection syntax,  which would include slashes

sdeping -i sde:oracle11g😕[dbserver]:[port]/[servicename] -u [username] -p [password] -v

                         -                 -

You can also use environment variables (SDEINSTANCE, SDEUSER, SDEPASSWORD) to avoid needing to place the parameters on the command-line, and wiithout a switch or environment variable, the toolkit utilities will no-echo prompt for the password.

Note that se_toolkit utilities were deprecated at 10.2.x because the 'C' API was deprecated.  They seem to continue to work against 10.3.x but the combination of unsupported and deprecated does make discovering them at this point a mixed blessing.

- V

[JasonTipton]

Yeah, using the "/"s I get TNS listener errors.

[VinceAngelo]

What client are you using?  I've not had an issue with this syntax while using either a full Client or an Instant Client.

- V

[JasonTipton]

I have the full "Administrator" 11gR2 client and use a tnsnames.ora file