Skip to content

Commit ae3326f

Browse files
jgebaljgebal
committed
Unified grants & synonyms scripts to avoid any disconnect between PUBLIC and private access to the framework.
Resolves: #957
1 parent 7fc8d2c commit ae3326f

File tree

5 files changed

+275
-379
lines changed

5 files changed

+275
-379
lines changed

‎source/create_grants.sql‎

Lines changed: 138 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,138 @@
1+
/*
2+
utPLSQL - Version 3
3+
Copyright 2016 - 2019 utPLSQL Project
4+
5+
Licensed under the Apache License, Version 2.0 (the "License"):
6+
you may not use this file except in compliance with the License.
7+
You may obtain a copy of the License at
8+
9+
http://www.apache.org/licenses/LICENSE-2.0
10+
11+
Unless required by applicable law or agreed to in writing, software
12+
distributed under the License is distributed on an "AS IS" BASIS,
13+
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14+
See the License for the specific language governing permissions and
15+
limitations under the License.
16+
17+
Create all necessary grant for the user who owns test packages and want to execute utPLSQL framework
18+
*/
19+
20+
@@define_ut3_owner_param.sql
21+
22+
column 2 new_value 2 noprint
23+
select null as "2" from dual where 1=0;
24+
spool params.sql.tmp
25+
select
26+
case
27+
when '&&2' is null then q'[ACCEPT ut3_user CHAR DEFAULT 'PUBLIC' PROMPT 'Provide schema which should be granted access to the utPLSQL v3 framework (PUBLIC): ']'
28+
else 'define ut3_user=&&2'
29+
end
30+
from dual;
31+
32+
spool off
33+
set termout on
34+
@params.sql.tmp
35+
set termout off
36+
/* cleanup temporary sql files */
37+
--try running on windows
38+
$ del params.sql.tmp
39+
--try running on linux/unix
40+
! rm params.sql.tmp
41+
set termout on
42+
43+
set echo off
44+
set feedback on
45+
set heading off
46+
set verify off
47+
48+
prompt Granting privileges on UTPLSQL objects in &&ut3_owner schema to user &&ut3_user
49+
50+
whenever sqlerror exit failure rollback
51+
whenever oserror exit failure rollback
52+
53+
alter session set current_schema = &&ut3_owner;
54+
55+
--public API
56+
grant execute on &&ut3_owner..ut to &ut3_user;
57+
grant execute on &&ut3_owner..ut_runner to &ut3_user;
58+
grant execute on &&ut3_owner..ut_file_mappings to &ut3_user;
59+
grant execute on &&ut3_owner..ut_file_mapping to &ut3_user;
60+
grant execute on &&ut3_owner..ut_file_mapper to &ut3_user;
61+
grant execute on &&ut3_owner..ut_suite_items_info to &ut3_user;
62+
grant execute on &&ut3_owner..ut_suite_item_info to &ut3_user;
63+
grant execute on &&ut3_owner..ut_run_info to &ut3_user;
64+
65+
--generic types
66+
grant execute on &&ut3_owner..ut_varchar2_list to &ut3_user;
67+
grant execute on &&ut3_owner..ut_varchar2_rows to &ut3_user;
68+
grant execute on &&ut3_owner..ut_integer_list to &ut3_user;
69+
grant execute on &&ut3_owner..ut_key_value_pairs to &ut3_user;
70+
grant execute on &&ut3_owner..ut_key_value_pair to &ut3_user;
71+
72+
--expectations
73+
grant execute on &&ut3_owner..ut_expectation to &ut3_user;
74+
grant execute on &&ut3_owner..ut_expectation_compound to &ut3_user;
75+
grant execute on &&ut3_owner..ut_expectation_json to &ut3_user;
76+
77+
--matchers
78+
grant execute on &&ut3_owner..ut_be_between to &ut3_user;
79+
grant execute on &&ut3_owner..ut_be_empty to &ut3_user;
80+
grant execute on &&ut3_owner..ut_be_false to &ut3_user;
81+
grant execute on &&ut3_owner..ut_be_greater_or_equal to &ut3_user;
82+
grant execute on &&ut3_owner..ut_be_greater_than to &ut3_user;
83+
grant execute on &&ut3_owner..ut_be_less_or_equal to &ut3_user;
84+
grant execute on &&ut3_owner..ut_be_less_than to &ut3_user;
85+
grant execute on &&ut3_owner..ut_be_like to &ut3_user;
86+
grant execute on &&ut3_owner..ut_be_not_null to &ut3_user;
87+
grant execute on &&ut3_owner..ut_be_null to &ut3_user;
88+
grant execute on &&ut3_owner..ut_be_true to &ut3_user;
89+
grant execute on &&ut3_owner..ut_contain to &ut3_user;
90+
grant execute on &&ut3_owner..ut_equal to &ut3_user;
91+
grant execute on &&ut3_owner..ut_have_count to &ut3_user;
92+
grant execute on &&ut3_owner..ut_match to &ut3_user;
93+
94+
--reporters - test results
95+
grant execute on &&ut3_owner..ut_teamcity_reporter to &ut3_user;
96+
grant execute on &&ut3_owner..ut_xunit_reporter to &ut3_user;
97+
grant execute on &&ut3_owner..ut_junit_reporter to &ut3_user;
98+
grant execute on &&ut3_owner..ut_tfs_junit_reporter to &ut3_user;
99+
grant execute on &&ut3_owner..ut_documentation_reporter to &ut3_user;
100+
grant execute on &&ut3_owner..ut_sonar_test_reporter to &ut3_user;
101+
grant execute on &&ut3_owner..ut_realtime_reporter to &ut3_user;
102+
--reporters - coverage
103+
grant execute on &&ut3_owner..ut_coverage_html_reporter to &ut3_user;
104+
grant execute on &&ut3_owner..ut_coverage_sonar_reporter to &ut3_user;
105+
grant execute on &&ut3_owner..ut_coveralls_reporter to &ut3_user;
106+
grant execute on &&ut3_owner..ut_coverage_cobertura_reporter to &ut3_user;
107+
--reporters - debug
108+
grant execute on &&ut3_owner..ut_debug_reporter to &ut3_user;
109+
110+
--reporters - base types
111+
grant execute on &&ut3_owner..ut_reporters to &ut3_user;
112+
grant execute on &&ut3_owner..ut_reporter_base to &ut3_user;
113+
grant execute on &&ut3_owner..ut_output_reporter_base to &ut3_user;
114+
115+
--outputs
116+
grant execute on &&ut3_owner..ut_output_buffer_base to &ut3_user;
117+
118+
--user temp tables
119+
grant select, insert, update, delete on &&ut3_owner..ut_compound_data_tmp to &ut3_user;
120+
grant select, insert, update, delete on &&ut3_owner..ut_compound_data_diff_tmp to &ut3_user;
121+
122+
--needed for selecting from annotation objects
123+
grant execute on &&ut3_owner..ut_annotation_objs_cache_info to &ut3_user;
124+
grant execute on &&ut3_owner..ut_annotation_obj_cache_info to &ut3_user;
125+
126+
--other grants
127+
grant execute on &&ut3_owner..ut_executables to &ut3_user;
128+
grant execute on &&ut3_owner..ut_executable_test to &ut3_user;
129+
grant select on &&ut3_owner..ut_suite_cache to &ut3_user;
130+
grant select on &&ut3_owner..ut_suite_cache_package to &ut3_user;
131+
grant select on &&ut3_owner..ut_suite_cache_schema to &ut3_user;
132+
133+
grant select, insert, delete, update on &&ut3_owner..dbmspcc_blocks to &ut3_user;
134+
grant select, insert, delete, update on &&ut3_owner..dbmspcc_runs to &ut3_user;
135+
grant select, insert, delete, update on &&ut3_owner..dbmspcc_units to &ut3_user;
136+
grant execute on &&ut3_owner..ut_matcher_options to &ut3_user;
137+
grant execute on &&ut3_owner..ut_matcher_options_items to &ut3_user;
138+

