Use Oracle's built-in security features for PHP

Most WEB applications today need to adopt at least some basic security policy. For example, a Web site that provides password-protected content, only Web sites with admin backend, blogs and personal magazines, E-commerce sites, intranet, and so on.

The most common design method for building these types of Web applications is to consolidate the security policy into the business logic of the Web application, whereby the application determines whether a user has access to a data in the database. In this case, the role of the database is only to store data and to provide data on request. In other words, if the WEB Application command database provides specific information, the database executes the command directly without checking the user's permissions.

In this article, you will learn how to implement application security rules at the database level with Oracle's built-in security features to improve the overall security of your application. As a spin-off benefit, implementing data access security directly in the database not only helps to improve the security of the application, but also helps reduce complexity.

Requirements for database-side security

What happens when you control data access from a WEB application? In most cases there is no problem; This is a good solution, especially when the data involved is not mission critical or top-secret. This approach is used in a number of books and online resources. In fact, a popular php/mysql book explicitly opposes the creation of more than one database user account per application because "additional users or complex permissions can reduce MySQL execution speed by checking for more information before an operation continues." That's true, but there are a few things you might consider before you give up the idea of consolidating security into database logic. Let's look at the following example.

Suppose you create a Content management system (CMS). It uses a database to store content published on a Web site. Most of the data is public, allowing anonymous Web users to read, but only editing change data. Use a single database account to access and modify records in the database and control security with PHP code by using password to protect access to pages that only administrators can access.

If the public side of the Web application suffers from an attack of SQL injection on a public search form (that is, a form that is not tightly coded), the intruder may be able to execute arbitrary SQL statements on the database objects that the public account can access. Of course, in this case, the execution of the SELECT statement does not cause any major problems, because the data is inherently public. However, because common permissions and administrative permissions use the same database account, intruders can also execute UPDATE and DELETE statements, or even delete tables from the database.

How can we prevent this from happening? The easiest way to do this is to completely limit the permissions on the public database account to modify data. Let's take a look at how Oracle solves this problem.

Basic overview of Oracle Security

Oracle databases provide WEB developers with many ways to control data access, from managing access to specific database objects, such as tables, views, and procedures, to data that controls individual rows or columns. Obviously, the discussion of each security feature or available option for Oracle is beyond the scope of this article. Here, we will not cover much detail, but only the most basic aspects of Oracle data Access security: Authentication and user accounts, permissions, roles

Authentication and user accounts. As with other databases, each user requesting access to Oracle (the database account) must pass validation. Validation work can be done by a database, an operating system, or a network service. In addition to Basic authentication (password validation), Oracle also supports strong validation mechanisms such as Kerberos, CyberSafe, RADIUS, and so on.

Role. An Oracle role is a well-known set of permissions. Although you can grant user account permissions directly, using roles can greatly simplify user management, especially when you need to manage a large number of users. It is highly efficient to create manageable small roles and then grant users one or more roles based on the user's security level. Not to mention how easy it is to modify permissions-simply modify the roles associated with the role, without modifying each user account.

To simplify the initial creation of a new user, Oracle has three predefined roles:

Connect role-This role enables users to connect to the database and perform basic operations, such as creating their own tables. By default, this role cannot access other users ' tables.

The RESOURCE role-resource role is similar to the CONNECT role, but it allows users to have more system privileges, such as creating triggers or stored procedures.

DBA role-allows users to have all system permissions.

Authorization and permissions in use

In this section, we will discuss how to use Oracle's authorization and permissions to improve the security of the simple CMS example discussed at the beginning of this article. Assume that the content that is provided to the application user is stored in the Web_content table.

First, create the table. Start the Oracle database Special Edition and log in as a system administrator. If the sample HR user has not been released, release it. Follow the instructions in the introductory guide that came with the special edition installation. Note that by default, the HR user is assigned the RESOURCE role. Here, give the user the DBA role, so that the account can be used to manage the database aspects of the CMS application. Of course, the HR user account is not used for online access, and it is used only to manage the database.

You can now create a new table by using the Object Browser or by executing the SQL Commands window. The following is the code that creates the table:

CREATE TABLE web_content (page_id number PRIMARY KEY, page_content VARCHAR2 (255));

Because the table was created using an HR user account, the table is owned by the HR account and is in HR mode, and cannot be accessed by other users until the permissions for the table are explicitly granted to another user. If not, you can create a new user and use that user to access the Web_content table to try.

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 the user will be used as a database account to provide content to the anonymous WEB user. The Cms_editor account will have more permissions on the table and will be used as the account for the CMS edit (the account needs to change and maintain the data in that table).

You can create a new user by using an XE graphical interface or by executing the following command:

CREATE USER Cms_user identified by Cms_user; CREATE USER Cms_editor identified by Cms_editor;

(For simplification purposes, the password here corresponds to the user name.) ）

In order for both accounts to log on to the database, we need to give them a CONNECT role. To do this, select the CONNECT check box under User information in the Administration/database users section of the XE graphical interface, or execute the following command:

GRANT CONNECT to Cms_user; GRANT CONNECT to Cms_editor;

Now, if you try to log in as a cms_user or cms_editor user and attempt to read from the Web_content table (SELECT * from Hr.web_content;), you will encounter the following error:

Ora-00942:table or view does not exist

To access the data or just see the table, you need to grant the Cms_user and Cms_editor accounts read-only access to the Web_content table:

GRANT SELECT on hr.web_content to Cms_user; GRANT SELECT on hr.web_content to Cms_editor;

The above code enables the two accounts to execute a SELECT statement on the Web_content table. If you try to execute another statement, you will encounter an error. For example, insert a row:

INSERT into Hr.web_content (page_id,page_content) VALUES (1, ' Hello World ');

An error message will be generated:

Ora-01031:insufficient privileges;

To allow Cms_editor to change the contents of this table, you need to grant the following permissions:

GRANT insert,update,delete on hr.web_content to Cms_editor;

From now on, the Cms_editor account can execute INSERT, UPDATE, and DELETE statements on the Web_content table.

You see, how simple it is! It can be seen that managing permissions through roles is a more efficient approach. If you are using an Oracle database that is not XE, you can do the following:

To create a role:

CREATE role Reader; CREATE role writer;

To grant role permissions:

GRANT SELECT on web_content to reader; GRANT Insert,update,delete on web_content to writer;

To give user 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 change the definition of the READER role, those changes affect all user accounts that have that role. If you are granting permissions directly to a user, you must update each user account individually.

After you complete the preceding steps, you can configure your PHP application to use the Cms_user account for all database connections that are requested by anonymous Web users, and use the Cms_editor account for connections raised by password-protected administration pages. Now, even if a public Web form is attacked, the impact on the database will be minimal because the Cms_user account has only read-only access.

Conclusions

In this article, we simply introduce some of the most basic features of Oracle data access security. In addition, Oracle has many other features that can increase the security of your WEB application to a new level-including virtual private database (VPD) and label security. For database security issues with PHP background, see my OTN article, "Add data security to your PHP application."