User Permission Setting

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;

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.