Merge pull request #1056 from utPLSQL/feature/improve_ut3_user_support

jgebal · web-flow · commit 5532191fb2e7 · 2020-03-14T17:03:14.000Z
Improvements to privilege handling in utPLSQL
diff --git a/.travis/install.sh b/.travis/install.sh
@@ -86,7 +86,7 @@ begin
       and generated = 'N'
       and object_name not like 'SYS%')
   loop
-    execute immediate 'grant execute on ut3."'||i.object_name||'" to UT3_TESTER';
+    execute immediate 'grant execute on $UT3_OWNER."'||i.object_name||'" to $UT3_TESTER';
   end loop;
 end;
 /
@@ -96,7 +96,7 @@ PROMPT Granting $UT3_OWNER tables to $UT3_TESTER
 begin
   for i in ( select table_name from all_tables t where  owner = 'UT3' and nested = 'NO' and iot_name is null)
   loop
-    execute immediate 'grant select on UT3.'||i.table_name||' to UT3_TESTER';
+    execute immediate 'grant select on $UT3_OWNER.'||i.table_name||' to $UT3_TESTER';
   end loop;
 end;
 /
diff --git a/source/check_sys_grants.sql b/source/check_sys_grants.sql
@@ -12,18 +12,29 @@ begin
       end if;
     end loop;
   end if;
+
+  with
+    x as (
+         select '' as remove from dual
+         union all
+         select ' ANY' as remove  from dual
+    )
   select listagg(' -  '||privilege,CHR(10)) within group(order by privilege)
-  into l_missing_grants
-  from (
-    select column_value as privilege
-    from table(l_expected_grants)
-    minus
-    (select privilege
-    from user_sys_privs
-    union all
-    select replace(privilege,' ANY') privilege
-    from user_sys_privs)
-  );
+    into l_missing_grants
+    from (
+      select column_value as privilege
+        from table(l_expected_grants)
+      minus (
+      select replace(p.privilege, x.remove) as privilege
+        from role_sys_privs p
+        join session_roles r using (role)
+        cross join  x
+      union all
+      select replace(p.privilege, x.remove) as privilege
+        from user_sys_privs p
+        cross join  x
+       )
+    );
   if l_missing_grants is not null then
     raise_application_error(
         -20000
diff --git a/source/core/annotations/ut_annotation_manager.pkb b/source/core/annotations/ut_annotation_manager.pkb
@@ -21,7 +21,7 @@ create or replace package body ut_annotation_manager as
 
   function user_can_see_whole_schema( a_schema_name varchar2 ) return boolean is
   begin
-    return sys_context('userenv','current_schema') = a_schema_name
+    return sys_context('userenv','current_user') = a_schema_name
       or ut_metadata.user_has_execute_any_proc()
       or ut_metadata.is_object_visible('dba_objects');
   end;
diff --git a/source/core/ut_metadata.pkb b/source/core/ut_metadata.pkb
@@ -121,9 +121,26 @@ create or replace package body ut_metadata as
   end;
 
   function user_has_execute_any_proc return boolean is
-    l_ut_owner     varchar2(250) := ut_utils.ut_owner;
+    l_has_execute_any varchar2(1);
   begin
-    return is_object_visible(l_ut_owner||'.ut_utils') and sys_context('userenv','current_schema') != l_ut_owner;
+    select decode( count( 1 ), 0, 'N', 'Y' )
+      into l_has_execute_any
+      from dual
+      where
+        exists(
+          select 1
+            from
+              role_sys_privs
+              join session_roles
+              using ( role )
+            where privilege = 'EXECUTE ANY PROCEDURE'
+          ) or
+        exists(
+          select 1
+            from user_sys_privs
+            where privilege = 'EXECUTE ANY PROCEDURE'
+          );
+    return l_has_execute_any = 'Y';
   end;
 
   function is_object_visible(a_object_name varchar2) return boolean is
diff --git a/source/core/ut_suite_manager.pkb b/source/core/ut_suite_manager.pkb
@@ -374,7 +374,7 @@ create or replace package body ut_suite_manager is
     a_owner_name         varchar2
   ) return boolean is
   begin
