Data security/filtering on field values

My team is developing a new software system that is replacing several
existing applications. The applications being replaced focus on billing and
customer relationship management.
Our company is an international corporation that serves a large number of
customers (hundreds of thousands) spread throughout the US and Canada. For
administrative and accounting reasons, we have divided the customers into
groups based on geographic region (Eastern, Central, and Western US, Canada)
.
Customers are also grouped within their Region into Service Areas.
Our new system requires that authorization be performed based on both the
roles of the user (basic RBAC) and on the set of data that the user is
authorized to view and manipulate.
For example, a customer service rep for the West region of the country
should only be able to see and work with customers that are located in the
West region – to this user, the rest of our customers are irrelevant. The
system should behave as though the only customers that exist are those
located in the region with which they work.
In addition, we need to set up non-geographic data filters, such as the
customer classification (similar to SIC code) and value fields such as
Salesperson.
The filtering of data must allow for both unions (“Or”) and intersection
s
(“And”) between different security settings; for instance, we must be ab
le to
support the following overlapping sets of data filters:
1) All customers in the “East” region and all customers with the
classification “Hospital”
2) All “East Hospital” customers (but no Hospitals outside of the East
region or East customers that are not Hospitals)
3) All customers in either the “East” or “West” regions, regardless
of
classification
We also need to support the concept of “Data Restrictions”, where the
default access to customers with a specific attribute is False unless a gran
t
is assigned.
We need a solution, whether it is a proven architectural approach or a
third-party technology, which will allow us to apply the defined data filter
s
at the lowest level of our application as possible with as little performanc
e
impact as possible. We would prefer to implement something in the database,
but if necessary we could implement it in the data access layer of our
software.
The solution should allow flexible definition of ‘securable attributes’
of a
customer, so that if an additional attributes are defined in the future we
don’t need to modify the database structure or recompile any code.
Questions:
1) Is there a standard or best-practice architectural approach to applying
the sort of security/filtering described above?
2) Are there any third-party tools or technologies that we could purchase
that would provide some or all of this functionality?
3) What sorts of approaches have been used before to address this type of
security requirement?
Thanks in advance,
GuyAs I flew above your text, that thing came in mind for me:
http://vyaskn.tripod.com/ row_level...as
es.htm
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Guy" <Guy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B4BD9CCC-1B6C-4B49-BEFA-49551E7F6167@.microsoft.com...
> My team is developing a new software system that is replacing several
> existing applications. The applications being replaced focus on billing
> and
> customer relationship management.
> Our company is an international corporation that serves a large number of
> customers (hundreds of thousands) spread throughout the US and Canada.
> For
> administrative and accounting reasons, we have divided the customers into
> groups based on geographic region (Eastern, Central, and Western US,
> Canada).
> Customers are also grouped within their Region into Service Areas.
> Our new system requires that authorization be performed based on both the
> roles of the user (basic RBAC) and on the set of data that the user is
> authorized to view and manipulate.
> For example, a customer service rep for the West region of the country
> should only be able to see and work with customers that are located in the
> West region - to this user, the rest of our customers are irrelevant. The
> system should behave as though the only customers that exist are those
> located in the region with which they work.
> In addition, we need to set up non-geographic data filters, such as the
> customer classification (similar to SIC code) and value fields such as
> Salesperson.
> The filtering of data must allow for both unions ("Or") and intersections
> ("And") between different security settings; for instance, we must be able
> to
> support the following overlapping sets of data filters:
> 1) All customers in the "East" region and all customers with the
> classification "Hospital"
> 2) All "East Hospital" customers (but no Hospitals outside of the East
> region or East customers that are not Hospitals)
> 3) All customers in either the "East" or "West" regions, regardless of
> classification
> We also need to support the concept of "Data Restrictions", where the
> default access to customers with a specific attribute is False unless a
> grant
> is assigned.
> We need a solution, whether it is a proven architectural approach or a
> third-party technology, which will allow us to apply the defined data
> filters
> at the lowest level of our application as possible with as little
> performance
> impact as possible. We would prefer to implement something in the
> database,
> but if necessary we could implement it in the data access layer of our
> software.
> The solution should allow flexible definition of 'securable attributes' of
> a
> customer, so that if an additional attributes are defined in the future we
> don't need to modify the database structure or recompile any code.
> Questions:
> 1) Is there a standard or best-practice architectural approach to applying
> the sort of security/filtering described above?
> 2) Are there any third-party tools or technologies that we could purchase
> that would provide some or all of this functionality?
> 3) What sorts of approaches have been used before to address this type of
> security requirement?
> Thanks in advance,
> Guysql

