Removed grants:

- grant select, insert, update, delete on ut_compound_data_tmp - grant select, insert, update, delete on ut_compound_data_diff_tmp
utPLSQL · jgebal · Jun 28, 2019 · Jun 22, 2019 · Jun 23, 2019 · Jun 23, 2019
commit 41c007c32eb763f5a0643bcf8183f616aa21e8c7
diff --git a/source/create_grants.sql b/source/create_grants.sql
@@ -115,11 +115,7 @@ grant execute on &&ut3_owner..ut_output_reporter_base to &ut3_user;
 --outputs
 grant execute on &&ut3_owner..ut_output_buffer_base to &ut3_user;
 
---user temp tables
-grant select, insert, update, delete on &&ut3_owner..ut_compound_data_tmp to &ut3_user;
-grant select, insert, update, delete on &&ut3_owner..ut_compound_data_diff_tmp to &ut3_user;
-
---needed for selecting from annotation objects
+--needed internally for selecting from annotation objects within packages that use invoker rights
 grant execute on &&ut3_owner..ut_annotation_objs_cache_info to &ut3_user;
 grant execute on &&ut3_owner..ut_annotation_obj_cache_info to &ut3_user;
 

diff --git a/source/expectations/data_values/ut_compound_data_helper.pkb b/source/expectations/data_values/ut_compound_data_helper.pkb
@@ -35,7 +35,7 @@ create or replace package body ut_compound_data_helper is
           ,x.data_id data_id 
           ,position + x.item_no item_no
           {:columns:}
