How to restrict any select to a subset?

Hi;

We are designing a web application where multiple companies can use it. We
want to give every company access to their data, and only their data. So if
both Oracle and Microsoft are using the system, only Microsoft can see the
Microsoft data and only Oracle can see the Oracle data.

But with that restriction, we want to give them access to all tables. And
for some tables determining that restriction can be indirect. For example, an
invoice record may have a FK of the company, but an invoice item record only
has a FK of the invoice.

Is there a way to restrict selects to only the rows of data a company
"owns." Or any other suggestions on how to do this? Creating a database for
each company would work - but that means creating thousands of databases on
one server and I am guessing that is inefficient???

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

David,

Proabably don't I understand your question.

What is in this case the difference between the Application User and a
Application User Company?

Cor

Hi;

They are both the same thing. The problem is we will have multiple different
Application User's with data in the DB.

Let me ask a different question as I may be under a false assumption. Lets
say we have 10,000 different Application Users. Can we then create 10,000
databases on a single Sql Server system? Or will that kill it? I've been
assuming that that would be bad (just like events are better than threads and
threads are better than processes and 10,000 processes will kill Windows).

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




"Cor Ligthert[MVP]" wrote:

David,

Proabably don't I understand your question.

What is in this case the difference between the Application User and a
Application User Company?

Cor

David,

Normally I and probably most of us would solve this by creating a table with
the users (companies), then the properties used and add those properties to
the tables to select the rows which are available for the users (companies).
Why is this approach not taken?

Cor

"David Thielen" <thielen (AT) nospam (DOT) nospam> schreef in bericht
news:1CAD6992-DCB6-48EF-806A-B61D936C406F (AT) microsoft (DOT) com...

Hi;

They are both the same thing. The problem is we will have multiple
different
Application User's with data in the DB.

Let me ask a different question as I may be under a false assumption. Lets
say we have 10,000 different Application Users. Can we then create 10,000
databases on a single Sql Server system? Or will that kill it? I've been
assuming that that would be bad (just like events are better than threads
and
threads are better than processes and 10,000 processes will kill Windows).

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




"Cor Ligthert[MVP]" wrote:

David,

Proabably don't I understand your question.

What is in this case the difference between the Application User and a
Application User Company?

Cor

It's a much more complicated schema than that. We have about 20 tables. One
example is the invoice table - which does have a column for the user. But
there is a table of invoice items and it's only foreign key is the PK to the
row in the invoice table.

If we control all of the selects, no problem (assuming we don't make any
mistakes). But we want to let our users have the abaility to pull any of
their data out and so we want to give them the ability to make their own
selects, but then insure they get only their data.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




"Cor Ligthert[MVP]" wrote:

David,

Normally I and probably most of us would solve this by creating a table with
the users (companies), then the properties used and add those properties to
the tables to select the rows which are available for the users (companies).
Why is this approach not taken?

Cor

"David Thielen" <thielen (AT) nospam (DOT) nospam> schreef in bericht
news:1CAD6992-DCB6-48EF-806A-B61D936C406F (AT) microsoft (DOT) com...
Hi;

They are both the same thing. The problem is we will have multiple
different
Application User's with data in the DB.

Let me ask a different question as I may be under a false assumption. Lets
say we have 10,000 different Application Users. Can we then create 10,000
databases on a single Sql Server system? Or will that kill it? I've been
assuming that that would be bad (just like events are better than threads
and
threads are better than processes and 10,000 processes will kill Windows).

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




"Cor Ligthert[MVP]" wrote:

David,

Proabably don't I understand your question.

What is in this case the difference between the Application User and a
Application User Company?

Cor

David,

Then why no helper tables to add the information that is needed to the rows?

Cor

"David Thielen" <thielen (AT) nospam (DOT) nospam> schreef in bericht
news:91764E3B-8C1A-4183-9B73-E90C82F3336F (AT) microsoft (DOT) com...

It's a much more complicated schema than that. We have about 20 tables.
One
example is the invoice table - which does have a column for the user. But
there is a table of invoice items and it's only foreign key is the PK to
the
row in the invoice table.

If we control all of the selects, no problem (assuming we don't make any
mistakes). But we want to let our users have the abaility to pull any of
their data out and so we want to give them the ability to make their own
selects, but then insure they get only their data.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




