Secure DAL Design using Stored Procedures

I normally avoid sProcs as much as possible. I dont like the language be it TSQL or PL/SQL; they seem archaic against Java/Dot-Net which I use. I go for them when a routine needs to fetch a lot of data, crunch it and generate a small set of output. Sitting inside the DB makes the fetching process a lot fast, no network hit. But that is all.

I recently came across a DAL design where absolutely all of the CURD operations were implemented in Stored Procedures. Actually one giant sProc to be precise. Here is the skeleton:

PROCEDURE myGenericProc(int QueryNo, varchar genericParam1, ..., varchar genericParamN)
BEGIN
    SWITCH queryNo
    CASE 1
        SELECT * FROM table1 INNER JOIN table 2 ON ...

    CASE 2
        DELETE FROM table 3 WHERE ...
    ...

    CASE n
        UPDATE table4 SET a=b WHERE ...
END

The designer’s logic behind this is: if I do these things in code, then the database-connection needs to have full rights on all the tables. The connection credentials are generally in connection string, which is on the application server. If the application server is compromised, inevitably the entire DB is also compromised.

As an alternative, have all the queries in the sProc, then grant that sProc full rights. Call only that sProc. This way, even if the application server is compromised, only the sProc interface can be attacked. No one can do DROP users_master.

While I agree with the principle, I hate the implementation. Unfortunately, some security paranoid clients (banks) want us to do exactly that. Also, the DBAs hate tuning access privileges on 200+ sProcs, they want as-few-as-possible items to audit.

Question:

Is there any other implementation that provides same level of security, but is more cleaner ?

8

Sprocs are very good for implementing a secure data access layer – you write sprocs for reading and writing data, and give the client execute access to the sprocs only – no access to the underlying tables or views.

This gives your DB an API that clients use, in much the same way as any class implemented in your business logic code would, but much more secure. It prevents the kind of exploits we keep reading about in the news where some hacker has gained access to every user’s password – if the only way to access a password was via a sproc, the attacker who gained access to the DB could only retrieve 1 password at a time, running select * from users just isn’t possible once he’s bypassed your publicly-facing servers.

In addition, you can partition your back-end DB into schemas so that some sprocs cannot even access other parts of the DB.

In short, its a nice way of implementing a controlled API for the DB rather than just letting anyone run whatever query that feel like against it. You can obviously improve performance using sprocs for data access that requires complex queries, and you can re-implement your back-end schema without any client realising its changed.

I worked in a highly secure system a few times (financial) that required the front-end web site access business logic in a middle-tier service, the service was secured so only the web server could access it, it in turn called sprocs on the db that were in turn secured so only the middle tier services that needed access to them were allowed. It might seem over complex but once you’d done the first example of each part it was very easy to understand where to put other features. It also meant specialists could write the relevant parts of the application (ie web, service or sql) and they’d come together later in integration.

I wouldn’t like to write a single sproc that caters to all API calls – that’s total pants. The DBAs should be happy with several sprocs, they can review and audit only changes then – and not audit the entire thing for 1 piddly change.

3

Modern Object Relation Mapping libraries, and even the lower level libraries allowing you to run SQL against the database, have guards in place to prevent SQL injection attacks. To use stored procedures because all other solutions are not secure is both short sighted and false. If you must hard code SQL, you can use parameterized queries, which guard against SQL injection attacks.

If your reason for using stored procedures is to centralize business logic, then you’ve got a legitimate reason for using them, but it had better be a good enough reason to make your system harder to test. Throwing a bunch of unit tests at classes in Java or C# is pretty easy. Unit testing your stored procedures is more difficult, but if you many different technologies needing the same database, and the same business rules then stored procedures are nice to have.

I have a love-hate relationship with them. On the one had, I hate having to map every single stinking parameter and I like how an ORM can just auto generate the INSERT, UPDATE and DELETE commands. Then again, most ORMs allow you to call stored procedures, so you get the dynamic querying ability of the ORM with the hardened interface provided by stored procedures.

Really, your reason for using stored procedures should not be “because it’s more secure”, as this likely results from not knowing how to use the other tools available.

3

I try to avoid stored proc’s if at all possible. But when I have to for security, my goal is a compromised approach. I use stored proc’s for updating commands (INSERT, UPDATE, DELETE) and the ORM for everything else.

Further, each stored proc does a single operation on a single table. The application code coordinates all of the calls and manages the transactions.

The biggest problem I’ve experienced with stored proc’s is that development becomes more difficult. Stored proc’s obfuscate the work:

  • Following the program flow is more difficult, especially if two different IDE’s must be used (one for the stored proc’s, one for the code)
  • Tracking bugs down is harder because of the mental context switch
  • When stored proc’s call other stored proc’s, the problems get exaggerated

1

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa

Secure DAL Design using Stored Procedures