-    return sys_context( 'userenv', 'current_schema' ) = a_owner_name or ut_metadata.user_has_execute_any_proc();
+    return sys_context( 'userenv', 'current_user' ) = a_owner_name or ut_metadata.user_has_execute_any_proc();
   end;
 
   procedure build_and_cache_suites(
diff --git a/test/ut3_tester/core/annotations/test_annotation_cache.pkb b/test/ut3_tester/core/annotations/test_annotation_cache.pkb
@@ -14,6 +14,19 @@ create or replace package body test_annotation_cache is
     ut.expect( l_actual_cache_info ).to_equal( l_expected_cache_info ).exclude( 'CACHE_ID,PARSE_TIME,OBJECT_TYPE' ).JOIN_BY('OBJECT_NAME');
   end;
 
+  procedure cant_run_any_packages(a_user varchar2) is
+    l_actual       clob;
+    l_current_time date := sysdate;
+    pragma autonomous_transaction;
+  begin
+    --Act
+    l_actual := annotation_cache_helper.run_tests_as( a_user );
+
+    --Assert - no suites are
+    ut.expect( l_actual ).to_be_like( '%0 tests, 0 failed%' );
+    rollback;
+  end;
+
   procedure can_run_one_package(a_user varchar2) is
     l_actual       clob;
     l_current_time date := sysdate;
@@ -380,6 +393,11 @@ create or replace package body test_annotation_cache is
     cache_populated_for_packages( ut_varchar2_rows( 'GRANTED_TEST_SUITE', 'NOT_GRANTED_TEST_SUITE' ) );
   end;
 
+  procedure t_ut_owner_cannot_run_tests is
+  begin
+    cant_run_any_packages( 'ut3' );
+    cache_populated_for_packages( ut_varchar2_rows( 'GRANTED_TEST_SUITE', 'NOT_GRANTED_TEST_SUITE' ) );
+  end;
 
 
 
diff --git a/test/ut3_tester/core/annotations/test_annotation_cache.pks b/test/ut3_tester/core/annotations/test_annotation_cache.pks
@@ -89,6 +89,13 @@ create or replace package test_annotation_cache is
 
     --%endcontext
 
+    --%context(utPLSQL framework owner)
+
+      --%test(Cannot see any tests and doesn't impact annotation cache )
+      procedure t_ut_owner_cannot_run_tests;
+
+    --%endcontext
+
   --%endcontext
 
   --%context(With DDL trigger disabled)
diff --git a/test/ut3_tester_helper/annotation_cache_helper.pkb b/test/ut3_tester_helper/annotation_cache_helper.pkb
@@ -122,21 +122,21 @@ create or replace package body annotation_cache_helper as
     pragma autonomous_transaction;
   begin
     execute immediate
-      'create or replace function ' || a_user || '.ut_run return clob is
+      'create or replace function ' || a_user || '.call_ut_run return clob is
         l_data    ut3.ut_varchar2_list;
         l_results clob;
       begin
         select * bulk collect into l_data from table (ut3.ut.run( ''ut3_cache_test_owner'' ));
         return ut3_tester_helper.main_helper.table_to_clob( l_data );
       end;
       ';
-    execute immediate 'grant execute on ' || a_user || '.ut_run to public ';
+    execute immediate 'grant execute on ' || a_user || '.call_ut_run to public ';
   end;
 
   procedure drop_run_function_for_user(a_user varchar2) is
     pragma autonomous_transaction;
   begin
-    execute immediate 'drop function ' || a_user || '.ut_run';
+    execute immediate 'drop function ' || a_user || '.call_ut_run';
   end;
 
   procedure create_run_function_for_users is
@@ -146,6 +146,7 @@ create or replace package body annotation_cache_helper as
     create_run_function_for_user( 'ut3_select_any_table_user' );
     create_run_function_for_user( 'ut3_execute_any_proc_user' );
     create_run_function_for_user( 'ut3_cache_test_owner' );
+    create_run_function_for_user( 'ut3' );
   end;
 
   procedure drop_run_function_for_users is
@@ -155,12 +156,13 @@ create or replace package body annotation_cache_helper as
     drop_run_function_for_user( 'ut3_select_any_table_user' );
     drop_run_function_for_user( 'ut3_execute_any_proc_user' );
     drop_run_function_for_user( 'ut3_cache_test_owner' );
+    drop_run_function_for_user( 'ut3' );
   end;
 
   function run_tests_as(a_user varchar2) return clob is
     l_results clob;
   begin
-    execute immediate 'begin :x := '||a_user||'.ut_run; end;' using out l_results;
+    execute immediate 'begin :x := '||a_user||'.call_ut_run; end;' using out l_results;
     return l_results;
   end;
 end;
