DeployPHP Series, Part 2: Bringing Data Security to PHP Applications
by Mikhail Seliverstov

How to build and deploy lightweight PHP front-end applications without compromising the security of your data.

Download for this article: Sample code and listings Oracle Database 10g Oracle Instant Client Oracle JDeveloper PHP Extension

Review complete DeployPHP index

Using PHP to build a Web-based, database-driven application is an increasingly popular way to give trusted users easy access to corporate data. However, the PHP developers must acknowledge that linking sensitive and often mission critical data to an application accessible outside the corporate network is a serious security risk.

At Department of Alumni Relations of McGill University, we've chosen to combine Oracle's industrial-strength security with PHP's ease of development to ensure data availability without incurring the risk of compromising the security of the entire database.

In this installment of the DeployPHP series, I'll highlight the major steps in building and deploying such a solution and walk you through the code of a sample application.

Data Access Authorization: Front End versus Back End

Your goal here is to create a lightweight front-end application that will allow users with various security privileges to seamlessly gain access to an appropriate amount of data. At the same time, you need to make sure the data access policies are not violated, even when the security of the PHP front end is breached.

Therefore, implementing data access authorization in the front-end application is inappropriate, because anyone who manages to bypass the application's security will have unrestricted access to all the data in the database. This includes not only people intentionally trying to break through the application security but also valid users who can run ad hoc queries or have access to reporting tools such as Infomaker or Crystal Reports.

To better understand this problem, take a look at the two scenarios illustrated in Figure 1:

Figure 1. Application-based versus database-based data authorization

In Scenario 1, the application authenticates the user and determines the user's rights. In this case, the database is not part of the authorization process and simply serves the data to any application that provides valid credentials. However, in the second scenario, the database, not the application, determines how much data can be served to the user, depending on this person's identity. Here, the application only authenticates the user and passes this information to the database server that enforces security policies and returns a record set tailored individually for each user. Therefore, to minimize the risk of unwanted data exposure, the security policies should be attached directly to the data rather than being enforced through a front-end application.

Fortunately, in Oracle8i and later, Oracle databases offer just the right set of features: Oracle Virtual Private Database (VPD) and Secure Application Context, which provide fine-grained access control. VPD provides developers with the ability to control access to individual rows of data and ensures that two users with different security privileges will never receive the same set of data, even when they are directly accessing the very same table or view. Application context allows for creation of custom session variables that may be used to uniquely identify each user session.

The application this article discusses relies on Oracle VPD and application context to ensure that the database server is in charge of enforcing the security requirements of our system and to make sure each user receives exactly the number of rows allowed for that person by the security policy, regardless of how the data is being accessed.

Deployment Strategy

Before a discussion of the application requirements and implementation details, here is an outline of the most important system requirements. For building the sample system, the following will be required:

• Oracle Database Enterprise Edition, version 9.2 or later
• A Web server with PHP installed

The physical architecture of the system may vary and can be as simple as a single server hosting the Web server, the PHP engine, and the Oracle database.

Because the PHP front-end application is intended merely for presenting the output to the end user and most of the security is handled by the database, the requirements for the Web server's operating system, Web server software, and PHP version are not critical.

The sample application discussed in this article was built on the following configuration:

Web server

• Debian Linux
• Apache v. 1.3
• PHP v 4.1

• Windows 2000
• Oracle9i Database Enterprise Edition

The second part of the article takes a look at a fictitious organization—XYZ Corporation—which is building a secure Web-enabled front-end application to interface with its central database. It discusses the application's functional and security requirements; outlines its logical architecture; walks through the implementation process, including the PHP and Oracle parts; and discusses how both parts work together in a single security model.

Problem Outline

XYZ Corporation is a midsize organization that sells computer software and hardware to other businesses. The company uses an Oracle database to keep track of its day-to-day operations and, in particular, to maintain sales records.

Assume that XYZ's sales team is widely scattered. To have an accurate picture of sales activity, the company decides to build a Web-based sales report that will list all transactions that have taken place to date. Due to the internal security policy, the sales records have to be filtered according to the user's department and position. The overall security requirements can be summarized as follows:

