创建新租户,并由新租户创建用户、创建角色和授权

初始化接入 MatrixOne 集群,系统会自动生成一个默认账号,即集群管理员。集群管理员默认用户名为 rootroot 既是集群管理员,同时也是系统租户管理员,root 可以创建和管理其他普通租户(非系统租户管理员)。

本篇文档将指导你创建一个新的租户,并切换至新租户登录,用新租户账号创建用户、创建角色、创建权限,并赋予用户权限。

前提条件

  • 已完成 MatrixOne 集群的部署与连接。
  • 已获取集群管理员用户名和密码(默认初始用户名为 root,密码为 111)。

操作步骤

步骤一:创建新租户

  1. 使用集群管理员的用户名(默认 root)和密码登录 MatrixOne:

    1. mysql -h 127.0.0.1 -P 6001 -u root -p
  2. 创建一个新的租户 a1,用户名和密码分别为:admin,test123:

    1. create account a1 ADMIN_NAME 'admin' IDENTIFIED BY 'test123';

    查看集群中的所有租户信息(仅 root 可查看):

    1. mysql> select * from mo_catalog.mo_account;
    2. +------------+--------------+--------+---------------------+----------------+----------------+
    3. | account_id | account_name | status | created_time | comments | suspended_time |
    4. +------------+--------------+--------+---------------------+----------------+----------------+
    5. | 1 | a1 | open | 2022-12-19 14:47:19 | | NULL |
    6. | 0 | sys | open | 2022-12-07 11:00:58 | system account | NULL |
    7. +------------+--------------+--------+---------------------+----------------+----------------+

步骤二:登录新租户账号,创建用户、创建角色和授权

  1. 你可以重新打开一个新的会话,使用 admin 登录租户 a1

    1. mysql -h 127.0.0.1 -P 6001 -u a1:admin -p
  2. 现在你可以作为租户 a1 查看租户下的默认用户和角色:

    1. mysql> select * from mo_catalog.mo_role;
    2. +---------+--------------+---------+-------+---------------------+----------+
    3. | role_id | role_name | creator | owner | created_time | comments |
    4. +---------+--------------+---------+-------+---------------------+----------+
    5. | 2 | accountadmin | 0 | 0 | 2022-12-19 14:47:20 | |
    6. | 1 | public | 0 | 0 | 2022-12-19 14:47:20 | |
    7. +---------+--------------+---------+-------+---------------------+----------+
    8. 2 rows in set (0.01 sec)
    9. mysql> select * from mo_catalog.mo_user;
    10. +---------+-----------+-----------+-----------------------+--------+---------------------+--------------+------------+---------+-------+--------------+
    11. | user_id | user_host | user_name | authentication_string | status | created_time | expired_time | login_type | creator | owner | default_role |
    12. +---------+-----------+-----------+-----------------------+--------+---------------------+--------------+------------+---------+-------+--------------+
    13. | 2 | localhost | admin | test123 | unlock | 2022-12-19 14:47:20 | NULL | PASSWORD | 0 | 0 | 2 |
    14. +---------+-----------+-----------+-----------------------+--------+---------------------+--------------+------------+---------+-------+--------------+
    15. 1 row in set (0.00 sec)

    租户 a1 被创建成功后便默认拥有了租户管理员权限,所以可以查看租户 a1 下的系统表信息。在 mo_user 表中可以观察到当前有一个用户名为 admin 的用户账号,即创建租户时指定的;此外,还有 accountadminpublic 两个默认角色:

    • accountadmin 拥有租户的最高权限,且默认授予用户名为 admin 的账号;
    • 系统会为每一个新的普通用户默认授权 public 角色,public 角色初始化的权限是 connect,即连接 MatrixOne。

    此外,你还可以在系统表中查看到这些默认角色的权限集合:

    1. mysql> select * from mo_catalog.mo_role_privs;
    2. +---------+--------------+----------+--------+--------------+--------------------+-----------------+-------------------+---------------------+-------------------+
    3. | role_id | role_name | obj_type | obj_id | privilege_id | privilege_name | privilege_level | operation_user_id | granted_time | with_grant_option |
    4. +---------+--------------+----------+--------+--------------+--------------------+-----------------+-------------------+---------------------+-------------------+
    5. | 2 | accountadmin | account | 0 | 3 | create user | * | 0 | 2022-12-19 14:47:20 | true |
    6. | 2 | accountadmin | account | 0 | 4 | drop user | * | 0 | 2022-12-19 14:47:20 | true |
    7. | 2 | accountadmin | account | 0 | 5 | alter user | * | 0 | 2022-12-19 14:47:20 | true |
    8. | 2 | accountadmin | account | 0 | 6 | create role | * | 0 | 2022-12-19 14:47:20 | true |
    9. | 2 | accountadmin | account | 0 | 7 | drop role | * | 0 | 2022-12-19 14:47:20 | true |
    10. | 2 | accountadmin | account | 0 | 9 | create database | * | 0 | 2022-12-19 14:47:20 | true |
    11. | 2 | accountadmin | account | 0 | 10 | drop database | * | 0 | 2022-12-19 14:47:20 | true |
    12. | 2 | accountadmin | account | 0 | 11 | show databases | * | 0 | 2022-12-19 14:47:20 | true |
    13. | 2 | accountadmin | account | 0 | 12 | connect | * | 0 | 2022-12-19 14:47:20 | true |
    14. | 2 | accountadmin | account | 0 | 13 | manage grants | * | 0 | 2022-12-19 14:47:20 | true |
    15. | 2 | accountadmin | account | 0 | 14 | account all | * | 0 | 2022-12-19 14:47:20 | true |
    16. | 2 | accountadmin | database | 0 | 18 | show tables | * | 0 | 2022-12-19 14:47:20 | true |
    17. | 2 | accountadmin | database | 0 | 20 | create table | * | 0 | 2022-12-19 14:47:20 | true |
    18. | 2 | accountadmin | database | 0 | 23 | drop table | * | 0 | 2022-12-19 14:47:20 | true |
    19. | 2 | accountadmin | database | 0 | 26 | alter table | * | 0 | 2022-12-19 14:47:20 | true |
    20. | 2 | accountadmin | database | 0 | 21 | create view | * | 0 | 2022-12-19 14:47:20 | true |
    21. | 2 | accountadmin | database | 0 | 24 | drop view | * | 0 | 2022-12-19 14:47:20 | true |
    22. | 2 | accountadmin | database | 0 | 27 | alter view | * | 0 | 2022-12-19 14:47:20 | true |
    23. | 2 | accountadmin | database | 0 | 28 | database all | * | 0 | 2022-12-19 14:47:20 | true |
    24. | 2 | accountadmin | database | 0 | 29 | database ownership | * | 0 | 2022-12-19 14:47:20 | true |
    25. | 2 | accountadmin | table | 0 | 30 | select | *.* | 0 | 2022-12-19 14:47:20 | true |
    26. | 2 | accountadmin | table | 0 | 31 | insert | *.* | 0 | 2022-12-19 14:47:20 | true |
    27. | 2 | accountadmin | table | 0 | 32 | update | *.* | 0 | 2022-12-19 14:47:20 | true |
    28. | 2 | accountadmin | table | 0 | 33 | truncate | *.* | 0 | 2022-12-19 14:47:20 | true |
    29. | 2 | accountadmin | table | 0 | 34 | delete | *.* | 0 | 2022-12-19 14:47:20 | true |
    30. | 2 | accountadmin | table | 0 | 35 | reference | *.* | 0 | 2022-12-19 14:47:20 | true |
    31. | 2 | accountadmin | table | 0 | 36 | index | *.* | 0 | 2022-12-19 14:47:20 | true |
    32. | 2 | accountadmin | table | 0 | 37 | table all | *.* | 0 | 2022-12-19 14:47:20 | true |
    33. | 2 | accountadmin | table | 0 | 38 | table ownership | *.* | 0 | 2022-12-19 14:47:20 | true |
    34. | 2 | accountadmin | table | 0 | 41 | values | t | 0 | 2022-12-19 14:47:20 | true |
    35. | 1 | public | account | 0 | 12 | connect | * | 0 | 2022-12-19 14:47:20 | true |
    36. +---------+--------------+----------+--------+--------------+--------------------+-----------------+-------------------+---------------------+-------------------+
  3. 在租户 a1 中,创建新的用户和角色:

    • 用户 u1 的用户名和密码分别为:u1,user123
    • 用户 u2 的用户名和密码分别为:u2,user456
    • 角色 r1 的命名为:r1
    • 角色 r2 的命名为:r2
    1. create user u1 identified by 'user123';
    2. create user u2 identified by 'user456';
    3. create role r1;
    4. create role r2;
  4. 创建数据库 db1,并在 db1 中创建表 t1

    1. create database db1;
    2. create table db1.t1(c1 int,c2 varchar);
  5. db1.t1select 权限授予给 r1insert 权限授予给 r2

    1. grant select on table db1.t1 to r1;
    2. grant insert on table db1.t1 to r2;
  6. 将角色 r1 授予给用户 u1;将角色 r2 授予给用户 u2

    1. grant r1 to u1;
    2. grant r2 to u2;

    此时,新建的用户、角色、对象权限关系如下图所示:

    新租户创建用户、创建角色和授权 - 图1

