Privileges Management Overview

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:

UsernameExplanationRolePrivilegeDescription
rootCluster administratorMOADMINCreate, edit, delete accountsAutomatically generated and granted after cluster creation
rootSystem account administratorMOADMINManage all resources under the system account, including users, roles, databases/tables/views, authorization managementAutomatically generated and granted after the cluster is created

Create Account

SQL Statement

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

Parameter Description

ParameterDescription
account_nameaccount name
user_nameThe administrator username of the newly created account, which will be automatically granted the highest privilege role of the account, namely ACCOUNTADMIN
passwordNewly created account administrator password

For more information, see CREATE ACCOUNT.

Select Account

SQL Statement

  1. select * from mo_catalog.mo_account;

Delect Account

SQL Statement

  1. drop account if exists <account_name>;

Parameter Description

ParameterDescription
account_nameThe name of the account to be deleted

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

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

Parameter Description

ParameterDescription
user_nameThe name of a new user
passwordThe password of a new user

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

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

  1. drop user if exist <user_name>;

Parameter Description

ParameterDescription
user_nameThe name of the user to be deleted

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

  1. create role <role_name>;

Parameter Description

ParameterDescription
role_nameThe name of a new role

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

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

  1. set role <role_name>;

Parameter Description

ParameterDescription
role_nameThe name of the role to be switched

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

  1. drop role if exists <role_name>;

Parameter Description

ParameterDescription
role_nameThe name of the role to be deleted

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

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

Parameter Description

ParameterDescription
privilegePrivilege Name
object_typeThe type of object
object_nameThe name of object
role_nameThe name of the role which is granted

For more information, see GRANT.

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

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

Parameter Description

ParameterDescription
privilegePrivilege Name
role_nameThe name of the role which is granted

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

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

  1. grant <role_name> to <user_name>;

Parameter Description

ParameterDescription
role_nameThe name of the role which is granted
user_nameThe name of the user which is granted

For more information, see GRANT.

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

  1. 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 GRANT 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

  1. show grants for <user_name>@<localhost>

Parameter Description

ParameterDescription
user_nameThe name of user which is granted.

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

  1. revoke <role_name> from <user_name>

Parameter Description

ParameterDescription
role_nameThe name of role which is granted.
user_nameThe name of user which is granted.

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

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

Parameter Description

ParameterDescription
privilegePrivilege
object_typeThe type of the object
object_nameThe name of the object
role_nameA role that needs to be granted

For more information, see REVOKE.