3 Replies Latest reply: Jan 8, 2013 10:10 PM by Chanchal Wankhade RSS

    unable to describe the table as system user

    user7202581
      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
          Salman Qureshi
          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
            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
              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.