• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Database Security Management
  5. User Permission Management
  6. Planning Users and Rights

Planning Users and Rights

Prerequisites

You have understood basic concepts of database users and roles. For details, see User and Permission Overview.

Planning Process

Table 1 describes the general process of planning a user and its rights.

Table 1 Planning Process

Procedure

Description

Planning Rule

1

Plan system permissions

By default, only system administrators have the system permissions. After the database is installed, you can assign system permissions as a system administrator to other users.

For security, ensure that system permissions are assigned to reliable users.

2

Plan object permissions

The planning of object permissions is flexible. As a system administrator, you can either grant all permissions of certain database objects to a user or partial permissions (such as the SELECT and UPDATE permissions) of certain database objects to different users.

3

Plan roles

In practice, if more than two users have the same object permissions, you are advised to group the users to a role, and assign these permissions to the role.

4

Grant permissions to users

Based on the preceding planning:

  • Use CREATE/ALTER USER to assign system permissions to a specified user.
  • Use GRANT/REVOKE to assign object permissions to a specified user.

Planning Rules

For security, you are advised to create accounts and grant corresponding permissions listed in Table 2 after installing the database.
Table 2 Account planning rules and creation methods

Account Type

Description

Creation Method

Initial user

Database administrator that is automatically created during the database installation and has the same name as the OS user. This user has the highest permission in the database.

You are advised to use an initial user only as a database administrator.

This account is automatically created during the database installation.

System administrator

Account having the SYSADMIN attribute.

The permission is only inferior to that of an initial user. You are advised to create only one system administrator.

The four types of accounts are created in the similar ways. For example, run the following command to create an audit administrator user_audit:

CREATE USER user_audit WITH AUDITADMIN IDENTIFIED BY "1234@abc";

  

  

  

Security administrator

Account having the CREATE ROLE attribute.

You are advised to create only one security administrator.

Audit administrator

Account having the AUDITADMIN permission.

You are advised to create only one audit administrator.

Private user

Account having the INDEPENDENT permission. Database administrators (including initial users and other administrators) must be authorized before they do INSERT, DELETE, SELECT, UPDATE, COPY, GRANT, REVOKE, and ALTER OWNER on the tables of private users.

Private users are created to prevent unauthorized database administrators from accessing table data. By default, database administrators are unauthorized. In this case, private users need to manage their own tables, for example, logically back up their data. Exercise caution when you perform operations as a private user.

Object operator

Meaning: Specifies the account that has the fewest permissions, but can connect to the default postgres database and can access the default system catalogs and views in the database.

Planning rule: Create a role and add a user to the role. Then, the user has the object permission of the role.

For example, perform the following steps if you want to create object operator user_read with the same object permission as role role1 that has the query permission on table tpcds.customer.

  1. Run the following command to create the role role1:
    CREATE ROLE role1 IDENTIFIED BY "abc@1234";
    CREATE ROLE
  2. Run the following command to grant permissions for the schema tpcds to the role role1:
    GRANT USAGE ON SCHEMA tpcds TO role1;
    GRANT
  3. Run the following command to grant the query permission for the table tpcds.customer to the role role1:
    GRANT SELECT ON TABLE tpcds.customer TO role1;
    GRANT
  4. Run the following command to create the object operator user_read and add it to role role1:
    CREATE USER user_read IN ROLE role1 PASSWORD "123@abcd";
    CREATE ROLE