Securing a .NET Application on the Oracle Database

Learn how to take full advantage of Oracle's built-in security features in your .NET application.

Downloads for this article: Sample code Oracle Database 10g for Windows Oracle Data Provider for .NET

Review complete "Mastering .NET Application with Oracle" index

Fortunately, .NET developers who choose Oracle as their database platform can use Oracle Data Provider for .NET (ODP.NET) to take advantage of Oracle's security features to build the foundation of a comprehensive security strategy.

In this installment of "Mastering .NET Application Development with Oracle," I will explain how to design an application with security in mind, including

• How to use proxy authentication to pass actual end user IDs to the Oracle server while maintaining all of the advantages of connection pooling. Proxy authentication also allows per-user authentication to the database server when connection pooling is used.
• How to use the client identifier to pass either custom identifier strings or actual end user IDs to the Oracle server
• How to use Windows authentication for Oracle to authenticate to the database server without using an Oracle user ID and password, also known as single sign-on
• How to prevent SQL injection via parameterized queries, thereby mitigating one of the biggest threats to database security

You will have the opportunity to apply what you have learned in five practice labs, ranging in difficulty from the relatively simple to the more complex.

This article assumes that you are familiar with Oracle Data Provider for .NET and creating a project in Visual Studio.NET, as described in my previous article, Build a .NET Application on the Oracle Database .

Proxy Authentication

Here is the code from the previous article used to define a simple Oracle connection string:

                               
Dim oradb  
                              
As String =
                              

  "Data Source=OraDb;User Id=scott;Password=tiger;"  
                              
' VB.NET 

                               
string oradb =
                              

  "Data Source=OraDb;User Id=scott;Password=tiger;";  
                              
// C#
                            

Connection strings like this are often used by applications that prompt users for a user ID and password. Web applications typically do this. It allows improved performance through connection pooling, because all application users connect with the same Oracle credentials, which is a requirement for connection pooling. From an auditing perspective, connecting through a common user ID is less than optimal, because all database actions are recorded as being made by the user defined in the connection string, not the actual user. Enjoying the performance advantages of connection pooling means that you have to allow anonymity. Within the database, you won't be able to discern the identity of the actual user.

Proxy authentication adds a Proxy User Id and the Proxy Password to the connection string. In other words, two user IDs are passed, one for the actual user and one for the pooled user (the proxy user). This feature allows you to maintain one midtier connection pool while keeping the ability to audit the activities of users through their User Id . It's important to notice a subtle difference in how the User Id and Password are used, depending on whether or not proxy authentication is used. When proxy authentication is used, the pooled user credentials are passed by use of the Proxy User Id and Proxy Password instead of the User Id and Password . For example:

                               
Dim oradb  
                              
As String = "Data Source=OraDb;User Id=ActualUser;Password=secret;
                              

    Proxy User Id=scott;Proxy Password=tiger;"  
                              
' VB.NET

                               
string oradb = "Data Source=OraDb;User Id=ActualUser;Password=secret;
                              

    Proxy User Id=scott;Proxy Password=tiger; ";  
                              
// C#
                            

In an actual application, you wouldn't hard-code the actual user ID and password. Instead, you'd provide text boxes where the user enters a user ID and a password, which would be passed into the connection string. When a user is presented with a dialog box for entering a user ID name and password on a Windows form or a Web page (which is called a Web form in Visual Studio), Microsoft refers to this as forms authentication.

In the database, proxy authentication creates a second session, known as a lightweight session, which is used to make the actual user known to the database. For this to work, the database administrator must explicitly grant the proxy user permission to create the lightweight connection. Here you will use SCOTT as our proxy (pooled) user.

                               
alter user ActualUser  
                              
grant connect through scott;
                            

You have the option of having the connection string include or exclude the actual user's password. If the actual user is used without the password being supplied, the connection will succeed. For the actual user to be authenticated, you must use the Password connection string attribute. If an invalid password is provided for a user, authentication will fail.