Data Flow Task Problem

I have a Windows XP X64 machine with SQL 2005 Developer and VS 2005 Team Edition for Architects on it. For the most part it appears that all normal VS and SQL functions are working properly with the exception of SSIS. If I open the developer studio and drag a Data Flow Task onto the design surface I get the message below. I have tried doing an unistall and reinstall of Integration Services as well as a repair on VS 2005 with no success. I've searched the web and newsgroups and can't find any mention of the problem I'm having. Any help greatly appreciated.
===================================

Failed to create the task. (Microsoft Visual Studio)

===================================

The designer could not be initialized. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=TaskDesignerCouldNotBeInitialized&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Design.PipelineTaskView.Microsoft.SqlServer.Dts.Runtime.Design.IDtsTaskUI.Initialize(TaskHost taskWrapper, IServiceProvider serviceProvider)
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.InitializeTaskUI()
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.OnNewTaskAdded()
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

Try this.

Go to Windows services, check account under which "SQL Server Integration Services" run.

Switch it to "Local System account"

BAZ

|||

I don't see how the service will effect a designer issue. The service is only used for server storage support or during execution. Local System is not very good practice either, you should understand the security risks involved.

Unfortunately I don't have a solution to the original problem either. Maybe try a Reset Toolbox, as this should clean out any rubbish, and it seems like some tasks are rubbish, or at least not installed correctly. This may prompt something.

|||Hi,

I had the error below when adding data flow tasks in the designer, and Baz' advice worked. Must be some strange permission issue, but i do not know enough about this stuff yet to comment further.

Registration information about the Data Flow task could not be retrieved. Confirm that this task is installed properly on the computer.

ADDITIONAL INFORMATION:

TaskHost "{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"' is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)|||I recently ran into this problem and BAZ's suggestion fixed the issue.
|||BAZMIC solution did'nt worked for me... don't know what to do...
|||I had the exact same problem and BAZMIC's solution worked for me as well.|||

Regarding how the IS service affects the designer...

When the designer starts, it needs to know what IS components (e.g. data flow components, tasks, enumerators, etc) are available on the machine. The discovery process can delay the designer start-up enough to cause a bad user experience. To improve the start-up time, the service caches the information which can then be accessed by the designer and saves it from having to go through the discovery process each time. If the designer starts up and the service is not started, the service will attempt to start and fill its cache. Knowing this might help you troubleshoot these kinds of issues further.

|||

I have just upgraded my SQL 2005 to SP2.

I had the error stated in this thread, and tried Baz's solution.

However now I am getting this error message whenever I try to open a DataTask in SSIS:

TITLE: Microsoft Visual Studio

Cannot show the editor for this task.

ADDITIONAL INFORMATION:

The task returned an unsupported control editor type. (Microsoft.DataTransformationServices.Design)

Can anyone please offer some advice?

|||

Anonymous581024 wrote:

BAZMIC solution did'nt worked for me... don't know what to do...

I tried everything and it never worked. I finally deinstalled SQL Server 2005 Integration Services and reinstalled it.
|||

Thanks Bazmic it works with me |||This is apparantly in some cases a problem with the SP2 that was deployed. After installing this on the SQL Server 2005 I have had a variety of problems including this one.

Data Flow Task Problem

I have a Windows XP X64 machine with SQL 2005 Developer and VS 2005 Team Edition for Architects on it. For the most part it appears that all normal VS and SQL functions are working properly with the exception of SSIS. If I open the developer studio and drag a Data Flow Task onto the design surface I get the message below. I have tried doing an unistall and reinstall of Integration Services as well as a repair on VS 2005 with no success. I've searched the web and newsgroups and can't find any mention of the problem I'm having. Any help greatly appreciated.
===================================

