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

Commit f3f2b12

Browse files
jgebaljgebal
authored
Merge pull request #954 from utPLSQL/feature/grants_cleanup
Cleanup grants of internal objects & tables
2 parents 7446819 + 6f0a939 commit f3f2b12

File tree

61 files changed

+1216
-1526
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

61 files changed

+1216
-1526
lines changed

‎.travis/install.sh‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -98,7 +98,7 @@ end;
9898
PROMPT Granting $UT3_OWNER tables to $UT3_TESTER
9999
100100
begin
101-
for i in ( select table_name from all_tables t where owner = 'UT3' and nested = 'NO' and IOT_TYPE is NULL)
101+
for i in ( select table_name from all_tables t where owner = 'UT3' and nested = 'NO' and IOT_NAME is NULL)
102102
loop
103103
execute immediate 'grant select on UT3.'||i.table_name||' to UT3_TESTER';
104104
end loop;

‎source/api/ut_runner.pkb‎

Lines changed: 3 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -44,14 +44,14 @@ create or replace package body ut_runner is
4444

4545
procedure finish_run(a_run ut_run, a_force_manual_rollback boolean) is
4646
begin
47-
ut_utils.cleanup_temp_tables;
4847
ut_event_manager.trigger_event(ut_event_manager.gc_finalize, a_run);
4948
ut_metadata.reset_source_definition_cache;
5049
ut_utils.read_cache_to_dbms_output();
5150
ut_coverage_helper.cleanup_tmp_table();
5251
ut_compound_data_helper.cleanup_diff();
5352
if not a_force_manual_rollback then
5453
rollback;
54+
ut_utils.cleanup_session_temp_tables;
5555
end if;
5656
end;
5757

@@ -196,7 +196,7 @@ create or replace package body ut_runner is
196196
function get_suites_info(a_owner varchar2 := null, a_package_name varchar2 := null) return ut_suite_items_info pipelined is
197197
l_cursor sys_refcursor;
198198
l_results ut_suite_items_info;
199-
c_bulk_limit constant integer := 10;
199+
c_bulk_limit constant integer := 100;
200200
begin
201201
l_cursor := ut_suite_manager.get_suites_info( nvl(a_owner,sys_context('userenv', 'current_schema')), a_package_name );
202202
loop
@@ -285,10 +285,7 @@ create or replace package body ut_runner is
285285
if l_item is not null then
286286
l_result :=
287287
l_result ||
288-
dbms_crypto.hash(
289-
to_char( dbms_utility.get_hash_value( l_item, 1, a_random_seed ) ),
290-
dbms_crypto.hash_sh1
291-
);
288+
ut_utils.get_hash( to_char( dbms_utility.get_hash_value( l_item, 1, a_random_seed ) ) );
292289
end if;
293290
exit when l_at_end;
294291
l_result := l_result || chr(0);

