7 Using ASP.NET with Oracle Database

This chapter contains:

Overview: Building an ASP.NET Application with Oracle Developer Tools

Oracle integrates directly with Microsoft ASP.NET in a number of ways:

  • Oracle Developer Tools for Visual Studio provides an easy way to design data-driven web sites.

  • ODP.NET enables ASP.NET data access.

  • Oracle Providers for ASP.NET integrate directly with Microsoft ASP.NET controls and services to provide state management capabilities for web sites.

This tutorial demonstrates some of these features, including how to build a data-driven web application using Oracle Developer Tools and how to add security to that application in a simple manner using Oracle Providers for ASP.NET.

First, you will use the tools to build a web application that retrieves employee data from the Oracle database into a data grid. Then, you add a login control and secure the application by permitting only authorized web users access to this employee information. Finally, you create authorized web users using Oracle Providers for ASP.NET. These web users are stored within the Oracle database for authentication by this application.

Before Beginning This Tutorial

Before you can build an ASP.NET application with Oracle Developer Tools, you may need to perform the setups described in the following sections:

Creating a Web Site and Connecting it to the Database

This section shows you how to create an ASP.NET web site that retrieves data from an Oracle database. The web site will display data in an ASP.NET GridView, allowing users to page through the results.

This section contains two topics:

Creating an ASP.NET Web Site

To create an ASP.NET web site with a grid:

  1. Start Visual Studio.

  2. From the File menu, select New, then Web Site...

    Description of aspnt01.gif follows
    Description of the illustration aspnt01.gif

  3. From New Web Site, select ASP.NET Web Site and enter or browse to the directory location for the web site. Click OK.

    Description of aspnt02.gif follows
    Description of the illustration aspnt02.gif

  4. In the Default.aspx tab, click the Design icon at the bottom of the screen.

    Description of aspnt03.gif follows
    Description of the illustration aspnt03.gif

  5. In the <div> element, which appears as a dotted rectangle, enter a title, such as Employees List.

    Description of aspnt04.gif follows
    Description of the illustration aspnt04.gif

  6. From the View menu, select Toolbox.

    Description of aspnt05.gif follows
    Description of the illustration aspnt05.gif

  7. Expand the Data group and drag the GridView control into the dotted rectangle labeled <div> of the Designer

    Description of aspnt06.gif follows
    Description of the illustration aspnt06.gif

  8. When a grid with dummy titles and contents appears, view the GridView Tasks list on the right.

    Description of aspnt08.gif follows
    Description of the illustration aspnt08.gif

    If you do not see the task list, select the grid, and then click the > symbol on the right.

    Description of asnpt07.gif follows
    Description of the illustration asnpt07.gif

Creating a Data Source

To Create a Data Source:

  1. Under the GridView Tasks, select <New data source...> from the Choose Data Source list, as shown in step 8 in the previous section.

    A Data Source Configuration wizard starts.

  2. Select Database. Enter HR as the ID for the data source. Click OK.

    Description of aspnt09.gif follows
    Description of the illustration aspnt09.gif

  3. Click the down arrow to choose HR.ORCL from the list. Click Next.

    Description of aspnt10.gif follows
    Description of the illustration aspnt10.gif

  4. Click Next to save the connection string in the application configuration file.

    Description of aspnt11.gif follows
    Description of the illustration aspnt11.gif

  5. Select the EMPLOYEES table from the Name list. In the Columns list, check the box next to the asterisk ( *).

    These selections tell Oracle to return all the rows from the EMPLOYEES table, just as if you had typed SELECT * FROM EMPLOYEES.

    Click Next.

    Description of aspnt12.gif follows
    Description of the illustration aspnt12.gif

  6. Click Test Query.

    Description of aspnt13.gif follows
    Description of the illustration aspnt13.gif

  7. Click Finish when the Test Query results appear.

    Description of aspnt14.gif follows
    Description of the illustration aspnt14.gif

  8. From the GridView Tasks, select Enable Paging. If you do not see the task list, select the grid, and then click the > symbol on the right. You may have to scroll right.

    Description of aspnt15.gif follows
    Description of the illustration aspnt15.gif

  9. From the View menu, select Solution Explorer, and then select the web site. Right-click and select Build Web Site. The status bar will indicate success or failure.

  10. From the View menu, select Debug, then Start Without Debugging.

    A browser window, such as the following, appears, showing the data requested by the query. You can page through the results using the numbers at the lower left section of the page.

    Description of aspnt16.gif follows
    Description of the illustration aspnt16.gif

  11. Close the browser.

