We have granted select privilege on one table of a schema to another user. Even after that the user was getting permission denied error while selecting data.
- Privilege was granted like this
PRIMDB=# grant all privileges on table SCOTT.SERVER_LOAD_INFO to prim_user;
Now connect to prim_user and run the select statement:
psql -d PRIMDB -U prim_user
PRIMDB=> select * from SCOTT.SERVER_LOAD_INFO;
ERROR: permission denied for schema SCOTT
LINE 1: select * from SCOTT.SERVER_LOAD_INFO;
We need to provide usage privilege on that schema to other user also.
As per postgres note:
By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the USAGE privilege on the schema.
NSMD2SIT=# grant usage on schema SCOTT to prim_user;
-- Now run the select statment: