Design Question 1

[dace]

Hi-

I'm just exploring options, and wanted some general opinions-

I have a large-scale custom .NET application that basically front-ends a lot of SQL database data, and controls a user's access to data, etc etc based on their Windows VPN login.

I'm looking to allow users to create Excel pivottables that show data from my app.
I don't want them to pull data directly from my database, I'd rather have my app handle security and permissions to only show the data a user can have access to according to my app- basically I want to be able to register an ODBC data source or something along those lines, and have access to data controlled by my app (ie, so I don't have to administrate a thousand SQL db user accounts). Could the MS SQL driver handle this, or would I need to (somehow!) create a custom driver? Does the driver even matter? How would I go about creating such a data source? Could I compile an OLAP cube and control information that way? (although I'd rather not).

I'm basically just stuck in design and looking for general thoughts

 

[johnherman]

A couple points to ponder:

OLAP is a good front-end for Excel pivot tables and security on the OLAP cube can be handled down to the individual cell level.

Security at the SQL Server level is handled via logins and roles. Roles could help reduce your security management problems, but may not be enough.

You can set up an ODBC driver which signs onto SQL Svr with wide open permissions and control the security via your application.

You could also set up views for the database and handle security at the view level. Remember that views do not override database security, however. They can only be more restrictive, not less.

Or you could use some combination of the above. For instance, one role for read-only applications, a set of views for this department or that, and a cube for another department.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[MDXer]

OLAP is a good front-end for Excel pivot tables and security on the OLAP cube can be handled down to the individual cell level.

While true it is unadvised if you have a large number of security roles and large dimensions, due to the way analysis services actually acomplishes this. Custom security based on role and dimension definitions can be a huge drain on available memory.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[RiverGuy]

1. Look up Application Roles in Books Online. You might want to go that route.

2. I think it would be way more work to try and circumvent the fact that to control each user individually, they will have to be separated and maintained somehow. Consider just making it standard procedure to add the user and permissions when they request access to your application.

 

[dace]

What I'm actually working on now is creating a custom data provider within the server-side architecture of my application by extending .NET interfaces to allow my application to process queries through ODBC and SQL. It seems to be working fairly well so far.

Thanks for the suggestions!