10

I am managing a database that has a number of schemas. I am having some difficulty with setting privileges and would like to request some help.

I have the schema called schemaA and a group_role db_writer

I use the following sql:

GRANT USAGE ON SCHEMA schemaA TO db_writer;
GRANT UPDATE, INSERT, SELECT, DELETE ON ALL TABLES IN SCHEMA schemaA TO db_writer;

However, the db_writer is unable to create views. They are returned with a permission denied error. Also, when I create views, she is then unable to select them...and I have to set the priviliges again for that view.

It was my understanding the views were treated as tables with respect to privileges...and if one is granted certain permissions to all tables in a schema this would apply to views also.

What am I missing? Any advice appreciated.

DLeh
  • 23,806
  • 16
  • 84
  • 128
user3770062
  • 153
  • 1
  • 1
  • 12

1 Answers1

14

The problem is USAGE does not allow users to create objects within the database.

Try

GRANT USAGE, CREATE ON SCHEMA schemaA TO db_writer

Edit:

New objects will get default privileges, for the user to have those privileges for objects created in the future you can do it as:

ALTER DEFAULT PRIVILEGES IN SCHEMA schemaA GRANT UPDATE, INSERT, SELECT, DELETE ON TABLES TO db_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA schemaA GRANT SELECT ON TABLES TO db_reader;

Check this answer for more info

Community
  • 1
  • 1
Filipe Roxo
  • 622
  • 3
  • 10
  • Miguel, your answer provides a solution that allows a user to create views. But what if there is another user db_reader that I would like to allow to 'select' from my views ...but not allow them to create views. Is this feasible? or do I have to grant 'CREATE' for a user to select from a view? – user3770062 Dec 11 '14 at 11:54
  • Of course it is feasible. In that case to db_reader you only grant 'USAGE' on that schema – Filipe Roxo Dec 11 '14 at 12:29
  • ah, yes - this is what I had done. ...but then when I created a new view the db_reader was not able to access it...until I had redone the permissions again. I had to grant them select permissions on the view after creating it - I had hoped that this would be done automatically is usage/select is already granted for them on the schema. ? – user3770062 Dec 11 '14 at 12:52
  • @user3770062 didn't notice your comment sorry. Just updated my answer. – Filipe Roxo Dec 22 '14 at 14:54