2 Replies Latest reply on Jul 9, 2015 5:52 PM by mgicdba

    Dropping user via SQL Developer also performs DML against MDSYS owned objects

    mgicdba

      Oracle DB 11.2.0.4

      SQL Developer

           4.0.2.15 (Build 15.21)

       

           Java(TM) Platform 1.7.0_60

            Oracle IDE 4.0.2.15.21

      I noticed that when dropping an Oracle database user, via SQL Developer, the tool also seems to perform one INSERT a bunch of DELETEs into MDSYS-owned tables. I'd like to find out why or if there is some type of explanation. Because this was reported on a Sarbanes-Oxley report that was sent to my upper management, I need to provide a valid explanation as to why this person performed DML against these tables.

       

      Here is what I gleaned from sys.aud$. The action sequence starts at the bottom and work your way up.

      The actions performed by the Security Admin were: he created the user, which included two GRANTs, and then he dropped the user because he had created it incorrectly.  I was able to reproduce the exact sequence of events.

       

      ACTIONSPERFORMEDOBJ$CREATOROBJ$NAME
      DROP USERN/ATESTUSER
      DELETE ROWMDSYSSDO_NETWORK_TIMESTAMPS
      DELETE ROWMDSYSSDO_NETWORK_HISTORIES
      DELETE ROWMDSYSSDO_NETWORK_USER_DATA
      DELETE ROWMDSYSSDO_NETWORK_LOCKS_WM
      DELETE ROWMDSYSSDO_NETWORK_CONSTRAINTS
      DELETE ROWMDSYSSDO_NETWORK_METADATA_TABLE
      DELETE ROWMDSYSSDO_GEOR_DDL__TABLE$$
      DELETE ROWMDSYSSDO_TIN_PC_SEQ
      DELETE ROWMDSYSSDO_TIN_PC_SYSDATA_TABLE
      DELETE ROWMDSYSSDO_GEOR_SYSDATA_TABLE
      DELETE ROWMDSYSSDO_ANNOTATION_TEXT_METADATA
      DELETE ROWMDSYSSDO_TOPO_METADATA_TABLE
      DELETE ROWMDSYSSDO_LRS_METADATA_TABLE
      DELETE ROWMDSYSSDO_THEMES_TABLE
      DELETE ROWMDSYSSDO_STYLES_TABLE
      DELETE ROWMDSYSSDO_CACHED_MAPS_TABLE
      DELETE ROWMDSYSSDO_MAPS_TABLE
      DELETE ROWMDSYSSDO_GEOM_METADATA_TABLE
      INSERT ROWMDSYSSDO_GEOR_DDL__TABLE$$
      SELECTSYSAUD$
      SELECTSYSAUD$
      ALTER USERN/ATESTUSER
      GRANT ROLEN/ASMTG_INQ
      GRANT ROLEN/ACONNECT
      CREATE USERN/ATESTUSER
      SELECTSYSOBJ$
      SELECTSYSTAB$
      SELECTSYSEXTERNAL_TAB$
      LOGON