Failed to create the task. (Microsoft Visual Studio)

===================================

The designer could not be initialized. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=TaskDesignerCouldNotBeInitialized&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Design.PipelineTaskView.Microsoft.SqlServer.Dts.Runtime.Design.IDtsTaskUI.Initialize(TaskHost taskWrapper, IServiceProvider serviceProvider)
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.InitializeTaskUI()
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.OnNewTaskAdded()
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

Try this.

Go to Windows services, check account under which "SQL Server Integration Services" run.

Switch it to "Local System account"

BAZ

|||

I don't see how the service will effect a designer issue. The service is only used for server storage support or during execution. Local System is not very good practice either, you should understand the security risks involved.

Unfortunately I don't have a solution to the original problem either. Maybe try a Reset Toolbox, as this should clean out any rubbish, and it seems like some tasks are rubbish, or at least not installed correctly. This may prompt something.

|||Hi,

I had the error below when adding data flow tasks in the designer, and Baz' advice worked. Must be some strange permission issue, but i do not know enough about this stuff yet to comment further.

Registration information about the Data Flow task could not be retrieved. Confirm that this task is installed properly on the computer.

ADDITIONAL INFORMATION:

TaskHost "{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"' is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)|||I recently ran into this problem and BAZ's suggestion fixed the issue.
|||BAZMIC solution did'nt worked for me... don't know what to do...
|||I had the exact same problem and BAZMIC's solution worked for me as well.|||

Regarding how the IS service affects the designer...

When the designer starts, it needs to know what IS components (e.g. data flow components, tasks, enumerators, etc) are available on the machine. The discovery process can delay the designer start-up enough to cause a bad user experience. To improve the start-up time, the service caches the information which can then be accessed by the designer and saves it from having to go through the discovery process each time. If the designer starts up and the service is not started, the service will attempt to start and fill its cache. Knowing this might help you troubleshoot these kinds of issues further.

|||

I have just upgraded my SQL 2005 to SP2.

I had the error stated in this thread, and tried Baz's solution.

However now I am getting this error message whenever I try to open a DataTask in SSIS:

TITLE: Microsoft Visual Studio

Cannot show the editor for this task.

ADDITIONAL INFORMATION:

The task returned an unsupported control editor type. (Microsoft.DataTransformationServices.Design)

Can anyone please offer some advice?

|||

Anonymous581024 wrote:

BAZMIC solution did'nt worked for me... don't know what to do...

I tried everything and it never worked. I finally deinstalled SQL Server 2005 Integration Services and reinstalled it.
|||Thanks Bazmic it works with me |||This is apparantly in some cases a problem with the SP2 that was deployed. After installing this on the SQL Server 2005 I have had a variety of problems including this one.

Data Flow Task Problem

I have a Windows XP X64 machine with SQL 2005 Developer and VS 2005 Team Edition for Architects on it. For the most part it appears that all normal VS and SQL functions are working properly with the exception of SSIS. If I open the developer studio and drag a Data Flow Task onto the design surface I get the message below. I have tried doing an unistall and reinstall of Integration Services as well as a repair on VS 2005 with no success. I've searched the web and newsgroups and can't find any mention of the problem I'm having. Any help greatly appreciated.
===================================

Failed to create the task. (Microsoft Visual Studio)

===================================

The designer could not be initialized. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=TaskDesignerCouldNotBeInitialized&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Design.PipelineTaskView.Microsoft.SqlServer.Dts.Runtime.Design.IDtsTaskUI.Initialize(TaskHost taskWrapper, IServiceProvider serviceProvider)
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.InitializeTaskUI()
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.OnNewTaskAdded()
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

Try this.

Go to Windows services, check account under which "SQL Server Integration Services" run.

Switch it to "Local System account"

BAZ

|||

I don't see how the service will effect a designer issue. The service is only used for server storage support or during execution. Local System is not very good practice either, you should understand the security risks involved.

