Conference orarep::nomahs::dbstars

772.0. "scott/tiger has access to too many tables" by BROKE::BITHER () Wed Apr 23 1997 15:21

     <<< NOMAHS::DISK$NOMAHS1:[NOTES$LIBRARY]DBINTEGRATOR_PUBLIC.NOTE;2 >>>
                      -< DB Integrator Public Conference >-
================================================================================
Note 1156.0        Scott sees too many tables via tg2 Oracle7          3 replies
CHSR38::RROHR "Cajun? Zeydeco? Both!"                69 lines  18-APR-1997 03:40
--------------------------------------------------------------------------------
    When connecting to O7 via sqlplus scott/tiger SCOTT sees less tables
    than when connecting from SQL via the gateway. Some of these tables
    should definetely not be seen. Is there something I have not setup
    correctly? I am especially scared about the V_* stuff.
    DBI 7.0. Rdb 7.0 O7 7.1.5.
    
    Thanks
    Regina
    
select * from      CATALOG0 where rownum <2
select * from      DICTIONARY0 where rownum <2
select * from      EXU7ANAL where rownum <2
select * from      EXU7BSZ where rownum <2
select * from      EXU7CCLO where rownum <2
select * from      EXU7CCLU where rownum <2
select * from      EXU7CCOU where rownum <2
select * from      EXU7CGRU where rownum <2
select * from      EXU7CLUU where rownum <2
select * from      EXU7COLU where rownum <2
select * from      EXU7CONU where rownum <2
select * from      EXU7FUL where rownum <2
select * from      EXU7GRNU where rownum <2
select * from      EXU7ICOU where rownum <2
select * from      EXU7INDU where rownum <2
select * from      EXU7JBQU where rownum <2
select * from      EXU7LNKU where rownum <2
select * from      EXU7ORD where rownum <2
select * from      EXU7PSTU where rownum <2
select * from      EXU7REFU where rownum <2
select * from      EXU7RGCU where rownum <2
select * from      EXU7RGSU where rownum <2
select * from      EXU7SEQU where rownum <2
select * from      EXU7SNAPLU where rownum <2
select * from      EXU7SNAPU where rownum <2
select * from      EXU7SPSU where rownum <2
select * from      EXU7SPU where rownum <2
select * from      EXU7STOU where rownum <2
select * from      EXU7SYNU where rownum <2
select * from      EXU7TABU where rownum <2
select * from      EXU7TGRU where rownum <2
select * from      EXU7TNE where rownum <2
select * from      EXU7USCU where rownum <2
select * from      EXU7USRU where rownum <2
select * from      EXU7VER where rownum <2
select * from      EXU7VEWU where rownum <2
select * from      PRODUCT_PRIVS where rownum <2
select * from      PSTUBTBL where rownum <2
select * from      PUBLICSYN where rownum <2
select * from      SMP_BLOB where rownum <2
select * from      SMP_BLOB0 where rownum <2
select * from      SMP_LONG_TEXT where rownum <2
select * from      SMP_LONG_TEXT0 where rownum <2
select * from      SM_$VERSION where rownum <2
select * from      SYNONYMS where rownum <2
select * from      SYSCATALOG where rownum <2
select * from      SYSFILES where rownum <2
select * from      SYSSEGOBJ where rownum <2
select * from      TABQUOTAS where rownum <2
select * from      USER_PRIVS where rownum <2
select * from      USER_RESOURCE_where rownum <2
select * from      V_$LOADCSTAT where rownum <2
select * from      V_$LOADTSTAT where rownum <2
select * from      V_$MLS_PARAMETERS where rownum <2
select * from      V_$NLS_PARAMETERS where rownum <2
select * from      V_$NLS_VALID_VALUES where rownum <2
select * from      V_$OPTION where rownum <2
select * from      V_$PQ_SESSTAT where rownum <2
select * from      V_$VERSION where rownum <2
    
================================================================================
Note 1156.1        Scott sees too many tables via tg2 Oracle7             1 of 3
CHSR38::RROHR "Cajun? Zeydeco? Both!"                 3 lines  22-APR-1997 03:53
--------------------------------------------------------------------------------
    Any comments? Pointers? Hints?
    
    /Regina
================================================================================
Note 1156.2        Scott sees too many tables via tg2 Oracle7             2 of 3
BROKE::ABUGOV                                        19 lines  22-APR-1997 13:42
       -< Maybe Oracle doesn't let you see the v* tables by default.... >-
--------------------------------------------------------------------------------
    
    Hi Regina,
    
    I'm not sure why we allow the user to see more tables.  What we try to
    do is let them see everything they have privileges to see.  I'm not
    sure where the V* tables come from - we get table info both from
    all_synonyms and from all_tables:
    
    SELECT SYNONYM_NAME,TABLE_NAME,TABLE_OWNER,DB_LINK FROM ALL_SYNONYMS 
    
    and
    
    SELECT OWNER,TABLE_NAME,NUM_ROWS,AVG_ROW_LEN FROM ALL_TABLES WHERE
    (TABLE_NAME IS NOT NULL)
    
    If the user has privs to see a table, we will show it....
    
    dan
    
================================================================================
Note 1156.3        Scott sees too many tables via tg2 Oracle7             3 of 3
CHSR38::RROHR "Cajun? Zeydeco? Both!"                16 lines  23-APR-1997 06:52
                         -< Synonyms and SYS. prefix >-
--------------------------------------------------------------------------------
    V_$xxxxx  are dynamic tables which are kept in memory. On these there
    are Views and on the views are synonyms.
    
    I discovered that the Rdb gateway displays all these tables, whilst in
    oracle you will have to add a sys.v_$xxxxxx to see them or use the
    synonym (ie. V$VERSION versus SYS.V_$VERSION). Rdb apparently can't
    make the difference and shows the real table name versus the synonym
    name you will see in Oracle7 itself. 
    
    But you can see them as well with SYS. prefix.
    
    I think my customer will be able to live with that.
    
    Thanks,
    Regina