Securing a .NET Application on the Oracle Database by John Paul Cook
With the ever increasing interest in security, companies are taking a more mature look at security beyond the simple basics of denying unwanted access. International, national, state, and provincial laws and regulations are driving a more detailed and granular approach to security. Protecting databases from unauthorized access is no longer enough. Maintaining audit trails showing which users have performed what actions in a database is also necessary.
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
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 .
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.
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.
John Paul Cook ( email@example.com ) is a database and .NET consultant based in Houston. He is the author of numerous articles about .NET, Oracle, and other topics and has been developing relational database applications since 1986. His current interests include security and IT governance, Visual Studio 2005, and Oracle 10g. He is an Oracle certified DBA and a Microsoft MCSD for .NET.