I normally avoid sProcs as much as possible. I dont like the language be it TSQL or PL/SQL; they seem archaic against Java/Dot-Net which I use. I go for them when a routine needs to fetch a lot of data, crunch it and generate a small set of output. Sitting inside the DB makes the fetching process a lot fast, no network hit. But that is all.

I recently came across a DAL design where absolutely all of the CURD operations were implemented in Stored Procedures. Actually one giant sProc to be precise. Here is the skeleton:

PROCEDURE myGenericProc(int QueryNo, varchar genericParam1, ..., varchar genericParamN)
BEGIN
    SWITCH queryNo
    CASE 1
        SELECT * FROM table1 INNER JOIN table 2 ON ...

    CASE 2
        DELETE FROM table 3 WHERE ...
    ...

    CASE n
        UPDATE table4 SET a=b WHERE ...
END

The designer’s logic behind this is: if I do these things in code, then the database-connection needs to have full rights on all the tables. The connection credentials are generally in connection string, which is on the application server. If the application server is compromised, inevitably the entire DB is also compromised.

As an alternative, have all the queries in the sProc, then grant that sProc full rights. Call only that sProc. This way, even if the application server is compromised, only the sProc interface can be attacked. No one can do DROP users_master.

While I agree with the principle, I hate the implementation. Unfortunately, some security paranoid clients (banks) want us to do exactly that. Also, the DBAs hate tuning access privileges on 200+ sProcs, they want as-few-as-possible items to audit.

Question:

Is there any other implementation that provides same level of security, but is more cleaner ?

8

Sprocs are very good for implementing a secure data access layer – you write sprocs for reading and writing data, and give the client execute access to the sprocs only – no access to the underlying tables or views.

This gives your DB an API that clients use, in much the same way as any class implemented in your business logic code would, but much more secure. It prevents the kind of exploits we keep reading about in the news where some hacker has gained access to every user’s password – if the only way to access a password was via a sproc, the attacker who gained access to the DB could only retrieve 1 password at a time, running select * from users just isn’t possible once he’s bypassed your publicly-facing servers.

In addition, you can partition your back-end DB into schemas so that some sprocs cannot even access other parts of the DB.

In short, its a nice way of implementing a controlled API for the DB rather than just letting anyone run whatever query that feel like against it. You can obviously improve performance using sprocs for data access that requires complex queries, and you can re-implement your back-end schema without any client realising its changed.

I worked in a highly secure system a few times (financial) that required the front-end web site access business logic in a middle-tier service, the service was secured so only the web server could access it, it in turn called sprocs on the db that were in turn secured so only the middle tier services that needed access to them were allowed. It might seem over complex but once you’d done the first example of each part it was very easy to understand where to put other features. It also meant specialists could write the relevant parts of the application (ie web, service or sql) and they’d come together later in integration.

I wouldn’t like to write a single sproc that caters to all API calls – that’s total pants. The DBAs should be happy with several sprocs, they can review and audit only changes then – and not audit the entire thing for 1 piddly change.

3

Modern Object Relation Mapping libraries, and even the lower level libraries allowing you to run SQL against the database, have guards in place to prevent SQL injection attacks. To use stored procedures because all other solutions are not secure is both short sighted and false. If you must hard code SQL, you can use parameterized queries, which guard against SQL injection attacks.

If your reason for using stored procedures is to centralize business logic, then you’ve got a legitimate reason for using them, but it had better be a good enough reason to make your system harder to test. Throwing a bunch of unit tests at classes in Java or C# is pretty easy. Unit testing your stored procedures is more difficult, but if you many different technologies needing the same database, and the same business rules then stored procedures are nice to have.

I have a love-hate relationship with them. On the one had, I hate having to map every single stinking parameter and I like how an ORM can just auto generate the INSERT, UPDATE and DELETE commands. Then again, most ORMs allow you to call stored procedures, so you get the dynamic querying ability of the ORM with the hardened interface provided by stored procedures.

Really, your reason for using stored procedures should not be “because it’s more secure”, as this likely results from not knowing how to use the other tools available.

3

I try to avoid stored proc’s if at all possible. But when I have to for security, my goal is a compromised approach. I use stored proc’s for updating commands (INSERT, UPDATE, DELETE) and the ORM for everything else.

Further, each stored proc does a single operation on a single table. The application code coordinates all of the calls and manages the transactions.

The biggest problem I’ve experienced with stored proc’s is that development becomes more difficult. Stored proc’s obfuscate the work:

  • Following the program flow is more difficult, especially if two different IDE’s must be used (one for the stored proc’s, one for the code)
  • Tracking bugs down is harder because of the mental context switch
  • When stored proc’s call other stored proc’s, the problems get exaggerated

1

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa

Secure DAL Design using Stored Procedures

