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