The proxy connection is used for the database activities invoked on behalf of the user. When the proxy connection is returned to the pool, the lightweight session is destroyed.

New to Oracle Database 10g Release 2 is the client identifier. Using a client identifier is similar to using proxy authentication without a Password connection string attribute. One key difference is that using a client identifier does not create a second session in the database. Another difference is that any string can be used as the client identifier. It does not have to correspond to the name of a database user. To set the client identifier, assign a string value to the ClientId property of the connection object after the connection is opened:

conn.ClientId = "SomeUser"   
                              
' VB.NET

conn.ClientId = "SomeUser";  
                              
// C#
                            

This assignment sets the value of CLIENT_IDENTIFIER for the user's session. If the application is using forms authentication, the user ID entered by the user can be used to set the value of ClientId . Because ClientId is part of USERENV , it can be used with Oracle Virtual Private Database to restrict access, even if the user is not an actual user defined in ALL_USERS. ClientId can also be used to enhance scalability. For example, a Web application can keep the database connection open when a user is done and reset ClientId to the next user.

When you log on to Windows, your Windows user is known to the operating system and can be obtained within .NET applications by use of the Windows.Security.Principal class. The code for doing this and setting ClientId to the Windows user is as follows:

                               
Dim user  
                              
As New WindowsPrincipal(WindowsIdentity.GetCurrent()) 
conn.ClientId = user.Identity.Name  
                              
' VB.NET

WindowsPrincipal user  
                              
= new WindowsPrincipal(WindowsIdentity.GetCurrent());
conn.ClientId = user.Identity.Name;  
                              
// C#
                            

With this technique, the Windows user can be made known to the Oracle database. The Windows user identity can also be passed to the database via the User Id connection string attribute when proxy authentication is used without the Password connection string attribute. In this case, as described previously, the User Id connection string attribute is used only to identify, not authenticate, the actual user.

Windows Authentication

This article has discussed using Oracle user IDs to authenticate users to the database. It is also possible to use the Windows operating system to authenticate users to Oracle , allowing single sign-on capabilities. I covered this in detail in Implementing Windows Authentication from Oracle , in Windows IT Pro magazine. Using Windows authentication requires a modification to the connection string:

"Data Source=ORCL10g;User Id=/;"

The slash (/) tells Oracle that Windows authentication will be used. The Password connection string attribute is removed, because it is used only when making an Oracle database connection. If Password is left in the connection string when Windows authentication is used, it is ignored.

With Windows authentication, either the Windows user must belong to a privileged Windows group such as ORA_DBA on the Oracle server or external authentication must be enabled. External authentication is not recommended, because it is less secure than access through group membership.

Understanding SQL Injection Attacks

So far this article has showed you how to keep track of who is accessing the database. As valuable as this is, it is not as important as keeping people from harming the contents of the database. One of the biggest threats to a database application is SQL injection, which happens when a malevolent user injects SQL commands into the application through unsecured code. SQL injection vulnerabilities are a consequence of constructing SQL statements from user inputs, not from using any particular vendor's products. All SQL databases from all vendors are vulnerable if programming safeguards are not followed.

Consider a simple application that counts the number of employees in a specified job category:

Examine the code used to construct the database command string:

cmd.CommandText = "select count(ename) from emp where " _
                + "job = '" + TextBox1.Text + "'"  
                              
' VB.NET

cmd.CommandText = "select count(ename) from emp where " 
                + "job = '" + TextBox1.Text + "'";  
                              
// C#
                            

If the user enters CLERK as shown above, the command text received by the database is this:

                               
select count(ename)  
                              
from emp  
                              
where job =  
                              
'CLERK'
                            

Everything works just fine as long as the user doesn't change the logic. Allowing the SQL string to be built at runtime based on user inputs passes the power to alter the SQL logic to the user. But suppose the user decides not to enter CLERK and enters this instead:

' or 1=1 --

