Help Center/ MapReduce Service/ Component Operation Guide (LTS) (Ankara Region)/ Using MOTService/ MOTService Permissions Management/ Users and Roles
Updated on 2024-11-29 GMT+08:00
View PDF
Share

Users and Roles

A database may be accessed by multiple users. To facilitate user management, you can manage users through database roles. A database role can be considered as a database user or a group of database users.

Background

For MOTService, users and roles are basically the same concept. The difference is that when CREATE ROLE is used to create a user, no schema with the same name is created and the user does not have the LOGIN permission by default. In other words, a role with the LOGIN permission can be considered as a user. In practice, you are advised to use roles to organize permissions instead of accessing databases.

Database Roles and Permissions

Permissions and roles work together to determine which users can access data and which SQL statements they can execute. For details, see Table 1.

System permissions are specified only by executing the CREATE USER, ALTER USER, CREATE ROLE, or ALTER ROLE statement and cannot be inherited from roles. The SYSADMIN permission can be granted or revoked using the grant all privileges or revoke all privileges syntax.

Table 1 MOTService permission types

Type

Description

System permission

System permissions are also called user attributes, which can be specified when a user is created or modified. System permissions include SYSADMIN, CREATEDB, CREATEROLE, AUDITADMIN, and LOGIN.

They are specified only by the CREATE USER or ALTER USER statement. System permissions except SYSADMIN cannot be granted or revoked by the GRANT or REVOKE statement. In addition, system permissions cannot be inherited from roles.

Object permission

Object permissions refer to the permissions to perform operations on database objects, such as tables, views, indexes, and functions. Object permissions include SELECT, INSERT, UPDATE, and DELETE.

Only an object owner or system administrator can use the GRANT/REVOKE statement to grant or revoke object permissions.

Role

A role is a set of permissions. Only object permissions of a role can be granted to other roles and users.

Procedure for Creating a User

  1. Log in to FusionInsight Manager, choose Cluster > Services > MOTService, click Instance, and view and record the service IP address of the MOTServer(Active) instance.
  2. Log in to the active MOTService node as user omm and run the following command to start environment variables:

    source ${MOTSERVER_HOME}/.motservice_profile

  3. For example, to connect to the default database postgres, set the port to 20105, and enter the password of the MOTService database user omm.

    gsql -d postgres -p 20105

    Contact the system administrator to obtain the password of user omm for the MOTService database.

  4. Create user accounts.

    For security purposes, you are advised to create accounts with the permissions listed in Table 2 after installing the MOTService database.

    Table 2 Account planning rules and creation methods

    Account Type

    Description

    Creation Method

    System administrator

    Account with the SYSADMIN permission

    When a database is installed or initialized, a system administrator with the same name as the user specified by the -U parameter is automatically generated. You do not need to create other system administrators.

    The three types of accounts are created in a similar way. For example, to create the audit administrator user_audit, run the following command:

    CREATE USER user_audit WITH AUDITADMIN IDENTIFIED BY "user_audit_password";

    Security administrator

    Account with the CREATEROLE permission

    Create only one security administrator with the CREATEROLE permission.

    Audit administrator

    Account with the AUDITADMIN permission

    Create only one audit administrator with the AUDITADMIN permission.

    Object operator

    User with the minimum permissions. This user can connect to the default MOTService database postgres and access the default system tables and views in the database.

    Create a role and add a user to the role. Then, the user has the object permissions of the role.

    For example, create role role1 with the query permission on the films table, create user user_read, and add it to role role1 with the same object permissions.

    1. Create the role role1.

      CREATE ROLE role1 IDENTIFIED BY "role1_password";

    2. Grant the query permission on the films table to the role1 role.

      GRANT SELECT ON TABLE films TO role1;

    3. Create the object operator user_read and add it to the role role1.

      CREATE USER user_read IN ROLE role1 PASSWORD "user_read_password";