none
Bug in SSMA For Oracle 6.0 for non-dba Oracle user

    Question

  • When attempting to connect to Oracle using a non-admin user (eg a schema owner), it fails with an object not found error.

    I turned up logging and found that SSMA was issuing a query that tried to read from SYS.MLOG$.

    This is the complete query:

    SELECT COUNT(*) FROM ( SELECT Usrs.user_id                                                     
          , Objs.object_name                                                 
          , Objs.object_type                                                 
          , Objs.created                                                     
          , Objs.last_ddl_time                                               
          , Objs.temporary                                                   
          , Objs.status AS status                                            
          , 0 as is_clustered                                                
          , NULL as table_type_name                                          
          , NULL as table_type_owner                                         
     FROM sys.all_objects Objs                                               
        , sys.all_users Usrs                                                 
     WHERE Objs.object_type IN ('FUNCTION', 'PROCEDURE', 'SEQUENCE', 'VIEW', 'MATERIALIZED VIEW') 
       AND Objs.subobject_name IS NULL                                       
       AND Objs.owner = Usrs.username                                        
       AND Objs.owner NOT IN ('ANONYMOUS', 'BI', 'AURORA$ORB$UNAUTHENTICATED', 'AURORA$JIS$UTILITY$', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPDBA', 'OLAPSVR', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OSE$HTTP$ADMIN', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_ES', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYSMAN', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB') 
     UNION ALL 
     SELECT Usrs.user_id                                                             
          , Objs.object_name                                                         
          , Objs.object_type                                                         
          , Objs.created                                                             
          , Objs.last_ddl_time                                                       
          , Objs.temporary                                                           
          , Objs.status AS status                                                    
          , decode(cluster_name,null,0,1) as is_clustered                            
          , NULL as table_type_name                                                  
          , NULL as table_type_owner                                                 
     FROM sys.all_objects Objs                                                       
        , sys.all_users Usrs                                                         
        , sys.all_tables Tbls                                                        
     WHERE Objs.object_type = 'TABLE'                                                
       AND Objs.subobject_name IS NULL                                               
       AND Objs.owner = Usrs.username                                                
       AND Objs.owner = Tbls.owner                                                   
       AND Objs.object_name = Tbls.table_name                                        
       AND Tbls.nested <> 'YES'                                                      
      AND (Objs.OWNER, Objs.OBJECT_NAME) NOT IN               
        (                                                     
          SELECT mv.OWNER, mv.CONTAINER_NAME                  
          FROM SYS.ALL_MVIEWS mv                              
          WHERE mv.OWNER IS NOT NULL                          
            AND mv.CONTAINER_NAME IS NOT NULL                 
          UNION ALL                                           
          SELECT mvl.LOG_OWNER, mvl.LOG_TABLE                 
          FROM SYS.ALL_MVIEW_LOGS mvl                         
          WHERE mvl.LOG_OWNER IS NOT NULL                     
            AND mvl.LOG_TABLE IS NOT NULL                     
          UNION ALL                                           
          SELECT mvl2.MOWNER, mvl2.TEMP_LOG                   
          FROM SYS.MLOG$ mvl2                                 
          WHERE mvl2.MOWNER IS NOT NULL                       
            AND mvl2.TEMP_LOG IS NOT NULL                     
        )                                                     
       AND TRIM(Tbls.instances) <> '0' 
       AND Tbls.dropped <> 'YES' 
       AND Objs.owner NOT IN ('ANONYMOUS', 'BI', 'AURORA$ORB$UNAUTHENTICATED', 'AURORA$JIS$UTILITY$', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPDBA', 'OLAPSVR', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OSE$HTTP$ADMIN', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_ES', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYSMAN', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB') 
     UNION ALL 
     SELECT Usrs.user_id                                                             
          , Objs.object_name                                                         
          , Objs.object_type                                                         
          , Objs.created                                                             
          , Objs.last_ddl_time                                                       
          , Objs.temporary                                                           
          , Objs.status AS status                                                    
          , decode(cluster_name,null,0,1) as is_clustered                            
          , Tbls.table_type as table_type_name                                       
          , Tbls.table_type_owner                                                    
     FROM sys.all_objects Objs                                                       
        , sys.all_users Usrs                                                         
        , sys.all_object_tables Tbls                                                 
     WHERE Objs.object_type = 'TABLE'                                                
       AND Objs.owner = Usrs.username                                                
       AND Objs.owner = Tbls.owner                                                   
       AND Objs.object_name = Tbls.table_name                                        
       AND Tbls.nested <> 'YES'                                                      
      AND (Objs.OWNER, Objs.OBJECT_NAME) NOT IN               
        (                                                     
          SELECT mv.OWNER, mv.CONTAINER_NAME                  
          FROM SYS.ALL_MVIEWS mv                              
          WHERE mv.OWNER IS NOT NULL                          
            AND mv.CONTAINER_NAME IS NOT NULL                 
          UNION ALL                                           
          SELECT mvl.LOG_OWNER, mvl.LOG_TABLE                 
          FROM SYS.ALL_MVIEW_LOGS mvl                         
          WHERE mvl.LOG_OWNER IS NOT NULL                     
            AND mvl.LOG_TABLE IS NOT NULL                     
          UNION ALL                                           
          SELECT mvl2.MOWNER, mvl2.TEMP_LOG                   
          FROM SYS.MLOG$ mvl2                                 
          WHERE mvl2.MOWNER IS NOT NULL                       
            AND mvl2.TEMP_LOG IS NOT NULL                     
        )                                                     
       AND Tbls.dropped <> 'YES' 
       AND Objs.owner NOT IN ('ANONYMOUS', 'BI', 'AURORA$ORB$UNAUTHENTICATED', 'AURORA$JIS$UTILITY$', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPDBA', 'OLAPSVR', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OSE$HTTP$ADMIN', 'OUTLN', 'PM', 'QS', 'QS_ADM', 'QS_ES', 'QS_WS', 'RMAN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYSMAN', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB') 
    )

    SSMA should really not be querying this table.

    The least-privilege way to proceed is to connect to Oralce as sysdba and grant the user SELECT on this table. 

    C:\>sqlplus sys/whatever as sysdba
    
    SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 8 19:20:26 2014
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
    
    SQL> grant select on sys.mlog$ to MyUser
      2  /
    
    Grant succeeded.
    
    SQL>

    David


    David http://blogs.msdn.com/b/dbrowne/


    Monday, September 08, 2014 7:34 PM

All replies

  • Hi David,

    Thanks for you posting a reply to share your solution and I will mark it as answer. That way, other community members could benefit from your sharing.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Tuesday, September 09, 2014 8:13 AM
    Moderator