SQLTeam.com | Weblogs | Forums

How can I protect my VIEW?


#1

I created a view and put a role on it with select only permission. I assume this means that the people in that role can select from it, but not perform updates (even the limited updates that SQL Server does allow on views).

But I want to be triple-sure...Because to me this is 90% of the value of creating the view in the first place. I want to be absolutely sure it is 100% non-updateable, by anyone anywhere.

Is there something in the DDL I can actually use when creating the view that ensures this? Or do I have to accomplish it by roles/permissions? I just want my view to be 100% non-updateable, ever.


#2

if you want to be sure, try to use it in an UPDATE while logged in as another user, even if you have to create a user just for that purpose. Also, you can control permissions at the table level and database schema level etc. which are worth considering


#3

The ugly truth is that part of my problem is.......the role that I am using which actually does grant them permission to update the base tables, it has to be there at the moment because they use a front end app I built (which has an MS Access front end) to make updates via the app interface, and the app does not impersonate, so to the extent that they use the app to make updates they have to have those permissions. (any suggestions to make this better would be awesome--I've searched for solutions and using MS Access have not found any, and really none using vb.net either without extremely sophisticated windows services and intermediate business layer apps, none of which I'm capable of building right now)..........So anyway, they DO have permissions to the base tables.
However, right now it's just security by obscurity, I don't publish where or what the base tables are (and have a lot of triggers/logging going on too), so it is what it is right now......

Basically I want to create something (this View) where they CAN create odbc links (like in excel or access) so that they can consume the data ad-hoc, like they crave to do. But I want to do it safely, at least as regards the view itself (I realize the whole situation can be critiqued as very unsafe - but I'm just focusing on the View object at the moment).


#4

I tested it and the update works- YUCK.
Then I found this: SO link

and it works like a charm (any one of the many options) - that's what I needed - very happy. Thanks for looking.


#5

What's to stop them just updating the database via the Tables (which they do have UPDATE rights to?)

Not sure what sort of user you need to protect again - perhaps just the casual user who might construct a query in Access and then accidentally type something into a cell grid and, inadvertently and unintentionally, update the DB ... read-only VIEWs will of course work OK for that ...

... but a more curious user will find a list of tables and query them ... and then update them (unintentionally). A user with malicious intent could do far worse ...

The only code we have (actually both for Select and UpSert/Delete - i.e. CRUD operation) is in Stored Procedures. We have GET, SAVE and DEL flavours, and they are all mechanically generated - once we'd worked out a template for exactly what we wanted (its evolved over the years, but we could regenerate them each time we improved our mouse-trap). So they aren't a lot of trouble to maintain :slight_smile: I suppose about 5% of them are then hand-customised to do something more than the standard. But for all the simple "This is a code table, just edit the Code and Description" type tables we don't have to write any code. As a consequence I'm 100% confident that there are no sneaky got-in-by-the-back-door security risks to the underlying tables


#6

I agree that, as I originally posted, right now our security situation is far less than ideal. if I could come up with a better way I would. My idea is that I need to give them SOMETHING to query ad hoc, so the views is better than nothing, and I'm just going to place them in a distance place from the tables, where they never go anyway. I realize this is not ideal!

I've looked into the "we do everything by stored procedures" methods pretty intensively. 90% of them left me wondering what the difference really was, because at the end of the day the user had to have permissions to run the stored proc from the calling app, which meant they had to have the permissions on the base tables. Again, unless you have some very sophisticated business layer application IN BETWEEN the calling app and the database server.

So how does the fact that the only code you have being in stored procedures protect the base tables?


#7

You've not got that bit quite right. The user has to have EXECUTE permission on the SProc, but the SProc runs with permissions on the table of the person that CREATED the Sproc (you can also impersonate a user/role/whatever if you want to, but that is more hassle IMHO).

Our users have no permissions (none whatsoever) on any tables, view, or anything else like that, only Execute on Sprocs - so if they want to delete every Customer record they will have to do them one-by-one using the Customer Delete SProc - good luck to them! You could grant them SELECT on VIEWs, in additional to EXECUTE on Sprocs, if they need to be able to do adhoc reports - our users have "criteria forms" - lots of fields that they can set values in for the "criteria" for their report, and they can pick the columns they want in the output from a list, but they are heavily constrained - I'm sure in other circumstances more freedom would be needed. The values / choices the user puts into the criteria form is then passed to an SProc which either uses a hard coded query (with the User's parameters) or builds some SQL dynamically and runs that (again, as a specific user [with permissions] not as using the user's own permissions)

Not recommending the way we do adhoc query / reporting per se, as I say it is probably not flexible enough for some places, but the SProcs for Insert/Update/Delete should be bullet-proof on permissions.

Not sure what the consequences are, but you might also be able to use sp_ExecuteSQL. That lets you provide some SQL and a list of parameters. It is very efficient (the query plan for the SQL is cached, like a Stored Procedure is), but you do have to use identical SQL each time. So instead of

UPDATE MyTable
SET Col1 = 'foo',
    Col2 = 'bar'

you have to do

UPDATE MyTable
SET Col1 = @Param1,
    Col2 = @Param2

and then pass @Param1 and @Param2 to sp_ExecuteSQL. Thus the actual SQL is identical each time. I only mention this in case it fits in with how you currently generate your pass-through SQL in Access in case it lends itself to working that way.

I don't know how you set the permissions when you run sp_ExecuteSQL though ... I expect there is a way. We have SProcs that run dynamic SQL using sp_ExecuteSQL and they have a permissions certificate and all sorts of jazz for permissions, which I expect would be a PITA to set up for someone not building Big Apps ... perhaps there is a more Lightweight way to EXECUTE AS or somesuch.


#8

Ok so this was the critical piece I was missing - I'm very glad to find out I was wrong about that initial thing. I'm telling you, I've been asking people (online, offline, in-line, bee-line!) about this general quandary....How to "secure" things a bit more than I currently do, given a SQL back-end with ACCESS front-end, and over and over I got the same {wrong} impression.
This is very helpful to me Kristen, thank you. I didn't realize the specific thing about the stored procedure - and giving the user Execute permissions, would not mean they necessarily had any permissions to do anything on the base tables.

I might have landed this as a guess eventually, considering my SSRS usage, but even there, since I use a method whereby I save a sort of "service account" credentials inside the ssrs data source, I knew that the user was running my SSRS report (which pointed as a stored proc) using that data source's credentials, not there own....If I'd been doing it using their own credentials to run it, I would have finally realized the truth about stored proc permissions.

This is good news. It means, at the very least, I could immediately improve the security situation in my Access interface program(s) by simply removing users' permissions to base tables, and then creating either 'one size fits all' SP's that the app would execute, or else creating individual SP's for each need..........

Yes, I do make liberal use of T-SQL in pass-thru queries, but more SP's, and occasionally dynamic SQL (don't tell anyone - I know that is a whole can of worms). I just never realized that I could have been doing it all by SP's and not given them permissions to the base tables.

Huge. Thanks!!


#9

Use sp_ExecuteSQL for your dynamic SQL and make sure you parametrise everything you can ... then SQL will cache & reuse the query plan (i.e. multiple users will execute the same SQL string, just with different parameters). If you do that then I don't think its a "can of worms" :slight_smile:


#10

Oops never saw this response until now, didn't get a notification email.

Thank you Kristen, all of this very helpful to me.