‎source/create_synonyms.sql‎

Lines changed: 133 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,133 @@
1+
/*
2+
utPLSQL - Version 3
3+
Copyright 2016 - 2019 utPLSQL Project
4+
5+
Licensed under the Apache License, Version 2.0 (the "License"):
6+
you may not use this file except in compliance with the License.
7+
You may obtain a copy of the License at
8+
9+
http://www.apache.org/licenses/LICENSE-2.0
10+
11+
Unless required by applicable law or agreed to in writing, software
12+
distributed under the License is distributed on an "AS IS" BASIS,
13+
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14+
See the License for the specific language governing permissions and
15+
limitations under the License.
16+
17+
Create all necessary grant for the user who owns test packages and want to execute utPLSQL framework
18+
*/
19+
20+
@@define_ut3_owner_param.sql
21+
22+
column 2 new_value 2 noprint
23+
select null as "2" from dual where 1=0;
24+
spool params.sql.tmp
25+
select
26+
case
27+
when '&&2' is null then q'[ACCEPT ut3_user CHAR DEFAULT 'PUBLIC' PROMPT 'Provide schema which should own synonyms for the utPLSQL v3 framework (PUBLIC): ']'
28+
else 'define ut3_user=&&2.'
29+
end
30+
from dual;
31+
spool off
32+
set termout on
33+
@params.sql.tmp
34+
set termout off
35+
36+
spool params.sql.tmp
37+
select
38+
case
39+
when upper('&&ut3_user') = 'PUBLIC' then q'[define action_type='public'
40+
]'||q'[define ut3_user=''
41+
]'||q'[define grantee='PUBLIC']'
42+
else q'[define action_type='or replace'
43+
]'||q'[define grantee='&&ut3_user']
44+
]'||q'[define ut3_user='&&ut3_user..']'
45+
end
46+
from dual;
47+
48+
spool off
49+
set termout on
50+
@params.sql.tmp
51+
set termout off
52+
/* cleanup temporary sql files */
53+
--try running on windows
54+
$ del params.sql.tmp
55+
--try running on linux/unix
56+
! rm params.sql.tmp
57+
set termout on
58+
59+
set echo off
60+
set feedback on
61+
set heading off
62+
set verify off
63+
64+
whenever sqlerror exit failure rollback
65+
whenever oserror exit failure rollback
66+
67+
alter session set current_schema = &&ut3_owner;
68+
69+
prompt Creating synonyms for UTPLSQL objects in &&ut3_owner schema to user &&grantee
70+
71+
--public API
72+
create &action_type. synonym &ut3_user.ut for &&ut3_owner..ut;
73+
create &action_type. synonym &ut3_user.ut_runner for &&ut3_owner..ut_runner;
74+
create &action_type. synonym &ut3_user.ut_file_mappings for &&ut3_owner..ut_file_mappings;
75+
create &action_type. synonym &ut3_user.ut_file_mapping for &&ut3_owner..ut_file_mapping;
76+
create &action_type. synonym &ut3_user.ut_file_mapper for &&ut3_owner..ut_file_mapper;
77+
create &action_type. synonym &ut3_user.ut_suite_items_info for &&ut3_owner..ut_suite_items_info;
78+
create &action_type. synonym &ut3_user.ut_suite_item_info for &&ut3_owner..ut_suite_item_info;
79+
create &action_type. synonym &ut3_user.ut_run_info for &&ut3_owner..ut_run_info;
80+
81+
--generic types
82+
create &action_type. synonym &ut3_user.ut_varchar2_list for &&ut3_owner..ut_varchar2_list;
83+
create &action_type. synonym &ut3_user.ut_varchar2_rows for &&ut3_owner..ut_varchar2_rows;
84+
create &action_type. synonym &ut3_user.ut_integer_list for &&ut3_owner..ut_integer_list;
85+
create &action_type. synonym &ut3_user.ut_key_value_pairs for &&ut3_owner..ut_key_value_pairs;
86+
create &action_type. synonym &ut3_user.ut_key_value_pair for &&ut3_owner..ut_key_value_pair;
87+
88+
--expectations
89+
create &action_type. synonym &ut3_user.ut_expectation for &&ut3_owner..ut_expectation;
90+
create &action_type. synonym &ut3_user.ut_expectation_compound for &&ut3_owner..ut_expectation_compound;
91+
create &action_type. synonym &ut3_user.ut_expectation_json for &&ut3_owner..ut_expectation_json;
92+
93+
--matchers
94+
create &action_type. synonym &ut3_user.be_between for &&ut3_owner..be_between;
95+
create &action_type. synonym &ut3_user.be_empty for &&ut3_owner..be_empty;
96+
create &action_type. synonym &ut3_user.be_false for &&ut3_owner..be_false;
97+
create &action_type. synonym &ut3_user.be_greater_or_equal for &&ut3_owner..be_greater_or_equal;
98+
create &action_type. synonym &ut3_user.be_greater_than for &&ut3_owner..be_greater_than;
99+
create &action_type. synonym &ut3_user.be_less_or_equal for &&ut3_owner..be_less_or_equal;
100+
create &action_type. synonym &ut3_user.be_less_than for &&ut3_owner..be_less_than;
101+
create &action_type. synonym &ut3_user.be_like for &&ut3_owner..be_like;
102+
create &action_type. synonym &ut3_user.be_not_null for &&ut3_owner..be_not_null;
103+
create &action_type. synonym &ut3_user.be_null for &&ut3_owner..be_null;
104+
create &action_type. synonym &ut3_user.be_true for &&ut3_owner..be_true;
105+
create &action_type. synonym &ut3_user.contain for &&ut3_owner..contain;
106+
create &action_type. synonym &ut3_user.equal for &&ut3_owner..equal;
107+
create &action_type. synonym &ut3_user.have_count for &&ut3_owner..have_count;
108+
create &action_type. synonym &ut3_user.match for &&ut3_owner..match;
109+
110+
--reporters - test results
111+
create &action_type. synonym &ut3_user.ut_teamcity_reporter for &&ut3_owner..ut_teamcity_reporter;
112+
create &action_type. synonym &ut3_user.ut_xunit_reporter for &&ut3_owner..ut_xunit_reporter;
113+
create &action_type. synonym &ut3_user.ut_junit_reporter for &&ut3_owner..ut_junit_reporter;
114+
create &action_type. synonym &ut3_user.ut_tfs_junit_reporter for &&ut3_owner..ut_tfs_junit_reporter;
115+
create &action_type. synonym &ut3_user.ut_documentation_reporter for &&ut3_owner..ut_documentation_reporter;
116+
create &action_type. synonym &ut3_user.ut_sonar_test_reporter for &&ut3_owner..ut_sonar_test_reporter;
117+
create &action_type. synonym &ut3_user.ut_realtime_reporter for &&ut3_owner..ut_realtime_reporter;
118+
--reporters - coverage
119+
create &action_type. synonym &ut3_user.ut_coverage_html_reporter for &&ut3_owner..ut_coverage_html_reporter;
120+
create &action_type. synonym &ut3_user.ut_coverage_sonar_reporter for &&ut3_owner..ut_coverage_sonar_reporter;
121+
create &action_type. synonym &ut3_user.ut_coveralls_reporter for &&ut3_owner..ut_coveralls_reporter;
122+
create &action_type. synonym &ut3_user.ut_coverage_cobertura_reporter for &&ut3_owner..ut_coverage_cobertura_reporter;
123+
--reporters - debug
124+
create &action_type. synonym &ut3_user.ut_debug_reporter for &&ut3_owner..ut_debug_reporter;
125+
--reporters - base types
126+
create &action_type. synonym &ut3_user.ut_reporters for &&ut3_owner..ut_reporters;
127+
create &action_type. synonym &ut3_user.ut_reporter_base for &&ut3_owner..ut_reporter_base;
128+
create &action_type. synonym &ut3_user.ut_output_reporter_base for &&ut3_owner..ut_output_reporter_base;
129+
130+
--other synonyms
131+
create &action_type. synonym &ut3_user.dbmspcc_blocks for &&ut3_owner..dbmspcc_blocks;
132+
create &action_type. synonym &ut3_user.dbmspcc_runs for &&ut3_owner..dbmspcc_runs;
133+
create &action_type. synonym &ut3_user.dbmspcc_units for &&ut3_owner..dbmspcc_units;

0 commit comments

Comments
 (0)