"Cor Ligthert[MVP]" wrote:

David,

Normally I and probably most of us would solve this by creating a table
with
the users (companies), then the properties used and add those properties
to
the tables to select the rows which are available for the users
(companies).
Why is this approach not taken?

Cor

"David Thielen" <thielen (AT) nospam (DOT) nospam> schreef in bericht
news:1CAD6992-DCB6-48EF-806A-B61D936C406F (AT) microsoft (DOT) com...
Hi;

They are both the same thing. The problem is we will have multiple
different
Application User's with data in the DB.

Let me ask a different question as I may be under a false assumption.
Lets
say we have 10,000 different Application Users. Can we then create
10,000
databases on a single Sql Server system? Or will that kill it? I've
been
assuming that that would be bad (just like events are better than
threads
and
threads are better than processes and 10,000 processes will kill
Windows).

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




"Cor Ligthert[MVP]" wrote:

David,

Proabably don't I understand your question.

What is in this case the difference between the Application User and a
Application User Company?

Cor

Hi;

I tried to find what you meant by helper tables but googling that got me all
kinds of different things. What do you mean by helper tables?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




"Cor Ligthert[MVP]" wrote:

David,

Then why no helper tables to add the information that is needed to the rows?

Cor

"David Thielen" <thielen (AT) nospam (DOT) nospam> schreef in bericht
news:91764E3B-8C1A-4183-9B73-E90C82F3336F (AT) microsoft (DOT) com...
It's a much more complicated schema than that. We have about 20 tables.
One
example is the invoice table - which does have a column for the user. But
there is a table of invoice items and it's only foreign key is the PK to
the
row in the invoice table.

If we control all of the selects, no problem (assuming we don't make any
mistakes). But we want to let our users have the abaility to pull any of
their data out and so we want to give them the ability to make their own
selects, but then insure they get only their data.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




"Cor Ligthert[MVP]" wrote:

David,

Normally I and probably most of us would solve this by creating a table
with
the users (companies), then the properties used and add those properties
to
the tables to select the rows which are available for the users
(companies).
Why is this approach not taken?

Cor

"David Thielen" <thielen (AT) nospam (DOT) nospam> schreef in bericht
news:1CAD6992-DCB6-48EF-806A-B61D936C406F (AT) microsoft (DOT) com...
Hi;

They are both the same thing. The problem is we will have multiple
different
Application User's with data in the DB.

Let me ask a different question as I may be under a false assumption.
Lets
say we have 10,000 different Application Users. Can we then create
10,000
databases on a single Sql Server system? Or will that kill it? I've
been
assuming that that would be bad (just like events are better than
threads
and
threads are better than processes and 10,000 processes will kill
Windows).

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




"Cor Ligthert[MVP]" wrote:

David,

Proabably don't I understand your question.

What is in this case the difference between the Application User and a
Application User Company?

Cor

It can be all kind of tables,

However you have your existing data and want to keep that seperated from
your data to find information.

Then you can add another table(s) with references to your data which hold
the properties to specify the row as a special row for a special company.

I don't know if the name of that is a helper table, it is just the name I
call it like that.

Cor

One approach would be to add a column to the target tables that would
specify the User/Company that has access to the row. This could be a coded
value that might mean that only a specific company has acess to the row or
that several users could gain access. Of course, multiple users/row would be
harder to manage concurrency as more than one user might "own" the row, so I
would avoid this approach--one user per row would be manageable. When you
executed the SELECT the User_Owns column would be specified in the WHERE
clause.

--
__________________________________________________ ________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
__________________________________________________ __________________________________________
"David Thielen" <thielen (AT) nospam (DOT) nospam> wrote

Hi;

We are designing a web application where multiple companies can use it. We
want to give every company access to their data, and only their data. So
if
both Oracle and Microsoft are using the system, only Microsoft can see the
Microsoft data and only Oracle can see the Oracle data.

But with that restriction, we want to give them access to all tables. And
for some tables determining that restriction can be indirect. For example,
an
invoice record may have a FK of the company, but an invoice item record
only
has a FK of the invoice.

Is there a way to restrict selects to only the rows of data a company
"owns." Or any other suggestions on how to do this? Creating a database
for
each company would work - but that means creating thousands of databases
on
one server and I am guessing that is inefficient???

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm