170 likes | 317 Views
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.
E N D
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
RDBMS ADO.NET Provider Connection Application Dataset
RDBMS ADO.NET Provider Connection Dataadapter Dataadapter Application Dataset Datatable Datatable
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
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
Security Overview – ADO.NET • Design for Security • Threat Modeling • The Principle of Least Privilege
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
Authorization • Restrict Unauthorized Code • Restrict Application Access to the Database
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
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
Secure Data Access • Authentication, Authorization and Permissions • Parameterized Commands and SQL Injection • Script Exploits • Probing Attacks
Privacy and Data Security • Cryptography and Hash Codes • Encrypting Configuration Files • Securing String Values in Memory
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
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