[dba-SQLServer] Schemas for separation of clients?

Mark Breen marklbreen at gmail.com
Tue Mar 31 09:21:52 CDT 2015


Hello Francisco

Thanks for the reply.

I agree with your comments.

My own comments were framed by firstly my not liking the duplicate sets of
tables from the outset.  Given the business app the real solution is
multiple dB's.

Perhaps I should have elaborated slightly on my particular case.  I built a
dB a few years ago in DNN and all the standard tables are dbo schema.  I
then added my own schema which became a pita.  All I really needed was a
suitable table name prefix, this would have established the sort order
grouped my tables together.  It was this grouping I was referring to in the
segregation concept.  This drove my suggestion to forget schemas.

I will re think my approach to schema's and security but for 100% of my
past cases, we use one user for every database.

On hindsight if Gustav did decide to use multiple security groups, schemas
are a great fit.

Thanks for the insight.

Mark.
On 31 Mar 2015 20:43, <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-importance-of-database-schemas-in-sql-server/
>
> --
> Francisco
>
> On Sun, Mar 29, 2015 at 10:59 AM Mark Breen <marklbreen at gmail.com> wrote:
>
> > Hello Gustav
> >
> > I would not take this approach for a few reasons.
> >
> > Before I start however, I see no technical reason why you cannot do it.
> In
> > fact you could even keep one schema and just set permission s at dB table
> > level.  I only used multiple schemas one time and found it a pita.
> >
> > 1 reason is such complexity for small financial benefit.  You are going
> > against the normal use and it will be a struggle sooner or later.
> >
> > 2 security will sooner or later leak across the tables.  You goal is
> > segregation but you are putting everything in one dB and hoping nobody
> > makes a mistake in a year or two.
> >
> > 3 financially, the gain is not sufficient to justify the hoops you have
> to
> > jump through.  Unless the customer is paying pennies.  If the budget is
> so
> > low to force this approach then reconsider and use a cloud VM and install
> > SQL express and have separate dB's for everyone.
> >
> > With aws and azure so cheap I would avoid the unconventional dB storage
> if
> > possible.
> >
> > Hth
> >
> > Mark
> >  On 29 Mar 2015 22:42, "Gustav Brock" <gustav at cactus.dk> wrote:
> >
> > > Hi Arthur
> > >
> > > The main reason is, that at Azure you don't pay by the instance but by
> > the
> > > database. The cheapest is B Basic at about USD 5 per month which is not
> > bad
> > > but, obviously, if that can serve perhaps 50 clients' data it's even
> > > better. An even better deal - given low usage - is the S0 Standard
> level
> > at
> > > about USD 14 per month for 250 GB database Space.
> > >
> > > As for the linking it should be quite easy; by default, a dbo.mytable
> is
> > > linked as dbo_mytable which you normally rename locally to mytable. So,
> > > when linking, you should link to client1.mytable, which will be given
> the
> > > name client1_mytable, which you rename to mytable. From that on, your
> > > application knows absolutely nothing about the tables of schema client2
> > etc.
> > >
> > > I cannot see why it shouldn't work, but I'm not a dba, indeed not
> > > regarding Azure SQL, so I'm looking for possible pitfalls.
> > >
> > > /gustav
> > >
> > > ________________________________________
> > > Fra: dba-SQLServer <dba-sqlserver-bounces at databaseadvisors.com> på
> vegne
> > > af Arthur Fuller <fuller.artful at gmail.com>
> > > Sendt: 29. marts 2015 15:13
> > > Til: Discussion concerning MS SQL Server
> > > Emne: Re: [dba-SQLServer] Schemas for separation of clients?
> > >
> > > Gustav,
> > >
> > > I don't have a precise answer for you. It's common practice in SQL
> Server
> > > to create "families" of tables; the AdventureWorks sample databases are
> > > show this ability off. But I have never tried to link to such a
> database
> > > from Access, so can't even guess how the table-names would appear in
> > > Access,
> > >
> > > It would seem that you are imposing a task on your potential users --
> all
> > > that renaming. I'm also short on knowledge about Azure, but despite
> > that, I
> > > must ask, Why not create one database for each user? Would that setup
> > > violate some constraint imposed by Azure's billing method?
> > >
> > > /Arthur
> > >
> > > On Sun, Mar 29, 2015 at 6:40 AM, Gustav Brock <gustav at cactus.dk>
> wrote:
> > >
> > > > Hi all
> > > >
> > > > Is it advisable and possible to create custom schemas in one database
> > to
> > > > to hold otherwise identical tables separated for each client?
> > > >
> > > > For example, two tables:
> > > >
> > > >     client1.invoice
> > > >     client2.invoice
> > > >
> > > > Then assign rights for client 1 to schema client1 only, and
> vice-versa
> > > for
> > > > client2.
> > > >
> > > > When linking the tables from Access, client1 would see only one set
> of
> > > > tables, link these, and name them locally using the last part of the
> > name
> > > > only: invoice.
> > > >
> > > > Would that be totally safe, so one client would never be able to see
> > data
> > > > belonging to other schemas?
> > > >
> > > > The reason for asking is, that the current minimum size offered at
> > Azure
> > > > (B Basic) is 2 GB which for the application, I have in mind, could
> hold
> > > > data for at least 25 clients. And the next step, S0 Standard, allows
> as
> > > > much as 250 GB. Of course, the DTUs also Counts but usage pr. client
> is
> > > > expected to be low.
> > > >
> > > > /gustav
> > > _______________________________________________
> > > dba-SQLServer mailing list
> > > dba-SQLServer at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > http://www.databaseadvisors.com
> > >
> > >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


More information about the dba-SQLServer mailing list