| -- ============================================================================ |
| -- 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; |
| |