Unfortunately I don't have a solution to the original problem either. Maybe try a Reset Toolbox, as this should clean out any rubbish, and it seems like some tasks are rubbish, or at least not installed correctly. This may prompt something.

|||Hi,

I had the error below when adding data flow tasks in the designer, and Baz' advice worked. Must be some strange permission issue, but i do not know enough about this stuff yet to comment further.

Registration information about the Data Flow task could not be retrieved. Confirm that this task is installed properly on the computer.

ADDITIONAL INFORMATION:

TaskHost "{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"' is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)|||I recently ran into this problem and BAZ's suggestion fixed the issue.|||BAZMIC solution did'nt worked for me... don't know what to do...|||I had the exact same problem and BAZMIC's solution worked for me as well.|||

Regarding how the IS service affects the designer...

When the designer starts, it needs to know what IS components (e.g. data flow components, tasks, enumerators, etc) are available on the machine. The discovery process can delay the designer start-up enough to cause a bad user experience. To improve the start-up time, the service caches the information which can then be accessed by the designer and saves it from having to go through the discovery process each time. If the designer starts up and the service is not started, the service will attempt to start and fill its cache. Knowing this might help you troubleshoot these kinds of issues further.

|||

I have just upgraded my SQL 2005 to SP2.

I had the error stated in this thread, and tried Baz's solution.

However now I am getting this error message whenever I try to open a DataTask in SSIS:

TITLE: Microsoft Visual Studio

Cannot show the editor for this task.

ADDITIONAL INFORMATION:

The task returned an unsupported control editor type. (Microsoft.DataTransformationServices.Design)

Can anyone please offer some advice?

|||

Anonymous581024 wrote:

BAZMIC solution did'nt worked for me... don't know what to do...

I tried everything and it never worked. I finally deinstalled SQL Server 2005 Integration Services and reinstalled it.

Data Flow Task Problem

I have a Windows XP X64 machine with SQL 2005 Developer and VS 2005 Team Edition for Architects on it. For the most part it appears that all normal VS and SQL functions are working properly with the exception of SSIS. If I open the developer studio and drag a Data Flow Task onto the design surface I get the message below. I have tried doing an unistall and reinstall of Integration Services as well as a repair on VS 2005 with no success. I've searched the web and newsgroups and can't find any mention of the problem I'm having. Any help greatly appreciated.
===================================

Failed to create the task. (Microsoft Visual Studio)

===================================

The designer could not be initialized. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=TaskDesignerCouldNotBeInitialized&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Design.PipelineTaskView.Microsoft.SqlServer.Dts.Runtime.Design.IDtsTaskUI.Initialize(TaskHost taskWrapper, IServiceProvider serviceProvider)
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.InitializeTaskUI()
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.OnNewTaskAdded()
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

Try this.

Go to Windows services, check account under which "SQL Server Integration Services" run.

Switch it to "Local System account"

BAZ

|||

I don't see how the service will effect a designer issue. The service is only used for server storage support or during execution. Local System is not very good practice either, you should understand the security risks involved.

Unfortunately I don't have a solution to the original problem either. Maybe try a Reset Toolbox, as this should clean out any rubbish, and it seems like some tasks are rubbish, or at least not installed correctly. This may prompt something.

|||Hi,

I had the error below when adding data flow tasks in the designer, and Baz' advice worked. Must be some strange permission issue, but i do not know enough about this stuff yet to comment further.

Registration information about the Data Flow task could not be retrieved. Confirm that this task is installed properly on the computer.

ADDITIONAL INFORMATION:

TaskHost "{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"' is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)|||I recently ran into this problem and BAZ's suggestion fixed the issue.
|||BAZMIC solution did'nt worked for me... don't know what to do...
|||I had the exact same problem and BAZMIC's solution worked for me as well.|||

Regarding how the IS service affects the designer...

When the designer starts, it needs to know what IS components (e.g. data flow components, tasks, enumerators, etc) are available on the machine. The discovery process can delay the designer start-up enough to cause a bad user experience. To improve the start-up time, the service caches the information which can then be accessed by the designer and saves it from having to go through the discovery process each time. If the designer starts up and the service is not started, the service will attempt to start and fill its cache. Knowing this might help you troubleshoot these kinds of issues further.

