Creating a DWS Database User or Role

Video Tutorial

Typical Workflow

After creating your first DWS cluster, you need to connect to the GaussDB database as the system administrator dbadmin and perform initial configuration in the sequence shown in the following figure.

Figure 1 Typical process of creating a user

Example:

• New deployment: If you want to deploy a new order management system, you need to create database order_db, role order_admin (with permissions to add, delete, modify, and query order tables), and user order_user (assigned the order_admin role).
• Data migration: To migrate upstream service data to DWS, you need to create databases, users, and roles in DWS based on the user and permission planning in the upstream system.

Constraints and Limitations

• Avoid having all business operations run under a single database user. Instead, plan different database users according to the business modules.
• Do not run workloads as system administrator (dbadmin). Use multiple users and permissions for access control of different modules.
  

  Running workloads as the system administrator poses security risks. Any misoperation may affect the data and configuration of the entire cluster. Create independent database users for different service modules and grant permissions to the users based on the principle of least privilege.

• For more information about the design specifications that need to be followed during the DWS database development, see DWS Development Design Specifications.
• System users or roles automatically generated during cluster creation cannot be modified.

Creating a Database

You can use the DDL syntax or SQL editor to create a database.

• Using DDL syntax: For details, see "CREATE DATABASE" in SQL Syntax Reference.
• SQL editor: For details, see Using the SQL Editor to Connect to a DWS Cluster.

Creating a Role

1. Log in to the DWS console.
2. In the navigation pane on the left, choose Cluster > Cluster List.
3. In the cluster list, click the name of the target cluster. The Cluster Information page is displayed.
4. In the navigation pane, choose User Management.
5. Click the Roles tab and then Create Role.
6. Configure role information. The parameters are described as follows:

   Table 1 Parameters for configuring role information

   Parameter	Description
   Role Name	The value must start with a letter and can contain a maximum of 63 characters, including letters, digits, and underscores (_).
   Expires	Expiration time of the role permissions. When the role expires, the permissions of the role will become invalid automatically, and users assigned the role will no longer have the permissions. If this parameter is not set, the user is permanently valid.
   System Administrator	Whether to grant the system administrator permissions to the role. A role with such permissions can perform all database operations, including managing other users, roles, and database objects. Exercise caution when granting the permissions. They are not recommended for common service roles.
   Create Database	Whether to grant the role the permission to create databases
   Create Role	Whether to grant the role the permission to create users or roles
   Inherit Permissions	Whether the role automatically inherits the permissions of the group to which it belongs. This function is enabled by default and you are advised not to disable it. If this function is disabled, this role will not automatically inherit the permissions of other roles even if it is assigned those roles. Instead, you need to manually grant permissions to the role.
   Description	Description of the role to be created, such as its functions and the service module it belongs to. The description can contain a maximum of 500 characters.

7. Confirm the settings and click Next.
8. Configure the permissions of the role.

   Click Add to add a permission configuration. Select the database object type and corresponding database object, and select permissions. Confirm the information and click Save. For details about permission definitions, see "DCL Syntax" > "GRANT" in DWS SQL Overview.

   

9. After the authorization is complete, click Create.

Creating a Database User

You can create a database user using the DDL syntax or on the DWS console. For details about DDL, see "CREATE USER" in SQL Syntax Reference.

Managing Roles

1. Log in to the DWS console.
2. In the navigation pane on the left, choose Cluster > Cluster List.
3. In the cluster list, click the name of the target cluster. The Cluster Information page is displayed.
4. In the navigation pane, choose User Management.
5. Click the Roles tab and perform operations on roles. The following tables lists the supported operations.

   Table 3 Role management operations

   Operation	Description
   Modifying a role	In the role list, locate the role you want to modify and click Modify in the Operation column. Configure permissions and click Save. Modify information about the role. For details, see Table 1. Configure permissions. Select a permission type as required, click Edit in the Operation column, and click Modify in the Permission column to add or remove permissions.
   Exporting roles	Click Export above the role list. In the displayed dialog box, set the parameters and click Export.
   Exporting role permissions	Locate a role in the role list and click Export Authority. In the displayed dialog box, set Export Data Volume and click Export to export permissions of the role.
   Deleting a role	In the role list, locate the role you want to delete and click Delete in the Operation column. In the displayed dialog box, click OK to delete the role. To prevent any problems with deleting a role, check for dependencies such as database objects beforehand. If there are any dependencies, delete them first before proceeding with the role deletion.

Managing Database Users

1. Log in to the DWS console.
2. In the navigation pane on the left, choose Cluster > Cluster List.
3. In the cluster list, click the name of the target cluster. The Cluster Information page is displayed.
4. In the navigation pane, choose User Management. Perform the following operations on database users.

   Table 4 User management operations

   Operation	Description
   Modifying a user	In the user list, locate a user and click Modify. Configure permissions and click Save. Modify information about the user. For details, see Table 2. Configure the roles you want to assign to the user. After selecting a permission type, you can click Edit in the Operation column and click Modify in the Permission column to add or remove a permission.
   Exporting users	Click Export above the user list. In the displayed dialog box, set Export Data Volume and click Export.
   Exporting user permissions	Locate a user in the user list and click Export Authority. In the displayed dialog box, set Export Data Volume and click Export to export permissions of the user. User dbadmin of a physical cluster only has system permissions, which cannot be exported. For this user, Export Authority is unavailable.
   Importing user permissions	Locate a user in the user list, click More in the Operation column, select Import Permission, and select the permission file.
   Deleting a user	In the user list, locate a user, click More in the Operation column, and select Delete. In the displayed dialog box, click OK. To prevent any problems with deleting a user, check for dependencies such as database objects beforehand. If there are any dependencies, delete them first before proceeding with the user deletion. If you select Forcibly delete and remove dependencies, tables, functions, and other database objects under the current user will be transferred to the administrator account.

Documentation

• Creating a role: CREATE ROLE
• Creating a user: CREATE USER
• Users and roles: Users and Roles