Security - Stored Procedure - Views - Tables

[DotNetWWW]

Hi all . I am going to consult you about a security concept . In my database I have a set of tables (eg. City , Country , ...) and I have a corresponding View for each Table (eg. V_City , V_Country , ...) and There are a set of Add/Delete/Update/List stored procedures for each object , the user which logs on to database has only EXECUTE Privilege on defined stored procedures and does not have any kind of access to any other object in database , In your opinion , Can Inserting into/Updating Views instead of Inserting into/Updating Tables cause any problem?and Is this model help improve security? Not that I access SQL Server Database from a .Net App. Thanks in advance.

[Colin Angus Mackay]

DotNetWWW wrote:

the user which logs on to database has only EXECUTE Privilege on defined stored procedures and does not have any kind of access to any other object in database , In your opinion , Can Inserting into/Updating Views instead of Inserting into/Updating Tables cause any problem?and Is this model help improve security?

Adding the rights to the views increases your attack surface. The attack surface is the amount of your system that is potentially open to abuse. You also say that you have tables, and corresponding views. If the view is defined as SELECT * FROM CorrespondingTable then I don't see any advantage in that. My personal opinion is that the best solution in most cases is to allow access only to the stored procedures that are required. That way SQL Server has the ability to verify the data before modifying the database, it can also veto and request for information. Whereas access to tables and views gives much wider scope for an application to abuse the database.

DotNetWWW wrote:

Not that I access SQL Server Database from a .Net App.

I wouldn't think the type of application would make much difference.

---

Upcoming FREE developer events: * Glasgow: SQL Server Managed Objects AND Reporting Services ... My website