8000 Merge pull request #1056 from utPLSQL/feature/improve_ut3_user_support · utPLSQL/utPLSQL@5532191 · GitHub
[go: up one dir, main page]
Skip to content

Commit 5532191

Browse files
jgebaljgebal
authored
Merge pull request #1056 from utPLSQL/feature/improve_ut3_user_support
Improvements to privilege handling in utPLSQL
2 parents 1b00d27 + 4979cf9 commit 5532191

File tree

8 files changed

+76
-21
lines changed

8 files changed

+76
-21
lines changed

‎.travis/install.sh

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -86,7 +86,7 @@ begin
8686
and generated = 'N'
8787
and object_name not like 'SYS%')
8888
loop
89-
execute immediate 'grant execute on ut3."'||i.object_name||'" to UT3_TESTER';
89+
execute immediate 'grant execute on $UT3_OWNER."'||i.object_name||'" to $UT3_TESTER';
9090
end loop;
9191
end;
9292
/
@@ -96,7 +96,7 @@ PROMPT Granting $UT3_OWNER tables to $UT3_TESTER
9696
begin
9797
for i in ( select table_name from all_tables t where owner = 'UT3' and nested = 'NO' and iot_name is null)
9898
loop
99-
execute immediate 'grant select on UT3.'||i.table_name||' to UT3_TESTER';
99+
execute immediate 'grant select on $UT3_OWNER.'||i.table_name||' to $UT3_TESTER';
100100
end loop;
101101
end;
102102
/

‎source/check_sys_grants.sql

Lines changed: 22 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -12,18 +12,29 @@ begin
1212
end if;
1313
end loop;
1414
end if;
15+
16+
with
17+
x as (
18+
select '' as remove from dual
19+
union all
20+
select ' ANY' as remove from dual
21+
)
1522
select listagg(' - '||privilege,CHR(10)) within group(order by privilege)
16-
into l_missing_grants
17-
from (
18-
select column_value as privilege
19-
from table(l_expected_grants)
20-
minus
21-
(select privilege
22-
from user_sys_privs
23-
union all
24-
select replace(privilege,' ANY') privilege
25-
from user_sys_privs)
26-
);
23+
into l_missing_grants
24+
from (
25+
select column_value as privilege
26+
from table(l_expected_grants)
27+
minus (
28+
select replace(p.privilege, x.remove) as privilege
29+
from role_sys_privs p
30+
join session_roles r using (role)
31+
cross join x
32+
union all
33+
select replace(p.privilege, x.remove) as privilege
34+
from user_sys_privs p
35+
cross join x
36+
)
37+
);
2738
if l_missing_grants is not null then
2839
raise_application_error(
2940
-20000

‎source/core/annotations/ut_annotation_manager.pkb

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ create or replace package body ut_annotation_manager as
2121

2222
function user_can_see_whole_schema( a_schema_name varchar2 ) return boolean is
2323
begin
24-
return sys_context('userenv','current_schema') = a_schema_name
24+
return sys_context('userenv','current_user') = a_schema_name
2525
or ut_metadata.user_has_execute_any_proc()
2626
or ut_metadata.is_object_visible('dba_objects');
2727
end;

‎source/core/ut_metadata.pkb

Lines changed: 19 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -121,9 +121,26 @@ create or replace package body ut_metadata as
121121
end;
122122

123123
function user_has_execute_any_proc return boolean is
124-
l_ut_owner varchar2(250) := ut_utils.ut_owner;
124+
l_has_execute_any varchar2(1);
125125
begin
126-
return is_object_visible(l_ut_owner||'.ut_utils') and sys_context('userenv','current_schema') != l_ut_owner;
126+
select decode( count( 1 ), 0, 'N', 'Y' )
127+
into l_has_execute_any
128+
from dual
129+
where
130+
exists(
131+
select 1
132+
from
133+
role_sys_privs
134+
join session_roles
135+
using ( role )
136+
where privilege = 'EXECUTE ANY PROCEDURE'
137+
) or
138+
exists(
139+
select 1
140+
from user_sys_privs
141+
where privilege = 'EXECUTE ANY PROCEDURE'
142+
);
143+
return l_has_execute_any = 'Y';
127144
end;
128145

129146
function is_object_visible(a_object_name varchar2) return boolean is

‎source/core/ut_suite_manager.pkb

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -374,7 +374,7 @@ create or replace package body ut_suite_manager is
374374
a_owner_name varchar2
375375
) return boolean is
376376
begin
377-
return sys_context( 'userenv', 'current_schema' ) = a_owner_name or ut_metadata.user_has_execute_any_proc();
377+
return sys_context( 'userenv', 'current_user' ) = a_owner_name or ut_metadata.user_has_execute_any_proc();
378378
end;
379379

