Create a new account, use the new account, creates users, create roles, and grant the privilege

When initializing access to the MatrixOne cluster, the system will automatically generate a default account, which is the cluster administrator. The default user name of the cluster administrator is root. root is both a cluster administrator and a system account administrator. root can create and manage other common accounts (non-system account administrators).

This document will guide you through creating a new account, switching to the new account to log in, using the new account account to create users, creating roles, creating permissions, and granting user privileges.

Before you start

  • MatrixOne cluster has been depolyed and connected.
  • You have obtained the cluster administrator user name and password (The default user name and password are root and 111 respectively).

Steps

Step 1: Create a new account

  1. Log into MatrixOne with the cluster administrator’s username (root by default) and password:

    1. mysql -h 127.0.0.1 -P 6001 -u root -p

  2. Create a new account a1; the username and password of account a1 are: admin, test123:

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

    Check all accounts information in the cluster (only root can view):

    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. +------------+--------------+--------+---------------------+----------------+----------------+

Step 2: Log in to the new account account, create users, create roles and grant the privilege

  1. You can reopen a new session with admin login to account a1:

    1. mysql -h 127.0.0.1 -P 6001 -u a1:admin -p

  2. Now you can check the default users and roles under the account as 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)
    10. mysql> select * from mo_catalog.mo_user;
    11. +---------+-----------+-----------+-----------------------+--------+---------------------+--------------+------------+---------+-------+--------------+
    12. | user_id | user_host | user_name | authentication_string | status | created_time        | expired_time | login_type | creator | owner | default_role |
    13. +---------+-----------+-----------+-----------------------+--------+---------------------+--------------+------------+---------+-------+--------------+
    14. |       2 | localhost | admin     | test123               | unlock | 2022-12-19 14:47:20 | NULL         | PASSWORD   |       0 |     0 |            2 |
    15. +---------+-----------+-----------+-----------------------+--------+---------------------+--------------+------------+---------+-------+--------------+
    16. 1 row in set (0.00 sec)

    After account a1 is successfully created, it has account administrator privileges by default so that you can view the system table information under account a1. In the mo_user table, it can be observed that there is currently a user account named admin, which is specified when creating a account; in addition, there are two default roles, accountadmin and public:

    • accountadmin has the highest authority of the account, and the account named admin is granted by default;
    • The system will authorize the public role for each new ordinary user by default, and the initial permission of the public role is connect, that is, to connect to MatrixOne.

    In addition, you can also view the privilege sets of these default roles in the system table:

    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. In account a1, create a new user and role:

    • Username and password for user u1 are: u1, user123
    • Username and password for user u2 are: u2, user456
    • Role r1 is named: r1
    • Role r2 is named: r2
    1. create user u1 identified by 'user123';
    2. create user u2 identified by 'user456';
    3. create role r1;
    4. create role r2;

  4. Create database db1 and create table t1 in db1:

    1. create database db1;
    2. create table db1.t1(c1 int,c2 varchar);

  5. Grant select privilege on db1.t1 to r1 and insert privilege to r2:

    1. grant select on table db1.t1 to r1;
    2. grant insert on table db1.t1 to r2;

  6. Grant role r1 to user u1; grant role r2 to user u2:

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

    At this point, the newly created user, role, and object permission relationship is shown in the following figure:

    Use the new account to creates users, roles, grant the privilege - 图1

Step 3: Verify the privilege is valid

Log in to account a1 with users u1 and u2, respectively, to verify that the privileges are in effect.

Since u2 has been granted the r2 role, and r2 has been granted the insert privilege of db1.t1, so u2 has the insert privilege of db1.t1, that is, db1.t1 insert data.

Use u1 to log into a1 to verify that the privilege is valid:

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

u2 can successfully insert data into the table db1.t1, but cannot view the data in the table db1.t1.

Similarly, you can use u1 to log in to a1 for privilege verification:

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

As shown in the above code, u1 can successfully query the data of table db1.t1, but cannot insert data into it.

Note

For more information about system tables, see MatrixOne System Database and Tables