-        from {:ut3_owner:}.ut_compound_data_tmp x,
+        from ut_compound_data_tmp x,
           xmltable('/ROWSET/ROW' passing x.item_data columns
             item_data xmltype path '*'
             ,position for ordinality
@@ -53,7 +53,7 @@ create or replace package body ut_compound_data_helper is
           ,x.data_id data_id
           ,position + x.item_no item_no 
           {:columns:}
-        from {:ut3_owner:}.ut_compound_data_tmp x,
+        from ut_compound_data_tmp x,
           xmltable('/ROWSET/ROW' passing x.item_data columns 
             item_data xmltype path '*'
             ,position for ordinality
@@ -207,10 +207,9 @@ create or replace package body ut_compound_data_helper is
   is
     l_alias varchar2(10) := a_alias;
     l_col_syntax varchar2(4000);
-    l_ut_owner varchar2(250) := ut_utils.ut_owner;
-  begin    
+  begin
     if a_data_info.is_sql_diffable = 0 then 
-      l_col_syntax :=  l_ut_owner ||'.ut_utils.get_hash('||l_alias||a_data_info.transformed_name||'.getClobVal()) as '||a_data_info.transformed_name ;
+      l_col_syntax :=  'ut_utils.get_hash('||l_alias||a_data_info.transformed_name||'.getClobVal()) as '||a_data_info.transformed_name ;
     elsif a_data_info.is_sql_diffable = 1  and a_data_info.column_type = 'DATE' then
       l_col_syntax :=  'to_date('||l_alias||a_data_info.transformed_name||') as '|| a_data_info.transformed_name;
     elsif  a_data_info.is_sql_diffable = 1  and a_data_info.column_type in ('TIMESTAMP') then
@@ -334,7 +333,6 @@ create or replace package body ut_compound_data_helper is
     l_join_on_stmt   clob;
     l_not_equal_stmt clob;
     l_where_stmt     clob;
-    l_ut_owner       varchar2(250) := ut_utils.ut_owner;
     l_join_by_list   ut_varchar2_list;
 
     function get_join_type(a_inclusion_compare in boolean,a_negated in boolean) return varchar2 is
@@ -379,8 +377,7 @@ create or replace package body ut_compound_data_helper is
 
     l_compare_sql := replace(l_compare_sql,'{:duplicate_number:}',l_partition_stmt);
     l_compare_sql := replace(l_compare_sql,'{:columns:}',l_select_stmt);
-    l_compare_sql := replace(l_compare_sql,'{:ut3_owner:}',l_ut_owner);
-    l_compare_sql := replace(l_compare_sql,'{:xml_to_columns:}',l_xmltable_stmt); 
+    l_compare_sql := replace(l_compare_sql,'{:xml_to_columns:}',l_xmltable_stmt);
     l_compare_sql := replace(l_compare_sql,'{:item_no:}',get_item_no(a_unordered));
     l_compare_sql := replace(l_compare_sql,'{:join_type:}',get_join_type(a_inclusion_type,a_is_negated));
     l_compare_sql := replace(l_compare_sql,'{:join_condition:}',l_join_on_stmt);
@@ -613,8 +610,28 @@ create or replace package body ut_compound_data_helper is
       ut_utils.remove_error_from_stack(sqlerrm,ut_utils.gc_xml_processing)||chr(10)||
       ut_expectation_processor.who_called_expectation(a_error_stack)||
       'Check the query and data for errors.';   
-  end; 
-
+  end;
+
+  procedure save_cursor_data_for_diff(a_data_id raw, a_set_id integer, a_xml xmltype) is
+  begin
+    insert into ut_compound_data_tmp (data_id, item_no, item_data) values (a_data_id, a_set_id, a_xml);
+  end;
+
+  function get_row_data_as_xml(a_data_id raw, a_max_rows integer) return ut_utils.t_clob_tab is
+    l_results       ut_utils.t_clob_tab;
+  begin
+    select xmlserialize( content ucd.item_data no indent)
+      bulk collect into l_results
+      from ut_compound_data_tmp tmp
+        ,xmltable ( '/ROWSET' passing tmp.item_data
+           columns item_data xmltype PATH '*'
+         ) ucd
+     where tmp.data_id = a_data_id
+           and rownum <= a_max_rows;
+
+    return l_results;
+  end;
+
   function type_no_length ( a_type_name varchar2) return boolean is
   begin
     return case 

diff --git a/source/expectations/data_values/ut_compound_data_helper.pks b/source/expectations/data_values/ut_compound_data_helper.pks
@@ -115,6 +115,10 @@ create or replace package ut_compound_data_helper authid definer is
   function get_compare_cursor(a_diff_cursor_text in clob,a_self_id raw, a_other_id raw) return sys_refcursor;
 
   function create_err_cursor_msg(a_error_stack varchar2) return varchar2;
+
+  procedure save_cursor_data_for_diff(a_data_id raw, a_set_id integer, a_xml xmltype);
+
+  function get_row_data_as_xml(a_data_id raw, a_max_rows integer) return ut_utils.t_clob_tab;
 
   /*
   * Function to return true or false if the type dont have an length

diff --git a/source/expectations/data_values/ut_compound_data_value.tpb b/source/expectations/data_values/ut_compound_data_value.tpb
@@ -44,18 +44,10 @@ create or replace type body ut_compound_data_value as
     if not self.is_null() then
       dbms_lob.createtemporary(l_result, true);
       ut_utils.append_to_clob(l_result,'Data:'||chr(10));
-      --return first c_max_rows rows
-      execute immediate '
-          select xmlserialize( content ucd.item_data no indent)
-            from '|| ut_utils.ut_owner ||q'[.ut_compound_data_tmp tmp
-            ,xmltable ( '/ROWSET' passing tmp.item_data
-            columns item_data xmltype PATH '*'         
-            ) ucd
-           where tmp.data_id = :data_id
-             and rownum <= :max_rows]'
-        bulk collect into l_results using self.data_id, ut_utils.gc_diff_max_rows;
-
-      ut_utils.append_to_clob(l_result,l_results);
+      ut_utils.append_to_clob(
+        l_result,
+        ut_compound_data_helper.get_row_data_as_xml( self.data_id, ut_utils.gc_diff_max_rows )
+      );
 
       l_result_string := ut_utils.to_string(l_result,null);
       dbms_lob.freetemporary(l_result);

diff --git a/source/expectations/data_values/ut_data_value_refcursor.tpb b/source/expectations/data_values/ut_data_value_refcursor.tpb
@@ -58,10 +58,7 @@ create or replace type body ut_data_value_refcursor as
         null;
       $end
       l_elements_count := l_elements_count + dbms_xmlgen.getNumRowsProcessed(l_ctx);
-      execute immediate
-      'insert into ' || l_ut_owner || '.ut_compound_data_tmp(data_id, item_no, item_data) ' ||
-      'values (:self_guid, :self_row_count, :l_xml)'
-      using in self.data_id, l_set_id, l_xml;       
+      ut_compound_data_helper.save_cursor_data_for_diff( self.data_id, l_set_id, l_xml );
       l_set_id := l_set_id + c_bulk_rows;   
     end loop;