380380
procedure build_and_cache_suites(

‎test/ut3_tester/core/annotations/test_annotation_cache.pkb

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,19 @@ create or replace package body test_annotation_cache is
1414
ut.expect( l_actual_cache_info ).to_equal( l_expected_cache_info ).exclude( 'CACHE_ID,PARSE_TIME,OBJECT_TYPE' ).JOIN_BY('OBJECT_NAME');
1515
end;
1616

17+
procedure cant_run_any_packages(a_user varchar2) is
18+
l_actual clob;
19+
l_current_time date := sysdate;
20+
pragma autonomous_transaction;
21+
begin
22+
--Act
23+
l_actual := annotation_cache_helper.run_tests_as( a_user );
24+
25+
--Assert - no suites are
26+
ut.expect( l_actual ).to_be_like( '%0 tests, 0 failed%' );
27+
rollback;
28+
end;
29+
1730
procedure can_run_one_package(a_user varchar2) is
1831
l_actual clob;
1932
l_current_time date := sysdate;
@@ -380,6 +393,11 @@ create or replace package body test_annotation_cache is
380393
cache_populated_for_packages( ut_varchar2_rows( 'GRANTED_TEST_SUITE', 'NOT_GRANTED_TEST_SUITE' ) );
381394
end;
382395

396+
procedure t_ut_owner_cannot_run_tests is
397+
begin
398+
cant_run_any_packages( 'ut3' );
399+
cache_populated_for_packages( ut_varchar2_rows( 'GRANTED_TEST_SUITE', 'NOT_GRANTED_TEST_SUITE' ) );
400+
end;
383401

384402

385403

‎test/ut3_tester/core/annotations/test_annotation_cache.pks

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -89,6 +89,13 @@ create or replace package test_annotation_cache is
8989

9090
--%endcontext
9191

92+
--%context(utPLSQL framework owner)
93+
94+
--%test(Cannot see any tests and doesn't impact annotation cache )
95+
procedure t_ut_owner_cannot_run_tests;
96+
97+
--%endcontext
98+
9299
--%endcontext
93100

94101
--%context(With DDL trigger disabled)

‎test/ut3_tester_helper/annotation_cache_helper.pkb

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -122,21 +122,21 @@ create or replace package body annotation_cache_helper as
122122
pragma autonomous_transaction;
123123
begin
124124
execute immediate
125-
'create or replace function ' || a_user || '.ut_run return clob is
125+
'create or replace function ' || a_user || '.call_ut_run return clob is
126126
l_data ut3.ut_varchar2_list;
127127
l_results clob;
128128
begin
129129
select * bulk collect into l_data from table (ut3.ut.run( ''ut3_cache_test_owner'' ));
130130
return ut3_tester_helper.main_helper.table_to_clob( l_data );
131131
end;
132132
';
133-
execute immediate 'grant execute on ' || a_user || '.ut_run to public ';
133+
execute immediate 'grant execute on ' || a_user || '.call_ut_run to public ';
134134
end;
135135

136136
procedure drop_run_function_for_user(a_user varchar2) is
137137
pragma autonomous_transaction;
138138
begin
139-
execute immediate 'drop function ' || a_user || '.ut_run';
139+
execute immediate 'drop function ' || a_user || '.call_ut_run';
140140
end;
141141

142142
procedure create_run_function_for_users is
@@ -146,6 +146,7 @@ create or replace package body annotation_cache_helper as
146146
create_run_function_for_user( 'ut3_select_any_table_user' );
147147
create_run_function_for_user( 'ut3_execute_any_proc_user' );
148148
create_run_function_for_user( 'ut3_cache_test_owner' );
149+
create_run_function_for_user( 'ut3' );
149150
end;
150151

151152
procedure drop_run_function_for_users is
@@ -155,12 +156,13 @@ create or replace package body annotation_cache_helper as
155156
drop_run_function_for_user( 'ut3_select_any_table_user' );
156157
drop_run_function_for_user( 'ut3_execute_any_proc_user' );
157158
drop_run_function_for_user( 'ut3_cache_test_owner' );
159+
drop_run_function_for_user( 'ut3' );
158160
end;
159161

160162
function run_tests_as(a_user varchar2) return clob is
161163
l_results clob;
162164
begin
163-
execute immediate 'begin :x := '||a_user||'.ut_run; end;' using out l_results;
165+
execute immediate 'begin :x := '||a_user||'.call_ut_run; end;' using out l_results;
164166
return l_results;
165167
end;
166168
end;

0 commit comments

Comments
 (0)
0