Pivotal Knowledge Base

Follow

Why do we need to use "GRANT USAGE ON SCHEMA" and what its purpose ?

Problem

Why doesn't a grant on the schema

GRANT USAGE ON SCHEMA TO <rolename/username>;

doesn't allow the table on the schema to be accessed.

Solution

Privileges must be granted for each object individually. For example, granting ALL on a database does not grant full access to the objects within that database. It only grants all of the database-level privileges (CONNECT, CREATE, TEMPORARY) to the database itself. Similiarly, GRANTing on a schema doesn't grant rights on the tables within.

So placing that into practice , If you have rights to SELECT from a table, but not the right to see it in the schema that contains it then you can't access the table.

But a object on PUBLIC schema has a default GRANT of all rights to the role public, which every user/group is a member of , so a object in PUBLIC schema doesn't need a extra GRANT command, unless users have been revoked from PUBLIC schema access.

Comments

Powered by Zendesk