• The sales director can view all sales transactions.
• Department managers can view the transactions of their subordinates.
• Sales representatives are limited to viewing only their own sales.
• Other employees have no access to the sales records.

To fulfill these requirements, XYZ decides to implement a VPD security solution to make sure that the above policy is enforced, regardless of how users access the data. In this case, the data authorization is performed by the database, which helps reduce the complexity of the front-end application and improve system's overall security. Because the role of the front end is now, in fact, reduced to rendering the output and simple session management, XYZ decides in favor of an easy-to-learn, lightweight scripting language—PHP—rather than a full-blown framework, such as J2EE or .NET.

Solution Outline

The high-level logical architecture of the proposed system is presented in Figure 2:

Figure 2: Conceptual architecture of PHP—Oracle Sales Report application

The system consists of two major parts: the PHP Web application and the Oracle back end. When a user initially tries to access the system, the application verifies whether this user has already logged in (has a valid user session) and prompts for credentials, if needed. If the credentials are valid, the back end returns the user's unique ID number to the Login script, which, in turn, starts a new user session and attaches the user's ID to that session as a session variable. At this point, the user may access the sales report, which retrieves data by connecting to the database, establishing the connection context, and executing a SELECT SQL query. The data access authorization is performed by the security policy attached to the Sales Transactions table. The policy dynamically modifies the original query, by generating the predicate (the WHERE clause) that automatically gets attached to the original SQL statement. The exact form of the predicate depends on the user's identity specified in the context. As a result, even though all application users execute the very same SELECT query and use the same database connection parameters, each user receives a unique set of rows.

Implementing Oracle back-end functionality.

The application relies on the data structure shown in Figure 3.

Figure 3. Data structure

The data structure consists of four tables. The EMPLOYEE and DEPARTMENT tables represent XYZ's organizational structure; the relationship between the tables signifies that each employee belongs to a department and each department has a manager. The list of sales transactions resides in the TRANSACTION table, and that's the table that needs to be protected by the security policy. Finally, the list of application users (that is, the users of the PHP front-end application) is presented in the LOGIN table and derives from the list of employees in the EMPLOYEE table. Listing 1 in the sample code download contains the code for setting up the tables and inserting the data. The tables are created in the SALES schema.

One of the most important steps of implementing the system is setting up the VPD security policy that will protect the data in the TRANSACTION table. This is covered in the next section.

Setting up the VPD.

Setting up a VPD solution for our sample system involves the following steps:

• Creating a dedicated user account for managing security policies and granting it appropriate privileges to limit the number of users able to modify security settings
• Creating context space—reserving a secure memory location designated for storing Oracle's session variables
• Creating a procedure to initialize application context
• Creating a security policy function
• Creating a security policy by assigning the policy function to the table that needs to be protected

You create security policies by using the functionality of the DBMS_RLS package. To make sure regular database users cannot tamper with security policies, you will create a dedicated user account (SEC_MANAGER) and grant it the privileges to execute functions and procedures in DBMS_RLS:

GRANT EXECUTE ON DBMS_RLS TO SEC_MANAGER;

SEC_MANAGER also requires the ability to set up the application context:

GRANT CREATE ANY CONTEXT TO SEC_MANAGER;

Because all users of the front-end PHP application will reuse the same Oracle user account (SALES) to access the database, you will have to rely on Oracle's application context to keep track of users' identity. To do that, you need to create the context space (TEST_SECURITY):

CREATE CONTEXT TEST_SECURITY USING sec_manager.pkg_sec_public

and create a procedure that assigns session specific context variables. Finally you need to grant the SALES user account the rights to execute this procedure. (See Listing 2 for the code that creates the SET_CONTEXT procedure within the PKG_SEC_PUBLIC package and grants the SALES user the right to execute the procedures in this package [GRANT EXECUTE ON PKG_SEC_PUBLIC TO SALES].) For better manageability, the permissions also can be granted through roles. For example, you can create an EMPLOYEE_ROLE and assign this role to any new database user account if, at some point, the application will require more than one account.

The next step is to create the policy function that enforces the rules outlined in the security requirements. In our case, the logic behind the policy function is quite simple:

