Take the 2-minute tour ×  I want to give different level of
access on tables to different users based on their department.
Should I use Schema or Roles for this purpose?  share|improve
this question add comment  closed as off topic by George Stocker
Jul 30 '12 at 13:52   2 Answers  up vote 6 down vote accepted
Roles would be the way to go here. Roles should be used anytime
you have groups (i.e. depts. in your case) that need specific
user rights to tables regardless of what schema they are in.
This prevents errors, and makes the job of the DB administrator
much easier.  Think of roles as groupings for users and the
associated rights for that group of users, while schemas are for
the logical grouping of sets of data.  Edit based on user
comment:  You can, but to limit access to a particular table
using schema is maybe not the best way to think about it.
Schemas are used for grouping of data. So you may have a "sales"
schema that has tables, procedures, etc. that are needed by the
sales team. You can then say