Privileges Management User guide

Manage Account

• Prerequisites: Account management is only possible with a cluster administrator (the default account is the root).

The roles and permissions corresponding to the root are shown in the following table:

Create Account

SQL Statement

create account <account_name> admin_name='<user_name>' identified by '<password>';

Parameter Description

For more information, see CREATE ACCOUNT.

Select Account

SQL Statement

select * from mo_catalog.mo_account;

Delect Account

SQL Statement

drop account if exists <account_name>;

Parameter Description

Note

After the account is deleted, it cannot be restored, including all data under the account account. Please use it with caution.

For more information, see DROP ACCOUNT.

Manage User

Create User

• Prerequisites: Has the CREATE USER privilege.

  · The default role with this privilege is MOADMIN or ACCOUNTADMIN: the cluster administrator (the default account is root), and the tenant administrator created by the cluster administrator has the privilege by default.

• Introduction: Create a username and password for a user in the current account.

SQL Statement

create user <user_name> identified by '<password>';

Parameter Description

For more information, see CREATE USER.

View User

• Prerequisites: Has the privilege to view users.

  · The default role with this privilege is MOADMIN or ACCOUNTADMIN: the cluster administrator (the default account is root), and the tenant administrator created by the cluster administrator has the privilege by default.

• Introduction: View all the users in the current account.

SQL Statement

select * from mo_catalog.mo_user;

Delete User

• Prerequisites: Has the DROP USER privilege.

  · The default role with this privilege is MOADMIN or ACCOUNTADMIN: the cluster administrator (the default account is root), and the tenant administrator created by the cluster administrator has the privilege by default.

• Introduction: Delete the specified user in the current account.

SQL Statement

drop user if exist <user_name>;

Parameter Description

Note

When deleting a user, you need to stop the user’s current session first, otherwise the deletion will fail.

For more information, see DROP USER.

Manage Role

Create Role

• Prerequisites: Has the CREATE ROLE privilege.

  · The default role with this privilege is MOADMIN or ACCOUNTADMIN: the cluster administrator (the default account is root), and the tenant administrator created by the cluster administrator has the privilege by default.

• Introduction: Create a custom role in the current account.

SQL Statement

create role <role_name>;

Parameter Description

For more information, see CREATE ROLE.

View Role

• Prerequisites: Has the privilege to view role.

  · The default role with this privilege is MOADMIN or ACCOUNTADMIN: the cluster administrator (the default account is root), and the tenant administrator created by the cluster administrator has the privilege by default.

• Introduction: View all the roles in the current account.

SQL Statement

select * from mo_catalog.mo_role;

Switch Role

• Prerequisites: Has the SET ROLE privilege. By default, all users have this privilege.

• Introduction: Switch the role of the user in the account, and obtain the privilege of the secondary role to execute the corresponding SQL.

SQL Statement

set role <role_name>;

Parameter Description

For more information, see SET ROLE.

Delete Role

• Prerequisites: Has the DROP ROLE privilege.

  · The default role with this privilege is MOADMIN or ACCOUNTADMIN: the cluster administrator (the default account is root), and the tenant administrator created by the cluster administrator has the privilege by default.

• Introduction: Delete the specified role in the current account.

SQL Statement

drop role if exists <role_name>;

Parameter Description

Note

When deleting a specified role, the roles of authorized users will be recovered simultaneously.

For more information, see DROP ROLE.

Manage Privilege

Grant an object privilege to a role

• Prerequisites: Has the MANAGE GRANTS privilege.

  · The default role with this privilege is MOADMIN or ACCOUNTADMIN: the cluster administrator (the default account is root), and the tenant administrator created by the cluster administrator has the privilege by default.

• Introduction: Grant an object privilege to a role.

SQL Statement

grant <privilege> on <object_type> <object_name> to <role_name>

Parameter Description

For more information, see DRANT PRIVILEGES.

Grant certain kind of object privileges to a role

• Prerequisites: Has the MANAGE GRANTS privilege.

  · The default role with this privilege is MOADMIN or ACCOUNTADMIN: the cluster administrator (the default account is root), and the tenant administrator created by the cluster administrator has the privilege by default.

• Introduction: Grant the privilege on all databases/tables to a role.

SQL Statement

grant <privilege> on database * to <role_name>;
grant <privilege> on table *.* to <role_name>;

Parameter Description

Note

Although this operation is relatively simple when authorizing multiple objects of the same category, it is also prone to permission leakage, so please use it with caution.

For more information, see DRANT PRIVILEGES.

Grant a role to a user

• Prerequisites: Has the MANAGE GRANTS privilege.

  · The default role with this privilege is MOADMIN or ACCOUNTADMIN: the cluster administrator (the default account is root), and the tenant administrator created by the cluster administrator has the privilege by default.

• Introduction: Grant a role to a user.

SQL Statement

grant <role_name> to <user_name>;

Parameter Description

For more information, see DRANT ROLE.

One role inherit the privileges of another role

• Prerequisites: Has the MANAGE GRANTS privilege.

  · The default role with this privilege is MOADMIN or ACCOUNTADMIN: the cluster administrator (the default account is root), and the tenant administrator created by the cluster administrator has the privilege by default.

• Introduction: Let role_b inherit all privileges of role_a.

SQL Statement

grant <role_a> to <role_b>;

Note

The permissions are inherited dynamically. If the permissions of role_a change, the permissions inherited by role_b will also change dynamically. MatrixOne does not allow role ring inheritance; that is, role1 inherits role2, role2 inherits role3, but role3 can not inherits role1.

For more information, see DRANT ROLE.

Show the privilege of user

• Prerequisites: Has the SHOW GRANTS privilege.

  · The default role with this privilege is MOADMIN or ACCOUNTADMIN: the cluster administrator (the default account is root), and the tenant administrator created by the cluster administrator has the privilege by default.

• Introduction: Show all privileges currently owned by the specified user.

SQL Statement

show grants for <user_name>@<localhost>

Parameter Description

For more information, see SHOW GRANTS.

Revoke the role of user

• Prerequisites: Has the REVOKE privilege.

  · The default role with this privilege is MOADMIN or ACCOUNTADMIN: the cluster administrator (the default account is root), and the tenant administrator created by the cluster administrator has the privilege by default.

• Introduction: Remove a role from a user.

SQL Statement

revoke <role_name> from <user_name>

Parameter Description

For more information, see REVOKE.

Revoke privilege of roles

• Prerequisites: Has the REVOKE privilege.

  · The default role with this privilege is MOADMIN or ACCOUNTADMIN: the cluster administrator (the default account is root), and the tenant administrator created by the cluster administrator has the privilege by default.

• Introduction: Revoke privileges on an object in a role.

SQL Statement

revoke <privilege> on <object_type> <object_name> to <role_name>;

Parameter Description

For more information, see REVOKE.