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