Help Center> Data Warehouse Service (DWS)> Developer Guide (Paris Region)> Database Security Management> Managing Users and Their Permissions> User Permission Setting
Updated on 2022-07-29 GMT+08:00
View PDF

User Permission Setting

  • To grant the permission for an object directly to a user, use GRANT.

    When permissions for a table or view in a schema are granted to a user or role, the USAGE permission of the schema must be granted together. Otherwise, the user or role can only see the names of the objects but cannot actually access them.

    In the following example, permissions for the schema tpcds are first granted to the user joe, and then the SELECT permission for the tpcds.web_returns table is also granted.

    1
2
    		 
    GRANT USAGE ON SCHEMA tpcds TO joe;
GRANT SELECT ON TABLE tpcds.web_returns to joe;
  • Granting a role to a user allows the user to inherit the object permissions of the role.
    1. Create a role.

      Create a role lily and grant the system permission CREATEDB to the role.

      1
      		 
      CREATE ROLE lily WITH CREATEDB PASSWORD 'password';
    2. To grant object permissions to a role, use GRANT.

      For example, first grant permissions for the schema tpcds to the role lily, and then grant the SELECT permission of the tpcds.web_returns table to lily.

      1
2
      		 
      GRANT USAGE ON SCHEMA tpcds TO lily;
GRANT SELECT ON TABLE tpcds.web_returns to lily;
    3. Grant the role permissions to a user. 
      1
      		 
      GRANT lily to joe;

      When the permissions of a role are granted to a user, the attributes of the role are not transferred together.

  • To revoke user permissions, use REVOKE.