github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/zz_accesscontrol/grant_privs_role.sql (about) 1 set global enable_privilege_cache = off; 2 --env prepare statement 3 drop user if exists user1,user2,user3,user4,user5,user11,user12,testuser,user_grant_1,user_grant_3,user_grant_4,user_grant_5,user_grant_6,user_grant_7,user_grant_8,user_grant_9,user_grant_10,user_prepare_01; 4 drop role if exists u_role,test_role,grant_role_1,role_sys_priv,role_account_priv_2,role_account_priv_3,role_account_priv_4,role_account_priv_5,role_account_priv_6,role_account_priv_7,role_account_priv_8,role_account_priv_9,role_account_priv_10,role_prepare_1; 5 drop database if exists grant_db; 6 drop database if exists testdb; 7 drop database if exists testdb4; 8 drop database if exists testdb5; 9 drop database if exists grant_db4; 10 drop database if exists grant_db5; 11 drop account if exists grant_account01; 12 drop table if exists table_4; 13 drop table if exists grant_table_10; 14 drop table if exists grant_table_30; 15 create account grant_account01 admin_name='admin' identified by '123456'; 16 create database grant_db; 17 18 --覆盖语法语义:部分授权，all，allowership，with grant option 19 create role test_role; 20 create database testdb; 21 create user testuser IDENTIFIED BY '123456'; 22 grant select,insert,update on table testdb.* to test_role with grant option; 23 select privilege_name,obj_type,privilege_level,with_grant_option from mo_catalog.mo_role_privs where role_name='test_role'; 24 grant all on account * to test_role; 25 select privilege_name,obj_type,privilege_level,with_grant_option from mo_catalog.mo_role_privs where role_name='test_role'; 26 grant OWNERSHIP on database *.* to test_role; 27 grant OWNERSHIP on table *.* to test_role; 28 select privilege_name,obj_type,privilege_level,with_grant_option from mo_catalog.mo_role_privs where role_name='test_role'; 29 30 --grant privs异常测试：object_type和priv_level不对应，语法错误：缺省object_type，role不存在，权限直接赋予用户,grant相同role 31 grant select,insert,update on testdb.* to test_role; 32 grant select,insert,update on account * to 'test_role'; 33 grant show tables,create,drop,alter on testdb.* to 'test_role'; 34 grant show tables,create,drop,alter on table testdb.* to 'test_role'; 35 grant select,insert,create database on table testdb.* to test_role; 36 grant select,insert,update on table to 'test_role'; 37 grant select,insert,update on table testdb.* to 'trole'; 38 grant select,insert,create database on account * to test_role; 39 grant select,insert,create database on account *.* to testuser; 40 41 --grant role异常测试：role不存在,grant相同role，重新授权给moadmin 42 grant role_not_exists to dump; 43 grant moadmin to user_not_exists; 44 create role grant_role_1; 45 grant create account, drop account, alter account on * to 'grant_role_1' with grant option; 46 grant grant_role_1 to grant_role_1; 47 grant show tables,create table,drop table on database * to moadmin; 48 49 -- sys下 role赋予account privs 50 create user 'user_grant_1' identified by '123456'; 51 create role 'role_sys_priv'; 52 grant create account, drop account, alter account on account * to role_sys_priv; 53 grant role_sys_priv to user_grant_1; 54 -- @session:id=2&user=sys:user_grant_1:role_sys_priv&password=123456 55 create account account01 admin_name='admin' identified by '123456'; 56 drop account account01 ; 57 -- @session 58 59 -- priv_type ：account level 覆盖所有权限，验证所有授权生效;object_type: account缺省/不缺省，priv_level：* 60 -- @session:id=3&user=grant_account01:admin&password=123456 61 create user if not exists user_grant_2 identified by '123456'; 62 create role if not exists 'role_account_priv_1'; 63 grant create user, drop user, alter user, create role, drop role, create database,drop database,show databases,connect,manage grants on account * to role_account_priv_1 with grant option; 64 grant select on table *.* to role_account_priv_1; 65 grant role_account_priv_1 to user_grant_2; 66 select user_name,role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_user_grant,mo_catalog.mo_user,mo_catalog.mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name='role_account_priv_1'; 67 -- @session 68 -- @session:id=4&user=grant_account01:user_grant_2:role_account_priv_1&password=123456 69 create user user_test_2 identified by '123456'; 70 select user_name,authentication_string from mo_catalog.mo_user where user_name='user_test_2'; 71 create role if not exists role_test_01; 72 select role_name from mo_catalog.mo_role where role_name='role_test_01'; 73 grant create user, drop user on account * to role_test_01; 74 grant insert,select on table *.* to role_test_01; 75 grant role_test_01 to user_test_2; 76 select user_name,role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_user_grant,mo_catalog.mo_user,mo_catalog.mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name='role_test_01'; 77 drop role role_test_01; 78 select role_name from mo_catalog.mo_role where role_name='role_test_01'; 79 drop user user_test_2; 80 select user_name,authentication_string from mo_catalog.mo_user where user_name='user_test_2'; 81 create database db_test_01; 82 use db_test_01; 83 drop database db_test_01; 84 -- @session 85 86 --priv_type ：account level 覆盖all权限,ownership权限0.6不支持;object_type: account，priv_level：* 87 create user 'user_grant_3' identified by '123456'; 88 create role 'role_account_priv_2'; 89 grant all on account * to 'role_account_priv_2'; 90 grant role_account_priv_2 to user_grant_3; 91 select user_name,role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_user_grant,mo_catalog.mo_user,mo_catalog.mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name='role_account_priv_2' with grant option ; 92 -- @session:id=5&user=sys:user_grant_3:role_account_priv_2&password=123456 93 create user 'user_test_3' identified by '123456'; 94 create role if not exists role_test_02; 95 grant all on account * to role_test_02; 96 grant role_test_02 to user_test_3; 97 drop role role_test_02; 98 create database db_test_01; 99 use db_test_01; 100 drop database db_test_01; 101 drop user user_test_3; 102 -- @session 103 104 --priv_type ：database level 覆盖所有权限;object_type: database；priv_level：* 105 create user 'user_grant_4' identified by '123456'; 106 create role 'role_account_priv_3'; 107 grant show tables,create table ,drop table,alter table on database grant_db to role_account_priv_3; 108 grant connect on account * to role_account_priv_3; 109 grant role_account_priv_3 to user_grant_4; 110 select user_name,role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_user_grant,mo_catalog.mo_user,mo_catalog.mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name='role_account_priv_3'; 111 -- @session:id=6&user=sys:user_grant_4:role_account_priv_3&password=123456 112 use grant_db; 113 show tables; 114 create table grant_table_03 (id int,name varchar(50),num double)PARTITION BY KEY(id) PARTITIONS 4; 115 show create table grant_table_03; 116 create view grant_v_1 as select * from grant_table_03; 117 drop table grant_table_03; 118 drop view grant_v_1; 119 -- @session 120 121 --priv_type ：database level all权限;object_type: database；priv_level：* 122 create user 'user_grant_5' identified by '123456'; 123 create role 'role_account_priv_4'; 124 create database grant_db4; 125 create database grant_db5; 126 grant all on database grant_db4 to role_account_priv_4; 127 grant connect on account * to role_account_priv_4; 128 grant role_account_priv_4 to user_grant_5; 129 select user_name,role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_user_grant,mo_catalog.mo_user,mo_catalog.mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name='role_account_priv_4'; 130 -- @session:id=7&user=sys:user_grant_5:role_account_priv_4&password=123456 131 use grant_db4; 132 show tables; 133 create table grant_table_04 (id int,name varchar(50),num double)PARTITION BY KEY(id) PARTITIONS 4; 134 show create table grant_table_04; 135 create view grant_v_2 as select * from grant_table_04; 136 drop table grant_table_04; 137 drop view grant_v_2; 138 use grant_db5; 139 show tables; 140 create table grant_table_04 (id int,name varchar(50),num double)PARTITION BY KEY(id) PARTITIONS 4; 141 -- @session 142 143 --priv_type ：database level ownership权限;object_type: database；priv_level：*，*.*,db_name(bug) 144 create user 'user_grant_6' identified by '123456'; 145 create role 'role_account_priv_5',role_account_priv_6; 146 grant ownership on database * to role_account_priv_5; 147 grant create role,connect on account * to role_account_priv_5; 148 grant role_account_priv_5 to user_grant_6; 149 select user_name,role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_user_grant,mo_catalog.mo_user,mo_catalog.mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name='role_account_priv_5'; 150 -- @session:id=8&user=sys:user_grant_6:role_account_priv_5&password=123456 151 use grant_db; 152 show tables; 153 create table grant_table_05 (id int,name varchar(50),num double)PARTITION BY KEY(id) PARTITIONS 4; 154 create role 'role_account_priv_10'; 155 grant create table,drop table on database * to role_account_priv_10; 156 -- @session 157 drop role role_account_priv_6; 158 159 --object_type: table level；priv_type ：select ,insert ,update权限;priv_level：*.* 160 create database testdb4; 161 create database testdb5; 162 create table testdb4.table_1(id int,name varchar(50),num double); 163 insert into testdb4.table_1 values (1,'banana',83.98),(2,'apple',0.003); 164 create table testdb5.table_2(id int,name varchar(50),num double); 165 insert into testdb5.table_2 values (3,'pear',3.8),(4,'orange',5.03); 166 create table testdb5.table_3(id int,name varchar(50),num double); 167 insert into testdb5.table_3 values (5,'aaa',3.8),(6,'bbb',5.03); 168 create table table_4(id int,name varchar(50),num double); 169 insert into table_4 values (7,'ccc',1.8),(8,'ddd',5.3); 170 create user 'user_grant_7' identified by '123456'; 171 create role 'role_account_priv_6'; 172 grant select ,insert ,update on table *.* to role_account_priv_6; 173 grant role_account_priv_6 to user_grant_7; 174 select user_name,role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_user_grant,mo_catalog.mo_user,mo_catalog.mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name='role_account_priv_6'; 175 -- @session:id=9&user=sys:user_grant_7:role_account_priv_6&password=123456 176 select * from testdb5.table_2; 177 insert into testdb5.table_2 values (9,'',8.00); 178 select * from testdb5.table_2; 179 update testdb4.table_1 set name='uuu' where id=2; 180 select * from testdb4.table_1; 181 delete from testdb4.table_1; 182 -- @session 183 184 --object_type: table level；priv_type ：truncate ,delete ,references ,index权限;priv_level：daname.* 185 create user 'user_grant_8' identified by '123456'; 186 create role 'role_account_priv_7'; 187 grant truncate ,delete ,reference ,index on table testdb4.* to role_account_priv_7; 188 grant role_account_priv_7 to user_grant_8; 189 select user_name,role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_user_grant,mo_catalog.mo_user,mo_catalog.mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name='role_account_priv_7'; 190 -- @session:id=10&user=sys:user_grant_8:role_account_priv_7&password=123456 191 --truncate table testdb4.table_1; 192 delete from testdb4.table_1; 193 select * from testdb4.table_1; 194 --create max index index_1 on testdb4.table_1(id); 195 --drop index index_1; 196 truncate table testdb5.table_2; 197 -- @session 198 199 --object_type: table level；priv_type ：ownership;priv_level：db_name.tblname 200 create user 'user_grant_9' identified by '123456'; 201 create role 'role_account_priv_8'; 202 grant ownership on table testdb5.table_2 to role_account_priv_8; 203 grant role_account_priv_8 to user_grant_9; 204 select user_name,role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_user_grant,mo_catalog.mo_user,mo_catalog.mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name='role_account_priv_8'; 205 -- @session:id=11&user=sys:user_grant_9:role_account_priv_8&password=123456 206 select * from testdb4.table_1; 207 delete from testdb4.table_1; 208 insert into testdb5.table_2 values(20,'yeah',10.20); 209 update testdb5.table_2 set name='bread' where id=20; 210 select * from testdb5.table_2; 211 -- @session 212 213 --object_type: table level；priv_type ：all;priv_level：daname.* 214 create user 'user_grant_10' identified by '123456'; 215 create role 'role_account_priv_9'; 216 create table grant_table_10(a int); 217 grant all on table testdb4.* to role_account_priv_9; 218 grant role_account_priv_9 to user_grant_10; 219 select user_name,role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_user_grant,mo_catalog.mo_user,mo_catalog.mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name='role_account_priv_9'; 220 -- @session:id=12&user=sys:user_grant_10:role_account_priv_9&password=123456 221 insert into testdb4.table_1 values (10,'ccc',1.8),(11,'ddd',5.3); 222 select * from testdb4.table_1; 223 update testdb4.table_1 set name='oppo' where id=10; 224 delete from testdb4.table_1; 225 select * from testdb4.table_1; 226 delete from testdb5.table_2; 227 show tables; 228 create database ttt; 229 create account `test@123456` ADMIN_NAME 'admin' IDENTIFIED BY '123456'; 230 drop table testdb4.table_1; 231 -- @session 232 233 --多个权限授权给多个role 234 drop role if exists r1,r2,r3,r4,r5,r6,r7,r8,r9,r10; 235 create role r1,r2,r3,r4,r5,r6,r7,r8,r9,r10; 236 grant select,insert ,update on table *.* to r1,r2,r3,r4,r5; 237 select role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_role_privs where role_name in ('r1','r2','r3','r4','r5'); 238 239 --一个权限授权给多个role,异常情况：权限存在不合法，role不存在情况 240 grant create table on database *.* to r1,r2,r3,r4,r5; 241 select role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_role_privs where role_name in ('r1','r2','r3','r4','r5') and obj_type="database"; 242 grant create table on database *.* to r1,r2,r15,r4,r5; 243 grant select on database *.* to r1,r2,r3,r4,r5; 244 245 --多个role授权给多个用户 246 create user user1 identified by '12345678',user2 identified by '12345678',user3 identified by '12345678',user4 identified by '12345678',user5 identified by '12345678'; 247 grant r1,r2,r3,r4,r5 to user1,user2,user3,user4,user5; 248 select count(*) from mo_catalog.mo_user_grant,mo_catalog.mo_user,mo_catalog.mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name in ('r1','r2','r3','r4','r5'); 249 -- @session:id=13&user=sys:user1:r1&password=12345678 250 create table grant_table_10(a int); 251 -- @session 252 253 --一个role授权给多个用户 254 grant create role on account * to r5; 255 grant r5 to user1,user2,user3,user4,user5; 256 select user_name,role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_user_grant,mo_catalog.mo_user,mo_catalog.mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name in ('r5'); 257 -- @session:id=14&user=sys:user3:r5&password=12345678 258 create role test_role; 259 select count(*) from mo_catalog.mo_role where role_name='test_role'; 260 -- @session 261 262 --多个role授权给多个role 263 create user user11 identified by '12345678'; 264 grant select ,insert ,update on table *.* to r1,r2 with grant option; 265 grant r1,r2 to r6,r7; 266 select mr.role_name,mp.role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_role_grant mg,mo_catalog.mo_role mr ,mo_catalog.mo_role_privs mp where mg.grantee_id=mr.role_id and mg.granted_id = mp.role_id and mr.role_name in ('r6','r7'); 267 268 --一个role授权给多个role 269 create user user12 identified by '12345678'; 270 grant r2 to r8,r9,r10; 271 select mr.role_name,mp.role_name,obj_type,privilege_name,privilege_level from mo_role_grant mg,mo_role mr ,mo_role_privs mp where mg.grantee_id=mr.role_id and mg.granted_id = mp.role_id and mr.role_name in ('r8','r9','10'); 272 273 --权限重复授权给role，role重复授权user，role重复授权给role 274 grant select ,insert ,update on table *.* to r1,r2 with grant option; 275 grant select ,insert ,update on table *.* to r1,r2 with grant option; 276 select role_name,obj_type,privilege_name,privilege_level,with_grant_option from mo_catalog.mo_role_privs where role_name in ('r1','r2'); 277 grant r1,r2 to user1,user2; 278 grant r1,r2 to user1,user2; 279 select user_name,role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_user_grant,mo_catalog.mo_user,mo_catalog.mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and user_name in ('user1','user2') and role_name in ('r1','r2'); 280 grant r1,r2 to r6,r7; 281 grant r1,r2 to r6,r7; 282 select mr.role_name,mp.role_name,obj_type,privilege_name,privilege_level from mo_catalog.mo_role_grant mg,mo_catalog.mo_role mr ,mo_catalog.mo_role_privs mp where mg.grantee_id=mr.role_id and mg.granted_id = mp.role_id and mr.role_name in ('r6','r7'); 283 284 drop user if exists user1,user2,user3,user4,user5,user11,user12,testuser,user_grant_1,user_grant_3,user_grant_4,user_grant_5,user_grant_6,user_grant_7,user_grant_8,user_grant_9,user_grant_10,user_prepare_01; 285 drop role if exists u_role,test_role,grant_role_1,role_sys_priv,role_account_priv_2,role_account_priv_3,role_account_priv_4,role_account_priv_5,role_account_priv_6,role_account_priv_7,role_account_priv_8,role_account_priv_9,role_account_priv_10,role_prepare_1; 286 drop database if exists grant_db; 287 drop database if exists testdb; 288 drop database if exists testdb4; 289 drop database if exists testdb5; 290 drop database if exists grant_db4; 291 drop database if exists grant_db5; 292 drop account if exists grant_account01; 293 drop table if exists table_4; 294 drop table if exists grant_table_10; 295 drop table if exists grant_table_30; 296 drop role if exists r1,r2,r3,r4,r5,r6,r7,r8,r9,r10; 297 set global enable_privilege_cache = on;