core/sql/regress/privs1/TEST136

-- ============================================================================
-- TEST136 - tests register and unregister user commands
--
-- @@@ 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 @@@
--
-- This test uses users sql_user1
--
--   REGISTER USER <external-user> 
--     [AS <database-user>] 
--   UNREGISTER USER <database-user> [{RESTRICT | CASCADE}]
--   ALTER USER <database-user> SET <options>
--     <options> ==> [{EXTERNAL NAME <external-name> | ONLINE | OFFLINE}]
--
-- Sections:
--   clean_up - removes database setup
--   set_up - prepares for test
--   create_objects - creates object needed by the test
--   tests - runs tests
-- ============================================================================

obey TEST136(clean_up);
set parserflags 64;
cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';
log LOG136 clear;
obey TEST136(create_db);
obey TEST136(register_tests);
obey TEST136(unregister_tests);
obey TEST136(alter_tests);
execute get_users;
log;
-- Try a register user without authority
#sh sh runmxci.ksh -i "TEST136(no_priv_test)" -u sql_user1;
obey TEST136(clean_up);
exit;

?section clean_up
-- drop database
drop schema t136sch cascade;

-- remove roles
revoke role football_players from test136_user1;
drop role football_players;
drop role baseball_players;

drop table "delimited_sch136".t1;
drop schema "delimited_sch136";
obey TEST136(drop_users);

?section drop_users
unregister user test136_user1;
unregister user test136_user2;
unregister user test136_u2;
unregister user test136_user3;
unregister user test136_user4;
unregister user test136_u4;
unregister user test136_user5;
unregister user test136_u5;
unregister user test136_user6;
unregister user "TEST136_~`!@#$%^&*()_+-={}[]:"";'<>?,./a";
unregister user 
  TEST136_901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678;
unregister user invalid_user1;
unregister user janedoe;
unregister user db__user1;
unregister user x;
unregister user y;
unregister user user1;
unregister user mickey;
unregister user donald;

execute get_users;

?section set_up
-- turn off LDAP checking
-- set parserflags 64;
-- Query to return currently registered users
prepare get_users from
select distinct
   substring (auth_db_name,1,20) as db_user_name,
   case
     when (auth_db_name = 'DB__ROOT') then 'ROOT'
     else substring (auth_ext_name,1,20)
   end as auth_ext_name,
   case
    when (auth_id > 33333) then 'NORMAL'
    else 'SYSTEM'
   end as auth_id,
   auth_is_valid as valid,
   case
     when (auth_type = 'U') then 'USER'
     when (auth_type = 'R') then 'ROLE'
     else auth_type
   end as auth_type
from trafodion."_MD_".auths u
  where
    (u.auth_db_name like 'TEST136%' or
     u.auth_db_name like 'CMU_T%' or
     u.auth_db_name in ('DB__ROOT', 'MICKEY', 'DONALD'))
  order by 1, 2
  for read uncommitted access
;

-- query to return table space owner information
prepare get_owners from
select
  substring (object_name,1,20) as object_name,
  object_owner
  from trafodion."_MD_".objects
  where
     object_type = 'BT'
  order by 1, 2
  for read uncommitted access
;

?section create_db
create schema $$TEST_CATALOG$$.t136sch;
set schema $$TEST_CATALOG$$.t136sch;

create role football_players;
grant role football_players to public;
create role baseball_players;

create schema "delimited_sch136";
create table "delimited_sch136".t1 (a int not null not droppable primary key, b int);
 
?section register_tests
-- =================================================================
-- this set of tests run basic register tests
-- =================================================================

obey TEST136(set_up);

log LOG136;
-- succeed: register users -> no clauses
register user test136_user1;

-- succeed: register user -> AS clause
register user test136_user2 
   as test136_u2;

set parserflags 1;
-- succeed: register user
register user test136_user5;

-- succeed: register user -> AS clause
register user test136_user6
  as test136_u6;
reset parserflags 1;

-- 7 users should be returned
execute get_users;

-- cleanup
unregister user test136_user1;
unregister user test136_u2;
unregister user test136_user3;
unregister user test136_u4;
unregister user test136_user5;
unregister user test136_u6;

-- should be 1 rows
execute get_users;

-- succeed: special cases
register user x;
showddl user x;
unregister user x;
register user test136_user1 as y;
showddl user y;
unregister user y;
register user test136_user3 as
  TEST136_901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678;
register user test136_user4 as test136_user4;
register user test136_user5 as test136_user6;
register user "/forward" as test136_user7;
register user "donald/duck@disneyland.com" as test136_user8;;
register user test136_user9 as "mickey/mouse@disneyland.com";

-- should be 6 rows
execute get_users;

-- cleanup
unregister user 
  TEST136_901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678;
unregister user test136_user4;
unregister user test136_user6;
unregister user test136_user7;
unregister user test136_user8;
unregister user "mickey/mouse@disneyland.com";
get users;

-- succeed:  test unreserved words
register user register as user1;
showddl user user1;
unregister user user1;
register user unregister as register;
showddl user register;
unregister user register;
register user user1 as unregister;
showddl user unregister;
unregister user unregister;
register user logon as test136_u2;
showddl user test136_u2;
unregister user test136_u2;
register user test136_u2 as logon;
showddl user logon;
unregister user logon;
register user "none" as user3;
showddl user user3;
unregister user user3;
register user user3 as "/forward";
showddl user "/forward";
unregister user "/forward";

