Question on security

Scenario: We have an Sql Server system and want to set up a payroll system.
We have about 30 tables in this database. We are now going to be handling
outside companies payroll information.
Setup 1: One of the fields in the tables would be a company code. This
would logically separate company A's data from company B's data.
Our question is on security. How secure is the data if it is all in one
database? If someone were to hack the database, they could potentially have
access to every companies data.
Setup 2: One way around that would be to replicate the 30 tables for each
company we bring on board. If we were to have 100 companies, we would have
100 databases and 300 tables.
This, of course, would be a problem as we make changes to the tables, since
we would have to make the changes to the same tables in each of the 100
databases.
One benefit to this setup is that if someone were to hack one database, they
wouldn't necessarilly have access to other companies data since they would
be in other database, even though all the databases reside on the same
machine under the same Sql Server.
This brings up the issue of multiple machines - which would really be going
overboard.
I am just trying to use due diligence here to see what my possible problems
are and whether we could be over thinking the problem.
Payroll data is very sensitive and we need to make sure we are going far
enough to protect it, but not too far to make it unwieldy.
The other question would be whether maybe having all the data in one
database might slow down the response too much where if we had separate
databases, searches and updates might be faster.
Just looking for ideas from others who may have wrestled with these
questions.
Thanks,
Tom.On Wed, 1 Dec 2004 15:57:48 -0800, "tshad"
<tscheiderich@.ftsolutions.com> wrote:
>Just looking for ideas from others who may have wrestled with these
>questions.
Old questions, everyone chooses their own answers.
You can partition tables and use views to restrict users to their own
company's rows and stuff like that, if you want to keep things in a
single database and/or single tables.
There are always tradeoffs between convenience, performance, and
security. What's the old cliche, "good, fast, cheap - choose two".
J.|||> The other question would be whether maybe having all the data in one
> database might slow down the response too much where if we had separate
> databases, searches and updates might be faster.
>
No, don't use separate databases. Nowadays you can have a very big database
and if you properly designed it you will not have any problem in terms of
perfomance. You probably would not want to maintain many databases all
system calagos and...
If you defined indexes on the tables and do provide tuning the database
searches and updates might be faster so it has not to do with having many
databases.
Also, I'd remove a guest account and make sure that all may users have
appropriate permissions to access the tables.
Don't GRANT them access to underlying tables instead create views to select
data.
Visit on this web site to read more about security of the database
http://vyaskn.tripod.com/sql_server_security_best_practices.htm
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OgCxbGA2EHA.2112@.TK2MSFTNGP15.phx.gbl...
> Scenario: We have an Sql Server system and want to set up a payroll
system.
> We have about 30 tables in this database. We are now going to be handling
> outside companies payroll information.
> Setup 1: One of the fields in the tables would be a company code. This
> would logically separate company A's data from company B's data.
> Our question is on security. How secure is the data if it is all in one
> database? If someone were to hack the database, they could potentially
have
> access to every companies data.
> Setup 2: One way around that would be to replicate the 30 tables for each
> company we bring on board. If we were to have 100 companies, we would
have
> 100 databases and 300 tables.
> This, of course, would be a problem as we make changes to the tables,
since
> we would have to make the changes to the same tables in each of the 100
> databases.
> One benefit to this setup is that if someone were to hack one database,
they
> wouldn't necessarilly have access to other companies data since they would
> be in other database, even though all the databases reside on the same
> machine under the same Sql Server.
> This brings up the issue of multiple machines - which would really be
going
> overboard.
> I am just trying to use due diligence here to see what my possible
problems
> are and whether we could be over thinking the problem.
> Payroll data is very sensitive and we need to make sure we are going far
> enough to protect it, but not too far to make it unwieldy.
> The other question would be whether maybe having all the data in one
> database might slow down the response too much where if we had separate
> databases, searches and updates might be faster.
> Just looking for ideas from others who may have wrestled with these
> questions.
> Thanks,
> Tom.
>