Enabling a Web Site for Authentication

This section shows you how to add web site authentication to limit the users that can access the employee data. We will authenticate using an ASP.NET login control, verifying against users created and stored with the Oracle Providers for ASP.NET.

In the section following this, "Enabling Oracle Providers for ASP.NET and Creating a Lightweight Web User", you will take the ASP.NET application you have just built and secure the employee data so that an authorized user can access the information. You will create a web user for the application using Oracle Providers for ASP.NET. This web user starts the web application through the login control, and if the credentials are correct, can then access the employees information.

  1. Reopen the web site you created in the previous section.

  2. Select View, then Solution Explorer, and click the web site.

    Description of aspnt17.gif follows
    Description of the illustration aspnt17.gif

  3. Right-click on the web site and Add New Item.

    Description of aspnt18.gif follows
    Description of the illustration aspnt18.gif

  4. Select Web Form, enter the name login.aspx and click Add.

    Description of aspnt19.gif follows
    Description of the illustration aspnt19.gif

  5. When the login.aspx page appears, switch to the Design tab.

  6. From the View menu, open the Toolbox, expand the Login section, and drag and drop the login control onto the form, into the dotted rectangle labeled <div>.

    Description of aspnt20.gif follows
    Description of the illustration aspnt20.gif

    This is a standard ASP.NET login control, which can retrieve and verify user login credentials stored in the Oracle database.

  7. Right-click the login control and select Properties. For DestinationPageUrl, select or enter Default.aspx.

    When a user successfully logs in, that user will be directed to the Default.aspx page, which contains the employee data. If a user does not successfully log in, they will be redirected back to the login page.

    Description of aspnt21.gif follows
    Description of the illustration aspnt21.gif

Enabling Oracle Providers for ASP.NET and Creating a Lightweight Web User

In this section, you will use the ASP.NET Web Site Administration Tool to do the following:

  • Direct the web site to use the Oracle ASP.NET providers.

  • Create a new web user specific to this web site, demonstrating the authentication features added to the site in "Enabling a Web Site for Authentication".

To direct the web site to use the Oracle ASP.NET providers and create new web site users, do the following:

  1. In Visual Studio, select Website, then ASP.NET Configuration.

    Description of aspnt23.gif follows
    Description of the illustration aspnt23.gif

  2. When the ASP.NET Web Site Administration Tool appears, select the Provider tab.

    Description of aspnt24.gif follows
    Description of the illustration aspnt24.gif

  3. On the Provider page, select the second link: Select a different provider for each feature (advanced).

    Description of aspnt25.gif follows
    Description of the illustration aspnt25.gif

  4. When the Provider page reappears, change the Membership Provider and Role Provider to the Oracle versions, if they are not selected.

    Description of aspnt26.gif follows
    Description of the illustration aspnt26.gif

  5. Go to the Security tab and under Users, click Select authentication type.

    By default, the ASP.NET site uses Windows authentication to identify users. You are building a web site that will identify users by their site-specific logins and passwords. Therefore, the site must be configured to expect to use logins and passwords.

    Description of aspnt27.gif follows
    Description of the illustration aspnt27.gif

  6. When the Security page reappears, choose From the internet and click Done.

    Description of aspnt28.gif follows
    Description of the illustration aspnt28.gif

  7. When the security tab reappears with new links under Users, choose Create user.

    Description of aspnt29.gif follows
    Description of the illustration aspnt29.gif

  8. In the Create User section, enter the information for the user that you are allowing to enter the web site, as shown. Enter a password that contains at least 7 characters, including one non-alphanumeric character.

    Click Create User.

    Description of aspnt30.gif follows
    Description of the illustration aspnt30.gif

  9. When the Security page reappears, indicating that your account has been successfully completed, click the Security tab.

    Description of aspnt31.gif follows
    Description of the illustration aspnt31.gif

    Note: Other options for this screen include continuing to create other users or going to a different tab.

  10. When the main Security page reappears, under Access Rules, select Manage access rules.

    Description of aspnt31a.gif follows
    Description of the illustration aspnt31a.gif

    NOTE: Under Users, there is now one existing user.

  11. When the Manage Access Rules section on the Security tab appears, click on Add new access rule.

    Description of aspnt32.gif follows
    Description of the illustration aspnt32.gif

  12. Select Anonymous users and Deny, then click OK.

    By default, anonymous access to the web site is enabled. The above settings secure the web site by disabling anonymous access. Now, only authenticated users can view the employee data.

    Description of aspnt33.gif follows
    Description of the illustration aspnt33.gif

  13. The Security page now indicates that the web site denies anonymous users access to the site.

    Click Done.

    Description of aspnt34.gif follows
    Description of the illustration aspnt34.gif

  14. Close the browser.