• If the context is invalid or not set, the function should return the predicate that would force the VPD to hide all sales transactions.
• If the context is valid and the user is the sales director, the function should allow that user to view all sales transactions.
• If the user is the sales manager of either the hardware or software department, the function should return the predicate that filters the sales transactions by department ID (... WHERE DEP_ID = department id).
• Finally, if the user is a sales representative, the function should filter the rows by this user's ID (... WHERE EMP_ID = user id).

The policy function resides in the PKG_SEC_POLICY package and is available to all users (GRANT EXECUTE ON PKG_SEC_POLICY TO SALES. (The complete code of the policy function (FN_SELECT) can be found in Listing 3, in the sample code download.) To simplify the code, you will also create a helper function that identifies whether a given user is a manager and returns that person's department ID. The code for the helper function (FN_MANAGER) is presented in Listing 4, and the contents of the SALES schema are shown in Figure 4.

Please note that the above scenario is just one of the many ways of implementing given security requirements. The structure and the logic of the policy function are totally user-defined and can be as simple or as complex as needed.

Figure 4. SALES schema

Finally, to enable dynamic filtering of sales transaction records, you have to create the security policy, by attaching the FN_SELECT function to the TRANSACTION table. This involves calling the ADD_POLICY procedure of the DBMS_RLS package:

BEGIN
dbms_rls.add_policy( 'sec_manager', 'transaction', 'transaction_select',
  'sec_manager', 'pkg_sec_policy.fn_select', 'select');)
END;

Now, the policy is set to dynamically modify all incoming SELECT SQL queries involving the TRANSACTION table by attaching the predicate returned by the FN_SELECT function. Importantly, the security policies are not limited to SELECT statements and can be used for monitoring INSERT, UPDATE, and DELETE queries as well. It is also possible to apply the same policy to more than one table, to apply multiple policies to the same table, and to reuse one policy function across multiple policies. In addition to tables, the policies can be also attached to views.

Implementing PHP front-end functionality.

The purpose of the PHP front-end application is to authenticate users, render the output, and provide for simple session management. This can be achieved with the following set of scripts:

• connect.inc.php—stores connection parameters and provides for database connection on demand
• login_form.php and login.inc.php—the form for collecting user credentials and the script that performs authentication and starts new user sessions
• authorize.inc.php—verifies that the user has logged in (that is, has a valid session)
• transaction_list.php—retrieves the sales transaction list from the database and renders the results as an HTML table
• demo.css—a simple stylesheet for controlling the look of the interface

The suffix inc indicates that the script is supposed to be included and executed from within some other script, rather than on its own. This is not a PHP requirement but merely an unofficial convention that may help distinguish between different scripts. Note, however, that in a real-life scenario, you might want to place the "includes" in a special directory (cgi-bin, for instance) that is not directly accessible to Web users.

How the Application Works.

Now let's take a closer look at each of these scripts and how they work together. The normal flow of the application is presented in Figure 5:

Figure 5. Front-end application flow

To view the sales transactions report, the user has to call transaction_list.php, which first executes the authorize.inc.php script to make sure the user is logged in and authenticated by the system:

require("authorize.inc.php");

The job of authorize.inc.php is to check that the user's session includes a session variable called "SESSION_TOKEN" and to verify that the content of this variable is valid. For simplicity, the script does not perform any special checks, except to verify that the length of the variable is greater than zero. It is strongly recommended to filter the content of the "SESSION_TOKEN" variable before using it in any validation routine. If the variable is missing or not initialized, the script will redirect the user to the login form:

if (!strlen($_SESSION["SESSION_TOKEN"]) > 0) {
header("Location: login_form.php?return_url=".$strReturnURL."&error_msg=".urlencode($strErrorMessage));
exit
}

The login script does not provide for extensive validation of user input, except for making sure that both the login name and the password are supplied. Yet again, rigorous filtering of the user's input is strongly suggested, to immunize the application against cross-site scripting or SQL injection attacks.

Once the input is validated, the script opens a database connection, by calling the GetConnection() function of connect.inc.php, and attempts to obtain the user's ID by executing the following query:

SELECT emp_id,login FROM LOGIN 
WHERE login='login_name' 
AND password='password';