-- fail: register a user that is not defined in the external directory service
-- turn on LDAP checking
--reset parserflags 64;
--register user invalid_user1;
--register user invalid_user1 as janedoe;
-- turn LDAP checking back off
--set parserflags 64;

-- fail: register an already defined user
execute get_users;
register user test136_user1;
execute get_users;
register user test136_user1 as user1;
register user test136_user1 as test136_user1;
unregister user test136_user1;

--- test for delimited catalog and unregister
register user user136_delim;
-- grant select on "delimited_sch136".t1 to user136_delim;
unregister user user136_delim;
drop table "delimited_sch136".t1;
unregister user user136_delim;;
drop schema "delimited_sch136";

-- fail:  register a user with the DB__ prefix
register user test136_user2 as DB__User1;

-- fail:  register a user with just the DB__ prefix
register user test136_user2 as DB__;

-- fail:  register a user as PUBLIC
register user test136_user2 as "PUBLIC"; 

-- fail:  register a user as _SYSTEM
register user test136_user2 as "_SYSTEM";

-- fail:  register a user with too long of a name
register user test136_user2 as
  TEST136_9012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789;

-- fail: bad SQL identifier
register user "\userx";
register user user1 as "\user1";

-- fail: case insensitive delimited identifier
register user test136_u2;
register user "test136_u2";
register user "USER2";
register user abc as "test136_u2";
register user abd as "USER2";
unregister user test136_u2;
unregister user "USER2";

-- fail:  specify a role where the user does not belong
-- register user test136_user2 logon role baseball_players;

-- fail: usage of reserved word
register user none;
register user user1 as none;

-- fail: invalid use of quotes
register user test136_user2 as "TEST136_~`!@#$%^&*()_+-={}[]:"";'<>?,./a";

-- fail: unsupported special characters
register user "try a space";
register user "try$$char";
register user "try *(char";
register user user1 as "try:@#$%^";

?section unregister_tests
obey TEST136(set_up);
log LOG136;
-- =================================================================
-- this set of tests run basic unregister tests
-- =================================================================

-- succeed:  
register user user1 as test136_user1;
-- should be 2 users
execute get_users;

grant create on schema $$TEST_CATALOG$$.t136sch to test136_user1;
create table $$TEST_CATALOG$$.t136sch.t136t1 (c1 int not null primary key, c2 int);
-- grant select on $$TEST_CATALOG$$.t136sch.t136t1 to test136_user1;
-- run as test136_user1 and create some objects
-- sh sh runmxci.ksh -i "TEST136(create_user1_objects)" -u test136_user1;

-- fail:  user1 owns objects, cannot unregister
-- unregister user test136_user1;
-- unregister user test136_user1 restrict;

-- fail:  user1 still has privileges
-- drop table $$TEST_CATALOG$$.t136sch.t136t2 cascade;
-- unregister user test136_user1;

-- fail:  try to unregister a predefined user
unregister user DB__ROOT;

-- fail:  try to unregister a user that has not been registered in the db
unregister user non_Existent_User;

-- succeed:  remove other relationships
showddl $$TEST_CATALOG$$.t136sch.t136t1, privileges;
-- revoke select on $$TEST_CATALOG$$.t136sch.t136t1 from test136_user1;
showddl $$TEST_CATALOG$$.t136sch.t136t1, privileges;
unregister user test136_user1;
grant create on schema $$TEST_CATALOG$$.t136sch to test136_user1;
showddl schema $$TEST_CATALOG$$.t136sch, privileges;
revoke create on schema $$TEST_CATALOG$$.t136sch from test136_user1;
showddl schema $$TEST_CATALOG$$.t136sch, privileges;
unregister user test136_user1;

-- Add more complex relationships
-- Add cascade tests later

?section alter_tests
obey TEST136(set_up);
log LOG136;
-- =================================================================
-- this set of tests run basic alter user tests
-- =================================================================
execute get_users;
register user "mickey/mouse@disney.com" as mickey;
register user "donald/duck@disney.com" as donald;
execute get_users;
showddl user mickey;
showddl user donald;
alter user mickey set external name "mickey/mouse@seaworld.com";
showddl user mickey;
alter user donald set offline;
showddl user donald;
execute get_users;
alter user donald set online;
showddl user donald;

-- run negative tests
-- error 1333
alter user minnie set offline;
-- error 1333
alter user "donald/duck@disney.com" set offline;
--error 1337
alter user DB__ROOT set offline;
--error 1335
alter user mickey set external name "donald/duck@disney.com";

-- cleanup
unregister user mickey;
unregister user donald;
execute get_users;

?section create_user1_objects
set schema $$TEST_CATALOG$$.t136sch;
create table t136t2 (c1 int not null primary key, c2 int);
insert into t136t2 values (1,1), (2,2), (3,3), (4,4), (5,5);
select * from t136t2;
-- grant select on t136t2 to test136_u2;

?section no_priv_test
obey TEST136(set_up);
log LOG136;
execute get_users;
-- these commands should fail because user does not have authority
register user test136_u2 as userx;
unregister user sql_user1;
execute get_users;
log;