Using Oracle's Built-in Security Features with PHP
by Mikhail Seliverstov
How to take advantage of Oracle's native security features to improve the security of your PHP Web applications.
Published March 2006
The majority of today's Web applications need to implement at least some basic kind of data security policy. The examples include the Web sites offering password-protected content, the sites with admin-only back-end, blogs and personal journals, e-commerce sites, corporate intranets, and so on.The most common and popular design approach for building these types of Web applications would be to incorporate the security policy in the business logic of the Web application—that is, to let the application decide if a particular user should have access to a particular piece of data in the database. In this case, the role of the database is reduced simply to storing the data and serving it on request. In other words, if the Web application commands the database to serve certain information, the database would do that without questioning user's authority. In this recipe you can learn how to take advantage of Oracle's built-in security features to enforce the application security rules at the database level, in order to enhance the overall security of your applications. As an added bonus, implementing data access security directly in the database will help not only to increase the application's security but also reduce its complexity.
The Need for Database-side Security
What's wrong with controlling data access from the Web application? In most cases, nothing; it's a good solution, especially if the data in question are not mission critical or top secret. This approach is widely adopted and taught by many books and online resources. In fact, one of the popular PHP/MySQL books explicitly advises against creating more than one database user account per application, as "extra users or complex privileges slow down MySQL since there's more information to check before an operation can proceed". That's true; however, there are a few things you may want to consider before dismissing the idea of incorporating security into your database logic. Let's look at the following example.Imagine you are creating a content management system (CMS). You are using the database to store the content published on your site. Most of the data is public and available for reading to anonymous Web users; however, only the editors should be able to change the data. You are using a single database account to access and modify the records in the database and control security in the PHP code by protecting access to admin-only pages with the password. If the public side of your Web application is compromised—for example, by a SQL injection attack on a public search form (a poorly coded form, that is)— the intruder may be able to execute arbitrary SQL statements against whatever database objects the public account has access to. Of course, executing SELECT statements would not be a big issue in your case, since the data is public anyway. However, as the same database account is being used for public and administrative access, the intruder will also be able to issue UPDATE and DELETE statements or even to drop tables from the database. What can you do to prevent this from happening? The easiest solution would be to restrict the public database account from being able to modify the data at all. Let's take a look at what Oracles has to offer to remedy the situation.
Basic Oracle Security Overview
Oracle database offers Web developers many ways to control data access starting from managing access to particular database objects (such as tables, views, and procedures) all the way to controlling access to individual rows and columns of data. Discussing every security feature or an option available in Oracle is obviously beyond the scope of this recipe. Here, without getting into many details, we will only touch on the most fundamental aspects of Oracle data access security:
Grants and Privileges In Action
In this section we will discuss how to use Oracles grants and privileges to improve the security of a simple CMS example discussed in the beginning of this recipe. Assume that the content delivered to users of your application is stored in the WEB_CONTENT table.First you have to create this table. Start your Oracle XE database and login as System Administrator. If you haven't unlocked the sample HR user, please do so. Follow the instructions in the Getting Started Guide included with the XE installation. Notice that by default the HR user is assigned to the RESOURCE role. Also assign this user to the DBA role, so you can use this account to manage the database aspects of our CMS application. Of course, you will not use HR user account for online access, only to manage the database. Now you can create a new table by either using the object browser or by executing the SQL Commands window. Here is the code to create the table:
CREATE TABLE WEB_CONTENT ( page_id NUMBER PRIMARY KEY, page_content VARCHAR2(255) );As you have created the table as HR user, the table is owned by the HR account (and resides in the HR schema) and no other user currently has access to it unless you explicitly grant it. You can test this by creating a new user and trying to access the WEB_CONTENT table. Now create two new users, CMS_USER and CMS_EDITOR. Eventually, CMS_USER will be granted read-only access to the WEB_CONTENT table and will be used as the database account to deliver content to anonymous Web users. The CMS_EDITOR account will have broader privileges on the table and will be used as the account for the CMS editors who need to alter and maintain the data in the table. You can create new users by using XE graphic interface or by executing the following commands:
CREATE USER cms_user IDENTIFIED BY cms_user; CREATE USER cms_editor IDENTIFIED BY cms_editor;(For simplicity the passwords match the user names.) In order to enable both accounts to log in into the database we need to assign them to the CONNECT role. You can do it by checking the CONNECT checkbox under the user profile in the Administration/Database Users section of XE graphic interface or by issuing these commands:
GRANT CONNECT to cms_user; GRANT CONNECT to cms_editor;Now if you try to log in as either CMS_USER or CMS_EDITOR and attempt to read data from the WEB_CONTENT table (select * from hr.web_content;) you'll get the following error:
ORA-00942: table or view does not existIn order to access the data or even just to see the table, you need to grant read-only access to the WEB_CONTENT table to both CMS_USER and CMS_EDITOR accounts:
GRANT SELECT on hr.web_content to cms_user; GRANT SELECT on hr.web_content to cms_editor;The code above allows both accounts to execute SELECT statements against the WEB_CONTENT table. If you try to execute any other statement you will get the error. For example, inserting a row:
INSERT INTO hr.web_content (page_id,page_content) VALUES (1,'hello world');will produce the error message
ORA-01031: insufficient privilegesTo allow CMS_EDITOR to alter the content of the table, this privilege needs to be granted (don�t forget to re-log in as HR):
GRANT INSERT,UPDATE,DELETE on hr.web_content to cms_editor;From now on the CMS_EDITOR account is allowed to execute INSERT, UPDATE, and DELETE statements against the WEB_CONTENT table. You can easily review the current user privileges by using one of the XE built-in security reports (Utilities - > Object Reports -> Security - > User Privileges -> Object Grants). As you can see this was rather easy; the more efficient approach would be to handle grants through roles. If you are running a version of Oracle other than XE, you can do the following: Create roles:
CREATE ROLE reader; CREATE ROLE writer;Grant privileges to roles:
GRANT SELECT ON web_content TO reader; GRANT INSERT,UPDATE,DELETE ON web_content TO writer;Assign users to the roles:
GRANT reader TO cms_user; GRANT reader TO cms_editor; (they need to read too) GRANT writer TO cms_editor;Note that if you ever needed to alter the definition of the READER role, the changes would be propagated to all user accounts granted this role. If you decided to grant privileges directly to users you would have to update each user account individually. Once the steps above are completed you can configure your PHP application to use CMS_USER account for all database connections requested by anonymous Web users and to use CMS_EDITOR account for the connections originating from password-protected admin pages. Now, even if the public Web form gets compromised the impact on the database will be minimal, since the CMS_USER account is restricted to read-only access.
In this recipe we only touched on the most basic features of Oracle data access security. Fortunately, Oracle has many more features that can take the security of your Web applications to a new level—including Virtual Private Database (VPD) and Label Security. For more information about the former in the context of PHP, see my OTN article, " Bringing Data Security to PHP Applications."
Mikhail Seliverstov is a Web Programming Team Leader for the office of Development and Alumni Relations at McGill University in Montreal. For the past three years, he has helped lead a large Oracle+PHP development project as a principal application architect and developer.