If the above query produces no results, the login script presents the user with an "Invalid user name or password" error message and prompts for new credentials. Otherwise, the script starts a new PHP session and assigns the user ID received from the LOGIN table as the contents of the "SESSION_TOKEN" variable. The user ID is the key component for initializing the VPD policy attached to the TRANSACTION table; therefore, having this information as a clear-text session variable is not suitable for a real-life system. There are many ways to protect the contents of the "SESSION_TOKEN" variable and to minimize the risk of session hijacking. These may include encryption; hashing; or adding more parameters, such as an expiration timestamp, encryption salt, or even a binding token payload to some part of users' IP addresses. However, discussing all these measures is beyond the scope of this article.

The last thing to note about how the application handles the sessions is that it requires users to enable cookies in their browsers, which, however, you can avoid by carrying session IDs over GET or POST requests.

Presenting the Data.

Finally, after the login succeeds and authorize.inc.php lets the user through, the control is in hands of the transaction_list.php script. The first thing transaction_list.php does is opens a new database connection:

$rsConnection = GetConnection($_SESSION["SESSION_TOKEN"]);

Once the connection is created, the script executes this SQL query:

SELECT T_ID,T_AMOUNT,EMP_NAME,EMP_POSITION,DEP_NAME
FROM TRANSACTION, EMPLOYEE, DEPARTMENT
WHERE TRANSACTION.EMP_ID = EMPLOYEE.EMP_ID
AND EMPLOYEE.DEP_ID = DEPARTMENT.DEP_ID;

Benefits of the VPD and PHP Combination

Analyzing the code of this sample system makes it very easy to outline the advantages of a VPD-centric approach to building secure Web applications. As you can see, the transaction_list.php script has no code to distinguish between different users and, in fact, executes the very same SQL statement for any user who passes the login script. Similarly, the connection.inc.php script employs the same Oracle account for opening database connections. Nonetheless, the front-end application is aware of the user's identity (the user ID in the "SESSION_TOKEN" variable), but it merely passes it to the database server, thus making the database server, not the PHP application, responsible for enforcing the security policy attached to the TRANSACTION table. What will happen if you attempt to connect without providing the user's identity? The application will still create a valid Oracle connection, but in this case, the connection has no context attached, which forces the policy to hide all the data in the TRANSACTION table. In other words, for any such connection (made either through the PHP front end or directly to the database), the TRANSACTION table will appear to have no data whatsoever.

To summarize, relaying the data authorization work from the front-end application to the database server presents the following benefits:

• Faster development of front-end applications and reports.
• Improved security—the security of data is no longer dependent solely on the security of the front-end application.
• Reduced maintenance—security policy updating is done centrally in the database without individual updates of each and every front-end application.

Test Drive

Now, when both the back- and front-end parts of the application are in place, you can implement a few test cases to make sure the system functions as dictated by the security requirements. The TRANSACTION table currently contains the following rows:

• Transaction 1 by Peter, Software Department
• Transaction 2 by Alan, Software Department
• Transaction 3 by Andy, Hardware Department

The table below lists five cases, as well as expected behavior in each case and the actual results.

Case #	User's name and position	Login name	Expected output	Actual result
1	Bob Sales Director	bob	Records 1, 2, and 3	See Figure 6
2	John Sales Manager Software Department	john	Records 1 and 2	See Figure 7
3	Alan Sales Representative Software Department	alan	Record 2	See Figure 8
4	Tony Sales Manager Hardware Department	tony	Record 3	See Figure 9
5	Dave Director's Assistant	dave	No records	See Figure 10

Conclusion

The purpose of the sample application discussed in this article is to demonstrate the concepts and the advantages of using PHP in conjunction with Oracle for implementing an easy-to-develop front end to VPD protected data. It is important to understand that the sample system was simplified to fit the format of the article. Although fully functional, it lacks certain features of an enterprise-ready application, such as filtering and validation of input, an adequate protection mechanism for PHP session data, encryption, and so on. Nonetheless, this application can be used as a good starting point for building real-life systems, ones that can provide a lightweight, yet secure interface to mission-critical and confidential data.