|||

I have just upgraded my SQL 2005 to SP2.

I had the error stated in this thread, and tried Baz's solution.

However now I am getting this error message whenever I try to open a DataTask in SSIS:

TITLE: Microsoft Visual Studio

Cannot show the editor for this task.

ADDITIONAL INFORMATION:

The task returned an unsupported control editor type. (Microsoft.DataTransformationServices.Design)

Can anyone please offer some advice?

|||

Anonymous581024 wrote:

BAZMIC solution did'nt worked for me... don't know what to do...

I tried everything and it never worked. I finally deinstalled SQL Server 2005 Integration Services and reinstalled it.

Data Flow Task Problem

I have a Windows XP X64 machine with SQL 2005 Developer and VS 2005 Team Edition for Architects on it. For the most part it appears that all normal VS and SQL functions are working properly with the exception of SSIS. If I open the developer studio and drag a Data Flow Task onto the design surface I get the message below. I have tried doing an unistall and reinstall of Integration Services as well as a repair on VS 2005 with no success. I've searched the web and newsgroups and can't find any mention of the problem I'm having. Any help greatly appreciated.
===================================

Failed to create the task. (Microsoft Visual Studio)

===================================

The designer could not be initialized. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=TaskDesignerCouldNotBeInitialized&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Design.PipelineTaskView.Microsoft.SqlServer.Dts.Runtime.Design.IDtsTaskUI.Initialize(TaskHost taskWrapper, IServiceProvider serviceProvider)
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.InitializeTaskUI()
at Microsoft.DataTransformationServices.Design.DtrTaskDesigner.OnNewTaskAdded()
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

Try this.

Go to Windows services, check account under which "SQL Server Integration Services" run.

Switch it to "Local System account"

BAZ

|||

I don't see how the service will effect a designer issue. The service is only used for server storage support or during execution. Local System is not very good practice either, you should understand the security risks involved.

Unfortunately I don't have a solution to the original problem either. Maybe try a Reset Toolbox, as this should clean out any rubbish, and it seems like some tasks are rubbish, or at least not installed correctly. This may prompt something.

|||Hi,

I had the error below when adding data flow tasks in the designer, and Baz' advice worked. Must be some strange permission issue, but i do not know enough about this stuff yet to comment further.

Registration information about the Data Flow task could not be retrieved. Confirm that this task is installed properly on the computer.

ADDITIONAL INFORMATION:

TaskHost "{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"' is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)|||I recently ran into this problem and BAZ's suggestion fixed the issue.
|||BAZMIC solution did'nt worked for me... don't know what to do...
|||I had the exact same problem and BAZMIC's solution worked for me as well.|||

Regarding how the IS service affects the designer...

When the designer starts, it needs to know what IS components (e.g. data flow components, tasks, enumerators, etc) are available on the machine. The discovery process can delay the designer start-up enough to cause a bad user experience. To improve the start-up time, the service caches the information which can then be accessed by the designer and saves it from having to go through the discovery process each time. If the designer starts up and the service is not started, the service will attempt to start and fill its cache. Knowing this might help you troubleshoot these kinds of issues further.

|||

I have just upgraded my SQL 2005 to SP2.

I had the error stated in this thread, and tried Baz's solution.

However now I am getting this error message whenever I try to open a DataTask in SSIS:

TITLE: Microsoft Visual Studio

Cannot show the editor for this task.

ADDITIONAL INFORMATION:

The task returned an unsupported control editor type. (Microsoft.DataTransformationServices.Design)

Can anyone please offer some advice?

|||

Anonymous581024 wrote:

BAZMIC solution did'nt worked for me... don't know what to do...

I tried everything and it never worked. I finally deinstalled SQL Server 2005 Integration Services and reinstalled it.
|||Thanks Bazmic it works with me |||This is apparantly in some cases a problem with the SP2 that was deployed. After installing this on the SQL Server 2005 I have had a variety of problems including this one.