[dba-SQLServer] Schemas for separation of clients?

[fhtapia at gmail.com]

John, (sorry for the late reply)
  In the past Schemas and Users were one in the same. Today you can
generate a Schema as a container.   This can offer benefits in security,
and data segregation.  It is entirely possible for you to generate a full
container for client data as if it was the model database.

core functionality can be maintained because default Views and Procedures
can be written where you do not explicitly describe the default_schema and
the container for these would be an app schema . That means that a select
statement in your view/sproc would look like:
---
Select * From AppName.vw_Clients
---
within that view [AppName].[vw_Clients] it would read as:

select * from tblMyClientTable

which is interpreted if my schema my user was assigned to was Client100 as:

SELECT * FROM Client100.tblMyClientTable

if the table is not found in the default schema such as
ClientID.tblMyClientTable then it will revert back to dbo.tblMyClientTable

if I want the same TABLE setup for all clients, I would need to script my
table changes and write a UPDATE statement that would loop through all
client tables that needed to be updated.  Yes, that's not ideal but that's
why you don't develop in production, you MUST and I can't stress this
enough MUST have a development environment where you can unit test you
changes. Such as a local copy on your own computer vs on another server.

There is no reason to create procedures and views in the default client
schema outside of dbo unless you want specific functionality for that
client schema.

the issues in your database from hell scenario can be problematic because
you DO NOT login with different schemas,  The idea for a schema with users
is that they login and the data is auto-segregated because of their
relationship to the schema and the objects it owns and this allows you to
simplify your coding by writing views and stored procedures that can be
found for all clients.

one possible work-around for your dbhell scenario is for you to create a
reporting user that you can login with and have initialization logic that
you provide it a schema_name and it would move your user into that schema
to simplify reporting for that client. (just a thought).

Maintenance: adding new objects (Tables and changes to Tables) does require
that your create new schema for client logic always includes all objects
for your model schema.


Regards,
Francisco


On Tue, Mar 31, 2015 at 7:06 AM John W. Colby <jwcolby at gmail.com> wrote:

> Francisco, thanks for replying to this discussion.  I worked at IBM in
> large databases.  The way they used schemas was that the schema allowed
> creating anything you wanted (tables, views, SPs etc) which were
> assigned to a specific schema.  The result was a horrendous mess simply
> because inside each database was a ton of stuff for each schema.
> Imagine in this case that you want identical stuff for each client, same
> tables, same views etc. and that there are 500 items total (tables /
> views / SPs etc).  Each client would have their own COPY of each item.
> If you fix one, you ONLY fix the item for that schema.  If you have 100
> clients..... imagine the quantity of items in the database container.
>
> Understand that I did not get into the hows and whys, just the results.
> The result was a mess.  At IBM, schemas appeared to be used to group
> what various employees could see, not what clients could see.  Since the
> quantity of employee "groups" was quantifiable, the result did kind of
> work, though it was still messy.  IIRC, the schemas could be assigned to
> login groups to limit ACCESS to various items.  It was all rather
> confusing to me but I was not tasked with understanding and implementing
> it.
>
> I do not understand how a schema could be used in the context of an
> infinite (or at least ever expanding) number of clients.
>
> I certainly don't have an answer for this problem but I am not
> convinced, given my run-in with schemas that they are that answer.
>
> You are the guru here though so I am interested in your take.
>
> John W. Colby
>
> On 3/31/2015 9:42 AM, fhtapia at gmail.com wrote:
> > Hello Mark,
> >    If the intent is to provide a segregated security space for multiple
> > users, then Schema's offers a huge advantage.  They are simply containers
> > to an entire database set per grouping.
> >
> > It is much simpler to set security based on the schema, than to focus on
> > the individual db Table.  not to mention, the peril of accidentally
> > returning data back to a customer that is not their own because the
> > query/sproc had access to any data in the table.
> >
> > more solid points:
> >
> > http://blog.sqlauthority.com/2009/09/07/sql-server-importanc
> e-of-database-schemas-in-sql-server/
> >
> > --
> > Francisco
> >
>