• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Database Security Management
  5. User Permission Management
  6. Creating a User and Assigning Permissions

Creating a User and Assigning Permissions

Scenarios

This section describes how to create a user in the database and assign permissions to the user.

Prerequisites

The user and permissions have been planned. For planning rules, see User and Permission Overview.

Procedure

  1. Connect to the database as a system administrator.
  2. Create a user and assign permissions to the user.

    • Create a user and assign system permissions.

      For details about the syntax for creating a user, see CREATE USER.

      Run the following command to create a user with the permission to create a database:

      CREATE USER joe WITH CREATEDB PASSWORD "abcd@123";
      CREATE ROLE
    • Assign specific object permissions to a user.

      For details about the syntax for assigning object permissions to a user, see GRANT.

      Assign the permission of the tpcds schema to user joe:

      GRANT USAGE ON SCHEMA tpcds TO joe;
      GRANT

      Assign the select permission of the customer table to user joe:

      GRANT SELECT ON TABLE tpcds.customer to joe;
      GRANT
    • Assign specified object permissions to a role.

      For details about the syntax for assigning object permissions to a role, see GRANT.

      Create role lily. For details about how to create a role, see CREATE ROLE.

      CREATE ROLE lily WITH CREATEDB PASSWORD "abcd@123";
      CREATE ROLE

      Assign the permission of the tpcds schema to the user lily:

      GRANT USAGE ON SCHEMA tpcds TO lily;
      GRANT

      Assign the select permission of the customer table to role lily:

      GRANT SELECT ON TABLE tpcds.customer to lily;
      GRANT
    • Assign permissions of a specified role to the user.

      For details about the syntax for assigning object permissions to a role, see GRANT.

      For example, run the following command to assign role lily's permissions to user joe:

      GRANT lily to joe;
      GRANT
    NOTE:

    For schemas, users having the USAGE permission can access to objects contained in the specified schema. Without this permission, users can only see the object names.