• Data Warehouse Service

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

Managing Users and Their Rights

Scenarios

This section describes how administrators manage users and their rights in a database. The common operations involve:

  • Viewing a User.
  • Modifying a User Account. For details about its syntax, see ALTER USER.
  • Revoking Permission. For details about its syntax, see REVOKE.
  • Deleting a User. You can delete unnecessary users. For details about its syntax, see DROP USER.
    NOTE:
    • While running the DROP USER command to delete a user, you must use CASCADE to delete objects that depend on the user (excluding databases). The locked objects cannot be deleted until they are unlocked or the process that locks them is killed.
    • If any databases depend on the user to be deleted, delete the databases before you delete the user. Because databases are important objects, you cannot delete them using CASCADE.
    • In DWS, the enable_kill_query parameter controls whether the system waits for the process locking a user's cascading objects to end before deleting the user.

Viewing a User

  1. Connect to the database as a system administrator.
  2. View system permissions of the specified users.

    For example, run the following command to view the system permission of the user joe:

    \du joe

    The following information is displayed:

               List of roles
     Role name | Attributes | Member of 
    -----------+------------+-----------
     JOE       | Create DB  | {lily}

    Create DB indicates that the user joe has the permission to create a database.

  3. View object permissions of the user.

    For example, view all databases that the user joe can access.

    1. Run the following command to switch to the user joe:
      SET ROLE joe password "abcd@123";

      If the following information is displayed, you have switched to the user joe:

      SET
    2. Run the following command to create a database named music1:
      CREATE DATABASE music1;
      CREATE DATABASE
    3. Run the following command to query all available databases:

      \l

      The information is displayed as follows:

                                      List of databases
            Name      |  Owner   | Encoding  | Collate | Ctype |   Access privileges   
      ----------------+----------+-----------+---------+-------+-----------------------
       music1         | joe      | SQL_ASCII | C       | C     | 
       postgres       | dwsadmin | SQL_ASCII | C       | C     | 
       template0      | dwsadmin | SQL_ASCII | C       | C     | =c/dwsadmin          +
                      |          |           |         |       | dwsadmin=CTc/dwsadmin
       template1      | dwsadmin | SQL_ASCII | C       | C     | =c/dwsadmin          +
                      |          |           |         |       | dwsadmin=CTc/dwsadmin
      (4 rows)
    4. Run the following command to delete the music1 database:
      DROP DATABASE music1;

      If the following information is displayed, the database has been deleted.

      DROP DATABACSE

Modifying a User Account

  1. Connect to the database as a system administrator.
  2. Modify the system permissions of a user.

    For example, run the following command to modify joe's system permissions, adding the system permission for the created user and revoking the system permission for the created database:

    ALTER USER joe WITH NOCREATEDB CREATEROLE PASSWORD "Bigdata123@";

    If the following information is displayed, the permissions have been modified.

    ALTER ROLE

Revoking Permission

  1. Revoke the permissions of roles and users.

    Revoke all the permissions of the role lily and the user joe:

    REVOKE ALL ON web_returns FROM lily;
    REVOKE ALL ON web_returns FROM joe;

    If the following information is displayed, the permissions have been revoked.

    REVOKE

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.

Deleting a User

  1. Connect to the database as a system administrator.
  2. Delete a user from the database.

    For example, run the following command to delete the user joe:

    DROP USER joe;

    If the following information is displayed, the user joe has been deleted.

    DROP ROLE