Locking down SQL Server

By Timothy Stockstill and Jonathan Lurie, Builder.com, 18 July, 2002 14:46

SQL Server does not provide an internal method of securing individual rows in the database. However, by using SQL Server views, it's possible to provide row-level security without too much effort. The primary component in a row-level security system is the use of views. Creating a view that resides on top of the table and giving the user access to the view but not the underlying table can enforce an effective row-level security system. The statement in Listing B demonstrates the creation of a single view per user approach. This view limits the records b_smith can access to only those from store 6380 (presumably the store where b_smith works). The statement also uses the WITH CHECK OPTION view option. This option prevents a user of the view (b_smith, in this case) from inserting a record that he or she would not be able to retrieve later. (In this case, b_smith would not be able to insert a record with a stor_id of any value other than 6380.) One disadvantage of this approach is that it requires views to be created for each role or user. More complicated views are used to provide row-level access to a table. These views generally provide a single view, as opposed to one per user, incorporating all of the row-level security mechanisms of all of the users in the database. This technique offers the advantage of easier administration since there are fewer views to keep track of, which subsequently translates into less code (our favorite advantage). Database security is worth the extra effort Providing a fully functional system for securing your database is a significant yet often overlooked step in developing a well-rounded application security system. It's often tempting to enforce security only within the application itself, with a single password giving all users access to the database. This entails less work, but it often leaves too many open holes in the database for crackers to exploit. (It's not difficult to connect to a SQL Server database using Access -- a user doesn't even have to know SQL.) It's a little more work to provide a robust security system at the database level, but the consequences of not doing so far outweigh the costs.

Have your say instantly in the Tech Update forum. Find out what's where in the new Tech Update with our Guided Tour. Let the editors know what you think in the Mailroom.

« Previous
1
2
Next »

Post your comment

In order to post a comment you need to be registered and logged in.

You can also log in with Facebook. Log in or create your ZDNet UK account below

By signing up for this service, you indicate that you agree to our Terms and Conditions and have read and understood our Privacy Policy. Questions about membership? Find the answers in the Community FAQ

ZDNet UK Live

> I'm told it's somewhat annoying when people have their Macs stolen > and Apple stores treat the thief as the owner, but there you go. Ouch,...

19 minutes ago by BrownieBoy on AMD Ultrathins to challenge Intel Ultrabooks

@kevinmchapman. OK, I acknowledge that 'most' was a gratuitous throwaway comment as an afterthought and too presumptuous. As to proof, as you...

5 hours ago by Moley on A tale of two distros: Ubuntu and Linux Mint

@BrownieBoy > Works really well for thieves.... >> Nice attempt to deflect the argument by tossing in a point that's totally >> irrelevant, even...

6 hours ago by Jack Schofield on AMD Ultrathins to challenge Intel Ultrabooks

fantastic that the so called piracy bills have been withdrawn. however, these anti-democracy supporters are still in the shadows so lets be alert...

7 hours ago by raskolnikof on SOPA, Protect IP support wavers in face of online protest

Please God no; teach them anything you like - thinking rationally, the uses and misuses of data, what data is and what it's not - but leave the...

9 hours ago by Tony Douglas via Facebook on Kids are the future. Teach ’em to code.

@Jack, > Works really well for thieves.... Nice attempt to deflect the argument by tossing in a point that's totally irrelevant, even it were...

23 hours ago by BrownieBoy on AMD Ultrathins to challenge Intel Ultrabooks

Make that 13 people now - I got refused today at Manchester airport. I thought I was up to date on this legislation - I knew of the EU ruling from...

1 day ago by bootlegger on UK airport body scans will not be opt out

Don't forget to check out apps like GoodReader or SlideShark either, they're indispensible for people on the go in presentation situations. Best...

1 day ago by tinycg on Four top iPad apps for people on the move

Well it seems there is something a number of us agree on. Why is the Ubuntu Unity launcher so ugly? I thought perhaps it was something to do with...

1 day ago by TerryRK on A tale of two distros: Ubuntu and Linux Mint

Duplicate comments are not made intentionally. Its very good to know that now you are keeping check on this problem because sometimes a commenter...

2 days ago by Freebies202 on Microsoft fixes blog comments, speeds up blogs with open source

"the very significant number of users" and "many (most) of us" - you have no evidence for these statements. It is a fact that most users are saying...

2 days ago by kevinmchapman on A tale of two distros: Ubuntu and Linux Mint