You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by rm...@apache.org on 2016/10/26 14:46:31 UTC
[1/2] incubator-trafodion git commit: Added regression test
privs2/TEST144
Repository: incubator-trafodion
Updated Branches:
refs/heads/master 9c0e5ab94 -> 8d8adf141
Added regression test privs2/TEST144
Added regression test privs2/TEST144 and its helpers that contains grant and
revoke tests for functions and procedures.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/94d78648
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/94d78648
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/94d78648
Branch: refs/heads/master
Commit: 94d786486b8a4560e3c91ec48e8142f1944f573a
Parents: de82dfb
Author: Roberta Marton <rm...@edev07.esgyn.local>
Authored: Tue Oct 25 16:59:19 2016 +0000
Committer: Roberta Marton <rm...@edev07.esgyn.local>
Committed: Tue Oct 25 16:59:19 2016 +0000
----------------------------------------------------------------------
core/sql/regress/privs2/EXPECTED144 | Bin 0 -> 59186 bytes
core/sql/regress/privs2/LOG144 | Bin 0 -> 17980 bytes
core/sql/regress/privs2/TEST144 | 241 +++++++++++++++++++++++++++++++
core/sql/regress/privs2/udfs.cpp | 174 ++++++++++++++++++++++
4 files changed, 415 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/94d78648/core/sql/regress/privs2/EXPECTED144
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs2/EXPECTED144 b/core/sql/regress/privs2/EXPECTED144
new file mode 100644
index 0000000..8b62d4a
Binary files /dev/null and b/core/sql/regress/privs2/EXPECTED144 differ
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/94d78648/core/sql/regress/privs2/LOG144
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs2/LOG144 b/core/sql/regress/privs2/LOG144
new file mode 100644
index 0000000..5738ebc
Binary files /dev/null and b/core/sql/regress/privs2/LOG144 differ
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/94d78648/core/sql/regress/privs2/TEST144
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs2/TEST144 b/core/sql/regress/privs2/TEST144
new file mode 100755
index 0000000..42e6238
--- /dev/null
+++ b/core/sql/regress/privs2/TEST144
@@ -0,0 +1,241 @@
+-- ============================================================================
+-- TEST144 - tests grant and revoke privileges for RI constraints
+--
+-- @@@ START COPYRIGHT @@@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+-- @@@ END COPYRIGHT @@@
+--
+-- Tests grant and revoke for functions
+-- ============================================================================
+
+cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
+obey TEST144(clean_up);
+log LOG144 clear;
+obey TEST144(create_db);
+obey TEST144(set_up);
+obey TEST144(test_grants);
+obey TEST144(test_revokes);
+log;
+obey TEST144(clean_up);
+exit;
+
+?section clean_up
+-- drop database
+drop schema if exists t144user1 cascade;
+
+revoke execute on procedure "_LIBMGR_".help from t144role1;
+revoke role t144role1 from sql_user4;
+drop role t144role1;
+
+revoke execute on procedure "_LIBMGR_".help from sql_user5 by sql_user3;
+revoke execute on procedure "_LIBMGR_".help from sql_user3 by sql_user2;
+revoke execute on procedure "_LIBMGR_".help from sql_user2 with grant option;
+
+?section create_db
+create schema t144user1 authorization sql_user1;
+set schema t144user1;
+
+-- compile cpp programs
+set pattern $$QUOTE$$ '''';
+
+sh rm -f ./etest140.dll;
+sh sh $$scriptsdir$$/tools/dll-compile.ksh etest140.cpp
+ 2>&1 | tee LOG144_MD_OUTPUT;
+set pattern $$DLL_MD$$ etest140.dll;
+
+sh rm -f ./udfs.dll;
+sh sh $$scriptsdir$$/tools/dll-compile.ksh udfs.cpp
+ 2>&1 | tee LOG144_UDF_OUTPUT;
+set pattern $$DLL_UDF$$ udfs.dll;
+
+-- create the library for metadata udf
+create library t144_l1 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL_MD$$ $$QUOTE$$ ;
+create function t144_translatePrivsBitmap(bitmap largeint) returns (bitmap_string char (20))
+language c parameter style sql external name 'translateBitmap'
+library t144_l1
+deterministic no sql final call allow any parallelism state area size 1024 ;
+grant execute on function t144_translatePrivsBitmap to "PUBLIC";
+
+-- create library and functions for the test
+create library t144_l2 file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL_UDF$$ $$QUOTE$$ ;
+
+drop function if exists gen_phone;
+create function gen_phone(seedValue int, areaCode char (4)) returns (results char (14))
+language c parameter style sql external name 'genPhoneNumber'
+library t144_l2
+deterministic no sql final call allow any parallelism state area size 1024 ;
+
+drop function if exists gen_random;
+create function gen_random(seedValue int, numberDigits int) returns (results char (14))
+language c parameter style sql external name 'genRandomNumber'
+library t144_l2
+deterministic no sql final call allow any parallelism state area size 1024 ;
+
+drop function if exists gen_time;
+create function gen_time(customer_number int) returns (timestamp_value largeint)
+language c parameter style sql external name 'genTimestamp'
+library t144_l2
+deterministic no sql final call allow any parallelism state area size 1024 ;
+
+create table if not exists customers
+ (tenant_id largeint default 1,
+ customer_id largeint generated by default as identity,
+ customer_name varchar(100) default null,
+ customer_areacode char(4) default null,
+ customer_phone_number char(10) default null,
+ customer_time_created timestamp default current_timestamp,
+ customer_time_updated timestamp default current_timestamp,
+ primary key (customer_id)
+);
+
+insert into customers (customer_name, customer_areacode) values
+ ('Tommy', '510'), ('Sammy', '408'), ('Billy', '610'), ('Joey', '619');
+
+select customer_id, customer_areacode from customers;
+grant select, insert on customers to "PUBLIC";
+showddl customers;
+create role t144role1;
+
+?section set_up
+set schema "_PRIVMGR_MD_";
+prepare get_privs from
+select distinct
+ trim(substring (o.object_name,1,15)) as object_name,
+ grantor_id, grantee_id,
+ t144user1.t144_translatePrivsBitmap(privileges_bitmap) as granted_privs,
+ t144user1.t144_translatePrivsBitmap(grantable_bitmap) as grantable_privs
+from object_privileges p, "_MD_".objects o
+where p.object_uid in
+ (select object_uid
+ from "_MD_".objects
+ where schema_name like 'T144USER%'
+ and object_name not like 'SB_%')
+ and p.object_uid = o.object_uid
+order by 1, 2, 3, 4
+;
+
+?section test_grants
+-- =================================================================
+-- this set of tests run basic grant tests for udrs
+-- =================================================================
+set schema t144user1;
+execute get_privs;
+
+-- user1 owns the udfs but does not have execute on procedure help
+sh sqlci -i "TEST144(cmds)" -u sql_user1;
+
+-- no other user or role has privileges
+sh sqlci -i "TEST144(cmds)" -u sql_user2;
+sh sqlci -i "TEST144(cmds)" -u sql_user3;
+sh sqlci -i "TEST144(cmds)" -u sql_user4;
+
+-- grant user2 execute
+grant execute on function gen_phone to sql_user2 with grant option;
+grant execute on function gen_random to sql_user2 with grant option;
+grant execute on function gen_time to sql_user2;
+grant execute on procedure "_LIBMGR_".help to sql_user2 with grant option;
+execute get_privs;
+
+-- user2 can execute
+sh sqlci -i "TEST144(cmds)" -u sql_user2;
+
+-- user3 still cannot execute
+sh sqlci -i "TEST144(cmds)" -u sql_user3;
+
+-- grant user3 by user2
+grant execute on function gen_phone to sql_user3 with grant option by sql_user2;
+grant execute on function gen_random to sql_user3 by sql_user2;
+grant execute on procedure "_LIBMGR_".help to sql_user3 by sql_user2;
+
+-- user2 does not have WGO on gen_time
+grant execute on function gen_time to sql_user3 by sql_user2;
+execute get_privs;
+
+-- user 3 can execute gen_phone, gen_random, and help but not gen_time
+sh sqlci -i "TEST144(cmds)" -u sql_user3;
+grant execute on function gen_phone to sql_user5 by sql_user3;
+grant execute on procedure "_LIBMGR_".help to sql_user5 by sql_user3;
+
+-- test execute privilege with roles
+grant execute on function gen_random to t144role1;
+grant execute on function gen_time to t144role1;
+grant execute on procedure "_LIBMGR_".help to t144role1;
+grant role t144role1 to sql_user4;
+execute get_privs;
+
+-- user4 can execute through role t144role1
+sh sqlci -i "TEST144(cmds)" -u sql_user4;
+
+?section test_revokes
+-- ============================================================================
+-- verify that revoking privileges handle EXECUTE privilege correctly
+-- ============================================================================
+set schema t144user1;
+execute get_privs;
+
+revoke grant option for execute on function gen_phone from sql_user3 by sql_user2;
+revoke execute on function gen_phone from sql_user5 by sql_user3;
+revoke grant option for execute on function gen_phone from sql_user3 by sql_user2;
+revoke grant option for execute on procedure "_LIBMGR_".help from sql_user3 by sql_user2;
+
+drop role t144role1;
+revoke role t144role1 from sql_user4;
+drop role t144role1;
+revoke execute on function gen_random from t144role1;
+revoke execute on function gen_time from t144role1;
+revoke execute on procedure "_LIBMGR_".help from t144role1;
+drop role t144role1;
+
+revoke execute on function gen_phone from sql_user3 by sql_user2;
+revoke execute on function gen_random from sql_user3 by sql_user2;
+revoke execute on procedure "_LIBMGR_".help from sql_user3 by sql_user2;
+
+revoke execute on function gen_phone from sql_user2;
+revoke execute on function gen_random from sql_user2;
+revoke execute on function gen_time from sql_user2;
+revoke execute on procedure "_LIBMGR_".help from sql_user2;
+execute get_privs;
+
+
+?section cmds
+-- ============================================================================
+-- execute functions
+-- ============================================================================
+log LOG144;
+values (user);
+set schema t144user1;
+select customer_id,
+ 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone,
+ customer_areacode
+from customers;
+select customer_id,
+ 'NUMBER: ' || gen_random(customer_id, 10) as tenant_id
+from customers;
+select customer_name,
+ 'TIME: ' || cast (gen_time(customer_id) as char(30)) as customer_time_updated
+from customers;
+select customer_id,
+ 'NUMBER: ' || gen_random(customer_id, 10) as tenant_id,
+ 'PHONE: ' || gen_phone(customer_id, customer_areacode) as phone,
+ customer_areacode
+from customers;
+
+set param ?proc 'rm';
+call "_LIBMGR_".help (?proc);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/94d78648/core/sql/regress/privs2/udfs.cpp
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs2/udfs.cpp b/core/sql/regress/privs2/udfs.cpp
new file mode 100644
index 0000000..1186119
--- /dev/null
+++ b/core/sql/regress/privs2/udfs.cpp
@@ -0,0 +1,174 @@
+// @@@ START COPYRIGHT @@@
+//
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+//
+// @@@ END COPYRIGHT @@@
+
+
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+#include "sqludr.h"
+
+extern "C" {
+
+/* genPhoneNumber */
+SQLUDR_LIBFUNC SQLUDR_INT32 genPhoneNumber(SQLUDR_INT32 *in1, // seed
+ SQLUDR_CHAR *in2, // areacode
+ SQLUDR_CHAR *out,
+ SQLUDR_INT16 *in1Ind,
+ SQLUDR_INT16 *in2Ind,
+ SQLUDR_INT16 *outInd,
+ SQLUDR_TRAIL_ARGS)
+{
+ if (calltype == SQLUDR_CALLTYPE_FINAL)
+ return SQLUDR_SUCCESS;
+
+ std::string result (in2);
+ if (*in1Ind == SQLUDR_NULL || *in2Ind == SQLUDR_NULL)
+ {
+ *outInd = SQLUDR_NULL;
+ }
+ else
+ {
+ srand(*in1);
+ int number = 7; // 7 digit phone number
+ for (int i = 0; i < number; i++)
+ {
+ int randNumber = rand() %10;
+ if (i == 0 && randNumber == 0)
+ randNumber++;
+ switch (randNumber)
+ {
+ case 0: result += '0'; break;
+ case 1: result += '1'; break;
+ case 2: result += '2'; break;
+ case 3: result += '3'; break;
+ case 4: result += '4'; break;
+ case 5: result += '5'; break;
+ case 6: result += '6'; break;
+ case 7: result += '7'; break;
+ case 8: result += '8'; break;
+ default : result += '9'; break;
+ }
+ }
+ }
+
+ strcpy(out, result.c_str());
+ return SQLUDR_SUCCESS;
+}
+
+
+/* genRandomNumber */
+SQLUDR_LIBFUNC SQLUDR_INT32 genRandomNumber(SQLUDR_INT32 *in1,
+ SQLUDR_INT32 *in2,
+ SQLUDR_CHAR *out,
+ SQLUDR_INT16 *in1Ind,
+ SQLUDR_INT16 *in2Ind,
+ SQLUDR_INT16 *outInd,
+ SQLUDR_TRAIL_ARGS)
+{
+ if (calltype == SQLUDR_CALLTYPE_FINAL)
+ return SQLUDR_SUCCESS;
+
+ std::string result;
+ if (*in1Ind == SQLUDR_NULL || *in2Ind == SQLUDR_NULL)
+ {
+ *outInd = SQLUDR_NULL;
+ }
+ else
+ {
+ int number = *in2;
+ for (int i = 0; i < number; i++)
+ {
+ int randNumber = rand() %10;
+ if (i == 0 && randNumber == 0)
+ randNumber++;
+ switch (randNumber)
+ {
+ case 0: result += '0'; break;
+ case 1: result += '1'; break;
+ case 2: result += '2'; break;
+ case 3: result += '3'; break;
+ case 4: result += '4'; break;
+ case 5: result += '5'; break;
+ case 6: result += '6'; break;
+ case 7: result += '7'; break;
+ case 8: result += '8'; break;
+ default : result += '9'; break;
+ }
+ }
+ }
+
+ strcpy(out, result.c_str());
+ return SQLUDR_SUCCESS;
+}
+
+SQLUDR_LIBFUNC SQLUDR_INT32 genTimestamp(SQLUDR_INT64 *in1,
+ SQLUDR_INT32 *in2,
+ SQLUDR_INT64 *in3,
+ SQLUDR_INT64 *out,
+ SQLUDR_INT16 *in1Ind,
+ SQLUDR_INT16 *in2Ind,
+ SQLUDR_INT16 *in3Ind,
+ SQLUDR_INT16 *outInd,
+ SQLUDR_TRAIL_ARGS)
+{
+ if (calltype == SQLUDR_CALLTYPE_FINAL)
+ return SQLUDR_SUCCESS;
+
+ // in1 - seed
+ // in2 - number days ahead to generate time
+ // in3 - julian time of start day
+
+ // 86400000000 is one day
+ long day = 86400000000;
+ long hour = day/24;
+ long min = hour/60;
+ long seconds = min/60;
+
+ long starttime = *in3;
+ int numberDays = *in2;
+ long low = starttime;
+ long high = starttime * numberDays;
+ long randNumber = low;
+
+ if (*in1Ind == SQLUDR_NULL ||
+ *in2Ind == SQLUDR_NULL ||
+ *in3Ind == SQLUDR_NULL)
+ {
+ *outInd = SQLUDR_NULL;
+ }
+ else
+ {
+ // Generate a random timestamp between the above values
+ srand(*in1);
+ int randDay = rand() %(60 - 1) + 1; // day increment
+ int randHour = rand() %(24); // hour increment
+ int randMin = rand() %(60); // min increment
+ int randSec = rand() %(60 - 1) + 1; // seconds increment
+ randNumber = low + (day * randDay) + (hour * randHour) +
+ (min * randMin) + (seconds * randSec) ;
+ }
+
+ *out = randNumber;
+ return SQLUDR_SUCCESS;
+}
+
+} /* extern "C" */
+
[2/2] incubator-trafodion git commit: Merge new regression test,
PR 781
Posted by rm...@apache.org.
Merge new regression test, PR 781
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/8d8adf14
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/8d8adf14
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/8d8adf14
Branch: refs/heads/master
Commit: 8d8adf141db22c3405ec6e6f544c684eaa09a623
Parents: 9c0e5ab 94d7864
Author: Roberta Marton <ro...@apache.org>
Authored: Wed Oct 26 14:46:12 2016 +0000
Committer: Roberta Marton <ro...@apache.org>
Committed: Wed Oct 26 14:46:12 2016 +0000
----------------------------------------------------------------------
core/sql/regress/privs2/EXPECTED144 | Bin 0 -> 59186 bytes
core/sql/regress/privs2/LOG144 | Bin 0 -> 17980 bytes
core/sql/regress/privs2/TEST144 | 241 +++++++++++++++++++++++++++++++
core/sql/regress/privs2/udfs.cpp | 174 ++++++++++++++++++++++
4 files changed, 415 insertions(+)
----------------------------------------------------------------------