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/01/12 23:47:08 UTC
[08/20] incubator-trafodion git commit: TRAFODION-1087 &
TRAFODION-1671 fixes
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/e3d65c15/core/sql/regress/privs1/EXPECTED136
----------------------------------------------------------------------
diff --git a/core/sql/regress/privs1/EXPECTED136 b/core/sql/regress/privs1/EXPECTED136
new file mode 100755
index 0000000..3cfc02d
--- /dev/null
+++ b/core/sql/regress/privs1/EXPECTED136
@@ -0,0 +1,870 @@
+>>obey TEST136(create_db);
+>>create schema $$TEST_CATALOG$$.t136sch;
+
+--- SQL operation complete.
+>>set schema $$TEST_CATALOG$$.t136sch;
+
+--- SQL operation complete.
+>>
+>>create role football_players;
+
+--- SQL operation complete.
+>>grant role football_players to public;
+
+*** ERROR[1355] Granting a role to PUBLIC or _SYSTEM is not allowed.
+
+--- SQL operation failed with errors.
+>>create role baseball_players;
+
+--- SQL operation complete.
+>>
+>>create schema "delimited_sch136";
+
+--- SQL operation complete.
+>>create table "delimited_sch136".t1 (a int not null not droppable primary key, b int);
+
+--- SQL operation complete.
+>>
+>>obey TEST136(register_tests);
+>>-- =================================================================
+>>-- this set of tests run basic register tests
+>>-- =================================================================
+>>
+>>obey TEST136(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
++>;
+
+--- SQL command prepared.
+>>
+>>-- 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
++>;
+
+--- SQL command prepared.
+>>
+>>
+>>log LOG136;
+>>-- succeed: register users -> no clauses
+>>register user test136_user1;
+
+--- SQL operation complete.
+>>
+>>-- succeed: register user -> AS clause
+>>register user test136_user2
++> as test136_u2;
+
+--- SQL operation complete.
+>>
+>>set parserflags 1;
+
+--- SQL operation complete.
+>>-- succeed: register user
+>>register user test136_user5;
+
+--- SQL operation complete.
+>>
+>>-- succeed: register user -> AS clause
+>>register user test136_user6
++> as test136_u6;
+
+--- SQL operation complete.
+>>reset parserflags 1;
+
+--- SQL operation complete.
+>>
+>>-- 7 users should be returned
+>>execute get_users;
+
+DB_USER_NAME AUTH_EXT_NAME AUTH_ID VALID AUTH_TYPE
+-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- ----- ---------
+
+DB__ROOT ROOT SYSTEM Y USER
+TEST136_U2 TEST136_USER2 NORMAL Y USER
+TEST136_U6 TEST136_USER6 NORMAL Y USER
+TEST136_USER1 TEST136_USER1 NORMAL Y USER
+TEST136_USER5 TEST136_USER5 NORMAL Y USER
+
+--- 5 row(s) selected.
+>>
+>>-- cleanup
+>>unregister user test136_user1;
+
+--- SQL operation complete.
+>>unregister user test136_u2;
+
+--- SQL operation complete.
+>>unregister user test136_user3;
+
+*** ERROR[1333] User TEST136_USER3 does not exist.
+
+--- SQL operation failed with errors.
+>>unregister user test136_u4;
+
+*** ERROR[1333] User TEST136_U4 does not exist.
+
+--- SQL operation failed with errors.
+>>unregister user test136_user5;
+
+--- SQL operation complete.
+>>unregister user test136_u6;
+
+--- SQL operation complete.
+>>
+>>-- should be 1 rows
+>>execute get_users;
+
+DB_USER_NAME AUTH_EXT_NAME AUTH_ID VALID AUTH_TYPE
+-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- ----- ---------
+
+DB__ROOT ROOT SYSTEM Y USER
+
+--- 1 row(s) selected.
+>>
+>>-- succeed: special cases
+>>register user x;
+
+--- SQL operation complete.
+>>showddl user x;
+
+REGISTER USER "X";
+
+--- SQL operation complete.
+>>unregister user x;
+
+--- SQL operation complete.
+>>register user test136_user1 as y;
+
+--- SQL operation complete.
+>>showddl user y;
+
+REGISTER USER "TEST136_USER1" AS "Y";
+
+--- SQL operation complete.
+>>unregister user y;
+
+--- SQL operation complete.
+>>register user test136_user3 as
++> TEST136_901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678;
+
+--- SQL operation complete.
+>>register user test136_user4 as test136_user4;
+
+--- SQL operation complete.
+>>register user test136_user5 as test136_user6;
+
+--- SQL operation complete.
+>>register user "/forward" as test136_user7;
+
+--- SQL operation complete.
+>>register user "donald/duck@disneyland.com" as test136_user8;
+
+--- SQL operation complete.
+>>register user test136_user9 as "mickey/mouse@disneyland.com";
+
+--- SQL operation complete.
+>>
+>>-- should be 6 rows
+>>execute get_users;
+
+DB_USER_NAME AUTH_EXT_NAME AUTH_ID VALID AUTH_TYPE
+-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- ----- ---------
+
+DB__ROOT ROOT SYSTEM Y USER
+TEST136_901234567890 TEST136_USER3 NORMAL Y USER
+TEST136_USER4 TEST136_USER4 NORMAL Y USER
+TEST136_USER6 TEST136_USER5 NORMAL Y USER
+TEST136_USER7 /FORWARD NORMAL Y USER
+TEST136_USER8 DONALD/DUCK@DISNEYLA NORMAL Y USER
+
+--- 6 row(s) selected.
+>>
+>>-- cleanup
+>>unregister user
++> TEST136_901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678;
+
+--- SQL operation complete.
+>>unregister user test136_user4;
+
+--- SQL operation complete.
+>>unregister user test136_user6;
+
+--- SQL operation complete.
+>>unregister user test136_user7;
+
+--- SQL operation complete.
+>>unregister user test136_user8;
+
+--- SQL operation complete.
+>>unregister user "mickey/mouse@disneyland.com";
+
+--- SQL operation complete.
+>>get users;
+
+Users
+=====
+
+DB__ROOT
+SQL_USER1
+SQL_USER10
+SQL_USER2
+SQL_USER3
+SQL_USER4
+SQL_USER5
+SQL_USER6
+SQL_USER7
+SQL_USER8
+SQL_USER9
+
+--- SQL operation complete.
+>>
+>>-- succeed: test unreserved words
+>>register user register as user1;
+
+--- SQL operation complete.
+>>showddl user user1;
+
+REGISTER USER "REGISTER" AS "USER1";
+
+--- SQL operation complete.
+>>unregister user user1;
+
+--- SQL operation complete.
+>>register user unregister as register;
+
+--- SQL operation complete.
+>>showddl user register;
+
+REGISTER USER "UNREGISTER" AS "REGISTER";
+
+--- SQL operation complete.
+>>unregister user register;
+
+--- SQL operation complete.
+>>register user user1 as unregister;
+
+--- SQL operation complete.
+>>showddl user unregister;
+
+REGISTER USER "USER1" AS "UNREGISTER";
+
+--- SQL operation complete.
+>>unregister user unregister;
+
+--- SQL operation complete.
+>>register user logon as test136_u2;
+
+--- SQL operation complete.
+>>showddl user test136_u2;
+
+REGISTER USER "LOGON" AS "TEST136_U2";
+
+--- SQL operation complete.
+>>unregister user test136_u2;
+
+--- SQL operation complete.
+>>register user test136_u2 as logon;
+
+--- SQL operation complete.
+>>showddl user logon;
+
+REGISTER USER "TEST136_U2" AS "LOGON";
+
+--- SQL operation complete.
+>>unregister user logon;
+
+--- SQL operation complete.
+>>register user "none" as user3;
+
+--- SQL operation complete.
+>>showddl user user3;
+
+REGISTER USER "NONE" AS "USER3";
+
+--- SQL operation complete.
+>>unregister user user3;
+
+--- SQL operation complete.
+>>register user user3 as "/forward";
+
+--- SQL operation complete.
+>>showddl user "/forward";
+
+REGISTER USER "USER3" AS "/FORWARD";
+
+--- SQL operation complete.
+>>unregister user "/forward";
+
+--- SQL operation complete.
+>>
+>>-- 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;
+
+DB_USER_NAME AUTH_EXT_NAME AUTH_ID VALID AUTH_TYPE
+-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- ----- ---------
+
+DB__ROOT ROOT SYSTEM Y USER
+
+--- 1 row(s) selected.
+>>register user test136_user1;
+
+--- SQL operation complete.
+>>execute get_users;
+
+DB_USER_NAME AUTH_EXT_NAME AUTH_ID VALID AUTH_TYPE
+-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- ----- ---------
+
+DB__ROOT ROOT SYSTEM Y USER
+TEST136_USER1 TEST136_USER1 NORMAL Y USER
+
+--- 2 row(s) selected.
+>>register user test136_user1 as user1;
+
+*** ERROR[1335] Directory service user TEST136_USER1 already defined in the database.
+
+--- SQL operation failed with errors.
+>>register user test136_user1 as test136_user1;
+
+*** ERROR[1334] User or role TEST136_USER1 already exists.
+
+--- SQL operation failed with errors.
+>>unregister user test136_user1;
+
+--- SQL operation complete.
+>>
+>>--- test for delimited catalog and unregister
+>>register user user136_delim;
+
+--- SQL operation complete.
+>>-- grant select on "delimited_sch136".t1 to user136_delim;
+>>unregister user user136_delim;
+
+--- SQL operation complete.
+>>drop table "delimited_sch136".t1;
+
+--- SQL operation complete.
+>>unregister user user136_delim;
+
+*** ERROR[1333] User USER136_DELIM does not exist.
+
+--- SQL operation failed with errors.
+>>drop schema "delimited_sch136";
+
+--- SQL operation complete.
+>>
+>>-- fail: register a user with the DB__ prefix
+>>register user test136_user2 as DB__User1;
+
+*** ERROR[1337] DB__USER1 is a reserved authorization identifier.
+
+--- SQL operation failed with errors.
+>>
+>>-- fail: register a user with just the DB__ prefix
+>>register user test136_user2 as DB__;
+
+*** ERROR[1337] DB__ is a reserved authorization identifier.
+
+--- SQL operation failed with errors.
+>>
+>>-- fail: register a user as PUBLIC
+>>register user test136_user2 as "PUBLIC";
+
+*** ERROR[1337] PUBLIC is a reserved authorization identifier.
+
+--- SQL operation failed with errors.
+>>
+>>-- fail: register a user as _SYSTEM
+>>register user test136_user2 as "_SYSTEM";
+
+*** ERROR[1337] _SYSTEM is a reserved authorization identifier.
+
+--- SQL operation failed with errors.
+>>
+>>-- fail: register a user with too long of a name
+>>register user test136_user2 as
++> TEST136_9012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789;
+
+*** ERROR[3118] Specified identifier is too long.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- fail: bad SQL identifier
+>>register user "\userx";
+
+*** ERROR[3127] An invalid character was found in identifier "\userx".
+
+*** ERROR[15001] A syntax error occurred at or before:
+register user "\userx";
+ ^ (17 characters from start of SQL statement)
+
+*** ERROR[8822] The statement was not prepared.
+
+>>register user user1 as "\user1";
+
+*** ERROR[3127] An invalid character was found in identifier "\user1".
+
+*** ERROR[15001] A syntax error occurred at or before:
+register user user1 as "\user1";
+ ^ (26 characters from start of SQL statement)
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- fail: case insensitive delimited identifier
+>>register user test136_u2;
+
+--- SQL operation complete.
+>>register user "test136_u2";
+
+*** ERROR[1334] User or role TEST136_U2 already exists.
+
+--- SQL operation failed with errors.
+>>register user "USER2";
+
+--- SQL operation complete.
+>>register user abc as "test136_u2";
+
+*** ERROR[1334] User or role TEST136_U2 already exists.
+
+--- SQL operation failed with errors.
+>>register user abd as "USER2";
+
+*** ERROR[1334] User or role USER2 already exists.
+
+--- SQL operation failed with errors.
+>>unregister user test136_u2;
+
+--- SQL operation complete.
+>>unregister user "USER2";
+
+--- SQL operation complete.
+>>
+>>-- 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;
+
+*** ERROR[15001] A syntax error occurred at or before:
+register user none;
+ ^ (18 characters from start of SQL statement)
+
+*** ERROR[8822] The statement was not prepared.
+
+>>register user user1 as none;
+
+*** ERROR[15001] A syntax error occurred at or before:
+register user user1 as none;
+ ^ (27 characters from start of SQL statement)
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- fail: invalid use of quotes
+>>register user test136_user2 as "TEST136_~`!@#$%^&*()_+-={}[]:"";'<>?,./a";
+
+*** ERROR[1370] The authorization name "TEST136_~`!@#$%^&*()_+-={}[]:";'<>?,./A" contains one or more invalid characters. A valid name can only contain these characters: [a-zA-Z_0-9-@./]
+
+--- SQL operation failed with errors.
+>>
+>>-- fail: unsupported special characters
+>>register user "try a space";
+
+*** ERROR[1370] The authorization name "TRY A SPACE" contains one or more invalid characters. A valid name can only contain these characters: [a-zA-Z_0-9-@./]
+
+--- SQL operation failed with errors.
+>>register user "try$$char";
+
+*** ERROR[1370] The authorization name "TRY$$CHAR" contains one or more invalid characters. A valid name can only contain these characters: [a-zA-Z_0-9-@./]
+
+--- SQL operation failed with errors.
+>>register user "try *(char";
+
+*** ERROR[1370] The authorization name "TRY *(CHAR" contains one or more invalid characters. A valid name can only contain these characters: [a-zA-Z_0-9-@./]
+
+--- SQL operation failed with errors.
+>>register user user1 as "try:@#$%^";
+
+*** ERROR[1370] The authorization name "TRY:@#$%^" contains one or more invalid characters. A valid name can only contain these characters: [a-zA-Z_0-9-@./]
+
+--- SQL operation failed with errors.
+>>
+>>obey TEST136(unregister_tests);
+>>obey TEST136(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
++>;
+
+--- SQL command prepared.
+>>
+>>-- 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
++>;
+
+--- SQL command prepared.
+>>
+>>log LOG136;
+>>-- =================================================================
+>>-- this set of tests run basic unregister tests
+>>-- =================================================================
+>>
+>>-- succeed:
+>>register user user1 as test136_user1;
+
+--- SQL operation complete.
+>>-- should be 2 users
+>>execute get_users;
+
+DB_USER_NAME AUTH_EXT_NAME AUTH_ID VALID AUTH_TYPE
+-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- ----- ---------
+
+DB__ROOT ROOT SYSTEM Y USER
+TEST136_USER1 USER1 NORMAL Y USER
+
+--- 2 row(s) selected.
+>>
+>>grant create on schema $$TEST_CATALOG$$.t136sch to test136_user1;
+
+*** ERROR[4222] The DDL feature is not supported in this software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>create table $$TEST_CATALOG$$.t136sch.t136t1 (c1 int not null primary key, c2 int);
+
+--- SQL operation complete.
+>>-- 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;
+
+*** ERROR[1337] DB__ROOT is a reserved authorization identifier.
+
+--- SQL operation failed with errors.
+>>
+>>-- fail: try to unregister a user that has not been registered in the db
+>>unregister user non_Existent_User;
+
+*** ERROR[1333] User NON_EXISTENT_USER does not exist.
+
+--- SQL operation failed with errors.
+>>
+>>-- succeed: remove other relationships
+>>showddl $$TEST_CATALOG$$.t136sch.t136t1, privileges;
+
+CREATE TABLE TRAFODION.T136SCH.T136T1
+ (
+ C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , C2 INT DEFAULT NULL
+ , PRIMARY KEY (C1 ASC)
+ )
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T136SCH.T136T1 TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>-- revoke select on $$TEST_CATALOG$$.t136sch.t136t1 from test136_user1;
+>>showddl $$TEST_CATALOG$$.t136sch.t136t1, privileges;
+
+CREATE TABLE TRAFODION.T136SCH.T136T1
+ (
+ C1 INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , C2 INT DEFAULT NULL
+ , PRIMARY KEY (C1 ASC)
+ )
+;
+
+-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T136SCH.T136T1 TO DB__ROOT WITH GRANT OPTION;
+
+--- SQL operation complete.
+>>unregister user test136_user1;
+
+--- SQL operation complete.
+>>grant create on schema $$TEST_CATALOG$$.t136sch to test136_user1;
+
+*** ERROR[4222] The DDL feature is not supported in this software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>showddl schema $$TEST_CATALOG$$.t136sch, privileges;
+
+CREATE PRIVATE SCHEMA "TRAFODION"."T136SCH" AUTHORIZATION "DB__ROOT";
+
+--- SQL operation complete.
+>>revoke create on schema $$TEST_CATALOG$$.t136sch from test136_user1;
+
+*** ERROR[4222] The DDL feature is not supported in this software version.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>showddl schema $$TEST_CATALOG$$.t136sch, privileges;
+
+CREATE PRIVATE SCHEMA "TRAFODION"."T136SCH" AUTHORIZATION "DB__ROOT";
+
+--- SQL operation complete.
+>>unregister user test136_user1;
+
+*** ERROR[1333] User TEST136_USER1 does not exist.
+
+--- SQL operation failed with errors.
+>>
+>>-- Add more complex relationships
+>>-- Add cascade tests later
+>>
+>>obey TEST136(alter_tests);
+>>obey TEST136(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
++>;
+
+--- SQL command prepared.
+>>
+>>-- 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
++>;
+
+--- SQL command prepared.
+>>
+>>log LOG136;
+>>-- =================================================================
+>>-- this set of tests run basic alter user tests
+>>-- =================================================================
+>>execute get_users;
+
+DB_USER_NAME AUTH_EXT_NAME AUTH_ID VALID AUTH_TYPE
+-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- ----- ---------
+
+DB__ROOT ROOT SYSTEM Y USER
+
+--- 1 row(s) selected.
+>>register user "mickey/mouse@disney.com" as mickey;
+
+--- SQL operation complete.
+>>register user "donald/duck@disney.com" as donald;
+
+--- SQL operation complete.
+>>execute get_users;
+
+DB_USER_NAME AUTH_EXT_NAME AUTH_ID VALID AUTH_TYPE
+-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- ----- ---------
+
+DB__ROOT ROOT SYSTEM Y USER
+DONALD DONALD/DUCK@DISNEY.C NORMAL Y USER
+MICKEY MICKEY/MOUSE@DISNEY. NORMAL Y USER
+
+--- 3 row(s) selected.
+>>showddl user mickey;
+
+REGISTER USER "MICKEY/MOUSE@DISNEY.COM" AS "MICKEY";
+
+--- SQL operation complete.
+>>showddl user donald;
+
+REGISTER USER "DONALD/DUCK@DISNEY.COM" AS "DONALD";
+
+--- SQL operation complete.
+>>alter user mickey set external name "mickey/mouse@seaworld.com";
+
+--- SQL operation complete.
+>>showddl user mickey;
+
+REGISTER USER "MICKEY/MOUSE@SEAWORLD.COM" AS "MICKEY";
+
+--- SQL operation complete.
+>>alter user donald set offline;
+
+--- SQL operation complete.
+>>showddl user donald;
+
+REGISTER USER "DONALD/DUCK@DISNEY.COM" AS "DONALD";
+ALTER USER "DONALD" SET
+ OFFLINE;
+
+--- SQL operation complete.
+>>execute get_users;
+
+DB_USER_NAME AUTH_EXT_NAME AUTH_ID VALID AUTH_TYPE
+-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- ----- ---------
+
+DB__ROOT ROOT SYSTEM Y USER
+DONALD DONALD/DUCK@DISNEY.C NORMAL N USER
+MICKEY MICKEY/MOUSE@SEAWORL NORMAL Y USER
+
+--- 3 row(s) selected.
+>>alter user donald set online;
+
+--- SQL operation complete.
+>>showddl user donald;
+
+REGISTER USER "DONALD/DUCK@DISNEY.COM" AS "DONALD";
+
+--- SQL operation complete.
+>>
+>>-- run negative tests
+>>-- error 1333
+>>alter user minnie set offline;
+
+*** ERROR[1333] User MINNIE does not exist.
+
+--- SQL operation failed with errors.
+>>-- error 1333
+>>alter user "donald/duck@disney.com" set offline;
+
+*** ERROR[1333] User DONALD/DUCK@DISNEY.COM does not exist.
+
+--- SQL operation failed with errors.
+>>--error 1337
+>>alter user DB__ROOT set offline;
+
+*** ERROR[1337] DB__ROOT is a reserved authorization identifier.
+
+--- SQL operation failed with errors.
+>>--error 1335
+>>alter user mickey set external name "donald/duck@disney.com";
+
+*** ERROR[1335] Directory service user DONALD/DUCK@DISNEY.COM already defined in the database.
+
+--- SQL operation failed with errors.
+>>
+>>-- cleanup
+>>unregister user mickey;
+
+--- SQL operation complete.
+>>unregister user donald;
+
+--- SQL operation complete.
+>>execute get_users;
+
+DB_USER_NAME AUTH_EXT_NAME AUTH_ID VALID AUTH_TYPE
+-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- ----- ---------
+
+DB__ROOT ROOT SYSTEM Y USER
+
+--- 1 row(s) selected.
+>>
+>>execute get_users;
+
+DB_USER_NAME AUTH_EXT_NAME AUTH_ID VALID AUTH_TYPE
+-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- ----- ---------
+
+DB__ROOT ROOT SYSTEM Y USER
+
+--- 1 row(s) selected.
+>>log;