This fundamentally changes the logic of the query to:

                               
select count(ename)  
                              
from emp  
                              
where job =  
                              
''  
                              
or 1=1  
                              
--'
                            

The application code always appends a trailing single quote. The inline comment causes the SQL parser to ignore the trailing single quote. Without this trick of using an inline comment, the modified SQL would have this invalid syntax:

                               
select count(ename)  
                              
from emp  
                              
where job =  
                              
''  
                              
or 1=1  
                              
'
                            

The SQL syntax as modified by the user causes every row in the table to be counted. Whereas where job = '' by itself would cause the count to be zero, the or 1=1 causes every row to be counted.

In the previous example, SQL injection does nothing more than cause the user to get an answer different from what the application designers intended. Now consider the following application window used to authenticate users:

Here is the code used to process the login credentials:

cmd.CommandText = "select user_role from app_login where " _
                + "user_id = '" + TextBox1.Text + "' and " _
                + "password = '" + TextBox2.Text + "'"  
                              
' VB.NET

cmd.CommandText = "select user_role from app_login where " 
                + "user_id = '" + TextBox1.Text + "' and " 
                + "password = '" + TextBox2.Text + "'";  
                              
// C#
                            

Assume a malevolent user enters the following:

admin' or 1=1 --

The resulting SQL is this:

                               
select user_role  
                              
from app_login
                               
where user_id =  
                              
'admin'  
                              
or 1=1  
                              
--' and password=''
                            

Once again, the inline comment plays an important rule in defeating the intended query logic. The -- inline comment causes the remainder of the query string to be treated as a comment, which makes the password unnecessary. This resulting query string has a where clause that is always true, which enables the user to log in as an administrator without providing a password. Depending on how the basic query string is structured, it might even be possible to log in to some applications without providing either a user ID or a password.

Preventing SQL Injection Attacks

No matter how hard you attempt to validate user input, malevolent users can be quite clever. You won't be able to identify all SQL injection attempts through input validation. The root of the problem is not that the user is entering SQL syntax against your wishes but that the user's input is treated as SQL syntax instead of as just a string of characters.

Treating the input as a string parameter means that the user's input is processed no longer as part of a SQL statement but as just a string of characters being passed as a value to the SQL query. Using an OracleParameter object renders the nefarious input harmless, because it is interpreted like this:

                               
select user_role  
                              
from app_login
                               
where user_id =  
                              
'admin' or 1=1 --'  
                              
and password =  
                              
''
                            

The double dash does not get treated as an inline comment but, rather, is treated just as a text string. User input doesn't become part of the syntax of the SQL query that is executed.

To create a parameterized query, modify the query string as follows:

cmd.CommandText = "select user_role from app_login where " _
                + "user_id = :user_id and password = :password" '  
                              
VB.NET

cmd.CommandText = "select user_role from app_login where " 
                + "user_id = :user_id and password = :password";  
                              
// C#
                            

Next instantiate OracleParameter objects and add them to the OracleParameters collection.

                               
Dim p1  
                              
As New OracleParameter("dname", OracleDbType.Varchar2)  
                              
' VB.NET
p1.Value = TextBox1.Text
cmd.Parameters.Add(p1)

                               
Dim p2  
                              
As New OracleParameter("loc", OracleDbType.Varchar2)
p2.Direction = ParameterDirection.Input   
                              
' optional property
p2.Size = 13   
                              
' optional property
p2.Value = TextBox2.Text
cmd.Parameters.Add(p2) 

OracleParameter p1  
                              
= new OracleParameter("dname", OracleDbType.Varchar2);
p1.Value = textBox1.Text;  
                              
// C#
cmd.Parameters.Add(p1);

OracleParameter p2  
                              
= new OracleParameter("loc", OracleDbType.Varchar2);
p2.Direction = ParameterDirection.Input;  
                              
' optional
p2.Size = 13;  
                              
' optional
p2.Value = textBox2.Text;
cmd.Parameters.Add(p2);  
                            