Testing Web Site Authentication

Now that you have created a web user specific to this web site, the web site will allow this user access to the employee data and deny access to all other users, including anonymous users.

In this section, you will attempt to access the employee data as an anonymous user, as an unauthorized user, as an authorized user with an incorrect password, and finally as an authorized user with the correct password. Only in the last scenario will the web site grant access to the employee data.

Note:

Five or more consecutive invalid passwords entered for an ASP.NET provider user within a ten minute period will lock the account to prevent unauthorized users from gaining access through password guessing. Oracle Membership Provider sets these security measures through the following properties, which you can modify in the machine.config file or web.config files: MaxInvalidPasswordAttempts (default: 5 attempts) and PasswordAttemptWindow (default: 10 minutes).

If the account is locked, then you can unlock the user by calling the UnlockUser method.

  1. From the Debug menu, select Start Without Debugging, and when the login web page appears, change the URL to end with Default.aspx rather than login.aspx and press the enter key.

    Description of aspnt39.gif follows
    Description of the illustration aspnt39.gif

    You are denied access and redirected back to the login page. This shows that anonymous users cannot browse the web site; only users with credentials have access.

    Description of aspnt40.gif follows
    Description of the illustration aspnt40.gif

    If you are experimenting with the authentication mechanism, you will likely repeat this step or try variations. For each variation, either start a new browser or clear the browser cache. Because browsers cache web pages, if you access Default.aspx again, you may see the cached version of this web page. This is not the intended behavior, rather the web page should undergo the ASP.NET provider authentication process, which is accomplished by using a new browser instance or clearing the browser cache.

  2. Remove the text of the URL after login.aspx. This returns the URL back to the original state when you first accessed the site.

    Enter the User Name Bob and a password that contains at least 7 characters, one of which is non-alphanumeric.

    Click Log In.

    Description of aspnt35.gif follows
    Description of the illustration aspnt35.gif

    The page reappears with the message "Your login attempt was not successful. Please try again."

    Bob is not an authorized user. The web site correctly denies access to the user.

  3. Enter the user name Anne, but with an incorrect password for that web site user.

    Click Log In.

    Description of aspnt38.gif follows
    Description of the illustration aspnt38.gif

    As the screen shot indicates, the user is denied access, demonstrating that the control could not verify this user's credentials with those stored by the Oracle Membership Provider.

  4. Enter the correct password for the web site user.

    Click Log In.

    The employee data appears. This demonstrates that only authorized users can access the data. Thus, Oracle Providers for ASP.NET provided web site security in a very simple manner.

You have now built a data-driven ASP.NET web application. It performs authentication and retrieves employee data from the database.