This discussion is archived
3 Replies Latest reply: Jan 8, 2013 8:10 PM by Chanchal Wankhade RSS

unable to describe the table as system user

user7202581 Newbie
Currently Being Moderated
Hi,

I'm able to describe the below table as sys user but not able to do so as system user.


SQL> desc IA_MBR_ID_D;
Name Null? Type
----------------------------------------- -------- ----------------------------
MBR_ID_EDW_SK NOT NULL NUMBER
MBR_ID NOT NULL CHAR(3)
MBR_SBSCR_ALTN_ID NOT NULL VARCHAR2(13)
MBR_LST_NM VARCHAR2
MBR_FRST_NM VARCHAR2
MBR_MID_INIT VARCHAR2
MBR_EFF_DT DATE
MBR_CANC_DT DATE
MBR_BTH_DT DATE
MBR_GNDR_CD NOT NULL CHAR(1)
MBR_RLNSP_CD NOT NULL CHAR(1)
MBR_TYP_OF_CONTR_CD NOT NULL VARCHAR2(3)
MBR_UNIQ_MBR_ID NOT NULL VARCHAR2(26)
MBR_SHRT_UNIQ_MBR_ID NOT NULL VARCHAR2(16)

SQL> conn system/.....@<DB>
Connected.
SQL> desc IA_MBR_ID_D;
ERROR:
ORA-04043: object IA_MBR_ID_D does not exist

SEGMENT_NAME SEGMENT_TYPE OWNER
--------------------------------------------------------------------------------- ------------------ ------------------------------
IA_MBR_ID_D TABLE EDW
IA_MBR_ID_D TABLE EDWSTG

Please share your thoughts.Not sure if this is a privilege issue as I'm not able to dec this as system user.

Regards,
VN
  • 1. Re: unable to describe the table as system user
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,
    You need to prefix owner name with table name while describin it. Try as follows while logged in as user SYSTEM
    desc edw.IA_MBR_ID_D
    desc edwstg.IA_MBR_ID_D
    So this table is in two schemas edw and edwstg. In SYS schema, you might have a synonym created for one of above 2 tables and hence you are able to describe without using owner name as prefixed. Use following query to find out which tables sysnonym in SYS schema is pointing to.
    select owner,synonym_name,table_owner,table_name from dba_synonyms where synonym_name = 'IA_MBR_ID_D';
    Salman
  • 2. Re: unable to describe the table as system user
    sb92075 Guru
    Currently Being Moderated
    user7202581 wrote:
    Hi,

    I'm able to describe the below table as sys user but not able to do so as system user.


    SQL> desc IA_MBR_ID_D;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    MBR_ID_EDW_SK NOT NULL NUMBER
    MBR_ID NOT NULL CHAR(3)
    MBR_SBSCR_ALTN_ID NOT NULL VARCHAR2(13)
    MBR_LST_NM VARCHAR2
    MBR_FRST_NM VARCHAR2
    MBR_MID_INIT VARCHAR2
    MBR_EFF_DT DATE
    MBR_CANC_DT DATE
    MBR_BTH_DT DATE
    MBR_GNDR_CD NOT NULL CHAR(1)
    MBR_RLNSP_CD NOT NULL CHAR(1)
    MBR_TYP_OF_CONTR_CD NOT NULL VARCHAR2(3)
    MBR_UNIQ_MBR_ID NOT NULL VARCHAR2(26)
    MBR_SHRT_UNIQ_MBR_ID NOT NULL VARCHAR2(16)

    SQL> conn system/.....@<DB>
    Connected.
    SQL> desc IA_MBR_ID_D;
    ERROR:
    ORA-04043: object IA_MBR_ID_D does not exist

    SEGMENT_NAME SEGMENT_TYPE OWNER
    --------------------------------------------------------------------------------- ------------------ ------------------------------
    IA_MBR_ID_D TABLE EDW
    IA_MBR_ID_D TABLE EDWSTG

    Please share your thoughts.Not sure if this is a privilege issue as I'm not able to dec this as system user.

    Regards,
    VN
    connect / as sysdba
    SELECT OBJECT_TYPE, OWNER FROM DBA_OBJECTS WHERE OBJECT_NAME = 'IA_MBR_ID_D';

    post results from SQL above
  • 3. Re: unable to describe the table as system user
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    Look at the below
    *SYS* 
    owns the oracle data dictionary. 
    Every object in the database all have a single owner. 
    For the database dictionary, and a whole lot of special tables (performance views and the like) are all owned by the SYS user. 
    
    *SYSTEM* 
    user is supposed to be the master DBA user, with access to all of these object. 
    This reflects an early, and long time, Oracle security design philosophy. 
    You build the application using one user, then create a second with access (select, update, delete) but not drop privileges. 
    This gives you a "super-user" access to your schema without being able to destroy it accidentally. 
    Over the years, thing have been added to the SYSTEM account that may have needed to be in the SYS account. 
    But very few people want to give out access to their SYS account if they don't have to. 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points