I use schemas to simplify granting permissions. For tables and views, I have three schemas:
- Data, the actual data my customers need. Can only be modified via sprocs.
- Staging, only visible to data loaders and devs. Full privileges on INSERT?UPDATE/DELETE for those who see it.
- Config, the configuration data used in loads, only visible to data loaders and devs. Can only be modified via sprocs.
For sprocs/UDFs I have the following schemas:
Also I have dbo schema for the objects that do not belong to any category, but there are less than 20 objects in it.
Roles are granted privileges on these schemas.
This approach saves me a considerable amount of time, because I do not have to grant privileges to each object individually. That would be a very tedious and error-prone process. I have hundreds of objects which need exactly the same set of privileges, and doing one and the same thing over and over again is not really efficient.
We never had any problems with this approach.
One more thing: I think that in most cases developers, not DBAs, should decide which schemas to use, because developers should know the subject area better.
This is a reply to posts by