github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/zz_accesscontrol/create_account.sql (about) 1 set global enable_privilege_cache = off; 2 --env prepare statement 3 drop account if exists `test@123456`; 4 drop account if exists testaccount; 5 drop account if exists 123_acc; 6 drop account if exists _acc; 7 drop account if exists a12; 8 drop account if exists _acc1; 9 drop account if exists FaSt; 10 drop account if exists `123`; 11 drop account if exists a123; 12 drop account if exists `abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffff`; 13 drop account if exists abc; 14 drop account if exists abcd; 15 drop account if exists cm1; 16 drop account if exists cm2; 17 drop account if exists cm3; 18 drop account if exists accout_Xingming_insert; 19 drop account if exists `ab.cd`; 20 drop account if exists `test/123`; 21 drop account if exists `test%`; 22 drop account if exists `非常`; 23 drop account if exists user_strip_01; 24 drop account if exists account_1; 25 drop account if exists aaa; 26 drop account if exists account; 27 drop account if exists ccc; 28 --2.account name字符，数字，特殊字符混合 ,admin_name/auth_string数字英文中文特殊符号组合，特殊字符打头，大小写不敏感，auth_string大小写敏感,覆盖"",'',`` 29 create account `test@123456` ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 30 select account_name from mo_catalog.mo_account where account_name='test@123456'; 31 create account testaccount ADMIN_NAME 'admin@123' IDENTIFIED BY 'yyyy_1234@126.com'; 32 select account_name from mo_catalog.mo_account where account_name='testaccount'; 33 create account 123_acc ADMIN_NAME '8888' IDENTIFIED BY 'ffffff'; 34 select account_name from mo_catalog.mo_account where account_name='123_acc'; 35 create account _acc ADMIN_NAME 'AbcDef' IDENTIFIED BY 'NIU_2345'; 36 select account_name from mo_catalog.mo_account where account_name='_acc'; 37 create account a12 ADMIN_NAME 'a12' IDENTIFIED BY 'aaaaa'; 38 select account_name from mo_catalog.mo_account where account_name='a12'; 39 create account _acc1 ADMIN_NAME '_AbcDef' IDENTIFIED BY '_2345'; 40 select account_name from mo_catalog.mo_account where account_name='_acc1'; 41 create account a123 ADMIN_NAME 'a12' IDENTIFIED BY 'aaaaa'; 42 select account_name from mo_catalog.mo_account where account_name='a12'; 43 create account FaSt ADMIN_NAME '账号' IDENTIFIED BY '账号密码'; 44 select account_name from mo_catalog.mo_account where account_name='fast'; 45 create account `ab.cd` ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 46 select account_name from mo_catalog.mo_account where account_name='ab.cd'; 47 create account `test/123` ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 48 select account_name from mo_catalog.mo_account where account_name='test/123'; 49 create account `test%` ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 50 select account_name from mo_catalog.mo_account where account_name='test%'; 51 create account `123` ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 52 select account_name from mo_catalog.mo_account where account_name='123'; 53 create account `非常` ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 54 select account_name from mo_catalog.mo_account where account_name='非常'; 55 56 --3.account name,admin_name,auth_string长度180字符, "",'',``前后空格清除 57 create account `abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffff` admin_name `abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffffdddddd` IDENTIFIED BY '1111111111111111111111111111111111111111111111111111111'; 58 select account_name from mo_catalog.mo_account where account_name='abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffff'; 59 create account ` user_strip_01 ` ADMIN_NAME " admin " IDENTIFIED BY " 123456 "; 60 select account_name from mo_catalog.mo_account where account_name='user_strip_01'; 61 62 --4.account name账户已存在,大小写敏感，if exist存在不存在,comment关键字中文字符英文长度 63 create account abc ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 64 select account_name from mo_catalog.mo_account where account_name='abc'; 65 create account ABC ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 66 create account Abc ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 67 create account IF NOT EXISTS Abc ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 68 create account IF NOT EXISTS Abcd ADMIN_NAME 'admin' IDENTIFIED BY '123456' comment '这是comment备注/123456'; 69 select account_name,comments from mo_catalog.mo_account where account_name='abcd'; 70 create account IF NOT EXISTS cm1 ADMIN_NAME 'admin' IDENTIFIED BY '123456' comment 'this is test comment , please check'; 71 select account_name,comments from mo_catalog.mo_account where account_name='cm1'; 72 create account cm2 ADMIN_NAME 'admin' IDENTIFIED BY '123456' comment 'abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffff'; 73 select account_name,comments from mo_catalog.mo_account where account_name='cm2'; 74 create account cm3 ADMIN_NAME 'admin' IDENTIFIED BY '123456' comment ''; 75 select account_name,comments from mo_catalog.mo_account where account_name='cm3'; 76 77 --5.异常测试：语法不合法 78 create accout a1 ADMIN_NAME ' admin' IDENTIFIED BY '123456'; 79 create account a1; 80 create accout a1 ADMIN_NAME ' admin'; 81 create accout a1 IDENTIFIED BY '123456'; 82 create accout a1 comment'aaaa'; 83 create account 123 ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 84 create account 非常 ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 85 create account "acc1" ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 86 create account 'acc1' ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 87 88 --6.异常值:account name/admin_name/auth_string为空值，冒号，关键字 89 create account bbb ADMIN_NAME '' IDENTIFIED BY '123456'; 90 create account `` ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 91 create account aaa ADMIN_NAME 'admin' IDENTIFIED BY ''; 92 create account test:account ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 93 create account 'test:account' ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 94 create account "test:account" ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 95 create account `test:account` ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 96 create account default ADMIN_NAME 'root' IDENTIFIED BY '123456'; 97 create account account ADMIN_NAME 'root' IDENTIFIED BY '123456'; 98 select count(*) from mo_catalog.mo_account where account_name in ('test:account','default','account'); 99 100 -- 7.account初始accountamdin权限验证：查询系统表;创建db，user，table;sys租户下root看不到account下的系统表数据 101 create account account_1 admin_name='admin' identified by '123456'; 102 -- @session:id=2&user=account_1:admin:accountadmin&password=123456 103 show databases; 104 use mo_catalog; 105 show tables; 106 select user_name,authentication_string,owner from mo_user; 107 select role_name,obj_type,privilege_name,privilege_level from mo_role_privs; 108 create database account_1_db; 109 CREATE USER account_1_user IDENTIFIED BY '123456'; 110 create table a(b int); 111 use account_1_db; 112 create table a(b int); 113 -- @session 114 use mo_catalog; 115 select user_name,authentication_string,owner from mo_user where user_name ='account_1_user'; 116 117 --8.CREATE-DROP-CREATE,CREATE-ALTER-CREATE场景 118 create account accout_Xingming_insert ADMIN_NAME 'root' IDENTIFIED BY '123456789'; 119 select account_name from mo_catalog.mo_account where account_name='accout_xingming_insert'; 120 drop account accout_Xingming_insert; 121 select account_name from mo_catalog.mo_account where account_name='accout_xingming_insert'; 122 create account if not exists accout_Xingming_insert ADMIN_NAME 'root' IDENTIFIED BY '123456789'; 123 select account_name from mo_catalog.mo_account where account_name='accout_xingming_insert'; 124 125 --9.CREATE ACCOUNT support prepared statement 126 PREPARE s1 FROM "create account ? ADMIN_NAME ? IDENTIFIED BY ?"; 127 set @a_var = 'ccc'; 128 set @b_var = 'admin'; 129 set @c_var = '111'; 130 EXECUTE s1 USING @a_var, @b_var, @c_var; 131 DEALLOCATE PREPARE s1; 132 select account_name from mo_catalog.mo_account where account_name='ccc'; 133 134 drop account ccc; 135 PREPARE s1 FROM "create account ? ADMIN_NAME 'admin' IDENTIFIED BY ?"; 136 set @a_var = 'ccc'; 137 set @c_var = '222'; 138 EXECUTE s1 USING @a_var, @c_var; 139 DEALLOCATE PREPARE s1; 140 -- @session:id=3&user=ccc:admin&password=222 141 select 1; 142 -- @session 143 144 drop account if exists `test@123456`; 145 drop account if exists testaccount; 146 drop account if exists 123_acc; 147 drop account if exists _acc; 148 drop account if exists a12; 149 drop account if exists _acc1; 150 drop account if exists FaSt; 151 drop account if exists `123`; 152 drop account if exists a123; 153 drop account if exists `abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffff`; 154 drop account if exists abc; 155 drop account if exists abcd; 156 drop account if exists cm1; 157 drop account if exists cm2; 158 drop account if exists cm3; 159 drop account if exists accout_Xingming_insert; 160 drop account if exists `ab.cd`; 161 drop account if exists `test/123`; 162 drop account if exists `test%`; 163 drop account if exists `非常`; 164 drop account if exists user_strip_01; 165 drop account if exists account_1; 166 drop account if exists aaa; 167 drop account if exists account; 168 drop account if exists ccc; 169 set global enable_privilege_cache = on;