December 1, 2010 at 3:45 pm
Long shot here, but has anyone ever tried to filter a table based on the sql user and/or login?
Specifically:
Table1
Col1 | Col2 | Col3
--------------------------
AA | 11 | 111
AA | 22 | 222
BB | 33 | 333
User Bob - public role, full access
User Larry - limited data visibility, need to filter out any rows in Table1 where Col1 = 'BB'
Bob runs:
Select * from Table1
Result - 3 Rows
AA | 11 | 111
AA | 22 | 222
BB | 33 | 333
Larry runs:
select * from Table1
Result - 2 Rows
AA | 11 | 111
AA | 22 | 222
It's a custom app developed in-house and due to politics our Dev's are "unable to allocate time for this project at this time".
I've never run across any way to filter a table like this... you'd basically have to intercept the query, examine the user, and insert an additional where clause before passing it along.
December 1, 2010 at 3:58 pm
You could use views that limit the data and only grant your users access to those views.
Another option would be to expose the data to your users via SSAS. SSAS allows you to assign permissions down to the dimension member level.
December 1, 2010 at 4:16 pm
Had a few thoughts along those lines... but, correct me if I'm wrong, anything along that line would require the dev's stepping in and changing how they are performing their queries.
December 1, 2010 at 4:18 pm
December 1, 2010 at 4:44 pm
It's a custom n-tier application which is filling a data grid of accounts. We have a contractor who we can't expose all the data to, only his accounts, etc etc.
The query is built within the app itself, not via SP. So the query isn't really exposed to us at the DBA level.
The dev's cannot modify the query, or rather are being told not to by helga the headmaster of development.
All in all, it's an ugly intra-department shafting that's going on, but we're in Health Care, and in the end there is a patient who needs answers from this contractor guy, so we're trying to facilitate.... keeping all that in mind, IF there isn't a reliable way to do it THEN I can report back that it can only be done at the dev level, then it gets escalated and someone who drives one of the really nice cars out in the parking lot will step in and stop the madness between the department heads. But I don't like interrupting those guy's game of mid-morning squash until I'm absolutely sure it's necessary.
please excuse the jaded tone of the author... he gets that way when he has to work late due to primadonna's[\i]
December 1, 2010 at 4:49 pm
Well, that doesn't sound good for you. If all user interaction is being done through the application and they say they can't spend DEV hours to change the app, there's not much to do. I can't think of a way to redirect the incoming queries and have them hit up against different/filtered objects. Even if there were a way at the DB layer, I'd advise against it. This sounds like a business requirement change and should be handled by the application developers. Let the managers hash this one out....if they want it bad enough, they'll pay the developers to get it done.
December 2, 2010 at 5:57 am
If somehow you could change the query in the app, you could use a table-valued function and pass the username as a parameter and have the function return only the rows he can see.
December 2, 2010 at 10:42 am
Yeah, it all boils down to being unable to change the query build.
My suspecions are confirmed that there is no way to do this at the Admin level, which honestly makes me happy... If we could do this, then it would seriously degrade the security of any application which queried a SQL Server.
Thanks to everyone who commented.
February 3, 2011 at 6:40 am
Perhaps you could try to rename the base table and build a view with the name of the table in which you can add a where clause with the restrictions; something like this
rename Table1 to Table1_table
then
create view Table1 as
select * from Table1
where col1 like (case system_user when 'Larry' then 'BB' else '%' end)
Should work!
February 3, 2011 at 10:35 am
Interesting idea. I'm not certain how this custom app would work against a view instead of a table. Basic CRUD works with views, however I'd have to test to make sure it didn't throw an exception in the app.
It's something to think about and test. I'll post back results in a few weeks.
Thanks for the idea
February 6, 2011 at 9:39 am
I have done this recently with a third party application in which I renamed and substituted a base table with such a view and everything is still working just fine - no complaints whatsoever (so far, anyway).
So - good luck!
February 6, 2011 at 10:14 am
Great, thanks for the feedback on it. I'll be meeting with my team about it in two weeks myself.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply