ArcSDE User(s) Auditing in Oracle

[MoizIshfaq]

Greetings all,
I have searched the subject matter through forums but could not get useful material on it. I am trying to audit a user activity in oracle database which performs various operations in ArcSDE e.g., recalculating spatial index for a feature dataset or an individual feature class.

I can not use Oracle Fine Grained Auditing (FGA) as it works on individual user objects. Please bear with me as I am new to oracle auditing stuff. When I implement user auditing through the 'audit alter table, select table, insert table, update table, delete table, grant table, grant procedure, execute procedure by USERNAME;' statement I can see the user selection and editing stuff on feature class but it is not showing 'rebuilding of spatial index' for feature dataset. I think there should be a statement for auditing oracle packages or other procedures execution which I need to include with this statement and don't have any idea about it. So, please share your thought or any clue on what I might be missing in this regard.

Thanks for your time and support in advance.

[anthonysanchez · Accepted solution]

Hi Mohammad,
I've been out of the ArcSDE game for a year but miss it dearly and therefore lurk in these forums. 🙂

I've got some ideas that may get you started down the right path but hopefully some of the brilliant folks that participate in these forums can chime in and get you what you need.

ArcSDE will only allow the owner of a feature class to recalculate the spatial index so you won't really know who did it if many people have the schema owner password.

As for when, this is an interesting challenge. What is your geometry type? sdelob, st_geometry, or SDO?

It may be as simple as auditing the gsize1,gsize2,and gsize3 columns in the sde.layers table assuming that a rebuild that yields the same grid size(s) as before the rebuild still update this table.

If sde.layers is not an option:

If using sdelob/sdebinary you may able to audit the spatial index table for a feature class S<registration_id>. Not sure if a rebuild issues a truncate against this table, a delete from, or a drop and recreate, but truncate would be ideal for auditing a spatial index rebuild since deletes will happen all the time from this table during editing (non versioned) and sde compress (versioned). Also, if you are frequently dropping and recreating feature classes this will become tedious to maintain as the S tables will get renamed each time.

If you are using ST_GEOMETRY or SDO there is no S<registration_id> table for a feature class. Not sure if you'll be able to audit a procedure in these cases or if you'll somehow be able to monitor the shape column or respective domain indexes for these that represent the spatial index. There is a SDE table that lists information about all the st_geometry spatial indexes (can't remember the name but it does have "ST_" in the name and its owned by SDE). You may be able to monitor this table for changes as well to determine when a spatial index for a st_geometry feature class was rebuilt.

I'd recommend conducting a level 12 trace of a spatial index rebuild for each of your geometry types in use to learn exactly what happens under the hood during a rebuild. Then you should be able to determine exactly what you need to audit and how to audit in such a way that you are not bombarded with erroneous audit activities that you need to filter through.

Anthony

[MoizIshfaq]

Thanks Anthony for the informative reply. I would try to trace session information as you mentioned and hopefully this should work. I was actually working with a schema owner user to change a feature dataset spatial reference and trace this user activity using the Oracle 'Audit ..... user by access' command and it was not showing any activity in the Oracle audit tables. If you take a look at the ArcSDE system tables for ArcSDE 9.3.x, the spatial reference information along with the domain for feature classes are stored in 'Layers' object but there is no link to check where domain information for feature datasets are stored. Here is the link to ArcSDE System tables/object model 'http://webhelp.esri.com/arcgisdesktop/9.3/pdf/93_sde_diagram.pdf'.

I need this information as I am facing an error about spatial reference when I copy or replicate one of the feature datasets from our legacy database. The error message is 'The spatial reference z values do not match - Error when replicating data'. A very simple work around would be if I just recreate this feature dataset and import data into it but I can not do it for certain business reasons. I have even tried to dig out this matter at ArcObjects level and the spatial reference of this specific dataset seems fine but I just wander what might be causing this problem. However, this is a second issue and is different than the subject matter of this question but thought would give you a background of what I am doing.

I would update the status as I try it with the oracle level 12 trace information. Thanks once again for your help.

[MoizIshfaq]

Thanks Anthony, your suggestion was helpful in tracing sql queries executed for calculation of spatial index and similar operations.