‎source/api/ut_suite_item_info.tps‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@ create or replace type ut_suite_item_info as object (
1818
object_owner varchar2( 250 ), -- the owner of test suite packages
1919
object_name varchar2( 250 ), -- the name of test suite package
2020
item_name varchar2( 250 ), -- the name of suite/test
21-
item_description varchar2( 250 ), -- the description of suite/suite item
21+
item_description varchar2( 4000 ), -- the description of suite/suite item
2222
item_type varchar2( 250 ), -- the type of item (UT_SUITE/UT_SUITE_CONTEXT/UT_TEST)
2323
item_line_no integer, -- line_number where annotation identifying the item exists
2424
path varchar2( 4000 ),-- suitepath of the item

‎source/core/annotations/ut_annotation_cache_manager.pkb‎

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -91,12 +91,29 @@ create or replace package body ut_annotation_cache_manager as
9191
commit;
9292
end;
9393

94+
function get_annotations_objects_info(a_object_owner varchar2, a_object_type varchar2) return ut_annotation_objs_cache_info is
95+
l_result ut_annotation_objs_cache_info;
96+
begin
97+
select ut_annotation_obj_cache_info(
98+
object_owner => i.object_owner,
99+
object_name => i.object_name,
100+
object_type => i.object_type,
101+
needs_refresh => 'N',
102+
parse_time => i.parse_time
103+
)
104+
bulk collect into l_result
105+
from ut_annotation_cache_info i
106+
where i.object_owner = a_object_owner
107+
and i.object_type = a_object_type;
108+
return l_result;
109+
end;
110+
94111
function get_cache_schema_info(a_object_owner varchar2, a_object_type varchar2) return t_cache_schema_info is
95112
l_result t_cache_schema_info;
96113
begin
97114
begin
98115
select *
99-
into l_result
116+
into l_result
100117
from ut_annotation_cache_schema s
101118
where s.object_type = a_object_type and s.object_owner = a_object_owner;
102119
exception

‎source/core/annotations/ut_annotation_cache_manager.pks‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -34,6 +34,9 @@ create or replace package ut_annotation_cache_manager authid definer as
3434
*/
3535
function get_annotations_for_objects(a_cached_objects ut_annotation_objs_cache_info, a_parse_time timestamp) return sys_refcursor;
3636

37+
38+
function get_annotations_objects_info(a_object_owner varchar2, a_object_type varchar2) return ut_annotation_objs_cache_info;
39+
3740
function get_cache_schema_info(a_object_owner varchar2, a_object_type varchar2) return t_cache_schema_info;
3841

3942
/**

‎source/core/annotations/ut_annotation_manager.pkb‎

Lines changed: 43 additions & 79 deletions
Original file line numberDiff line numberDiff line change
@@ -24,29 +24,28 @@ create or replace package body ut_annotation_manager as
2424
l_ut_owner varchar2(250) := ut_utils.ut_owner;
2525
l_objects_view varchar2(200) := ut_metadata.get_objects_view_name();
2626
l_cursor_text varchar2(32767);
27+
l_data ut_annotation_objs_cache_info;
2728
l_result ut_annotation_objs_cache_info;
29+
l_card natural;
2830
begin
31+
l_data := ut_annotation_cache_manager.get_annotations_objects_info(a_object_owner, a_object_type);
32+
l_card := ut_utils.scale_cardinality(cardinality(l_data));
33+
2934
l_cursor_text :=
30-
q'[select ]'||l_ut_owner||q'[.ut_annotation_obj_cache_info(
31-
object_owner => i.object_owner,
32-
object_name => i.object_name,
33-
object_type => i.object_type,
34-
needs_refresh => null
35-
)
36-
from ]'||l_ut_owner||q'[.ut_annotation_cache_info i
35+
'select /*+ cardinality(i '||l_card||') */
36+
value(i)
37+
from table( cast( :l_data as '||l_ut_owner||'.ut_annotation_objs_cache_info ) ) i
3738
where
3839
not exists (
39-
select 1 from ]'||l_objects_view||q'[ o
40+
select 1 from '||l_objects_view||q'[ o
4041
where o.owner = i.object_owner
4142
and o.object_name = i.object_name
4243
and o.object_type = i.object_type
43-
and o.owner = :a_object_owner
44-
and o.object_type = :a_object_type
45-
)
46-
and i.object_owner = :a_object_owner
47-
and i.object_type = :a_object_type]';
48-
open l_rows for l_cursor_text using a_object_owner, a_object_type, a_object_owner, a_object_type;
49-
fetch l_rows bulk collect into l_result limit 1000000;
44+
and o.owner = ']'||ut_utils.qualified_sql_name(a_object_owner)||q'['
45+
and o.object_type = ']'||ut_utils.qualified_sql_name(a_object_type)||q'['
46+
)]';
47+
open l_rows for l_cursor_text using l_data;
48+
fetch l_rows bulk collect into l_result limit ut_utils.gc_max_objects_fetch_limit;
5049
close l_rows;
5150
return l_result;
5251
end;
@@ -61,83 +60,48 @@ create or replace package body ut_annotation_manager as
6160
l_ut_owner varchar2(250) := ut_utils.ut_owner;
6261
l_objects_view varchar2(200) := ut_metadata.get_objects_view_name();
6362
l_cursor_text varchar2(32767);
63+
l_data ut_annotation_objs_cache_info;
6464
l_result ut_annotation_objs_cache_info;
65-
l_object_owner varchar2(250);
66-
l_object_type varchar2(250);
6765
begin
6866
ut_event_manager.trigger_event(
6967
'get_annotation_objs_info - start ( a_full_scan = ' || ut_utils.to_string(a_full_scan) || ' )'
7068
);
69+
70+
l_data := ut_annotation_cache_manager.get_annotations_objects_info(a_object_owner, a_object_type);
71+
7172
if not a_full_scan then
72-
l_cursor_text :=
73-
q'[select ]'||l_ut_owner||q'[.ut_annotation_obj_cache_info(
74-
object_owner => i.object_owner,
75-
object_name => i.object_name,
76-
object_type => i.object_type,
77-
needs_refresh => 'N'
78-
)
79-
from ]'||l_ut_owner||q'[.ut_annotation_cache_info i
80-
where i.object_owner = :a_object_owner
81-
and i.object_type = :a_object_type]';
82-
open l_rows for l_cursor_text using a_object_owner, a_object_type;
73+
l_result := l_data;
8374
else
84-
if a_object_owner is not null then
85-
l_object_owner := sys.dbms_assert.qualified_sql_name(a_object_owner);
86-
end if;
87-
if a_object_type is not null then
88-
l_object_type := sys.dbms_assert.qualified_sql_name(a_object_type);
89-
end if;
9075
l_cursor_text :=
91-
q'[select ]'||l_ut_owner||q'[.ut_annotation_obj_cache_info(
92-
object_owner => o.owner,
93-
object_name => o.object_name,
94-
object_type => o.object_type,
95-
needs_refresh => case when o.last_ddl_time < cast(i.parse_time as date) then 'N' else 'Y' end
96-
)
97-
from ]'||l_objects_view||q'[ o
98-
left join ]'||l_ut_owner||q'[.ut_annotation_cache_info i
76+
'select /*+ cardinality(i '||ut_utils.scale_cardinality(cardinality(l_data))||') */
77+
'||l_ut_owner||q'[.ut_annotation_obj_cache_info(
78+
object_owner => o.owner,
79+
object_name => o.object_name,
80+
object_type => o.object_type,
81+
needs_refresh => case when o.last_ddl_time < cast(i.parse_time as date) then 'N' else 'Y' end,
82+
parse_time => i.parse_time
83+
)
84+
from ]'||l_objects_view||' o
85+
left join table( cast(:l_data as '||l_ut_owner||q'[.ut_annotation_objs_cache_info ) ) i
9986
on o.owner = i.object_owner
10087
and o.object_name = i.object_name
10188
and o.object_type = i.object_type
102-
where o.owner = ']'||l_object_owner||q'['
103-
and o.object_type = ']'||l_object_type||q'['
89+
where o.owner = ']'||ut_utils.qualified_sql_name(a_object_owner)||q'['
90+
and o.object_type = ']'||ut_utils.qualified_sql_name(a_object_type)||q'['
10491
and ]'
10592
|| case
10693
when a_parse_date is null
10794
then ':a_parse_date is null'
10895
else 'o.last_ddl_time >= cast(:a_parse_date as date)'
10996
end;
110-
open l_rows for l_cursor_text using a_parse_date;
97+
open l_rows for l_cursor_text using l_data, a_parse_date;
98+
fetch l_rows bulk collect into l_result limit ut_utils.gc_max_objects_fetch_limit;
99+
close l_rows;
111100
end if;
112-
fetch l_rows bulk collect into l_result limit 10000000;
113-
close l_rows;
114101
ut_event_manager.trigger_event('get_annotation_objs_info - end (count='||l_result.count||')');
115102
return l_result;
116103
end;
117104

118-
function get_sources_to_annotate(a_object_owner varchar2, a_object_type varchar2) return sys_refcursor is
119-
l_result sys_refcursor;
120-
l_sources_view varchar2(200) := ut_metadata.get_source_view_name();
121-
begin
122-
open l_result for
123-
q'[select s.name, s.text
124-
from (select s.name, s.text, s.line,
125-
max(case when s.text like '%--%\%%' escape '\'
126-
and regexp_like(s.text,'--\s*%')
127-
then 'Y' else 'N' end
128-
)
129-
over(partition by s.name) is_annotated
130-
from ]'||l_sources_view||q'[ s
131-
where s.type = :a_object_type
132-
and s.owner = :a_object_owner
133-
) s
134-
where s.is_annotated = 'Y'
135-
order by s.name, s.line]'
136-
using a_object_type, a_object_owner, a_object_type, a_object_owner;
137-
138-
return l_result;
139-
end;
140-
141105
function get_sources_to_annotate(a_object_owner varchar2, a_object_type varchar2, a_objects_to_refresh ut_annotation_objs_cache_info) return sys_refcursor is
142106
l_result sys_refcursor;
143107
l_sources_view varchar2(200) := ut_metadata.get_source_view_name();
@@ -158,12 +122,12 @@ create or replace package body ut_annotation_manager as
158122
on s.name = r.object_name
159123
and s.owner = r.object_owner
160124
and s.type = r.object_type
161-
where s.type = :a_object_type
162-
and s.owner = :a_object_owner
125+
where s.owner = ']'||ut_utils.qualified_sql_name(a_object_owner)||q'['
126+
and s.type = ']'||ut_utils.qualified_sql_name(a_object_type)||q'['
163127
) s
164128
where s.is_annotated = 'Y'
165129
order by s.name, s.line]'
166-
using a_objects_to_refresh, a_object_type, a_object_owner;
130+
using a_objects_to_refresh;
167131

