Ado net and stored procedures
Download
1 / 17

ADO.NET and Stored Procedures - PowerPoint PPT Presentation


  • 106 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' ADO.NET and Stored Procedures' - mab


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

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
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
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
Security Overview – ADO.NET

  • Design for Security

    • Threat Modeling

  • The Principle of Least Privilege


Authentication
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
Authorization

  • Restrict Unauthorized Code

  • Restrict Application Access to the Database


Configuration and connection strings
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
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
Secure Data Access

  • Authentication, Authorization and Permissions

  • Parameterized Commands and SQL Injection

  • Script Exploits

  • Probing Attacks


Privacy and data security
Privacy and Data Security

  • Cryptography and Hash Codes

  • Encrypting Configuration Files

  • Securing String Values in Memory


Best practices stored procedures
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
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



ad