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

openhgs has made the point for Windows 8 multiple monitors without realising it! With Windows 7 you have to switch the mouse and so your focus...

42 minutes ago by PatrickG on Windows 8 could speed multi-monitor uptake

Mozilla has threatened to stop supporting Linux. I guess that UBUNTU is going with another browser. I indicated that if Mozilla stops supporting...

2 hours ago by Leslie Satenstein via Facebook on Firefox rapid release improves Fedora Linux

Much as I abhor Microsoft's licensing practices, this is almost certainly down to purchasing IT equipment via 3rd party consultants - you get the...

2 hours ago by Andy Bolstridge via Facebook on 6 million wasted licences and £1,200 PCs: welcome to government IT

@openhgs Windows users have had multiple desktops since Linus started writing Linux. They just haven't shipped as standard because not enough...

19 hours ago by Jack Schofield on Windows 8 could speed multi-monitor uptake

@Phil at Cloud4 What, Microsoft gets £1,200 per PC and £1,622 per server? Gosh, I'm amazed....

19 hours ago by Jack Schofield on 6 million wasted licences and £1,200 PCs: welcome to government IT

You guys have no idea what is going on at Autonomy. Autonomy could have been a much more profitable organization. The sales operations at Autonomy...

21 hours ago by craigsc on HP cuts 27,000 staff as Autonomy chief Lynch leaves

How does this impact on dual or multi booting? Seems to me to more or less prohibit this, from Windows 8 anyway. Will Grub 2 recognise Windows 8,...

21 hours ago by Moley on Windows 8 start-up speed forces USB boot workaround

I don't understand why there cannot be a slight pause during the boot process so the user can press a key. Many operating systems do this, even if...

22 hours ago by apexwm on Windows 8 start-up speed forces USB boot workaround

You can now buy the Xi3 modular computer in the UK at http://www.ocdistribution.com . This can be bought with the Tand3m software, pricing and...

22 hours ago by Gavin Goodman on CES 2012: Xi3 microSERV3R

I agree: Mike Lynch can clearly build a business and manage strategy. I suspect the exit of Mike is more likely the end of a planned handover...

1 day ago by Phil at Cloud4 on HP cuts 27,000 staff as Autonomy chief Lynch leaves

This is unbeleivable government wastage with only one winner... Microsoft 1 - Tax payer Nil!

1 day ago by Phil at Cloud4 on 6 million wasted licences and £1,200 PCs: welcome to government IT

So what do you do when you can't boot into windows? Why can't I just hold Shift while I power up instead of having to boot into windows and click a...

1 day ago by Mispam on Windows 8 start-up speed forces USB boot workaround

I've also seen that Mac OS X for Intel machines is supposed to run in VirtualBox, which would also be a nice solution. I've never tried it though.

1 day ago by apexwm on xTreme Triple Booting: Linux, Mac & Windows

What I wonder is why when companies are caught bang to rights in not providing contracted services, people bend over to smear the customers? Surely...

1 day ago by dave heasman on Virgin throttles broadband for high-speed customers

Strange statement from HP regarding Mike Lynch and not capable of scaling a company. Autonomy was a $7bn purchase which started as a small company...

1 day ago by pjc158 on HP cuts 27,000 staff as Autonomy chief Lynch leaves

Or - possibly, they will destroy business by ensuring people do not invest where there is no return. Another socialist idea, well beyond it's...

1 day ago by lojolondon on Open Data Institute will act as biz incubator

Good stuff Jake, very interesting. Thanks. jw

1 day ago by J.A. Watson on xTreme Triple Booting: Linux, Mac & Windows

"the cost of a second LCD screen is about the same as one day of an office worker's time, so this should soon be recouped in extra productivity."...

1 day ago by openhgs on Windows 8 could speed multi-monitor uptake

I also installed the KDE version; I also will probably try out razorqt since I really haven't had a chance to before. I'm looking forward to the...

2 days ago by Thomas Gellhaus via Facebook on Mageia 2 Released

Acquiring when reinvention/cannibalization is too challenging for a large organization can be an excellent strategy- still, so many mergers stumble...

2 days ago by francisabigail on Ariba buy parks SAP on Oracle's cloud turf

Featured white papers

Latest jobs