5 Replies Latest reply on Jul 9, 2020 7:16 PM by dba_chad

    Unable to see Editioning Views in SQL Developer

    dba_chad

      Greetings,

       

      We currently use Edition Based Redefinition in our databases and we've noticed something with several different versions of SQL Developer, including the latest 20.2 release.  In our environment, we have the owner schema that owns all of the database objects, including base tables and then the Editioning Views that point to these tables.  We then have personal accounts that have SELECT permissions to the Editioning views and no permissions to the underlying base tables.  We then create a service that points to the desired edition.  When we create a new edition, they can still see all of the editioning views that were there from the previous edition when connecting to the new service; however, any newly created Editioning View is not viewable under the Views section within the GUI.  They can open a SQL Worksheet and query the Editioning View.  The view does not appear in the GUI under Views.  I also noticed that the new edition does not appear under the Editions section within SQL Deverloper.  The use of the edition is granted to public.  I do have a SR ticket active for this; however, they've recommended I post here.

       

      Here's a simple test case that reproduces the issue.  Just place in .sql file and run with DBA privileged account.  Following, verify with SQL Developer by connecting to the DB_ED0_SRVC and the user/pass of app_owner/app_owner and see if you can see DATA_OWNER.NEW_TABLE under Views in the GUI.

       

       

       

      prompt Performing Initialization Cleanup

      prompt Note:  Errors can be ignored

      drop user data_owner cascade;

      drop user app_owner cascade;

      exec dbms_service.stop_service('DB_ED0_SRVC');

      exec dbms_service.delete_service('DB_ED0_SRVC');

      drop edition db_ed_0;

       

      prompt Create data_owner schema

      create user data_owner identified by data_owner default tablespace sysaux quota unlimited on sysaux;

      grant create session to data_owner;

       

      prompt Create app_owner schema

      create user app_owner identified by app_owner default tablespace sysaux quota unlimited on sysaux;

      grant create session to app_owner;

       

      prompt Enable editioning

      alter user data_owner enable editions;

       

      prompt Creating an initial table in ORA$BASE

      create table data_owner.test_table_base (

      id  number,

      val varchar2(1) );

       

      prompt Creating the editioning view in data_owner and granting app_owner select to this view

      create or replace editioning view data_owner.test_table as

      select id, val from data_owner.test_table_base;

      grant select on data_owner.test_table to app_owner;

       

      prompt Creating child edition db_ed_0

      create edition db_ed_0 as child of ORA$BASE;

      grant use on edition db_ed_0 to public;

       

      prompt Set session to edition db_ed_0

      alter session set edition = db_ed_0;

       

      prompt Creating a new table in data_owner

      create table data_owner.new_table_base (

      id2 number,

      val2 varchar2(1) );

       

      prompt Creating the editioning view in data_owner and granting app_owner select to this view

      create or replace editioning view data_owner.new_table as

      select id2, val2 from data_owner.new_table_base;

      grant select on data_owner.new_table to app_owner;

       

      prompt Creating and starting Service DB_ED0_SRVC, pointing to edition DB_ED_0

      exec dbms_service.create_service(service_name=>'DB_ED0_SRVC',network_name=>'DB_ED0_SRVC',edition=>'DB_ED_0');

      exec dbms_service.start_service('DB_ED0_SRVC');

       

      prompt Post Instruction:

      prompt Log into SQL Developer as app_owner/app_owner to DB_ED0_SRVC

      prompt and validate whether you can see DATA_OWNER.NEW_TABLE

      prompt within the object browser under Views.

        • 1. Re: Unable to see Editioning Views in SQL Developer
          thatJeffSmith-Oracle

          this is the query behind the VIEWS object in the Connection panel

           

          SELECT *

            FROM (

              SELECT ao.object_name,

                     ao.object_id,

                     ''                    short_name,

                     decode(ao.status, 'INVALID', 'TRUE',

                            'FALSE')       invalid,

                     ao.owner              object_owner,

                     ao.created,

                     ao.last_ddl_time,

                     av.editioning_view    editioning_view

                FROM sys.dba_objects    ao,

                     sys.dba_views      av

               WHERE ao.owner = :schema

                 AND av.owner        = :schema

                 AND ao.object_type  = :type

                 AND ao.subobject_name IS NULL

                 AND ao.object_name  = av.view_name

              UNION ALL

              SELECT object_name,

                     object_id,

                     syn.synonym_name      short_name,

                     decode(status, 'INVALID', 'TRUE',

                            'FALSE')       invalid,

                     syn.table_owner       object_owner,

                     o.created,

                     o.last_ddl_time,

                     av.editioning_view    editioning_view

                FROM sys.dba_objects      o,

                     sys.user_synonyms    syn,

                     sys.dba_views        av

               WHERE syn.table_owner = o.owner

                 AND syn.table_name = o.object_name

                 AND o.object_type  = :type

                 AND o.object_name  = av.view_name

                 AND o.owner        = av.owner

                 AND :include_syns  = 1

                 AND subobject_name IS NULL

          )

          • 2. Re: Unable to see Editioning Views in SQL Developer
            dba_chad

            Interesting.  So I wonder if SQL Developer is executing that as a privileged account somehow, although I'm not sure how.  Our should this really be the user_* or all_* dictionary tables I wonder?  My test user account does not have access to the dba_ views.  I did execute this query, with a few things commented out and added in to see what gets outputted.  Looks like the view is coming back as it should on this query.

             

            > SELECT *

              2    FROM (

              3      SELECT ao.object_name,

              4             ao.object_id,

              5             ''                    short_name,

              6             decode(ao.status, 'INVALID', 'TRUE',

              7                    'FALSE')       invalid,

              8             ao.owner              object_owner,

              9             ao.created,

            10             ao.last_ddl_time,

            11             av.editioning_view    editioning_view,

            12             ao.object_type,

            13             'A' as val

            14        FROM sys.dba_objects    ao,

            15             sys.dba_views      av

            16       WHERE ao.owner = 'DATA_OWNER'

            17         AND av.owner        = 'DATA_OWNER'

            18         --AND ao.object_type  = :type

            19         AND ao.subobject_name IS NULL

            20         AND ao.object_name  = av.view_name

            21      UNION ALL

            22      SELECT object_name,

            23             object_id,

            24             syn.synonym_name      short_name,

            25             decode(status, 'INVALID', 'TRUE',

            26                    'FALSE')       invalid,

            27             syn.table_owner       object_owner,

            28             o.created,

            29             o.last_ddl_time,

            30             av.editioning_view    editioning_view,

            31             o.object_type,

            32             'B' as val

            33        FROM sys.dba_objects      o,

            34             sys.user_synonyms    syn,

            35             sys.dba_views        av

            36       WHERE syn.table_owner = o.owner

            37         AND syn.table_name = o.object_name

            38         --AND o.object_type  = :type

            39         AND o.object_name  = av.view_name

            40         AND o.owner        = av.owner

            41         --AND :include_syns  = 1

            42         AND subobject_name IS NULL

            43  );

             

             

            OBJECT_NAM  OBJECT_ID SHORT_NAME                                                                                                                       INVAL OBJECT_OWN CREATED   LAST_DDL_ E OBJECT_TYPE             V

            ---------- ---------- -------------------------------------------------------------------------------------------------------------------------------- ----- ---------- --------- --------- - ----------------------- -

            TEST_TABLE      73554                                                                                                                                  FALSE DATA_OWNER 09-JUL-20 09-JUL-20 Y VIEW                    A

            NEW_TABLE       73558                                                                                                                                  FALSE DATA_OWNER 09-JUL-20 09-JUL-20 Y VIEW                    A

            LOADER_DIR       6171 DATAPUMP_DIR_OBJS                                                                                                                FALSE SYS        17-APR-19 17-APR-19 N VIEW                    B

            _OBJS

            • 3. Re: Unable to see Editioning Views in SQL Developer
              dba_chad

              For whatever reason, the new view does not appear in the all_views or all_views_ae dictionary view when connected as the user to the new edition.  The view DOES appear if you query all_objects however.  When I connect to the new edition and query as SYS, the new view does appear in both all_views and all_views_ae.  Not really sure why it wouldn't show when connected as the user in question.

              • 4. Re: Unable to see Editioning Views in SQL Developer
                thatJeffSmith-Oracle

                i'm logged in as 'god' so it uses the DBA_ views, otherwise it falls back to the ALL_ views

                • 5. Re: Unable to see Editioning Views in SQL Developer
                  dba_chad

                  That makes sense.  Any idea why that view wouldn't show in the ALL_VIEWS dictionary tables?