168132
return l_result;
169133
end;
@@ -207,7 +171,6 @@ create or replace package body ut_annotation_manager as
207171
l_object_lines.delete;
208172
end if;
209173
close a_sources_cursor;
210-
commit;
211174
end;
212175

213176

@@ -257,7 +220,6 @@ create or replace package body ut_annotation_manager as
257220
procedure trigger_obj_annotation_rebuild is
258221
l_sql_text ora_name_list_t;
259222
l_parts binary_integer;
260-
l_object_to_parse ut_annotation_obj_cache_info;
261223
l_restricted_users ora_name_list_t;
262224

263225
function get_source_from_sql_text(a_object_name varchar2, a_sql_text ora_name_list_t, a_parts binary_integer) return sys_refcursor is
@@ -312,8 +274,6 @@ create or replace package body ut_annotation_manager as
312274
return;
313275
end if;
314276

315-
l_object_to_parse := ut_annotation_obj_cache_info(ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, 'Y');
316-
317277
if ora_sysevent = 'CREATE' then
318278
l_parts := ORA_SQL_TXT(l_sql_text);
319279
build_annot_cache_for_sources(
@@ -324,9 +284,13 @@ create or replace package body ut_annotation_manager as
324284
build_annot_cache_for_sources(
325285
ora_dict_obj_owner, ora_dict_obj_type,
326286
get_source_for_object(ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type)
327-
);
287+
);
328288
elsif ora_sysevent = 'DROP' then
329-
ut_annotation_cache_manager.remove_from_cache(ut_annotation_objs_cache_info(l_object_to_parse));
289+
ut_annotation_cache_manager.remove_from_cache(
290+
ut_annotation_objs_cache_info(
291+
ut_annotation_obj_cache_info(ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, 'Y', null)
292+
)
293+
);
330294
end if;
331295
end if;
332296
end;

‎source/core/annotations/ut_annotation_obj_cache_info.tps‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,7 @@ create type ut_annotation_obj_cache_info as object(
1818
object_owner varchar2(250),
1919
object_name varchar2(250),
2020
object_type varchar2(250),
21-
needs_refresh varchar2(1)
21+
needs_refresh varchar2(1),
22+
parse_time timestamp
2223
)
2324
/

0 commit comments

Comments
 (0)
0