Limiting views on data in a table

From: Mike Lerch (mlerchNOSPAMTHANKS_at_nycap.rr.com)
Date: 06/12/03

  • Next message: Gustavo G. Forster: "Re: Problems with MSDE installation" 
    Date: Thu, 12 Jun 2003 17:00:59 -0400

    I have a scenario that I haven't read a solution to. Imagine that you
    have a table full of national sales info. You want the field
    salespeople to be able to view reports of their sales info, but they
    shouldn't be able to see other people's stuff.

    The way I was implementing this was to use Windows Auth with a Windows
    Group. For instance, all the sales people would be a member of
    Domain\Sales. I already have a table that shows which cities are
    covered by which salespeople, an access table. My thought was to use
    stored procedures that would get passed the username of the
    salesperson. The procedure would figure out, from the access table,
    what records from the sales table they are able to see, and only those
    records would be returned. My understanding is that you can deny read
    permission to a table but give execute permission to a stored
    procedure that uses that table...so the salespeople wouldn't be able
    to just open the table even if they wanted to: they have to interact
    with it via the stored procedures.

    But here's the rub: assuming that one of the salespeople knew the
    path to the server, what would stop one of the salespeople from using
    an MS Access Project or for that matter Query Analyzer or Enterprise
    Manager to get into the database and run those stored procedures?
    After all, they have to have access to the database to be able to have
    execute the stored procedures, right?

    Please let me know if that is correct and, if so, how to deal with it!
    One thing we DON'T want to do is create separate views for
    everyone...the access list changes all the time as people are added or
    dropped and as territories are shifted around. Any help is greatly
    appreciated!

  • Next message: Gustavo G. Forster: "Re: Problems with MSDE installation"