1 / 17

ADO.NET and Stored Procedures

ADO.NET and Stored Procedures. - Swetha Kulkarni. RDBMS. ADO.NET Provider. SqlClient OracleClient OleDb ODBC SqlServerCE. System.Data.SqlClient System.Data.OracleClient System.Data.OleDb System.Data.Odbc System.Data.SqlServerCe. Application. Dataset.

mab
Download Presentation

ADO.NET and Stored Procedures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. ADO.NET and Stored Procedures - Swetha Kulkarni

  2. RDBMS ADO.NET Provider • SqlClient • OracleClient • OleDb • ODBC • SqlServerCE • System.Data.SqlClient • System.Data.OracleClient • System.Data.OleDb • System.Data.Odbc • System.Data.SqlServerCe Application Dataset

  3. RDBMS ADO.NET Provider Connection Application Dataset

  4. RDBMS ADO.NET Provider Connection Dataadapter Dataadapter Application Dataset Datatable Datatable

  5. ADO.NET Objects System.Data • Contains the “main” classes of ADO.NET • In-memory cache of data • In-memory cache of a database table • Used to manipulate a row in a DataTable • Used to define the columns in a DataTable • Used to relate 2 DataTables to each other DataSet DataTable DataRow DataColumn DataRelation

  6. Benefits of Stored Procedures • Stored procedures pass less information over the network on the initial request. Hence faster • Parameterized stored procedures that validate all user input can be used to thwart SQL injection attacks • Errors can be handled in procedure code without being passed directly to client applications • Stored procedures can be written once, and accessed by many applications

  7. Security Overview – ADO.NET • Design for Security • Threat Modeling • The Principle of Least Privilege

  8. Authentication • If possible, use Windows authentication • SqlConnectionpubsConn = new SqlConnection( "server=dbserver; database=pubs; Integrated Security=SSPI;"); • If you use SQL authentication, use strong passwords • SqlConnectionString = "Server=YourServer\Instance; Database=YourDatabase; uid=sa; pwd=;" • Consider Which Identity to Use to Connect to the Database

  9. Ownership chain

  10. Authorization • Restrict Unauthorized Code • Restrict Application Access to the Database

  11. Configuration and Connection Strings • Avoid Credentials in Connection Strings • Store Encrypted Connection Strings in Configuration Files <connectionStrings> <add name="MyDatabaseConnection" connectionString="Persist Security Info=False;Integrated Security=SSPI;database=Northwind;server=(local);" providerName="System.Data.SqlClient" /> </connectionStrings> • Do Not Use Persist Security Info="true" or "yes" • Avoid Connection Strings Constructed With User Input

  12. Exception Management • Use Finally Blocks to Make Sure that Database Connections Are Closed • Consider Employing the Using Statement to Make Sure that Database Connections Are Closed • Avoid Propagating ADO.NET Exceptions to Users • In ASP.NET, Use a Generic Error Page , Log exceptions on the server

  13. Secure Data Access • Authentication, Authorization and Permissions • Parameterized Commands and SQL Injection • Script Exploits • Probing Attacks

  14. Privacy and Data Security • Cryptography and Hash Codes • Encrypting Configuration Files • Securing String Values in Memory

  15. Best Practices – Stored Procedures • Grant EXECUTE permissions for database roles • Revoke or deny all permissions to the underlying tables for all roles and users in the database • Do not add users or roles to the sysadmin or db_owner roles • Disable the guest account. This will prevent anonymous users from connecting to the database

  16. References • http://www.guidanceshare.com/wiki/ADO.NET_2.0_Security_Guidelines • http://msdn.microsoft.com/en-us/library/ms971481.aspx • http://msdn.microsoft.com/en-us/library/bb669058.aspx

  17. Thank You

More Related