Ado net and stored procedures
This presentation is the property of its rightful owner.
Sponsored Links
1 / 17

ADO.NET and Stored Procedures PowerPoint PPT Presentation


  • 79 Views
  • Uploaded on
  • Presentation posted in: General

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.

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.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


Ado net and stored procedures

ADO.NET and Stored Procedures

- Swetha Kulkarni


Ado net and stored procedures

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


Ado net and stored procedures

RDBMS

ADO.NET Provider

Connection

Application

Dataset


Ado net and stored procedures

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


Ownership chain

Ownership chain


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


Ado net and stored procedures

Thank You


  • Login