There are several constructors for OracleParameter and multiple overloads on Parameters.Add . Furthermore, there are various properties that you can or must set, depending on exactly what your query is. Passing parameters to stored procedures follows the same coding pattern detailed above.

Lab 1: Displaying the Database User   1. Begin by adding a reference to Oracle.DataAccess. See Build a .NET Application on the Oracle Database if you need detailed instructions. 2. Add a button control and a label control to the Windows form. 3. Add code to retrieve data from the Oracle database, and display the results on the form. Put the code into a click event handler for the button. The easiest way to get started with this task is to double-click on the button, because that operation will create a stub for the event handler. 4. Add VB.NET Imports statements before the Public Class declaration or C# using statements before the namespace declaration. (Note: By using the downloadable code samples, you can do all the lab exercises without actually typing any code.)   Imports System.Data ' VB.NET Imports Oracle.DataAccess.Client ' ODP.NET Oracle managed provider using System.Data; // C# using Oracle.DataAccess.Client; // ODP.NET Oracle managed provider   5. This example assumes that you have a tnsnames.ora alias of ORCL10g. If you are not using tnsnames.ora, you must modify the connection string. My previous article shows a connection string that does not rely on tnsnames.ora. Add the VB.NET version of the click event handler code between the Private Sub and End Sub statements.   Dim oradb As String = "Data Source=ORCL10g;User Id=scott;Password=tiger;" Dim conn As New OracleConnection(oradb) ' VB.NET conn.Open() Dim cmd As New OracleCommand cmd.Connection = conn cmd.CommandText = "select user from user_users" cmd.CommandType = CommandType.Text Dim dr As OracleDataReader = cmd.ExecuteReader() dr.Read() label1.Text = dr.Item("user") ' or dr.Item(0) conn.Dispose()   Add the following C# code to the click event handler between the { and } curly braces for the button's click event handler.   string oradb = "Data Source=ORCL10g;User Id=scott;Password=tiger;"; OracleConnection conn = new OracleConnection(oradb); // C# conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "select user from user_users"; cmd.CommandType = CommandType.Text; OracleDataReader dr = cmd.ExecuteReader(); dr.Read(); label1.Text = dr.GetString(0); conn.Dispose();   6. Run the application, and click on the button. You should see the following:   Lab 2: Adding Proxy Authentication   Now that you can verify which user the Oracle database server recognizes you as, the next step is to add proxy authentication to your connection string. 1. Create a new user to serve as the actual user when logged in as SCOTT in the database server.   create user ActualUser identified by secret; grant connect to ActualUser; alter user ActualUser grant connect through scott;   2. Modify the connection string to include ActualUser and use proxy authentication:   Dim oradb As String = "Data Source=ORCL10g;" _ + "User Id=ActualUser;Password=secret;" _ + "Proxy User Id=scott;Proxy Password=tiger;" ' VB string oradb = "Data Source=ORCL10g;" + "User Id=ActualUser;Password=secret;" + "Proxy User Id=scott;Proxy Password=tiger;"; // C#   3. Run the application, and click on the button. You should see the following:   4. Remove Password=secret from your connection string, and run the application again. Click on the button. You will see the same result you saw in the previous step, even though you did not provide a password. That is because you are not authenticating your actual user-you are just passing the actual user's name to the database.   5. Add Password=wrongsecret to your connection string, and run the application again. The application will fail with a runtime error, because authentication for the actual user failed. Lab 3: Using the Client Identifier 1. Modify the connection string from Lab 2 as follows:   Dim oradb As String = "Data Source=ORCL10g;" _ + "User Id=scott;Password=tiger;" ' VB string oradb = "Data Source=ORCL10g;" + "User Id=scott;Password=tiger;"; // C#   2. Find the connection's Open method, and set the ClientId property on the line below it:   conn.Open() ' VB conn.ClientId = "SomeUser" ' add this conn.Open(); // C# conn.ClientId = "SomeUser"; ' add this   3. Modify the CommandText property as follows:   cmd.CommandText = "SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') FROM DUAL" ' VB cmd.CommandText = "SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') FROM DUAL"; // C#   4. Modify the DataReader's Item property as follows:   Label1.Text = dr.Item(0) ' VB label1.Text = dr.GetString(0); // C#   5. Run the application, and click on the button. You should see the following:     Lab 4: Using Windows.Identity.Principal 1. Modify the code for Lab 3, by adding an Imports or using statement:   Imports System.Security.Principal ' VB using System.Security.Principal; // C#   2. Instantiate a WindowsPrincipal object, and use it to set the ClientId property:   Dim user As New WindowsPrincipal(WindowsIdentity.GetCurrent()) conn.ClientId = user.Identity.Name ' VB WindowsPrincipal user = new WindowsPrincipal(WindowsIdentity.GetCurrent()); conn.ClientId = user.Identity.Name; // C#   3. Run the application, and click on the button. You should see something similar to the following:     Understand that on the test machine, I was logged on to the ORAWIN domain as Windows user Winuser . You will see your domain name or machine name and your Windows user name. Lab 5: Parameterized Queries 1. Modify the form from a previous lab, by adding two labels and two text boxes. The result should look something like this:   2. Modify the code that creates the query string to accept user inputs:     cmd.CommandText = "select count(deptno) from dept where " _ + "dname = '" + TextBox1.Text + "' and " _ + "loc = '" + TextBox2.Text + "'" ' VB cmd.CommandText = "select count(deptno) from dept where " _ + "dname = '" + textBox1.Text + "' and " _ + "loc = '" + textBox2.Text + "'"; // C#   Instead of creating and populating a new table, we'll use the DEPT table and pretend the DNAME column contains user IDs and that the LOC column contains passwords. 3. Run the application, and enter the following valid input: Enter SALES for User Id. Enter CHICAGO for Password. You will see a count of 1 displayed on the form. This is analogous to a valid login. 3. Run the application, and enter the following invalid input: SALES, for User Id. INVALID, for Password. You will see a count of 0 displayed on the form. This is analogous to a failed login. 4. Run the application. Enter the following invalid input in the first text box:   SALES' and 1=1 --   This input is equivalent to entering a valid user ID without a password. Because the input is invalid, a count of 0 should be displayed, but it is not. Instead, a count of 1 is displayed, which indicates that a user can authenticate to the application without supplying a password. 5. Modify the query string to use parameters:   cmd.CommandText = "select count(deptno) from dept where " _ + "dname = :dname and loc = :loc" ' VB cmd.CommandText = "select count(deptno) from dept where " _ + "dname = :dname and loc = :loc"; // C#   6. Add code to bind input values to the parameters in the query:   Dim p1 As New OracleParameter("dname", OracleDbType.Varchar2) p1.Value = TextBox1.Text cmd.Parameters.Add(p1) Dim p2 As New OracleParameter("loc", OracleDbType.Varchar2) p2.Direction = ParameterDirection.Input ' optional p2.Size = 13 ' optional p2.Value = TextBox2.Text cmd.Parameters.Add(p2) ' VB.NET OracleParameter p1 = new OracleParameter("dname", OracleDbType.Varchar2); p1.Value = textBox1.Text; cmd.Parameters.Add(p1); OracleParameter p2 = new OracleParameter("loc", OracleDbType.Varchar2); p2.Direction = ParameterDirection.Input; ' optional p2.Size = 13; ' optional p2.Value = textBox2.Text; cmd.Parameters.Add(p2); // C#   7. If you are using C#, make the following change:   label1.Text = dr.GetOracleDecimal(0).ToString(); // C#   8. Run the application, and enter the following invalid input in the first text box:   SALES' and 1=1 --   This time a count of 0 is displayed, which indicates that the input text was processed just as a series of letters in a varchar2 variable instead of as part of a SQL query string.