• Data Warehouse Service

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

User and Permission Overview

DWS supports access control by role. A role associates a user with its permission, which greatly simplifies permission authorization and management. The role mechanism allows you to batch assign the same permissions to a group of users by granting the permissions to a role and assigning the role to the group.

Context

A database may be used by many users, and users are grouped into a database role for easy management. A database role can be one or a group of database users.

In a database, users and roles are similar concepts. Different from CREATE USER, CREATE ROLE neither creates a schema of the same name nor grants the LOGIN permission to the created role. In business design, you are advised to use a role to manage permissions rather than access databases.

Database Users

Database users are used to connect databases, access database objects, and run SQL statements. You must use an existing database user to connect to the database. Therefore, a database administrator must plan a database user for each person who wants to connect to the database.

While creating a cluster, you must specify the user name and password of the database administrator. After a cluster is created, you can use the administrator account to connect to the database. For details, see the Connecting to a Cluster. Then, you can run the \du+ command to view information about all users. For example:

postgres=> \du+
                                               List of roles 
Role name |                               Attributes                               | Member of | Description
-----------+------------------------------------------------------------------------+-----------+------------- 
Ruby      | Sysadmin, Create role, Create DB, Replication, Administer audit, UseFT | {}        | 
dbadmin   | Sysadmin                                                               | {}        |
postgres=>
NOTE:
  • User Ruby, an initial user, will be automatically created for internal management and maintenance.
  • The italic dbadmin in the preceding example indicates the administrator specified during cluster creation.

By default, database users can be classified into two types, as listed in Table 1.

Table 1 User types

Type

Description

System administrator

Has the highest database permissions, that is, has all system and object permissions.

When you create a data warehouse cluster, the system automatically generates an administrator account based on your settings.

The system administrator is not affected by the settings of the object permissions. This is similar to the root permissions in a Unix system. For security, you are advised not to operate as a system administrator unless necessary.

Common user

By default, a common user can access the default database system catalogs (excluding pg_authid, pg_largeobject, pg_user_status, and pg_auth_history) and views, connect to the default database postgres, and can use the objects in the public schema, including tables, views, and functions.

  • You can specify system permissions based on guidelines provided in CREATE USER and ALTER USER or run GRANT ALL PRIVILEGE to assign the SYSADMIN permission.
  • You can execute the GRANT statement to assign certain object permissions to common users.
  • You can run the GRANT statement to assign other roles' or users' permissions to a common user.

DWS has two user permission models:

Database Permissions

Permissions and roles work together to determine the data users can access and the SQL statements they can run. For details, see Table 2.

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

Table 2 Permission types

Type

Description

System permission

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

They can be 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 are operation permissions for tables, views, indexes, sequences, and functions. These 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 group of permissions. A role's permissions can be granted to other users or roles.

Only object permissions are valid for a role and can be inherited by other users or roles.