April 12, 2012 at 5:16 pm
I've got a dumb question... so dumb, I don't know how to ask it, what key words to search, or where to start reading. I'm not even sure this is the right board!
None-the-less... here goes. I just need to be pointed in the right direction...
I have this database, it has about 800 or so tables and about as many views from a financial transaction platform data model that I don't control.
I have end users that would like to have a little "freedom" in creating their own reports and such (and an rabid internal auditor that wants to be able to access data without IT controlling what he sees). It is completely unreasonable to assume that the end users understand table linking and table cardinality...especially on a database so large. God help me if we have teach them about Left outer Joins.
So... what I want to do is create "layers"... views over the top of the model the present "most commonly accessed" data in a flat structure that allows me to present a simplified data model. I thought I would even go so far as to put those objects in a seperate database, call it MiniMart (for the lack of a better name), and then have those views reach over to the other database to return data. Then the user is presented with a less daunting 20 objects, instead of an overwhelming 2000.
use minimart
go
create view customer_detail as
select customer_name, account_nbr, yada, yada
from datamart.dbo.customer cust
inner join datmart.dbo.accounts acct
on blah=blah
go
Here is my dillema. If I create views in my minimart database, give users access that database and the views I want them to have... their user would still need rights to the datamart database for those views to access the data in the datamart database...right? If I had an adventurous user with a copy of Excel, he could browse databases, and get to my underlying objects... and defeat the purpose.
Surely there is a way to create a layered object structure that would allow me to create objects that accessed unerlying objects without actually allowing the end user direct access to them? (And not have to set up seperate instances of SQL Server with linked servers in between)
Am I crazy?
April 12, 2012 at 5:24 pm
Read up on ownership chains.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 12, 2012 at 5:27 pm
You are correct, you would have to grant access to the tables or views in the original database. You could use Table-Valued functions instead of views and sign them. Here's a blog post I wrote about signing http://wiseman-wiseguy.blogspot.com/2009/11/maintaining-security-and-performance.html[/url] which also has links to other resources on signing. Unfortunately you can't sign a view, at least not that I'm aware of, which is why I'm recommending that you use TVF's
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 12, 2012 at 7:48 pm
This can be done with proper use of cross-database ownership chaining. It's one of the few times you actually want to consider such an option. However, this isn't a simple thing. Do the users already have access to the tables in the DB?
K. Brian Kelley
@kbriankelley
May 10, 2012 at 2:03 pm
You might consider moving a snapshot of the subset of data over to a separate database where you can set up any kind of structure you want. You would only need to refresh the data on whatever schedule your people can live with.
Of course if your people must have real-time data then this wouldn't work. But it sounds like this is not required for the kind of stuff you just described.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply