Re: Limiting Selection of Data by User ID

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Toby Herring (therring*_at_*teletrack.com)
Date: 11/16/04 

Date: Tue, 16 Nov 2004 10:27:10 -0500

I have five thoughts on this:

1) As MC suggested, do everything through stored procedures, with every
stored procedure REQUIRING a @CompanyId parameter.

2) An extension of #1, that would still allow ad-hoc or parameterized
queries to be executed outside stored procs would be to create an inline
table function that matches every regular table, taking @CompanyId as a
parameter.

3) Create a set of views for each table, where each view only displays data
from the matching table with the proper CompanyId. (Like your original
thought.) This has the major disadvantage that if you ever add another
company, you have to create yet another set of duplicate views for the new
company, and modify them appropriately. Bleh.

4) Create a separate database for each company, where the DB name is
related to the company somehow (i.e. DB_1, DB_2 or Acme, MyCompany). Each
of these databases would have no tables. Each table from the main database
would have a view in the company-centric database with the same name, that
filtered the data from the data table based on the CompanyId corresponding
to the database the view exists in. If done properly, adding a new company
database is as simple as scripting objects from one of the existing ones,
and executing the script in the new database. This can be cumbersome and
has a side effect in that you'll take certain performance hits for the
cross-database references (frequent recompiles, mainly.)

5) Simply create a separate database for each company, and save yourself
the headaches of trying to keep everyone from accessing each others' data. 

-- 
Toby Herring
MCDBA, MCSD, MCP+SB
Need a Second Life? 
http://secondlife.com/ss/?u=03e0e5b303c234bf08e80ee40119a65e
"jim_guyette" <jim_guyette@discussions.microsoft.com> wrote in message 
news:9DDCE797-8F02-4779-AAA3-9FAF4C33AB0D@microsoft.com...
>I have a table in a database that will be used by three seperate companies.
> Each company needs to be able to view and update their own data, but not 
> see
> any of the other companies data.
>
> I need to see all three companies data.
>
> I was thinking of using a 'View' to do this:
> Create View Company1View as
>    Select * from Table where CompanyID = Company1
>
> I would set up a view for each company and give them the appropriate 
> rights
> to the view, but no rights to the table.
>
> I don't personally like this solution because it requires custom code for
> each company when it's time to select the data.  Is there a better way to 
> do
> this?
>
>

Relevant Pages

  • Re: Database Model - Class, objects and interaction
    ... I was just proving stored procedures can call views. ... stuff like security and logging. ... So given 100,000 users, you would create database accounts for each? ... Part of our system's integrity was its reliability, and reliability is often assisted by simplicity. ...
    (comp.object)
  • Re: Database Model - Class, objects and interaction
    ... Stored procedures should only be used as a last resort if you cant use ... I've not said that views shouldn't be created, only that they shouldn't be exposed as the interface to the database. ... But business rules can be more complicated than simply defining overdue invoices. ...
    (comp.object)
  • Re: Infinite Loops and Explicit Exits
    ... the whole payroll system would be dependent on proper database ... I design large systems the same way. ... Stored procedures ARE application code. ... any language, including Cobol. ...
    (comp.lang.cobol)
  • Re: Data security
    ... look into using Stored procedures. ... > database. ... > the database and revoke rights at tat moment in time? ... > developers are developing and be comfortable that the data is secure? ...
    (microsoft.public.sqlserver.security)
  • Re: Identity Column
    ... the end use is using Crystal Reports - where is the magic? ... The user will connect using Active Directory from their desktop to the database server or do please explain the magic.... ... Using stored procedures you permission on the stored proc only; the user can only execute the stored procedure with the parameters defined, there is no select * from to get all the information in your database. ...
    (microsoft.public.sqlserver.programming)