步骤三:验证授权生效

分别使用用户 u1u2 登录租户 a1,验证权限是否生效。

由于 u2 被授予了 r2 角色,且 r2 被授予了 db1.t1insert 权限,所以 u2 具备 db1.t1insert 权限,即可以向 db1.t1 插入数据,

使用 u1 登录 a1 进行验证:

  1. mysql -h 127.0.0.1 -P 6001 -u a1:u2:r2 -p
  2. mysql> insert into db1.t1 values (1,'shanghai'),(2,'beijing');
  3. Query OK, 2 rows affected (0.04 sec)
  4. mysql> select * from db1.t1;
  5. ERROR 20101 (HY000): internal error: do not have privilege to execute the statement

u2 可以成功向表 db1.t1 插入数据,但无法查看 db1.t1 表里的数据。

同样的,你可以使用 u1 登录 a1 进行权限验证:

  1. mysql -h 127.0.0.1 -P 6001 -u a1:u1:r1 -p
  2. mysql> select * from db1.t1;
  3. +------+----------+
  4. | c1 | c2 |
  5. +------+----------+
  6. | 1 | shanghai |
  7. | 2 | beijing |
  8. +------+----------+
  9. 2 rows in set (0.01 sec)
  10. mysql> insert into db1.t1 values (3,'guangzhou');
  11. ERROR 20101 (HY000): internal error: do not have privilege to execute the statement

如上述代码所示,u1 可以成功的查询表 db1.t1 的数据,但不能向其插入数据。

Note

上述操作步骤中,更多有关查看的系统表信息,参见 MatrixOne 系统数据库和表