Controlling Division Access in Corporate Consolodated db

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
JimJim
Go thru this article. I am sure you'll find the answer.
"Jim Shaw" <jeshaw2@.comcast.net.work> wrote in message
news:%23lQmF4y8EHA.3416@.TK2MSFTNGP09.phx.gbl...
> 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
>|||Sorry
http://vyaskn.tripod.com/sql_server...t_practices.htm --secu
rity
best practices
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ljzLJz8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Jim
> Go thru this article. I am sure you'll find the answer.
>
> "Jim Shaw" <jeshaw2@.comcast.net.work> wrote in message
> news:%23lQmF4y8EHA.3416@.TK2MSFTNGP09.phx.gbl...
for[vbcol=seagreen]
in[vbcol=seagreen]
> that
> data
is[vbcol=seagreen]
user's[vbcol=seagreen]
> are
of[vbcol=seagreen]
> security?
and[vbcol=seagreen]
> corporate
division...there[vbcol=seagreen]
> queries/SELECT
>