I normally avoid sProcs as much as possible. I dont like the language be it TSQL or PL/SQL; they seem archaic against Java/Dot-Net which I use. I go for them when a routine needs to fetch a lot of data, crunch it and generate a small set of output. Sitting inside the DB makes the fetching process a lot fast, no network hit. But that is all.

I recently came across a DAL design where absolutely all of the CURD operations were implemented in Stored Procedures. Actually one giant sProc to be precise. Here is the skeleton:

PROCEDURE myGenericProc(int QueryNo, varchar genericParam1, ..., varchar genericParamN)
BEGIN
    SWITCH queryNo
    CASE 1
        SELECT * FROM table1 INNER JOIN table 2 ON ...

    CASE 2
        DELETE FROM table 3 WHERE ...
    ...

    CASE n
        UPDATE table4 SET a=b WHERE ...
END

The designer’s logic behind this is: if I do these things in code, then the database-connection needs to have full rights on all the tables. The connection credentials are generally in connection string, which is on the application server. If the application server is compromised, inevitably the entire DB is also compromised.

As an alternative, have all the queries in the sProc, then grant that sProc full rights. Call only that sProc. This way, even if the application server is compromised, only the sProc interface can be attacked. No one can do DROP users_master.

While I agree with the principle, I hate the implementation. Unfortunately, some security paranoid clients (banks) want us to do exactly that. Also, the DBAs hate tuning access privileges on 200+ sProcs, they want as-few-as-possible items to audit.

Question:

Is there any other implementation that provides same level of security, but is more cleaner ?

8

Sprocs are very good for implementing a secure data access layer – you write sprocs for reading and writing data, and give the client execute access to the sprocs only – no access to the underlying tables or views.

This gives your DB an API that clients use, in much the same way as any class implemented in your business logic code would, but much more secure. It prevents the kind of exploits we keep reading about in the news where some hacker has gained access to every user’s password – if the only way to access a password was via a sproc, the attacker who gained access to the DB could only retrieve 1 password at a time, running select * from users just isn’t possible once he’s bypassed your publicly-facing servers.

In addition, you can partition your back-end DB into schemas so that some sprocs cannot even access other parts of the DB.

In short, its a nice way of implementing a controlled API for the DB rather than just letting anyone run whatever query that feel like against it. You can obviously improve performance using sprocs for data access that requires complex queries, and you can re-implement your back-end schema without any client realising its changed.

I worked in a highly secure system a few times (financial) that required the front-end web site access business logic in a middle-tier service, the service was secured so only the web server could access it, it in turn called sprocs on the db that were in turn secured so only the middle tier services that needed access to them were allowed. It might seem over complex but once you’d done the first example of each part it was very easy to understand where to put other features. It also meant specialists could write the relevant parts of the application (ie web, service or sql) and they’d come together later in integration.

I wouldn’t like to write a single sproc that caters to all API calls – that’s total pants. The DBAs should be happy with several sprocs, they can review and audit only changes then – and not audit the entire thing for 1 piddly change.

3

Modern Object Relation Mapping libraries, and even the lower level libraries allowing you to run SQL against the database, have guards in place to prevent SQL injection attacks. To use stored procedures because all other solutions are not secure is both short sighted and false. If you must hard code SQL, you can use parameterized queries, which guard against SQL injection attacks.

If your reason for using stored procedures is to centralize business logic, then you’ve got a legitimate reason for using them, but it had better be a good enough reason to make your system harder to test. Throwing a bunch of unit tests at classes in Java or C# is pretty easy. Unit testing your stored procedures is more difficult, but if you many different technologies needing the same database, and the same business rules then stored procedures are nice to have.

I have a love-hate relationship with them. On the one had, I hate having to map every single stinking parameter and I like how an ORM can just auto generate the INSERT, UPDATE and DELETE commands. Then again, most ORMs allow you to call stored procedures, so you get the dynamic querying ability of the ORM with the hardened interface provided by stored procedures.

Really, your reason for using stored procedures should not be “because it’s more secure”, as this likely results from not knowing how to use the other tools available.

3

I try to avoid stored proc’s if at all possible. But when I have to for security, my goal is a compromised approach. I use stored proc’s for updating commands (INSERT, UPDATE, DELETE) and the ORM for everything else.

Further, each stored proc does a single operation on a single table. The application code coordinates all of the calls and manages the transactions.

The biggest problem I’ve experienced with stored proc’s is that development becomes more difficult. Stored proc’s obfuscate the work:

  • Following the program flow is more difficult, especially if two different IDE’s must be used (one for the stored proc’s, one for the code)
  • Tracking bugs down is harder because of the mental context switch
  • When stored proc’s call other stored proc’s, the problems get exaggerated

1

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa
Thiết kế website Thiết kế website Thiết kế website Cách kháng tài khoản quảng cáo Mua bán Fanpage Facebook Dịch vụ SEO Tổ chức sinh nhật