Controlling Division Access in Corporate Consolodated db

From: Jim Shaw (jeshaw2_at_comcast.net.work)
Date: 01/05/05 

Date: Wed, 5 Jan 2005 08:54:05 -0500

BlankI'm designing a corporate level database which holds information for
each of the corporation's operating divisions. Divisions are dynamic, in
that they added and deleted to the corporate structure frequently. Also,
employees are moved from one division to another frequently. The roles that
employees perform are standardized across the entire corporation. The data
algorithms & structure is also set by corporate policy. Each division is
uniquely identified by a 3-digit "FacilityID" code.

In all the db tables, views, functions, etc., I need to restrict a user's
privileges to the rows of data that relate to the division in which they are
currently employed. Corporate users should have privileges across all of
the multiple divisions data.

Is there a "best Practices" established to implement this type of security?

I'm thinking of using views to define the role's access to data tables and
columns. Then using Select statements to access the view with a WHERE
clause to specify the FacilityID. Can this be done in SQL 2000?

I Would like to avoid the need for separate Select statements for corporate
and division users.

I also want to avoid different hard coded views for each division...there
are over 200 them. I'm thinking about dynamically generated queries/SELECT
statements (like what can be done in Access 2000)?

Thanks
Jim

Relevant Pages

  • Re: Controlling Division Access in Corporate Consolodated db
    ... > employees perform are standardized across the entire corporation. ... > privileges to the rows of data that relate to the division in which they ... Corporate users should have privileges across all of ... > I also want to avoid different hard coded views for each division...there ...
    (microsoft.public.sqlserver.security)
  • Re: Controlling Division Access in Corporate Consolodated db
    ... >> employees perform are standardized across the entire corporation. ... Corporate users should have privileges across all ... >> I also want to avoid different hard coded views for each ...
    (microsoft.public.sqlserver.security)
  • Re: values of hash of hash
    ... Each project have many employees, each employee have many tasks, each task have time, budget etc... ... I try to avoid. ... I want to get 7 and 6 without using three for loop and keys function How can I do it? ...
    (comp.lang.perl.misc)
  • Partial Replication
    ... Is it possible to replicate just some records in a table and can the filter ... be implemented (I am trying to avoid a situation that employees will have ...
    (microsoft.public.sqlserver.replication)